PL/SQL

Oracle's Procedural SQL

Usually PL/SQL programs are written within other applications (such as forms or reports).
  1. Variable Declaration
    variable_name data_type;

  2. Reference Variable declarations inherit their data type from a referenced database field.
    variable_name table_name.field_name%TYPE;

  3. Scalar Data Types

  4. PL/SQL Program Structure
    DECLARE
    Variable Declarations
    BEGIN
    Program Statements
    EXCEPTION
    Error-handling Statements
    END;

  5. Comments
    Multiline comments are delimited with /* ... */

    Single line comments begin with two hyphens --

  6. Operators
    + - * / and **
    =, <> or !=, >, <, >=, <= assignment is :=
    string concatenation is ||

  7. Activate Memory Buffer
    SQL> SET SERVEROUPUT ON SIZE buffer_size; (typical = 4000)
    This command must be executed to activate the internal buffer that displays program output.

  8. Output Data
    DBMS_OUTPUT.PUT_LINE('output_string');
    You can only write one line at a time and can display only character data.

  9. / will execute PL/SQL in SQL*PLUS

  10. Data Type Conversion Functions
    TO_DATE(date_string, date_format_string);
    TO_NUMBER(number_string);
    TO_CHAR(date_type, date/time_format_string);

  11. Character String Functions
    RTRIM(string)
    LENGTH(string)
    UPPER(string), LOWER(string)
    start_location = INSTR(string_to_search, string_searched_for);
    sub_string = SUBSTR(string, location, number_of_chars);

  12. IF-THEN-ELSE-END IF
    IF condition THEN
    statements
    ELSE
    statements
    END IF;

  13. LOOPS
    Loop
    statements IF condition
    EXIT; END IF;
    statements END LOOP;

    LOOP statements
    EXIT WHEN condition;     END LOOP;

    WHILE condition;
    LOOP
    statements END LOOP;

    FOR counter
    IN start_val .. end_val
    LOOP
    statements END LOOP;

  14. Cursors
    A cursor is a pointer that references the context area which contains command information such as the number of rows processed and the active set (the set of data rows returned by the query).

    Cursor Attributes
    Implicit Cursors are created automatically for each INSERT, UPDATE, DELETE and SELECT command.
    The command may return only one record to use an implicit cursor.

    SELECT field_names
    INTO declared_variable_names
    FROM table_names
    WHERE join_condition
    AND record_selection_condition_for_exactly_one_record;

    Explicit cursors must be declared and can be used with queries that return any number of records.
    DECLARE
    variable declarations
    CURSOR cursor_name IS
    Select_statement;
     BEGIN OPEN cursor_name;

    LOOP
    FETCH cursor_name INTO
    variable_name(s);
    EXIT WHEN cursor_name%NOTFOUND;     END LOOP

    CLOSE cursor_name;     END;
    /
    DECLARE
    variable declarations
    CURSOR cursor_name IS
    Select_statement;
    %ROWTYPE declaration     BEGIN FOR variable_name(s) in cursor_name LOOP
    statements END LOOP;
     END;
    /
    DECLARE
    variable declarations
    CURSOR cursor_name IS
    SELECT field_names
    FROM table_name
    WHERE record_selection_condition
    FOR UPDATE;
    row_variable cursor_name%ROWTYPE     BEGIN FOR variable_name(s) in cursor_name LOOP
    UPDATE table_name
    SET field_name = value
    WHERE CURRENT OF cursor_name;     END LOOP;
     END;
    /
    NOTE: WHERE CURRENT OF is a reference to the current row.

  15. Tables are indexed arrays of data from a field in a table.
    First define the appropriate user-defined data subtype.
    Then declare the table itself.

    TYPE user-defined_data_subtype_name IS TABLE OF item_data_type
    INDEX BY BINARY_INTEGER;
    table_name user-defined_data_subtype_name;

    Insert data into a table with an ordinary array assignment.
    table_variable_name(index) := value;

    Example:
    DECLARE
    TYPE state_code_table IS TABLE OF VARCHAR2(2);
    state_codes state_code_table;

    state_codes(1) := 'AK';

  16. Table of Records
    A table of records stores one or more entire records from a table in memory for fast access; especially useful for lookups involving small tables.
    Define a table of records using the following syntax.

    TYPE table_data_type_name
    IS TABLE OF database_table_name%ROWTYPE
    INDEX BY BINARY_INTEGER;

    To assign a value to a field of a record in a table of records use the following syntax.

    table_name(key_value).database_field_name := field_value;

  17. Error Handling in PL/SQL Programs.

    Code to handle run-time errors in a PL/SQL program is located in the EXCEPTION section of a PL/SQL program.

    PL/SQL Program Structure
    DECLARE
    Variable Declarations
    BEGIN
    Program Statements
    EXCEPTION
    Error-handling Statements
    END;





Top of this page   Top of page      Home page   Home page