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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- free calculator stored on desktop
- where are screenshots stored in windows 10
- where is print screen stored windows 10
- where are screenshots stored windows 10
- where are screenshots stored on windows 10
- find stored passwords windows 7
- internet passwords stored windows 10
- where are passwords stored in windows10
- access stored usernames and passwords
- where are my stored passwords
- stored passwords on this device
- where are my stored passwords windows 10