Introduction to PL/SQL

1. Introduction to PL/SQL Francis Thottungal

2. The outline • The basic PL/SQL code structure is : • DECLARE -- optional, which declares and define variables, cursors and user-defined exceptions. • BEGIN -- mandatory • - SQL statements • - PL/SQL statements • EXCEPTION -- optional, which specifies what actions to take when error occurs. • END; -- mandatory

3. Hello World DECLAREv1  Varchar2(50);BEGIN   v1 := ‘Hello World’;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END;

4. SQL Plus Usage At command prompt: C:>SQL SET SERVEROUTPUT ON DECLAREv1  Varchar2(50);BEGIN   v1 := ‘Hello World’;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END; / Note that a PL/SQL block is terminated by a slash / or a line byitself.

5. EXAMPLE-2 DECLAREv1  NUMBER(3);BEGIN   v1 := 3;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END;

6. Handling Variables • Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such  array type VARRAY. • Only TRUE and FALSE can be assigned to BOOLEAN type of variables. • AND, OR, NOT operators can be used to connect BOOLEAN values. • % TYPE attribute can be used to define a variable which is of type the same as a database column's type definition. • Users can customize the variable types by using TYPE ... IS ... statement.

7. The following code block illustrates the use of TYPE..IS... and VARRAY. In this sample, a type v_arr is defined as an variable array of maximum 25 elements which are of type NUMBER(3).  Then a variable v1 is defined as type v_arr.    This sample code also demonstrates the use of %TYPE attribute. DECLARETYPE v_arr IS VARRAY(25) of NUMBER(3); v1 v_arr; v_empnoemployee.empno%TYPE;BEGIN     v1(2) := 3;    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2));  v_empno  := 4; END;

8. Programming Guidelines • Single-line comments are prefixed with two dashes --. • Multiple-line comments can be enclosed with the symbols /* and */. • Variables and function identifiers can contain up to 30 characters, and should not have the same name as a database column name. • Identifiers must begin with an alphanumerical character. • SQL functions can be used in PL/SQL. • Code blocks can be nested and unqualified variables can locally scoped. • It is recommended that variable names are prefixed by v_, and parameter names in procedures/functions are prefixed by _p.

9. DECLAREv_salemployee.sal%TYPE;BEGIN     INSERT INTO employee VALUES (6, 'TOM LEE', 10000);     UPDATE employee SET sal = sal + 5000 WHERE empno = 6; •       SELECT sal INTO v_sal FROM employee WHERE empno = 6;     DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);      DELETE FROM employee WHERE empno = 6;       COMMIT;END;/

10. A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers. • Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.

11. DECLARE CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%'; BEGIN FOR rec_ac IN csr_ac ('LE')LOOP   DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal); END LOOP ; CLOSE csr_ac; END; /