Introduction to pl sql procedures cursors
This presentation is the property of its rightful owner.
Sponsored Links
1 / 32

Lecture 4 PowerPoint PPT Presentation


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

Introduction to PL/SQL Procedures & Cursors. Lecture 4. Overview. Overview of PL/SQL Development of a coded block Interacting with an Oracle Database Controlling PL/SQL process flow Cursor handling. Embedding SQL. SQL is not functionally complete

Download Presentation

Lecture 4

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


Introduction to pl sql procedures cursors

Introduction to PL/SQL

Procedures & Cursors

Lecture 4


Overview

Overview

  • Overview of PL/SQL

  • Development of a coded block

  • Interacting with an Oracle Database

  • Controlling PL/SQL process flow

  • Cursor handling


Embedding sql

Embedding SQL

  • SQL is not functionally complete

    • Lacks the full facilities of a programming language

      • variables, flow of control etc.

  • All DBMSs top up functionality by embedding SQL in a procedural language

  • But details vary from one DBMS to another.

    • However, procedures and functions can be ported between systems.


Pl sql introduction

PL/SQL - Introduction

  • An Oracle-specific procedural extension to SQL, allowing for modularity, variable declaration, loops and logical constructs.

  • Allows for advanced error handling.

  • Communicates natively with other Oracle database objects.

  • Managed centrally within the Oracle database.


Why use pl sql

Why use PL/SQL?

  • Manage business rules – through middle layer application logic.

  • Generate code for triggers.

  • Generate code for the user interface.

  • Enable database-centric client/server applications.


Centralised vs decentralised

Begin

:

End;

Begin

:

End;

Begin

:

End;

Multiple copies of executable code on the decentralised system – multiple copies to maintain leading to increase difficulty in maintaining the system

Begin

:

End;

Server

Server

Common copy of executed code – one copy to maintain

Centralised vs. Decentralised


Advantages of using pl sql to access oracle

Advantages of using PL/SQL to access Oracle

  • PL/SQL is managed centrally within the database.

  • Code is managed by the DBA, and execution privileges are managed in the same way as with other objects.

  • PL/SQL objects are first-class Oracle DB objects.

  • Easy to read:

    • with modularity features and error handling.


Centralised control

Centralised Control

  • Enables the DBA to:

    • specify rules in one place (as a procedure, function, trigger or package in PL/SQL);

    • force user access through the predefined PL/SQL, so users cannot write their own procedural code and use this instead

      • e.g. define security privileges giving users access to table(s) only through a particular procedure.


Using pl sql as a programming language

Using PL/SQL as a Programming Language

  • Permits all “flow of control” operations of standard programming languages, e.g.

    • JumpsGOTO

    • ConditionsIF-THEN-END IF;IF-THEN-ELSE-END IF

    • LoopsLOOP-EXIT;WHEN-END LOOP;FOR-END LOOP;WHILE-END LOOP

  • Allows extraction of data into variables and its subsequent manipulation.


Modules in pl sql

Modules in PL/SQL

There are 4 types of modules in PL/SQL

  • Procedure – a series of statements which may or may not return a value.

  • Function – a series of statements which must return a single value.

  • Trigger – a series of statements which is executed after an event has triggered a condition.

  • Package – a collection of procedures and functions which has 2 parts:

    • a listing and a body.


Use of data types

Use of Data Types

  • Number – used to store any number.

  • Char(size) & varchar2(size)e.g. char(10) – used to store alphanumerical text strings; the char data type will pad the value stored to the full length declared.

  • Date – used to store dates and times.

  • Long – used to store large blocks of text up to 2 gigabytes in length (limited operations)


Non db data types

Non-DB Data Types

  • DEC, DECIMAL, REAL, INTEGER, INT – these are numerical data types that are a subset of number.

  • Binary_integer – binary format for number type but can not be stored in database unless converted first.

  • Character – same as char.

  • Boolean – true/false value.

  • Table/record – tables can be used to store the equivalent of an array while records store the variables with composite data types.


