Procedures and cursors in PL/SQL

Document Sample
Procedures and cursors in PL/SQL Powered By Docstoc
					PL/SQL programming Procedures and Cursors Lecture 1 [Part 2]

SQL refresher


Basic commands


SELECT, INSERT, DELETE, UPDATE







Always remember to state the table(s) you are selecting data from Join tables by primary and foreign keys Filter data using WHERE clause SELECT ... FROM ... [WHERE ...]

SQL scripts
  

Set of commands to run in sequence. Stored in a ‘note pad’ not data dictionary and accessed by file name Executed by @ command
Script called:
Create_lecturer_copy.sql Executed by:

SQL> @create_lecturer_copy.sql

The SQL Procedure
  

   

Block of SQL statements stored in the Data dictionary and called by applications Satisfies frequently-used or critical application logic When called all code within the procedure is executed (unlike packages) Action takes place on server not client Does not return value to calling program Not available in Oracle 6 or older Aids security as DBA may grant access to procedures not tables, therefore some users cannot access tables except through a procedure

Building a procedure: contents
1. 2.

3.
4. 5. 6. 7.

Create or replace command Object to be created Name of object Any variables accessed or imported Local variables declared Code End procedure declaration

1.

Create or replace command
Object to be created

2.

This procedure is called inflation_rise and uses 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.

3.
4.

Name of object
Any variables accessed or imported Declared local variables Code 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;

5.

6. 7.

Compiling and executing procedures
 

Like any program the code needs to be compiled. @inflation_rise
 

compiles the procedure in a file with this name makes it available to the database

 



Execute inflation_rise executes the procedure. Remember to compile a procedure again once it has been amended. For ease of use, it is best to write procedures in notepad and then run them, this means that they can be easily edited and you have a backup copy

Example
Create or replace procedure validate_customer (v_cust in varchar2(10)) as Count Begin count = select count(*) from customer number;

Local variables used by procedure

Any variables passed into procedure

SQLwhere cust_code = v_cust;
if count > 0 then

dbms.output ‘customer valid’;
else dbms.output ‘customer not recognised’; end if;

End;

Cursors in SQL
  



Enable users to loop around a set of data. Store data selected from a query in a temp area for use when opened. Useful in complex actions which would not be feasible in standard SQL selection queries Cursor attributes - %notfound, %rowcount, %found & %isopen

Syntax for Cursors



 

Declared as a variable in the same way as standard variables Identified as cursor type SQL retrieval set is included Cursor cur_emp is e.g.
Select emp_id, surname ‘name’, grade, salary From employee Where regrade is true;

Cursor Population
 

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 cursor data is looped around until an exit clause is reached.

THE JELLY BABY DEMO!


Data has been selected from the employee table. This data needs to be amended in the following way:
 





Each member of staff is to be increased one grade. Each member of staff is to have a £500 pay rise If the pay rise does not take them to the minimum for their new grade they are to be increased to the minimum for that grade If the pay rise moves them above the maximum for a grade they are to be increased to the maximum only

25463
Create or replace procedure proc_test as v_empid number; Cursor cur_sample is Select empid from employee where grade > 4; Begin open cur_sample; loop fetch cur_sample into v_empid; exit when Stop when cur_sample%notfound no more update employee records set salary = salary + 500 are found where empid = v_empid; end loop; End; Declare Cursor

Data returned by cursor

12245

55983
12524 98543

Open cursor for use Loops round each value returned by the cursor

Places the value from the cursor into the variable v_empid

Using cursors


DECLARE, OPEN, LOOP & FETCH

Declare V_empid number; 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;

Loops round each value returned by the select statement and updates the salary accordingly

Loops and cursors
Loop – exit
Declare V_empid number; 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;

This is the simplest type of loop. Exit will indicate the criteria for the loop to drop out

Loops and cursors continued
While - loop
Declare V_empid number; Cursor cur_sample is Select empid from employee where grade > 4; Begin open cur_sample; fetch cur_sample into v_empid; while cur_sample%found loop update employee set salary = salary + 500 where empid = v_empid; end loop; End;

Will loop around as long as %found returns a true value

Loops and cursors continued
for - loop
Declare V_empid number; V_count number :=1; V_max number; Cursor cur_sample is Select empid from employee where grade > 4; Begin open cur_sample; v_max :=cur_sample%rowcount fetch cur_sample into v_empid; for v_count in 1 .. V_max loop update employee set salary = salary + 500 where empid = v_empid; v_count := v_count + 1; end loop; End;

Declare the additional variables required

Will loop around as long as v_count is not greater than the number of rows returned Remember to increment the value of v_count

Placing cursors into procedures
Creation command Variable declarations Body of code
Create or replace procedure sample1 as v_deptname varchar2(10); v_deptid number cursor cur_deptchange is select distinct deptid, deptname from dept; BEGIN open cur_deptchange; fetch cur_dept into v_deptid, v_deptname; loop exit when cur_deptchange%notfound update employee set emp_deptname = v_deptname

where emp_deptid = v_deptid;
commit; end loop; END;

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

Use of conditions


If statements can be used
If <condition> then ….. End if;

Remember to end the if statement Use of indented code will make the code easier to debug!


				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:129
posted:7/29/2009
language:English
pages:20
Description: About procedures and cursors in PL?SQL