What is PL/SQL?
PL/SQL stands for Procedural Language
extension of SQL.
PL/SQL
is a combination of SQL along with the procedural features of programming
languages.
It was
developed by Oracle Corporation in the early 90’s to enhance the capabilities
of SQL.
The PL/SQL Engine:
Oracle
uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be
stored in the client system (client-side) or in the database (server-side).
|
A Simple PL/SQL Block:
Each
PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
PL/SQL Block consists of three sections:
- The Declaration section (optional).
- The Execution section (mandatory).
- The Exception (or Error) Handling section (optional).
Declaration Section:
The Declaration section of a
PL/SQL Block starts with the reserved keyword DECLARE. This section is optional
and is used to declare any placeholders like variables, constants, records and
cursors, which are used to manipulate data in the execution section.
Placeholders may be any of Variables, Constants and Records, which stores data
temporarily. Cursors are also declared in this section.
Execution Section:
The Execution section of a
PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is
a mandatory section and is the section where the program logic is written to
perform any task. The programmatic constructs like loops, conditional statement
and SQL statements form the part of execution section.
Exception Section:
The Exception section of a
PL/SQL Block starts with the reserved keyword EXCEPTION. This section is
optional. Any errors in the program can be handled in this section, so that the
PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions
that cannot be handled, the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
How a Sample PL/SQL Block Looks
DECLARE
Variable declaration BEGIN Program Execution EXCEPTION Exception handling END; |
PL/SQL Placeholders
Placeholders
are temporary storage area. Placeholders can be any of Variables, Constants and
Records. Oracle defines placeholders to store data temporarily, which are used
to manipulate data during the execution of a PL SQL block.
Depending
on the kind of data you want to store, you can define placeholders with a name
and a datatype. Few of the datatypes used to define placeholders are as given
below.
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
PL/SQL Variables
These are
placeholders that store the values that can change through the PL/SQL Block.
General Syntax to declare a variable isvariable_name datatype [NOT NULL := value ];
DECLARE salary number (6);
* “salary” is a variable of datatype number and of length 6.When a variable is specified as NOT NULL, you must initialize the variable when it is declared. |
DECLARE
salary number(4);
dept varchar2(10) NOT NULL := “HR Dept”;
The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.
1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;
2) We can assign values to variables directly from the
database columns by using a SELECT.. INTO statement. The General Syntax is:
SELECT column_name INTO variable_name FROM table_name [WHERE condition]; |
Example: The below program will get the salary of an employee with id '1116' and display it on the screen.
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
END;
/
NOTE:
The backward slash '/' in the above program indicates to execute the above
PL/SQL Block.
Scope of PS/SQL Variables
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.- Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
- Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.
1> DECLARE 2> var_num1 number;
3> var_num2 number; 4> BEGIN 5> var_num1 := 100; 6> var_num2 := 200; 7> DECLARE 8> var_mult number; 9> BEGIN 10> var_mult := var_num1 * var_num2; 11> END; 12> END; 13> /
For example, to declare salary_increase, you can write code as follows:
DECLARE
salary_increase CONSTANT number (3) := 10;
You must assign a value to a constant at the time you declare it. If you do not
assign a value to a constant while declaring it and try to assign a value in
the execution section, you will get a error. If you execute the below Pl/SQL
block you will get error.
PL/SQL Records
NOTE: You can use also %type to declare variables and constants.
The General Syntax to declare a record of a uer-defined datatype is:
3) IF condition 1 THEN statement 1; statement 2; ELSIF condtion2 THEN statement 3; ELSE statement 4; END IF; 4) IF condition1 THEN ELSE IF condition2 THEN statement1; END IF; ELSIF condition3 THEN statement2; END IF; Iterative Statements in PL/SQL
General Syntax to write a Simple Loop is:LOOP
Important
steps to follow when executing a while loop:
1) The counter variable is implicitly declared in the declaration section,
so it's not necessary to declare it explicity.
2) The counter variable is incremented by 1 and does not need to be incremented
explicitly.
3) EXIT WHEN statement and EXIT statements can be used in FOR loops but it's
not done oftenly.
NOTE: The above Loops are explained with a example when dealing
with Explicit Cursors.
What are Cursors?A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set. There are two types of cursors in PL/SQL:
Implicit Cursors: ApplicationWhen you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected. The status of the cursor for each of these attributes are defined in the below table.
BEGIN UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table. Explicit CursorsAn explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
General Syntax for creating a
cursor is as given below:
CURSOR cursor_name IS select_statement;
DECLARE the cursor in the declaration
section.
OPEN the cursor in the Execution
Section.
FETCH the data from cursor into PL/SQL
variables or records in the Execution Section.
CLOSE the cursor in the Execution Section
before you end the PL/SQL Block.
1) Declaring a Cursor in the Declaration Section:
DECLARE
CURSOR emp_cur IS
SELECT *
FROM emp_tbl
WHERE salary > 5000;
In the above example we are creating a cursor ‘emp_cur’ on a query which returns the records
of all the employees with salary greater than 5000. Here ‘emp_tbl’ in the table which contains records of all the
employees.
2) Accessing the records in the cursor:
Once the cursor is created in the declaration section we can access the cursor in the execution
section of the PL/SQL program.
How to access an Explicit Cursor?
These are the three steps in
accessing the cursor.
1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.
General Syntax to open a cursor is:
OPEN cursor_name;
Using Loops with Explicit Cursors:
Oracle provides three types of cursors namely
SIMPLE LOOP, WHILE LOOP and FOR LOOP. These loops can be used to process
multiple rows in the cursor. Here I will modify the same example for each loops
to explain how to use loops with cursors.
Cursor with a Simple Loop:1> DECLARE 2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN 6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> LOOP
10> FETCH emp_cur INTO emp_rec;
11> EXIT WHEN emp_cur%NOTFOUND;
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
14> END LOOP;
15> END;
16> /
In the above example we are using two cursor
attributes %ISOPEN and %NOTFOUND.
In line no 6, we are using the cursor attribute %ISOPEN to check if the cursor
is open, if the condition is true the program does not open the cursor again,
it directly moves to line no 9.
In line no 11, we are using the cursor attribute %NOTFOUND to check whether the
fetch returned any row. If there is no rows found the program would exit, a
condition which exists when you fetch the cursor after the last row, if there
is a row found the program continues.
We can use %FOUND in place of %NOTFOUND and vice
versa. If we do so, we need to reverse the logic of the program. So use these
attributes in appropriate instances.
Cursor with a While Loop:
Lets modify the above program to use while loop.
1> DECLARE 2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN 6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> FETCH sales_cur INTO sales_rec; 10> WHILE sales_cur%FOUND THEN 11> LOOP
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
15> FETCH sales_cur INTO sales_rec;
16> END LOOP;
17> END; 18> /
In the above example, in line no 10 we are using
%FOUND to evaluate if the first fetch statement in line no 9 returned a row, if
true the program moves into the while loop. In the loop we use fetch statement
again (line no 15) to process the next row. If the fetch statement is not
executed once before the while loop the while condition will return false in
the first instance and the while loop is skipped. In the loop, before fetching
the record again, always process the record retrieved by the first fetch
statement, else you will skip the first row.
Cursor with a FOR Loop:
When using FOR LOOP you need not declare a record
or variables to store the cursor values, need not open, fetch and close the
cursor. These functions are accomplished by the FOR LOOP automatically.
General Syntax for using FOR LOOP:
FOR record_name IN cusror_name
LOOP process the row...
END LOOP;
Let’s use the above example to learn how to use
for loops in cursors.
1> DECLARE 2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN 6> FOR emp_rec in sales_cur
7> LOOP 8> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
9> || ' ' ||emp_cur.salary); 10> END LOOP; 11>END; 12> /
In the above example, when the FOR loop is
processed a record ‘emp_rec’of structure ‘emp_cur’ gets created, the cursor is
opened, the rows are fetched to the record ‘emp_rec’ and the cursor is closed
after the last row is processed. By using FOR Loop in your program, you can
reduce the number of lines in the program.
NOTE: In the examples given
above, we are using backward slash ‘/’ at the end of the program. This
indicates the oracle engine that the PL/SQL program has ended and it can begin
processing the statements.
|
No comments:
Post a Comment