PL/SQL Block Structure

PL/SQL Block Structure • DECLARE - Optional Variables, cursors, user-defined exceptions • BEGIN - Mandatory SQL Statements PL/SQL Statements • EXCEPTIONS - Optional Actions to perform when errors occur • END; - Mandatory

Anonymous Procedure Function [DECLARE] BEGIN --statements [EXCEPTION] END; PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements [EXCEPTION] END; Block Types

Handling Variables • Declare and initialize variables in the declaration section. • Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters. • View results through output variables.

Variable Declaration • Two variables can have the same name, provided they are in different block. • identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; • %TYPE Attribute • Declare a variable as a database table, column or previously declared variable.

Variable Declaration • %ROWTYPE • Declare a variable according to a collection of columns in a database table • Example • emp_record emp%ROWTYPE; • Assigning value • emp_record.emp_name = ‘DOUGLAS’;

Syntax • Delimiters • +, -, *, /, =, @, ||, <>, !=, --, /*, */, :=, ; • Identifiers • Can contain up to 30 characters. • Literals • Must be enclosed in single quotation marks. • Comments • Single-line comments: -- • Multi-line comments: /* … */

Nested Blocks and Variable Scope • Statements can be nested wherever an executable statement is allowed. • An exception section can contain nested blocks. • Scope • A block can look up to the enclosing block. • A block cannot look down to enclosed blocks.

Programming Guidelines • Code Convention • SQL Statements: Uppercase • PL/SQL Keywords: Uppercase • Datatypes: Uppercase • Identifiers and Parameters: Lowercase • Database Tables and Columns: Lowercase

Programming Guidelines • Name Convention • Variable: v_name • Constant: c_name • Cursor: name_cursor • Exception: e_name • Table: name_table • Record: name_record • Parameter: p_name • Global Variable: g_name

Programming Guidelines • Indenting • Indent each level of code DECLARE v_dept_no NUMBER(2); v_location VARCHAR2(13); BEGIN SELECT dept_no, loc INTO v_dept_no v_location FROM dept WHERE d_name = ‘SALES’ END;

SQL Statements in PL/SQL • SELECT • Can return on and only one row SELECT select_list INTO variable FROM table WHERE condition; • The variable can also be a record

SQL Statements in PL/SQL • SELECT • Exceptions • NO_DATA_FOUND • TOO_MANY_ROWS • Example SELECT emp_id, name INTO v_emp_id, v_name FROM emp WHERE dept_name = ‘SALES’;

SQL Statements in PL/SQL • INSERT INSERT INTO table (columns) VALUES (columns); • The order of the columns in the INSERT INTO statement must be the same as in the VALUES statement. • Example INSERT INTO emp (emp_no, emp_name) VALUES (emp_no_seq.NEXTVAL, ‘DOUGLAS’);

SQL Statements in PL/SQL • UPDATE UPDATE table SET sal = sal + v_sal_increase WHERE job = ‘ANALYST’; • Can effect zero or more rows in a single statement.

SQL Statements in PL/SQL • DELETE DELETE FROM table WHERE column = value; • Can effect zero or more rows in a single statement. • Example DELETE FROM emp WHERE emp_name = ‘DOUGLAS’;

Control Structures • IF Statement IF Statements ELSIF Statements ELSE Statements END IF;

Basic Loop LOOP Statements; EXIT [WHEN condition] END LOOP; • Example LOOP v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;

FOR Loop FOR counter in lower_bound .. upper_bound LOOP Statements; END LOOP; • Example FOR i IN 1 .. 10 LOOP v_counter := v_counter + 1; END LOOP;

WHILE Loop WHILE condition LOOP Statements; END LOOP; • Example WHILE v_counter <= 10 LOOP v_counter := v_counter + 1; END LOOP;

PL/SQL Record TYPE type_name IS RECORD (field_declaration, field_declaration); identifier type_name; • Example TYPE emp_record_type IS RECORD (emp_name VARCHAR2(10), sal NUMBER(7,2)); emp_record emp_record_type;

Cursors • Private work area • Implicit • Used by the Oracle Server to parse and execute SQL statements. • Explicit • Declared and named by the programmer. • Declared in the DECLARE section of the block structure.

