Procedural Language Structured Query Language PL/SQL (II) Eng ...

1 downloads 250 Views 703KB Size Report
Nov 30, 2014 - Faculty of Engineering. Computer Engineering Dept. Database Lab (ECOM 4113). Lab 8. Procedural Language.
Islamic University of Gaza Faculty of Engineering Computer Engineering Dept. Database Lab (ECOM 4113)

Lab 8

Procedural Language Structured Query Language PL/SQL (II)

Eng. Mohammed Alokshiya

November 30, 2014

SQL Cursor A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. The syntax of cursor is:

Cursor Syntax CURSOR C IS SELECT_STATEMENT;

Controlling Cursors

Example SET SERVEROUTPUT ON; DECLARE CURSOR EMP_CURSOR IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE; LNAME EMPLOYEES.LAST_NAME%TYPE; BEGIN OPEN EMP_CURSOR; FETCH EMP_CURSOR INTO EMPNO, LNAME; DBMS_OUTPUT.PUT_LINE(EMPNO || ' ' || LNAME); END;

2

The previous code will print the first record returned form the query. You can use loop to print all records:

Fetching All Records DECLARE CURSOR EMP_CURSOR IS SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE; LNAME EMPLOYEES.LAST_NAME%TYPE; BEGIN OPEN EMP_CURSOR; LOOP FETCH EMP_CURSOR INTO EMPNO, LNAME; EXIT WHEN EMP_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(EMPNO || ' ' || LNAME); END LOOP; END;

You should close the cursor when the loop terminate.

Closing Cursor BEGIN OPEN EMP_CURSOR; ... FETCH ... ... CLOSE EMP_CURSOR; END;

3

Note that you can process the rows of the active set by fetching values into a PL/SQL record:

Using ROWTYPE Attribute SET SERVEROUTPUT ON; DECLARE CURSOR EMP_CURSOR IS SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; EMP_RECORD EMP_CURSOR%ROWTYPE; BEGIN OPEN EMP_CURSOR; LOOP FETCH EMP_CURSOR INTO EMP_RECORD; EXIT WHEN EMP_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(EMP_RECORD.FIRST_NAME || ' ' || EMP_RECORD.LAST_NAME); END LOOP; CLOSE EMP_CURSOR; END;

Cursor’s FOR Loops

Cursor’s FOR Loops DECLARE CURSOR EMP_CURSOR IS SELECT EMPLOYEE_FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; BEGIN FOR EMP_RECORD IN EMP_CURSOR LOOP DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPLOYEE_FIRST_NAME || ' ' || EMP_RECORD.LAST_NAME); END LOOP; END;

Explicit Cursor Attributes Attributes that Obtain status information about a cursor. The following table illustrates explicit cursors you can use with cursors:

Attribute Type Description %ISOPEN Boolean Evaluates to TRUE if the cursor is open %NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row %FOUND Boolean Complement of %NOTFOUND %ROWCOUNT Number Evaluates to the total number of rows returned so far

4

Using Cursor’s Attributes DECLARE CURSOR EMP_CURSOR IS SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; EMP_RECORD EMP_CURSOR%ROWTYPE; BEGIN OPEN EMP_CURSOR; IF EMP_CURSOR%ISOPEN THEN LOOP FETCH EMP_CURSOR INTO EMP_RECORD; EXIT WHEN EMP_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(EMP_CURSOR%ROWCOUNT); DBMS_OUTPUT.PUT_LINE(EMP_RECORD.FIRST_NAME || ' ' || EMP_RECORD.LAST_NAME); END LOOP; END IF; CLOSE EMP_CURSOR; END;

OUTPUT anonymous block completed 1 Den Raphaely 2 Alexander Khoo 3 Shelli Baida 4 Sigal Tobias 5 Guy Himuro 6 Karen Colmenares

5

Procedures A procedure is a type of subprogram that performs an action. It can be stored in the database as a schema object. The syntax of procedure is:

