Advanced SQL: Cursors & Stored Procedures

[Pages:32]Advanced SQL: Cursors & Stored Procedures

Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu

1

Today's Roadmap

l Views l Triggers l Cursors l Stored Procedures

2

Using Select Inside Triggers

Create Trigger EmpDate

Before Insert On Employee

For Each Row

- Execute Select inside trigger

Declare temp date;

- Store the result in temp variables

Begin

Select sysdate into temp from dual; IF (:new.hireDate is null) Then

Works fine if the Select

:new.hireDate := temp; End IF;

returns one tuple...

End;

/

Create Trigger EmpDate

Before Insert On Employee For Each Row Declare

- Select two columns into two variables

tempAge number;

tempName varchar2(100);

Begin

Select age, name into tempAge, tempName from R where ID = 5;

....

End;

3

/

Cursors: Introduction

l Select statement may return many records

Select empID, name, salary From Employee Where salary > 120,000;

Get 0 or more records

l What if inside a trigger:

l Want to execute a select statement l Get one record at a time l Do something with each record

This's what a cursor does for you...

4

What is a Cursor

l A mechanism to navigate tuple-by-tuple over a relation l Typically used inside triggers, stored procedures, or stored

functions l Main Idea

l When we execute a query, a relation is returned l It is stored in private work area for the query l Cursor is a pointer to this area l Move the cursor to navigate over the tuples

5

Creating a Cursor

Cursor name

Any query can go here

Cursor IS ;

Cursor HighSalEmp IS Select empID, name, salary From Employee Where salary > 120,000;

6

Cursor Operations

l Create cursor

Cursor HighSalEmp IS Select empID, name, salary From Employee Where salary > 120,000;

l Open cursor

Open HighSalEmp;

l Execute the query and put the pointer at the first tuple

l Fetch next tuple Fetch HighSalEmp into ;

l Pointer moves automatically when a tuple is fetched

l Close cursor

Close HighSalEmp;

7

Example 1

l Have two tables: Customer & Product l When insert a new customer

l Put in Marketing table, the customer ID along with the products labeled `OnSale'

Create Trigger NewCust

After Insert On Customer

For Each Row

Define the cursor in `Declare' section

Declare

pid number;

cursor C1 is Select product_id From Product Where label = 'OnSale';

Begin open C1;

Open the cursor

Loop

Loop over each record at a time

Fetch C1 Into pid; IF (C1%Found) Then

If the fetch returned a record

Insert into Marketing(Cust_id, Product_id) values (:new.Id, pid);

END IF;

Exit When C1%NotFound; END Loop;

Customer ID

close C1;

Close the cursor

8

End; /

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download