Pl sql programming procedures and cursors lecture 1
This presentation is the property of its rightful owner.
Sponsored Links
1 / 23

PL/SQL programming Procedures and Cursors Lecture 1 PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on
  • Presentation posted in: General

PL/SQL programming Procedures and Cursors Lecture 1. Akhtar Ali. 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

Download Presentation

PL/SQL programming Procedures and Cursors Lecture 1

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Pl sql programming procedures and cursors lecture 1

PL/SQL programming Procedures and CursorsLecture 1

Akhtar Ali


Sql refresher

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


Sql scripts

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


Procedures in sql

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.


Building a procedure

Building a procedure

  • 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


Akhtar ali

Create or replace command

Object to be created

Name of object

Any variables accessed or imported

Declare local variables

Body

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;


Compiling and executing procedures

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


Example

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;

Local variables used by procedure

Any variables passed into procedure

SQL


Cursors in sql

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


Syntax for cursors

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;


Cursors

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.


Akhtar ali

Cursor

Cursor Functions

Active set

7369SMITHCLERK

7566JONESMANAGER

7788SCOTTANALYST

7876ADAMSCLERK

7902FORDANALYST

Current row


Akhtar ali

OPEN

FETCH

Yes

EMPTY?

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


Akhtar ali

Pointer

Cursor

Pointer

Cursor

Pointer

Cursor

Cursor

Controlling Cursor…

Open the cursor.

Fetch a row from the cursor.

Continue until empty.

Close the cursor.


Akhtar ali

Cursor Attributes

Obtain status information about a cursor.

AttributeType Description

%ISOPENBoolean Evaluates to TRUE if the cursor is open

%NOTFOUNDBoolean Evaluates to TRUE if the most recent fetch does not return a row

%FOUNDBoolean Evaluates to TRUE if the mostrecent fetch returns a row; complement of %NOTFOUND

%ROWCOUNTNumberEvaluates to the total number of rows returned so far


Akhtar ali

25463

12245

55983

12524

98543

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;

Datareturned by cursor

DeclareCursor

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


Akhtar ali

Notepad file called:

Create_procedure.sql

1) Open SQL*Plus and logon

2) At the prompt enter:

@create_procedure

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

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;

. . .


The isopen attribute

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...


Akhtar ali

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

SELECTempno, ename

FROMemp;

emp_recordemp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

...


Cursor for loops

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;


Cursor for loops an example

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;


Seminar exercise

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.


  • Login