Sql scripts

Script called:

Create_lecturer_copy.sql

SQL Scripts

  • A set of commands to run in sequence.

  • Stored as a text file (e.g. using Notepad) on disk and not in the data dictionary. It is accessed by its file name using @ or Start.

Executed by:

SQL> @U:\create_lecturer_copy


The sql procedure

The SQL Procedure

  • A 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 the server, not the client.

  • Does not (normally) return a value to the calling program.

  • Not available in Oracle 6 or older.

  • Aids security as DBA may grant access to procedures rather than tables, therefore some users cannot access tables except through a procedure.


Building a procedure contents

Building a Procedure: Contents

  • CREATE OR REPLACE command;

  • Object to be created;

  • Name of object;

  • Any variables accessed or imported;

  • Local variables declared;

  • Code block enclosed by BEGIN … END;


Lecture 4

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.

Create or replace command

Object to be created

Name of object

Any variables accessed or imported

Declared local variables

Code

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

    • compiles the procedure from a file with this name;

    • makes it available to the data base.

  • Execute inflation_rise executes the procedure.

  • Remember to re-compile a procedure after editing.

  • For ease of use, it is best to write procedures in Notepad, then they can be easily edited and you have a back-up copy.


Example

Local variable used by procedure

Variable passed into procedure

SQL Code Block

Example

CREATE OR REPLACE PROCEDURE validate_customer (v_cust VARCHAR) AS

v_countNUMBER;

BEGIN

SELECT COUNT(*) INTO v_count

FROM CUSTOMER

WHERE CUST_CODE = v_cust;

IF v_count > 0 THEN

DBMS_OUTPUT.PUT_LINE(‘customer valid’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘customer not recognised’);

END IF;

END;


Cursors in sql

Cursors in SQL

  • Enables users to loop round a selection of data.

  • Stores data select from a query in a temp area for use when opened.

  • Use complex actions which would not be feasible in standard SQL selection queries


Declaring cursors

Cursor cur_emp is

Select emp_id, surname ‘name’, grade, salary

From employee

Where regrade is true;

Declaring Cursors

  • Declared as a variable in the same way as standard variables.

  • Identified as cursor type.

  • SQL included, e.g.


Cursors

Cursors

  • A cursor is a temporary store of data.

  • The data is populated when the cursor is opened.

  • Once opened, the data must be moved from the temporary 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 set is looped round till an exit clause is reached.


Lecture 4

Active set

7369SMITHCLERK

7566JONESMANAGER

7788SCOTTANALYST

7876ADAMSCLERK

7902FORDANALYST

Current row

Cursor

Cursor Functions


Lecture 4

OPEN

FETCH

EMPTY?

Controlling the Cursor

No

DECLARE

CLOSE

Yes

  • 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


Lecture 4

Pointer

Cursor

Pointer

Cursor

Pointer

Cursor

Cursor

Controlling the Cursor…

Open the cursor.

Fetch a row from the cursor.

Continue until empty.

Close the cursor.


Lecture 4

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; logicalcomplement of %NOTFOUND

%ROWCOUNTNumberEvaluates to the total number of rows returned so far

Cursor Attributes

To obtain status information about a cursor.


Lecture 4

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 no more records are found


Lecture 4

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’ otherwise use

SHOW ERRORS to view errors in the code.

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;

  • E.g.

  • Remember to end the IF statement

  • 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

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


Lecture 4

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;

...


Lecture 4

Cursor FOR Loops

  • The cursor FOR loop is a shortcut to process cursors.

  • Syntax

  • Implicitly opens, fetches, and closes cursor.

  • The record is implicitly declared.

FOR record_name IN cursor_name LOOP

statement1;

statement2;

. . .

END LOOP;


Lecture 4

Cursor FOR Loops: An Example

  • Retrieve employees one by one until no more areleft:

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;


  • Login