Tutorial Contents:
- 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:
<<PARENT_BLOCK>>
DECLARE
parent_var VARCHAR2(20):='FIRST BLOCK';
BEGIN
DBMS_OUTPUT.PUT_LINE(parent_var);
<<CHILD_BLOCK>>
DECLARE
child_var VARCHAR2(20):='SECOND BLOCK';
BEGIN
DBMS_OUTPUT.PUT_LINE(child_var);
<<GRAND_CHILD_BLOCK>>
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
<variable name> <datatype>;
--
--
END;
Syntax to declare and assign a variable:
DECLARE
<variable name> <datatype>;
BEGIN
<variable name> := <value>;
Syntax to declare and assign a variable at the same time:
DECLARE
<variable name> <datatype> := <value>;
BEGIN
--
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
<variable_name> <table_name>.<column_name>%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
<variable_name> <table_name>%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
<variable_name> CONSTANT <data_type> := <value>;
--
--
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 <condition> 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;
<<other executable statements>>
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');
<<other executable statements>>
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 <condition> 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;
<<other executable statements>>
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 <condition> THEN
<<set of statements>>
IF <<condition>> THEN
<<set of statements>>
--
--
ELSE
<<set of statements>>
--
--
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 <condition> THEN
---
---
---
ELSIF <condition> THEN
---
---
---
ELSIF <condition> THEN
---
---
---
ELSE
---
<<this is optional and executes when all the above conditions fail>>
---
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 <<variable>>
WHEN <<condition>> THEN
---
---
WHEN <<condition>> THEN
---
---
WHEN <<condition>> 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 <<condition>> THEN
---
---
WHEN <<condition>> THEN
---
---
WHEN <<condition>> 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
<<set of statements>>
exit when <<cursor%notfound or some variable_name>>;
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 <<loop_variable>> IN <<start_index>>..<<end_index>>
LOOP
<<set of statements>>
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 <<condition>>
LOOP
<<set of statements>>
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 <<LABEL>> defined position.
Syntax:
BEGIN
---
GOTO some_label;
---
---
<<some_label>>
---
--- <<set of statements>>
---
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;
<<OLAB>>
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
<<IFTAG>>
DBMS_OUTPUT.PUT_LINE(i);
i:=i+1;
IF i=5 THEN
GOTO OLAB;
END IF;
END LOOP;
<<OLAB>>
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;
<<OLAB>>
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 <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
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..