Procedure Syntax CREATE [OR REPLACE] PROCEDURE PROCEDURE_NAME [(PARAMETER1 [MODE] DATATYPE1, PARAMETER2 [MODE] DATATYPE2, ...)] IS|AS [LOCAL_VARIABLE_DECLARATIONS; ...] BEGIN ... END [PROCEDURE_NAME];

 PARAMETER1 represents the name of a parameter.  The mode option defines how a parameter is used: IN (default), OUT, or IN OUT.  DATATYPE1 specifies the parameter data type, WITHOUT any precision.

Example:

Raise Salary Procedure CREATE OR REPLACE PROCEDURE RAISE_SALARY (ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, PERCENT IN NUMBER) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + (SALARY * PERCENT / 100) WHERE EMPLOYEE_ID = ID; END RAISE_SALARY;

6

You can execute it from another procedure or anonymous block:

Executing a Procedure DECLARE BEGIN RAISE_SALARY (114, 10); END;

To check the effects of executing procedure, check the salary before and after execution.

Executing a Procedure DECLARE SAL EMPLOYEES.SALARY%TYPE; BEGIN SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID = 114; SYS.DBMS_OUTPUT.PUT_LINE('SALARY BEFORE EXECUTING PROCEDURE: ' || SAL); RAISE_SALARY (114, 10); SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID = 114; SYS.DBMS_OUTPUT.PUT_LINE('SALARY AFTER EXECUTING PROCEDURE: ' || SAL); END;

Example: Using Out Mode

QUERY_EMP Procedure CREATE OR REPLACE PROCEDURE QUERY_EMP (ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, NAME OUT EMPLOYEES.LAST_NAME%TYPE, SALARY OUT EMPLOYEES.SALARY%TYPE) IS BEGIN SELECT LAST_NAME, SALARY INTO NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = ID; END QUERY_EMP;

Executing a Procedure DECLARE EMP_NAME EMPLOYEES.LAST_NAME%TYPE; EMP_SAL EMPLOYEES.SALARY%TYPE; BEGIN QUERY_EMP(171, EMP_NAME, EMP_SAL); DBMS_OUTPUT.PUT_LINE(EMP_NAME); DBMS_OUTPUT.PUT_LINE(EMP_SAL); END;

Note that you have to pass variable to “Name” and “Salary” attributes. You cannot pass static values to them.

7

Functions A function is a named PL/SQL block that returns a value. It can be stored in the database as a schema object for repeated execution. It is called as part of an expression or is used to provide a parameter value

Function Syntax CREATE [OR REPLACE] FUNCTION FUNCTION_NAME [(PARAMETER1 DATATYPE1, PARAMETER2 DATATYPE2, ...)] RETURN DATATYPE IS|AS [LOCAL_VARIABLE_DECLARATIONS; ...] BEGIN ... RETURN EXPRESSOIN; END [FUNCTION_NAME];

Example:

FORMAT_NUM Function CREATE OR REPLACE FUNCTION FORMAT_NUM (NUM VARCHAR2) RETURN VARCHAR2 IS TEMP VARCHAR2(12) := ''; BEGIN TEMP := SUBSTR(NUM, 1, 3) || '.' || SUBSTR(NUM, 4, 3) || '.' || SUBSTR(NUM, 7, 4); RETURN TEMP; END FORMAT_NUM;

Then use it as a build-in function:

Using Functions in Select Statement SELECT FORMAT_NUM('1234567890') FROM DUAL;

8

Viewing Procedures/Functions in the Data Dictionary Information for PL/SQL procedures is saved in the following data dictionary views:  USER_SOURCE: to view the subprograms that you own.  ALL_SOURCE: to view the procedures that are owned by others who have granted you the EXECUTE privilege.

View All Sources SELECT * FROM USER_SOURCE;

To view the code for a specific procedure/function, select TEXT Column only, and specify the name of procedure/function in where clause

View Specific Procedure/Function Code SELECT TEXT FROM USER_SOURCE WHERE NAME = 'QUERY_EMP';

9