Oracle PL/SQL Cheatsheet
| ; Semicolon. | Statement terminator |
| % Percent sign | Attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE). Also used as multibyte wildcard symbol, as in SQL. |
| _ Single underscore | Single-byte wildcard symbol, as in SQL |
| : Colon | Host variable indicator, such as :block.item in Oracle Forms |
| ** Double asterisk | Exponentiation operator |
| < > and != | Not equals" |
| || Double vertical bar | Concatenation operator |
| << and >> | Label delimiters |
| := | Assignment operator |
| => | Association operator for positional notation |
| -- | Double dash: single-line comment indicator |
| /* and */ | Beginning and ending multiline comment block delimiters |
| Database types NUMBER CHAR(N), VARCHAR2(N) DATE LONG LONG RAW ROWID MLSLABEL |
Definition Used to store any number Used for storing text Oracle system date Stores large blocks of text Stores large blocks of binary data Smaller binary data store Uesd for row identifier Security label |
| Non database types. | DEC, DECIMAL, REAL, DOUBLE-PRECISION, INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC, BINARY-INTEGER, CHARACTER, VARCHAR, BOOLEAN, TABLE, RECORD |
| Procedure | A non-formal function that can accept paremeters via value or reference. Similar in form to a function. |
| Function | A classical function that returns one value. Usually contains declaration, execution and exception sections. |
| Package | A library, consisting of a specification with function/prototype signatures, and a body with actual code. eg |
| Trigger | Code attached to a table that fires on certian conditions. |
| Variable Declaration | DECLARE employee-id employee.empid%TYPE, pi CONSTANT number := 3.14, ratio REAL,.. BEGIN.. |
| Executable Section | .. BEGIN select * into my_employee where employee.emid = 42; END; .. |
| Exception Handler. | END; EXCEPTIONS .. END; |
| Specification | PACKAGE package_name IS [ declarations of variables and types ] [ specifications of cursors ] [ specifications of modules ] END [ package_name ]; |
| Body | PACKAGE BODY package_name IS [ declarations of variables and types ] [ specification and SELECT statement of cursors ] [ specification and body of modules ] [ BEGIN executable statements ] [ EXCEPTION exception handlers ] END [ package_name ]; |
| General SQL*Plus script | |
| Testing script | |
| Stored procedure | |
| Stored function | |
| Stored package body | |
| Stored package specification |
| %NOTFOUND | True if fetch did not return row. |
| %ROWCOUNT | Number of rows processed by this cursor |
| %FOUND | Opposite of %NOTFOUND |
| %ISOPEN | If currently open for processing then true. |
| Same Options as SQL | COMMIT, ROLLBACK, SAVEPOINT |
| Transaction begins at execution of first change of data. | Rollbacks go to last COMMIT or SAVE_POINT |
| DBMS_TRANSACTION | A package with functions for transaction control. |
| Predefined | Relates to an oracle error. No need to invoke. Just catch. EXCEPTION WHEN NO_DATA_FOUN THEN DBMS_OUTPUT.PUT_LINE('No data found'); |
| User defined. | Need to be declared, tested and handled in their respective blocks. DECLARE My_salary_null EXCEPTION; .. EBGIN.. IF my_emp_record.salary IS NULL THEN RAISE my_salary_null; END IF; EXCEPTION.. WHEN my_salary_null THEN DBMS_OUTPUT.PUT_LINE('Salary column was null for employee'); END |
| Pragmas. | Associate a predefined error with a exception handler. eg to have my_salary_null catch Oracle error -1400 DECLARE PRAGMA EXCEPTION INIT(my_salary_null, -1400); |
| IF..THEN..ELSE..ENDIF; | As usual. |
| LOOP .. IF (condition) THEN EXIT END IF; .. END LOOP: |
Equivalent to if (conition) then break; |
| WHILE cond LOOP..END LOOP; | while () {}; |
| FOR var IN n..m LOOP .. END LOOP; | for thing in range(n,m) {} |
| EXECUTE function_name; | Function call FUNCTION name (parameter type,..) ..body.. END: |
| Cursor for. | Opens cursor, loops across until %NOTFOUND. FOR variables IN cursor LOOP..END LOOP; |
| Implict cursor named by developer. | Think of it as a select statement that has a name. |
| Implict cusror is called SQL | IF SQL%NOTFOUND THEN .. |
| Declaring an explicit cursor. | DECLARE CURSOR employee_crsr IS SELECT empid, salary FROM employee BEGIN .. |
| Executing a cursor | OPEN employee_cursor LOOP FETCH employee_cursor INTO my_empid, my_salary; EXIT WHEN employee_crsr%NOTFOUND; ..do stuff.. ENDLOOP; |
| FETCH | Obtains next record from cursor.Can fetch into individual variables (as above) or a RECORD. |
| Declaring an explicit cursor using a record. | TYPE t_emp IS RECORD (T_Salary number, t_empid number); my_emprec t_emp; CURSOR employee_crsr IS SELECT empid, salary FROM employee; |
| Executing explicit cursror using record. | OPEN employee_cursr; LOOP FETCH emloyee_crsr INTO my_emprec EXIT WHEN employee_crsr%NOTFOUND; IF my_emprec.t_empid .. |
| Cursor Parameters. | Declaring parameters to be used at OPEN time. DECLARE .. CURSOR employee_crsr(low_end VARCHAR2, high_end VARCHAR2) IS SELECT empid, salary FROM employee WHERE substr(lastname,1,1) BETWEEN UPPER(low_end) AND UPPER(high_end); |
| INVALID_CURSOR | Occurs when you attempt to close a cursor that has not been opened. |
| CURSOR_ALREADY_OPEN | Occurs when you attempt to open a cursor the second time |
| DUP_VAL_ON_INDEX | Unique or primary key constraint violation |
| TOO_MANY_ROWS | More than one row was opbtained by a single row subquery, or another context when Oracle was expecting one row. |
| ZERO_DIVIDE | An attempt to divide by zero. |
| ROWTYPE_MISMATCH | An attempt to FETCH a cursor into an incompatible variable type. |
| INVALID_NUMBER | An char type was referenced as a number. |
| OTHERS | Special catchall exception. |
| EXCEPTION_INIT | Tells the compiler to associate a particular error number with an identifier you have declared as an exception in your program. |
| RESTRICT_REFERENCES | Tells the compiler the purity level (freedom from side effects) of a packaged program. |
| SERIALLY_REUSABLE | Tells the PL/SQL runtime engine that package-level data should not persist between references to that data. See Chapter 25, Tuning PL/SQL Applications for more information. |
