PL/SQL Quick Reference - Oracle

Basic Block Structure¶

Variables¶

Declaration Syntax¶

Scalar Data Types¶

Assignment¶

Retrieving Data into Variables¶

SELECT ColumnNameList
INTO { variable_name [, variable_name] . . . | record_name }
FROM table
WHERE condition

• Host variables must be prefixed with a colon
• Must return exactly one row. Use explicit cursors for multi-row queries
• Trap exceptions NO_DATA_FOUND and TOO_MANY_ROWS in the exception handling block
  

Bind Variables¶

Output¶

SET SERVEROUTPUT ON
DBMS_OUTPUT.PUT_LINE('Monthly salary = ' || TO_CHAR(v_sal)); 

Control Structures¶

IF ''condition'' THEN
   ''statements'';
[ELSIF ''condition'' THEN // NOTE SPELLING OF KEYWORD !!!
   ''statements'';]
[ELSE
   ''statements'';]
END IF;

LOOP
   statement1;
   . . .
   EXIT [WHEN condition];
END LOOP;

FOR ''counter'' in [REVERSE] ''lower_bound''..''upper_bound'' LOOP
   ''statements'';
END LOOP

• Keyword REVERSE causes the counter to decrement. In this usage, the lower_bound is still referenced first
• Do not declare counter; it is declared implicitly as an integer
  

WHILE condition LOOP
   statements;
END LOOP

PL/SQL Records¶

TYPE type_name IS RECORD
   (field_declaration [, field_declaration] . . .);
identifier type_name;

field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE}
   [[NOT NULL] {:= | DEFAULT | expr]

. . .
TYPE emp_record_type IS RECORD
   (ename VARCHAR2(10), // these column names should appear
   job VARCHAR2(9),     // in the same order as in the database
   sal NUMBER(7,2));
emp_record emp_record_type;
. . .

PL/SQL Tables¶

Declaration Syntax¶

TYPE type_name IS TABLE OF
   { column_type | variable%TYPE | table.column%TYPE }
   [NOT NULL] [INDEX BY BINARY_INTEGER];
identifier type_name;

TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;

• PL/SQL Tables can have one column and a primary key, neither of which can be named.
• The primary key is not the same as a database primary key, but a unique identifier.
• It doesn't have to be contiguous. There can be gaps in the sequence.
  

Referencing Syntax¶

pl/sql_table_name(primary_key_value)

ename_table(3) . . .

PL/SQL Table Methods¶

table_name.method_name[ (parameters) ]

Exception Handling¶

Syntax¶

EXCEPTION
   WHEN exception1 [OR exception2 . . . ] THEN
     statements;
   [WHEN exception3 [OR exception3 . . .] THEN
     statements;]
   [WHEN OTHERS THEN
     statements;]

Predefined Exceptions¶

• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX
  

Functions Used Within Exception Handling¶

• SQLCODE - numeric value for the exception
• SQLERRM - message associated with the exception
  

User-Defined Errors¶

DECLARE
   e_emps_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT (e_emps_remaining, -2292)
  . . .
EXCEPTION
   WHEN e_emps_remaining THEN
     . . .
END;

PRAGMA EXCEPTION_INIT Statement¶

PROCEDURE xxx()
IS

    PRAGMA  EXCEPTION_INIT(BAD_CUSTOMER_ACCOUNT, -20001)

BEGIN

    RAISE_APPLICATION_ERROR(BAD_CUSTOMER_ACCOUNT,  'Customer account' || cust_account_in || ' is invalid');

EXCEPTION

    WHEN BAD_CUSTOMER_ACCOUNT
        DBMS_OUTPUT.PUT_LINE(SQLERRM);   // log the error message
        RAISE;                           // re-raise the exception for VB 

END;

Raising Errors¶

• Do not have to define a special OUT parameter for the error message.
• ROLLBACK is automatically called for you.
• The error comes back as a VB exception that can be handled with a normal error handler.
  

raise_application_error(error_number, message [, use_stack ] );

RAISE_APPLICATION_ERROR(-20001, 'Customer account' || cust_account_in || ' is invalid')'

See Also¶