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