Transcript Document 7773693

PL/SQL programming
Procedures and Cursors
Lecture 1
Akhtar Ali
1
SQL refresher

Basic commands





SELECT, INSERT, DELETE, UPDATE
Always remember to state the table(s) you
are selecting data from
Join tables by keys
Filter data wherever possible
Procedures are different from scripts
2
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_lecturer_copy.sql
Executed by:
SQL> @create_lecturer_copy.sql
3
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.
4
Building a procedure
1.
2.
3.
4.
5.
6.
7.
Create or replace command
Type of object to be created
Name of object
Any variables accessed or imported
Declare local variables
Write body of the object (code)
End procedure declaration
5
1.
Create or replace
command
2.
Object to be
created
3.
Name of object
4.
Any variables
accessed or
imported
5.
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.
Create or replace procedure inflation_rise (inf_rate in
number)
Begin
update employee
set salary = salary + (salary * inf_rate / 100);
Declare local
variables
commit;
End;
6.
Body
7.
End procedure
declaration
6
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
7
Example
CREATE OR REPLACE PROCEDURE validate_customer ( v_cust IN VARCHAR ) AS
v_count NUMBER;
BEGIN
SQL
Local variables used
by procedure
SELECT COUNT(*) INTO V_COUNT
Any variables
passed into
procedure
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;
8
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
9
Syntax for Cursors




Declared as a variable in the same way as
standard variables
Identified as cursor type
SQL included
Cursor cur_emp is
E.g.
Select emp_id, surname ‘name’, grade,
salary
From employee
Where regrade is true;
10
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.
11
Cursor Functions
Active set
Cursor
7369 SMITH
CLERK
7566 JONES
MANAGER
7788 SCOTT
ANALYST
7876 ADAMS
CLERK
7902 FORD
ANALYST
Current row
12
Controlling Cursor
No
Yes
DECLARE
OPEN
• Create a
• Identify
named
SQL area
the active
set
EMPTY?
CLOSE
• Load the
• Test for
• Release
current
row into
variables
existing
rows
FETCH
the active
set
• Return to
FETCH if
rows
found
13
Controlling Cursor…
Open the cursor.
Pointer
Cursor
Fetch a row from the cursor.
Pointer
Cursor
Continue until empty.
Pointer
Cursor
Close the cursor.
Cursor
14
Cursor Attributes
Obtain status information about a cursor.
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
15
25463
Create or replace procedure proc_test as
v_empid number;
Cursor cur_sample is
Select empid from employee
where grade > 4;
12245
55983
12524
Declare
Cursor
98543
Open cursor for use.
Begin
Stop when
not more
records
are found
Data
returned
by cursor
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;
Loops round each value
returned by the cursor
Place the value from the
cursor into the variable
v_empid
End;
16
Notepad file called:
Create_procedures.sql
1) Open SQL*Plus and logon
2) At the prompt enter:
@create_procedures
You will get a prompt which should say ‘procedure created’
3) To run the procedure enter:
Execute proc_test
4) If you check your data you should now find that the procedure has run successfully
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
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
Cursors and Records
Process
the rows of the active set
conveniently by fetching values into a
PL/SQL RECORD.
Example
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename
FROM
emp;
emp_record
emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
...
20
Cursor FOR Loops
Syntax
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
The
cursor FOR loop is a shortcut to process
cursors.
Implicitly opens, fetches, and closes cursor.
The record is implicitly declared.
21
Cursor FOR Loops: An Example
Retrieve employees one by one until no more are
left.
 Example

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;
22
Seminar exercise


The purpose of this exercise is to create a procedure which, when
evoked, will increase the salary of a grade by £500 or £1000
depending on the level of the grade. You will need to populate the
table first with a minimum of 10 entries, the empgrade value should
be between 1 and 5, and the idea is the higher the grade the higher the
manager. There is a script for this on the BlackBoard/homepage.
Once you have done this you should compile the code this will then
indicate if the procedure has compiled or not. If compilation is
unsuccessful entering the command show errors will list any
problems with the code. To run the procedure you type execute
<procedure name> at the SQL prompt.
23