PL/SQL programming Procedures and Cursors Lecture 1

1
PL/SQL programming Procedures and
Cursors Lecture 1

• Akhtar Ali

2
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

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 _at_ or Start.

Script called Create_lecturer_copy.sql
Executed by SQLgt _at_create_lecturer_copy.sql
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
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

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

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) commit End
7
Compiling and executing procedures

• Like any program the code needs to be compiled.
• _at_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
Example
CREATE OR REPLACE PROCEDURE validate_customer (
v_cust IN VARCHAR ) AS v_count NUMBER BEGIN
SELECT COUNT() INTO V_COUNT
FROM customer WHERE c_id
v_cust IF v_count gt 0 THEN DBMS_OUTPUT.PUT_LIN
E( 'customer valid') ELSE DBMS_OUTPUT.PUT_LIN
E('customer not recognised') END IF END
Local variables used by procedure
Any variables passed into procedure
SQL
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
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
regrade is true
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
Cursor Functions
Active set
7369 SMITH CLERK 7566 JONES MANAGER 7788 SCOTT ANA
LYST 7876 ADAMS CLERK 7902 FORD ANALYST
Current row
13
Controlling Cursor
No
DECLARE
CLOSE

• Create a named SQL area

• Identify the active set

• Load the current row into variables

• Test for existing rows

• Release the active set

• Return to FETCH if rows found

14
Controlling Cursor
Open the cursor.





Fetch a row from the cursor.

Continue until empty.

Close the cursor.
15
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
16
25463 12245 55983 12524 98543

• Create or replace procedure proc_test as
• v_empid number
• Cursor cur_sample is
• Select empid from employee
• where grade gt 4
• Begin
• open cur_sample
• loop
• fetch cur_sample into v_empid
• exit when cur_samplenotfound
• update employee
• set salary salary 500
• where empid v_empid
• end loop
• End

Data returned by cursor
Declare Cursor
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
17
Notepad file called Create_procedures.sql
1) Open SQLPlus and logon 2) At the prompt
enter _at_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
18
Use of conditions

• If statements can be used
• If ltconditiongt 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 . . .
19
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_sampleISOPEN THEN OPEN
cur_sample END IF LOOP FETCH cur_sample...
20
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_cursorROWTY
PE BEGIN OPEN emp_cursor LOOP FETCH
emp_cursor INTO emp_record ...
21
Cursor FOR Loops

• Syntax
• The cursor FOR loop is a shortcut to process
  cursors.
• Implicitly opens, fetches, and closes cursor.
• The record is implicitly declared.

FOR record_name IN cursor_name LOOP
statement1 statement2 . . . END LOOP
22
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
23
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 ltprocedure namegt at the SQL
  prompt.