Tutorial Contents: Chapter 1: Introduction What is PL/SQL? PL/SQL Basic Syntax (Block) Nested PL/SQL Block Advantages Of PL/SQL Chapter 2: PL/SQL Variables & Data Types What are Variables? Scalar Data Types Reference Data Types LOB Data Types Constant Variables Scope Of Variables Chapter 3: Conditional Statements Conditional Statements ONLY IF STATEMENT IF – ELSE NESTED IF ELSIF CASE Statement Based On Variable CASE Statement Based on Condition Chapter 4: Iterative Statements Iteration Control Statements (LOOP) Simple Loop While Loop For Loop Chapter 5: GOTO Statements GOTO And LABELS Restrictions for using GOTO Chapter 6: Cursors Cursors Implicit Cursor Explicit Cursor Cursor using FOR Loop Parameterized Cursors Chapter 7: Error Handling What is an EXCEPTION? Pre-Defined Exceptions Unnamed system exceptions User Defined Exceptions Chapter 8: Record Types Record Type In PL/SQL Record Using %ROWTYPE Record Using General Syntax Chapter 9: Sub-Programs Stored Procedure PL/SQL Function Parameters In Procedures And Functions Subprogram Dependencies Chapter 10: Packages What is a Package Components Of A Package Package Syntax Package Example Scope of various objects in a package Advantages Of Packages Chapter 11: Collections What is a collection? Collections Methods VARRAYS Nested Tables Index By Tables or Associate Arrays Differences Among Collections Chapter 12: Triggers What is a Trigger ROW LEVEL TRIGGER STATEMENT LEVEL TRIGGER Trigger Execution Hierarchy Mutating Triggers Chapter 13: Some Useful Snippets What is PL/SQL? Before we start the tutorials we should know what SQL cannot do and why we should use PL/SQL instead of an SQL statement. SQL cannot be used to write loops, conditional statements, procedures, user defined functions, packages, triggers. SQL statements will take to much time to execute a bunch of SQL statements together, we can overcome this problem by writing PL/SQL programs. For instance if you want to update, delete or insert (DML operation) any bulk data (say lakhs of records) then you need to execute the SQL for lakhs of time which is tedious and time consuming! Instead if you use PL/SQL then you can achieve the same task in a very little amount of time and efficiently! Running lakhs of DML statements individually in real time is definitely not recommended because when you execute an SQL statement each and every statement will make a trip to the server where the database is residing and executes itself and returns the result set. Instead of this if we send the statement as a single unit in a PL/SQL block the statements are executed and the result set is sent as a single unit and hence the server performance will increase as well as your programs! NOTE: I am assuming the learner is well versed in Oracle SQL (basics atleast). CHAPTER 1: Introduction PL/SQL Basic Syntax (Block) A PL/SQL block is a smallest unit of program which includes set of SQL statements to perform a specific task. Basically a PL/SQL block consists of 3 sections. 1. DECLARATIVE SECTION (OPTIONAL) 2. EXECUTABLE SECTION (MANDATORY) 3. EXCEPTION OR ERROR HANDLING SECTION (OPTIONAL) A declarative section starts with a "DECLARE" keyword and is not mandatory. We use this section to declare variables, cursors, constants, records, collections, exceptions which are used in the executable section. An executable section starts with a "BEGIN" keyword and should end with an "END" keyword. This is mandatory section which contains the business logic to achieve the required task. Constructs like LOOPS, SQL Statements etc will be written here. An exception section starts with "EXCEPTION" keyword. This is not mandatory section. This section is used to capture any run time errors which are not captured explicitly. We use exception handlers to handle the errors which we will see in the EXCEPTIONS chapter. NOTE: As we are familiar, many programming languages will be having a print statement to print the values of the variables but in PL/SQL we have debugging statement named DBMS_OUTPUT.PUT_LINE where DBMS_OUTPUT is a package and PUT_LINE is a packaged subprogram which we will come to know in PACKAGES chapter. If you are using SQL * PLUS then at the beginning of the session we need to set the SPOOLING command known as SET SERVEROUTPUT ON; to see the debugging statement output. You can use any PL/SQL editor that are available in the market but there should be Oracle Database underlying. My database version at the time of writing was Oracle 11g R2. A simple PL/SQL block without DECLARE and EXCEPTION sections. BEGIN DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL TUTORIALS!'); END; OUTPUT: WELCOME TO PL/SQL TUTORIALS! A simple PL/SQL block with DECLARE BEGIN END sections but without EXCEPTION section. DECLARE myvar VARCHAR2(20) := 'NITHIN'; BEGIN DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL TUTORIALS! '||myvar); END; OUTPUT: WELCOME TO PL/SQL TUTORIALS! NITHIN A simple PL/SQL block with all three sections. DECLARE myvar VARCHAR2(20) := 'NITHIN'; BEGIN DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL TUTORIALS! '||myvar); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION RAISED HERE!'); END; NOTE: In the last example the "WHEN OTHERS THEN" is an inbuilt exception handler which we will study in the EXCEPTIONS chapter. Every statement should be ended with a semicolon. A basic PL/SQL block can be nested within another PL/SQL block. In PL/SQL the assignment operator used is ':='. Ex: a := 10; b := 20; c := 10+20; Nested PL/SQL Block: In the previous section we discussed about "Anonymous PL/SQL Block". In this section we are going to discuss Nested PL/SQL Block. As the name suggests a nested PL/SQL block is calling of one PL/SQL block from inside of another PL/SQL block. We can have any number of PL/SQL block inside another PL/SQL block to achieve a given task. Ex: DECLARE parent_var VARCHAR2(20):='FIRST BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(parent_var); DECLARE child_var VARCHAR2(20):='SECOND BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(child_var); DECLARE grand_child_var VARCHAR2(20):='THIRD BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(grand_child_var); END; END; END; OUTPUT: FIRST BLOCK SECOND BLOCK THIRD BLOCK If you observe properly I have written 2 PL/SQL blocks inside already existing block. One disadvantage of this is it becomes very tedious to understand and to identify the blocks! Hence for the better visibility and readability we can specify the names on top of DECLARE keyword. The name that we give can be anything and it doesn't hold any logic. Ex: <> DECLARE parent_var VARCHAR2(20):='FIRST BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(parent_var); <> DECLARE child_var VARCHAR2(20):='SECOND BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(child_var); <> DECLARE grand_child_var VARCHAR2(20):='THIRD BLOCK'; BEGIN DBMS_OUTPUT.PUT_LINE(grand_child_var); END GRAND_CHILD_BLOCK; END CHILD_BLOCK; END PARENT_BLOCK; Hence the above example PL/SQL Block is also known as "Named PL/SQL Block" because we have named the appropriate nested blocks. Advantages of PL/SQL: The advantages of PL/SQL are: Integration with SQL statements Code Re-usability Better Performance Full Portability I am not going to discuss these topics. I will leave this section to the learner. CHAPTER 2: PL/SQL Variables & Data Types What are variables? A variable is a placeholder or a temporary storage area which is used to manipulate the data at run time. We can change the variable data at any point of time during the program execution. Variables are always declared in the declarative section and can be used in executable or exception sections. During the declaration of variables in the declarative section we need to foresee what kind of data we have to store and accordingly we need to use the data type. Syntax to declare a variable: DECLARE ; -- -- END; Syntax to declare and assign a variable: DECLARE ; BEGIN := ; -- -- END; Syntax to declare and assign a variable at the same time: DECLARE := ; BEGIN -- -- END; Below are different ways to declare and assign a variable: DECLARE a NUMBER; b NUMBER:=2; c NUMBER DEFAULT:=3; d CONSTANT NUMBER:=4; e NUMBER(2); f NUMBER(2,1):=1.0; g BOOLEAN:=FALSE; BEGIN -- -- END; PL/SQL Data Types: Data type is nothing but the type of data the variable can hold in it. i.e., it describes the data that is stored in the variable. Data Types are classified into four categories: a. Scalar Data Types : Scalar types are nothing but the types that we use in SQL. These can also be used to define variables in PL/SQL also and can store one value at a time. b. Reference Data Types: We use these to make the variable refer to the same data type that is defined in the table. c. Composite Data Types: These are the data types that we use in advanced PL/SQL. d. LOB Data Types: We use this when we want to store large object data like images, videos etc. Scalar Data Types: VARCHAR2 -> Variable length character string CHAR -> Fixed length character length NUMBER -> Floating point, integer number DATE -> Date and Time Ex: DECLARE emp_name VARCHAR2(30) :='&name'; emp_numb NUMBER(4); join_date DATE; BEGIN -- END; Here, the employee name is a character variable and it can hold only one value. The employee number is a number type which stores only numeric value. The join_date is a date type which can store date and time values. Reference Data Types: When we want a variable to hold the same data type as that of a particular column in a table then we can use these types. If at all if there is any change in the requirement and we change our column data type then we do not have to worry about the variables because it automatically refers to whatever changes made to the table. There are two types of Reference Data Types: a. %TYPE b. %ROWTYPE %TYPE: This allows us to copy the size and data type of the column in the table. Syntax: DECLARE .%TYPE; -- -- END; Ex: DECLARE emp_name EMP.ENAME%TYPE; job_desc EMP.JOB%TYPE; BEGIN SELECT ename, job INTO emp_name, job_desc FROM EMP WHERE empno=7788; DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME->'||emp_name||' WORKS AS '||job_desc); END; In the above example the 'emp_name' will have the same size and data type as that of the ENAME column of EMP table and 'job_desc' will have the same size and data type as that of the JOB column of EMP table using Reference Types. %ROWTYPE: This allows us to copy the entire record in a database table. Syntax: DECLARE %ROWTYPE; -- -- END; We will study the %ROWTYPE in Record Types Chapter. LOB Data Types: When we want to store large amount of unstructured data then we can use LOB data types. Different LOB Types are: a. Binary Large Objects (BLOB): Column or variable of type BLOB can store up to 4GB of binary data in each record. b. Character Large Objects (CLOB): Column or variable of type CLOB can store up to 4GB of character data in each record. c. BFILE: It can store a file of size 4GB externally outside database for each record and can refer to that from inside of the database. Constant Variables: Variables that are having a fixed value and cannot change during the program's execution is known as a Constant Variable. These variables need to be assigned a value before they are used and if we do not do so then the program will result in an error. We should make use of CONSTANT keyword to make a variable a constant variable. Syntax: DECLARE CONSTANT := ; -- -- Ex: DECLARE pi CONSTANT NUMBER := 3.1428; -- -- If you ever try to change the value of the constant inside the program then you will see an error message as shown below Ex: DECLARE pi CONSTANT NUMBER := 3.1428; BEGIN pi := 3.14; DBMS_OUTPUT.PUT_LINE(pi); END; OUTPUT: PLS-00363: expression 'PI' cannot be used as an assignment target. Scope Of Variables: Scope of variables is nothing but the portion of program in which the variables can be used. Based on the type of declaration we can specify the types of scope as Local Variables: Local Variables are the variables declared and used within a specific block. Global Variables: Global Variables are the variables declared in the parent block and are also referenced by the inner block. Ex: DECLARE num1 NUMBER := 10; num2 NUMBER := 20; BEGIN DECLARE res NUMBER; BEGIN res := num1 + num2; DBMS_OUTPUT.PUT_LINE(res); -> OUTPUTS 30 END; DBMS_OUTPUT.PUT_LINE(res); -> Raises an error END; Here the second debugging statement would raise an error saying, PLS-00201: identifier 'RES' must be declared PL/SQL: Statement ignored The global variables can also be created using PACKAGES which we will see in PACKAGES chapter. CHAPTER 3: Conditional Statements Conditional Statements Conditional Statement means to process a task whether a certain condition is met or not. i.e., to execute a part of code when one condition is met or execute other part of code when other condition is met. The above diagram may help you understand better. Below are the different types of Conditional Statements available. a. IF statement b. IF-ELSE statement c. NESTED IF statement d. IF-ELSIF statement e. CASE statement Based on variable Based on condition IF statement: IF statement is used to execute a sequence of statement based on the condition. An IF statement should have a mandatory END IF. Syntax: IF THEN -- -- << set of statements >> -- END IF; Ex: DECLARE A VARCHAR2(2) := 'A'; B VARCHAR2(2) := 'A'; BEGIN IF A=B THEN DBMS_OUTPUT.PUT_LINE('CONDITION IS TRUE'); END IF; <> END; OUTPUT: CONDITION IS TRUE As you can see in the above example, I have checked for the equality of two character type variables. As both variables hold the same value the control goes inside the IF condition or else continues with the program flow. Ex: DECLARE A VARCHAR2(2) := 'A'; B VARCHAR2(2) := 'B'; BEGIN IF A=B THEN DBMS_OUTPUT.PUT_LINE('CONDITION IS TRUE'); END IF; DBMS_OUTPUT.PUT_LINE('CONDITION IS FALSE'); <> END; OUTPUT: CONDITION IS FALSE This above program outputs the "CONDITION IS FALSE" as A is not equal to B and continues with the execution of other statements after it. IF-ELSE statement: This is the continuation of IF statement with ELSE section. One group is evaluated when the condition evaluates to TRUE and the other group is evaluated when the condition evaluates to FALSE. Syntax: IF THEN << set of statements >> ELSE << set of statements >> END IF; Ex: DECLARE A VARCHAR2(2) := 'A'; B VARCHAR2(2) := 'B'; BEGIN IF A=B THEN DBMS_OUTPUT.PUT_LINE('CONTROL IS IN IF'); ELSE DBMS_OUTPUT.PUT_LINE('CONTROL IS IN ELSE'); END IF; <> END; OUTPUT: CONTROL IS IN ELSE (A is not equal to B) Nested IF statement: Calling of one IF statement within another IF statement is called as Nested IF statement. Based on the condition of first IF statement the second IF statement is going to execute. Each nested IF statement should be terminated with their corresponding END IF statement. It follows a top-down approach i.e., the parent IF condition will be executed first and then the child IF statement will be executed. There can n number of nested IF statements. Syntax: IF THEN <> IF <> THEN <> -- -- ELSE <> -- -- END IF; ELSE -- -- END IF; Ex: DECLARE DEPNO NUMBER(20); DEPNM VARCHAR2(50):='ACCOUNTS'; BEGIN SELECT DEPTNO INTO DEPTNO FROM EMP WHERE DNAME=DEPNM; IF DEPNO=10 THEN DBMS_OUTPUT.PUT_LINE('INSIDE DEPT 10'); IF DEPNO=20 THEN DBMS_OUTPUT.PUT_LINE('INSIDE DEPT 20'); ELSE DBMS_OUTPUT.PUT_LINE('INSIDE DEPT 30'); END IF; ELSE DBMS_OUTPUT.PUT_LINE('INSIDE DEPT 40'); END IF; END; Based on the department name we are querying for department number. I have colored the IF loops for your understanding. Predict the output. ELSIF: This works just like NESTED IF but here we specify the IF statements. This is the combination of ELSE and IF (ELSIF). This also works in top-bottom approach i.e., If first IF condition evaluates to FALSE then the ELSIF clause evaluates another condition. There can be n number of ELSIF conditions based on the requirement. The last ELSE part is optional. Syntax: IF THEN --- --- --- ELSIF THEN --- --- --- ELSIF THEN --- --- --- ELSE --- <> --- END IF; If any of the conditions satisfy then the corresponding statement inside the condition are executed and then the control jumps out of the IF condition. If none of the conditions are TRUE then the control comes to the ELSE part. NOTE: There is no 'E' in ELSIF. This is not a typo. Please be careful while coding. Ex: DECLARE eno NUMBER (4):=7788; esl NUMBER (6); BEGIN SELECT sal INTO esl FROM emp WHERE empno = eno; DBMS_OUTPUT.PUT_LINE ('MONEY IN DOLLARS'); IF (esl <= 1000) THEN DBMS_OUTPUT.PUT_LINE ('GET A LIFE!'); ELSIF (esl <= 2000) THEN DBMS_OUTPUT.PUT_LINE ('PHEW!'); ELSIF (esl <= 3000) THEN DBMS_OUTPUT.PUT_LINE ('HMM!!'); ELSIF (esl <= 4000) THEN DBMS_OUTPUT.PUT_LINE ('GOOD!'); ELSE DBMS_OUTPUT.PUT_LINE ('WHOA!'); END IF; END; Lets assume salary is 4250 for empno 7788 then (try for different outputs by yourself), OUTPUT: MONEY IN DOLLARS WHOA! CASE Statement Based On Variable: CASE is another type of conditional statement which allows us to execute the statements based on a selector. The selector can be a variable (as in this case), function or an expression. Syntax: CASE <> WHEN <> THEN --- --- WHEN <> THEN --- --- WHEN <> THEN --- --- ELSE --- --- END CASE; Ex: DECLARE depnm VARCHAR2(20); dloc VARCHAR2(20):='DALLAS'; BEGIN SELECT DNAME INTO depnm FROM DEPT WHERE LOC=dloc; CASE dloc WHEN 'NEW YORK' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); WHEN 'DALLAS' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); WHEN 'INDIA' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); ELSE DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); END CASE; END; Here in the above scenario we are displaying the department name for a given location. So when you pass appropriate inputs the appropriate condition gets satisfied and then the debugging statement will display the message. CASE Statement Based On Condition: Here we do not use the selector rather we use the condition directly. The condition is nothing but an 'expression' which evaluates to Boolean value. Syntax: CASE WHEN <> THEN --- --- WHEN <> THEN --- --- WHEN <> THEN --- --- ELSE --- --- END CASE; Ex: DECLARE depnm VARCHAR2(20); dloc VARCHAR2(20):='DALLAS'; BEGIN SELECT DNAME INTO depnm FROM DEPT WHERE LOC=dloc; CASE WHEN dloc='NEW YORK' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); WHEN dloc='DALLAS' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); WHEN dloc='INDIA' THEN DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); ELSE DBMS_OUTPUT.PUT_LINE('YOUR DEPARTMENT NAME IS '||depnm); END CASE; END; CHAPTER 4: Iterative Statements Iteration Control Statements (LOOP): Iterative Statements are used when we want to execute some set of statements repeatedly until the condition evaluates to FALSE. This loop should when we do not know how many times the loop gets executed. Types of Iterative Statements: a. Simple Loop b. For Loop c. While Loop Simple Loop: Simple loop is a loop where we can execute some statements atleast once before terminating. Here we need to explicitly declare a variable outside the loop and we need to terminate it inside the loop or else it will result in infinite loop. Syntax: loop <> exit when <>; end loop; Ex: DECLARE -- Local variables here i INTEGER:=0; BEGIN -- Test statements here LOOP i:=i+1; DBMS_OUTPUT_PUT_LINE(i); EXIT WHEN i>=10; END LOOP; END; OUTPUT: 1 2 3 4 5 6 7 8 9 10 In the above example as you can see, I have declared one variable i with an initial value of 0. Inside the loop I have incremented it and at the end of the loop when the value of becomes 10 then it will exit the loop. For Loop: FOR loop is a loop where a set of statements get executed for a given number of times. This loop should be used when we know before hand how many times the loop should get executed. Here we don't have to declare the loop variable outside the loop and we do not have to worry about the loop increment as it gets incremented automatically by one. We can also print the loop content in the reverse order by using "REVERSE" keyword. Syntax: FOR <> IN <>..<> LOOP <> END LOOP; Ex: a. Prints the number from 1 to 10 BEGIN -- Test statements here FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; Ex: b. Prints the number from 10 to 1 BEGIN -- Test statements here FOR i IN REVERSE 1..10 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; NOTE: Even when we used "REVERSE" we did not change the index from 1..10 to 10..1 While Loop: This loop is same as that of Simple Loop because whatever we can achieve using WHILE Loop can be achieved using Simple Loop as well. Syntax: WHILE <> LOOP <> END LOOP; Ex: DECLARE i INTEGER:=0; BEGIN -- Test statements here WHILE i<=10 LOOP DBMS_OUTPUT.PUT_LINE(i); i:=i+1; END LOOP; END; OUTPUT: 0 1 2 3 4 5 6 7 8 9 10 CHAPTER 5: GOTO Statements GOTO AND LABELS: GOTO is used when you want the program to execute some other set of statements. The GOTO will drag the control from the current position and starts executing the statements after the <> defined position. Syntax: BEGIN --- GOTO some_label; --- --- <> --- --- <> --- END; Ex: DECLARE i INTEGER:=0; BEGIN -- Test statements here WHILE i<=10 LOOP DBMS_OUTPUT.PUT_LINE(i); i:=i+1; IF i=5 THEN GOTO OLAB; END IF; END LOOP; <> DBMS_OUTPUT.PUT_LINE('GOTO AND LABELS IN ACTION'); END; OUTPUT: 0 1 2 3 4 GOTO AND LABELS IN ACTION Restrictions for using GOTO: GOTO cannot be used wherever you want in PL/SQL, there are several dont's for GOTO as it takes the control from one statement to another. a. We cannot use the GOTO to enter into a LOOP directly. b. Atleast one executable statement must follow the LABEL used for a GOTO. Ex: a. DECLARE i INTEGER:=0; BEGIN -- Test statements here IF i=3 THEN GOTO IFTAG; END IF; WHILE i<=10 LOOP <> DBMS_OUTPUT.PUT_LINE(i); i:=i+1; IF i=5 THEN GOTO OLAB; END IF; END LOOP; <> DBMS_OUTPUT.PUT_LINE('GOTO AND LABELS IN ACTION'); END; OUTPUT: PLS-00201: identifier 'IFTAG' must be declared PL/SQL: Statement ignored The control will be not able to go inside the WHILE LOOP directly. Ex: b. DECLARE i INTEGER:=0; BEGIN -- Test statements here WHILE i<=10 LOOP DBMS_OUTPUT.PUT_LINE(i); i:=i+1; IF i=5 THEN GOTO OLAB; END IF; END LOOP; <> END; OUTPUT: PLS-00103: Encountered the symbol "END" when expecting one of the following: (begin case declare exit for goto if loop mod null raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge>>) Hence, there should be atleast one executable statement after the LABEL. CHAPTER 6: Cursors Cursors: A cursor is a memory object which is used to process an SQL statement. A cursor is also called as a pointer to the active result set. A cursor is created in the RAM (memory) and that's why it is called as a memory object. Cursor Processing Steps: a) DECLARE b) OPEN c) FETCH d) CLOSE Types of cursors: a) Implicit Cursors b) Explicit Cursors c) Reference Cursors (Ref Cursors) Implicit Cursors: Implicit cursors are automatically processed by Oracle whenever we execute an SQL statement. These are implicitly declared, opened, fetched and closed. Implicit Cursor Attributes: a) SQL%ISOPEN: Always FALSE, we cannot get the OPEN status of the cursor. b) SQL%FOUND: Returns TRUE if the previous SQL statement is successfully executed else returns FALSE. c) SQL%NOTFOUND: Returns TRUE if the previous SQL statement is not successfully executed else returns FALSE. d) SQL%ROWCOUNT: Returns the number of records affected by the previous SQL statement. This attributes helps to know the status of the previously executed SQL statement. Ex: BEGIN UPDATE EMP SET SAL=2000 WHERE DEPTNO=90 -> won't throw NO_DATA_FOUND IF SQL%ROWCOUNT=0 THEN DBMS_OUTPUT.PUT_LINE('NO RECORDS AFFECTED'); ELSE DBMS_OUTPUT.PUT_LINE('RECORDS AFFECTED'); END IF; END; NOTE: In the IF condition we can also use SQL%FOUND to check whether the records were affected or not. i.e., IF SQL%FOUND THEN Explicit Cursors: Explicit cursors are created by the users for processing multi records within PL/SQL program. An explicit cursor will have a name associated with it. Steps in processing an explicit cursors: a) DECLARE: In this step a space is identified within the memory for storing the result set. But the query is not executed. Ex: DECLARE cursor cur_emp is select ename, sal from emp where deptno=20; BEGIN .... END; b) OPEN: In this step, it executes the query and loads the data to a cursor and also places the pointer at the first record of the result set. Ex: .... BEGIN OPEN cur_emp; .... END; c) FETCH: In this step, it fetches the current record to the local variables also moves the control to next record. Ex: .... BEGIN OPEN cur_emp; FETCH cur_emp INTO vnm, vsal; DBMS_OUTPUT.PUT_LINE(vnm||'-'||vsal); END; d) CLOSE: This will close the opened cursor. It deallocates the memory held by the RAM. Ex: .... BEGIN OPEN cur_emp; FETCH cur_emp INTO vnm, vsal; .... .... CLOSE cur_emp; END; Explicit Cursor Attributes: a) CURSOR_NAME%ISOPEN: TRUE if cursor is opened, else returns FALSE. b) CURSOR_NAME%FOUND: TRUE if record is successfully fetched, else FALSE. c) CURSOR_NAME%NOTFOUND: FALSE if record is successfully fetched, else TRUE. d) CURSOR_NAME%ROWCOUNT: Returns the number of records fetched so far. Ex: i) For a given deptno, print all the employee names and their salaries. DECLARE VDNO NUMBER:=&dno; CURSOR cur_emp IS SELECT ENAME, SAL FROM EMP WHERE DEPTNO=VDNO; VNM VARCHAR(20); VSAL NUMBER(7,2); BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO VNM, VSAL; EXIT WHEN cur_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(VNM||'-'||VSAL); END LOOP; CLOSE cur_emp; END; Common-errors in the cursors: a) ORA-01001: invalid cursor [INVALID_CURSOR] This occurs when we try to access a cursor after it is called. Remedy is to use the cursor attributes before it is used. Ex: DECLARE VDNO NUMBER:=&dno; CURSOR cur_emp IS SELECT ENAME, SAL FROM EMP WHERE DEPTNO=VDNO; VNM VARCHAR(20); VSAL NUMBER(7,2); BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO VNM, VSAL; EXIT WHEN cur_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(VNM||'-'||VSAL); END LOOP; CLOSE cur_emp; DBMS_OUTPUT.PUT_LINE(cur_emp%ROWCOUNT); -> (INVALID_CURSOR) END; b) ORA-06511: cursor already open [CURSOR_ALREADY_OPEN] This error occurs when we try to open a cursor which is already opened. To overcome this, we need to explicitly close the cursor and then reopen it. Ex: DECLARE CURSOR cur_emp IS SELECT ENAME FROM EMP WHERE DEPTNO = 20; BEGIN OPEN cur_emp; FETCH cur_emp INTO VTMP; LOOP DBMS_OUTPUT.PUT_LINE(VTMP); END LOOP; OPEN cur_emp; -> (CURSOR_ALREADY_OPEN) CLOSE cur_emp; END; c) Maximum number of cursors opened are exceeded (DBA error) i) Login as SYSTEM or SYS. ii) SHOW PARAMETER OPEN_CURSORS; iii) Next, we can increase the parameter size to a higher value (DBA). iv) Close all cursors properly. NOTE: a) If we do not close the cursor we will not get any error, it will be closed after the completion of the execution. b) We should always close the explicit cursors otherwise it leads to some performance issues. c) To test whether the cursor is open or not we should use, IF CUR_NAME%ISOPEN THEN CLOSE cur_emp; END IF; FOR LOOP for processing the cursors: a) FOR loop will open the cursor, fetch from the cursor and closes the same implicitly. b) FOR loop is much faster than the simple loop. c) FOR loop cannot process the REF CURSORS where as simple loop can do it. d) Ex: DECLARE VJOB EMP.JOB%TYPE := '&job'; CURSOR cur_test IS SELECT A.ENAME, B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO AND A.JOB = B.JOB; BEGIN FOR VREC IN cur_test --> OPENS THE CURSOR LOOP DBMS_OUTPUT.PUT_LINE(VREC.ENAME||'--'||VREC.DNAME); EXIT WHEN cur_test%ROWCOUNT = 2; END LOOP; --> CLOSES THE CURSOR END; e) Cursor Sub-queries: BEGIN FOR VREC IN (SELECT ENAME, SAL FROM EMP WHERE DEPTNO=20) LOOP DBMS_OUTPUT.PUT_LINE(VREC.ENAME||'-'||VREC.SAL); EXIT WHEN cur_test%NOTFOUND; --> cant be used as cursor is not defined. END LOOP; END; These can be used when query is simple and not too complex. Parameterised Cursors: a) Its a cursor with parameters. b) These are useful when we don't know the value during declaration to be passed but will come to know during the execution of cursors. Ex: DECLARE VJOB VARCHAR(20); CURSOR cur_test (PJOB VARCHAR) IS SELECT A.ENAME, B.DNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO AND A.JOB = PJOB; BEGIN SELECT JOB INTO VJOB FROM EMP WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP); OPEN cur_test(VJOB); FOR VREC IN cur_test(VJOB) LOOP DBMS_OUTPUT.PUT_LINE(VREC.ENAME||'-'||VREC.DNAME); END LOOP; END; TO BE CONTINUED..