Explicit Cursors • Controlling Cursors • Declare • Create a named SQL area • Open • Identify the active set • Fetch • Load the current row into variables • Empty • Test for existing rows • Close • Release the active set

Explicit Cursors • Declaring the Cursor CURSOR cursor_name IS select_statement; • Do not include the INTO clause in the cursor declaration • ORDER BY clause can be used if necessary • Example CURSOR emp_cursor IS SELECT emp_no, emp_name FROM emp;

Explicit Cursors • Opening the Cursor OPEN cursor_name; • executes the query and identify the active set • No rows, no exception • Always test if cursor is open before opening. IF NOT cursor_name%ISOPEN THEN OPEN cursor_name END IF;

Explicit Cursors • Fetching Data from the Cursor • FETCH cursor_name INTO variables; • A record can replace the variables. • Testing for rows IF cursor_name%NOTFOUND THEN Statements END IF;

Explicit Cursors • Closing the Cursor • CLOSE cursor_name; • Test for a closed cursor IF cursor_name%ISOPEN THEN CLOSE cursor_name; END IF;

Explicit Cursors • Attributes • %ISOPEN • Evaluates to TRUE if the cursor is open. • %NOTFOUND • Evaluates to TRUE if the most recent fetch does not return a row. • %FOUND • Evaluates to TRUE if the most recent retch does return a row. • %ROWCOUND • Evaluates to the total number of records returned so far.

Explicit Cursors • Cursor FOR loops FOR record_name IN cursor_name LOOP Statements END LOOP; • Example FOR emp_record IN emp_cursor LOOP Statements END LOOP; • Implicit open, fetch and close occur.

Explicit Cursors • Parameters CURSOR cursor_name (parameter_namedatatype) IS select_statement; • Example CURSOR emp_cursor (p_dept_no NUMBER) IS select_statement; OPEN emp_cursor(10); FOR emp_record IN emp_cursor(10) LOOP

Explicit Cursors • FOR UPDATE Clause SELECT … FROM FOR UPDATE [OF column_reference] [NOWAIT] • Explicit locking lets you deny access for the duration of a transaction • Locks the rows before the update or delete • Last clause in a select statement • Use only when necessary • NOWAIT will check if rows have already been locked

Explicit Cursors • WHERE CURRENT OF Clause WHERE CURRENT OF cursor; • Use cursors to update or delete the current row. • Include the FOR UPDATE clause in the cursor in the cursor query to lock the rows first • Use the WHERE CURRENT OF clause to reference the current row from an explicit cursor. • Example UPDATE emp SET sal = emp_record.sal *1.10 WHERE CURRENT OF sal_cursor;

Trapping Exceptions • Performed in the EXCEPTION section of the Block Structure EXCEPTION WHEN exception1 [OR exception2] THEN Statements WHEN exception3 [OR exception4] THEN Statements WHEN OTHERS THEN Statements

Trapping Exceptions • WHEN OTHERS is the last clause • Will trap unspecified exceptions • Only one WHEN OTHERS clause is allowed • Only one handler is processed before leaving the block • Exceptions can be defined in the DECLARE section and used in the EXCEPTION section

Predefined Exceptions • There are many predefined exceptions • Examples • NO_DATE_FOUND • TOO_MANY_ROWS • INVALID_CURSOR • ZERO_DIVIDE • DUP_VAL_ON_INDEX

Functions for Trapping Exceptions • SQLCODE • Returns the numeric value for the error code. • Can be assigned to a NUMBER variable. • SQLERRM • Returns the message associated with the error number. • Can be assigned to a VARCHAR2 variable.

PL/SQL • DBMS_OUTPUT.PUT_LINE • Will display information to the screen. • Must SET SERVEROUTPUT ON in SQL*Plus window. • Other DBMS_OUTPUT procedures • Check the manual • Slash (/) • PL/SQL block is terminated by a slash on a line by itself. • Used when creating blocks in SQL*Plus.

PL/SQL • COMMIT • Will save all changes to the database permanently. • ROLLBACK • Will remove all changes from the database back to the last COMMIT. • DDL (Data Definition Language) • Cannot be used in a PL/SQL block • Example • CREAT TABLE • DROP TABLE

PL/SQL • IS NULL • Checks if a value is null.

PL/SQL Block Structure