PL/SQL Stored Procedures And Cursors.ppt

It is a Database management systems PLSQL subtopic i.e. Procedure and Cursors

1. 1
PL/SQL programming
Procedures and Cursors
Lecture 1
Akhtar Ali

2. 2
SQL refresher
Basic commands
SELECT, INSERT, DELETE, UPDATE
Always remember to state the table(s) you
are selecting data from
Join tables using keys (Primary / Foreign)
Filter data wherever possible
Procedures are different from scripts

3. 3
SQL scripts
Set of commands to run in sequence.
Stored as a text file (e.g. using Notepad) on a disk
and not in the data dictionary. It is accessed by
file name
Executed using @ or Start.
Script called:
Create_User.sql
Executed by:
SQL> @Create_User.sql

4. 4
Procedures in SQL
Block of SQL statements stored in the Data dictionary
and called by applications or from SQL* plus prompt.
Usually used to implement application/business logic.
When called all code within a procedure is executed
(unlike packages).
Action takes place on server side not client.
Do not return value to calling program.
Not available in Oracle 6 or older.
Aid security as DBA will grant access to procedures not
tables, therefore users can not access tables unless via a
procedure.

5. 5
Building a procedure
1.Create or replace command
2.Type of object to be created
3.Name of object
4.Any variables accessed or imported
5.Declare local variables
6.Write body of the object (code)
7.End procedure declaration

6. 6
1.Create or replace
command
2.Object to be
created
3.Name of object
4.Any variables
accessed or
imported
5.Declare local
variables
6.Body
7.End procedure
declaration
Create or replace procedure inflation_rise (inf_rate in
number)
Begin
update employee
set salary = salary + (salary * inf_rate / 100);
commit;
End;
This procedure is called inflation_rise and used a
variable accessed as inf_rate which is a number,
this is passed in when the procedure is used. It
simply updates the salary by the rate of inflation.

7. 7
Compiling and executing
procedures
Like any program the code needs to be compiled.
@inflation_rise will compile the procedure and make it
available in the database
Execute inflation_rise(2) will cause the procedure to
execute, with 2 as an inflation rate.
Remember to compile a procedure once it has been
amended.
For ease of use, it is easiest to write procedures in
notepad, store as script files, and then run them, this
means that they can be easily edited – also you will have
a copy if required

8. 8
Example
CREATE OR REPLACE PROCEDURE validate_customer ( v_cust IN VARCHAR ) AS
v_countNUMBER;
BEGIN
SELECT COUNT(*) INTO V_COUNT
FROM customer
WHERE c_id = v_cust;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE( 'customer valid');
ELSE
DBMS_OUTPUT.PUT_LINE('customer not recognised');
END IF;
END;
Any variables
passed into
procedure
Local variables used
by procedure
SQL

9. 9
Cursors in SQL
Enables users to loop around a selection of
data.
Stores data selected from a query in a temp
area for use when opened.
Use complex actions which would not be
feasible in standard SQL selection queries

10. 10
Syntax for Cursors
Declared as a variable in the same way as
standard variables
Identified as cursor type
SQL included
E.g.
Cursor cur_emp is
Select emp_id, surname name, grade, salary
From employee
Where salary>30000;

11. 11
Cursors
A cursor is a temp store of data.
The data is populated when the cursor is opened.
Once opened the data must be moved from the
temp area to a local variable to be used by the
program. These variables must be populated in
the same order that the data is held in the cursor.
The data is looped round till an exit clause is
reached.

12. 12
Active setActive set
Current rowCurrent rowCursor
7369SMITH CLERK
7566JONES MANAGER
7788SCOTT ANALYST
7876ADAMS CLERK
7902FORD ANALYST
Cursor FunctionsCursor Functions

13. 13
Controlling CursorControlling Cursor
•Create a Create a
named named
SQL areaSQL area
DECLAREDECLARE
•Identify Identify
the active the active
setset
OPENOPEN
•Load the Load the
current current
row into row into
variablesvariables
FETCHFETCH
•Test for Test for
existing existing
rowsrows
EMPTY?
•Return to Return to
FETCH if FETCH if
rows rows
foundfound
NoNo
•Release Release
the active the active
setset
CLOSECLOSE
YesYes

14. 14
Controlling Cursor…Controlling Cursor…
Open the cursor.Open the cursor.
CursorCursor
PointerPointer
Fetch a row from the cursor.Fetch a row from the cursor.
CursorCursor
PointerPointer
Continue until empty.Continue until empty.
CursorCursor
PointerPointer
Close the cursor.Close the cursor.
CursorCursor

15. 15
Cursor AttributesCursor Attributes
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 Evaluates to TRUE if the most
recent fetch returns a row;
complement of %NOTFOUND
%ROWCOUNT Number Evaluates to the total number of
rows returned so far
Obtain status information about a cursor.

16. 16
Create or replace procedure proc_test as
v_empidnumber;
Cursor cur_sample is
Select empid from employee
where grade > 4;
Begin
open cur_sample;
loop
fetch cur_sample into v_empid;
exit when cur_sample%notfound;
update employee
set salary = salary + 500
where empid = v_empid;
end loop;
End;
Open cursor for use.
Loops round each value
returned by the cursor
Place the value from the
cursor into the variable
v_empid
Stop
when not
more
records
are found
25463
12245
55983
12524
98543
Data
returned
by
cursor
Declare
Cursor

17. 17
Use of conditions
If statements can be used
If <condition> Then
…..
End if;
Example
Remember to end the if statement and use of indented code
will make it easier to debug!
. . .
IF v_ename = 'MILLER' THEN
v_job := 'SALESMAN';
v_deptno := 35;
v_new_comm := sal * 0.20;
END IF;
. . .

18. 18
The %ISOPEN Attribute
Fetch rows only when the cursor is open.
Use the %ISOPEN cursor attribute before
performing a fetch to test whether the cursor is
open.
Example
IF NOT cur_sample%ISOPEN THEN
OPEN cur_sample;
END IF;
LOOP
FETCH cur_sample...

19. 19
DECLARE
CURSOR emp_cursor IS
SELECTempno, ename
FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
...
Cursors and RecordsCursors and Records
 Process the rows of the active set
conveniently by fetching values into a
PL/SQL RECORD.
 Example

20. 20
Cursor FOR LoopsCursor FOR Loops
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
Syntax
The cursor FOR loop is a shortcut to process
cursors.
Implicitly opens, fetches, and closes cursor.
The record is implicitly declared.

21. 21
Cursor FOR Loops: An ExampleCursor FOR Loops: An Example

Retrieve employees one by one until no more are Retrieve employees one by one until no more are
left.left.

ExampleExample
DECLARE
CURSOR emp_cursor IS
SELECT ename, deptno
FROM emp;
BEGIN
FOR emp_record IN emp_cursor LOOP
-- implicit open and implicit fetch occur
IF emp_record.deptno = 30 THEN
...
END LOOP; -- implicit close occurs
END;