Advanced SQL: Cursors & Stored Procedures
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
................
................
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 download
- embedded sql in rpg beyond the basics
- sql server execute statement with result set clause
- using vfp 9 s sql commands
- built in sql functions drexel university
- advanced sql cursors stored procedures
- a two step sql query with the execute function
- claris filemaker sql reference guide
- working with databases and groovy
- call execute let your program run your macro
- installshield tip accessing the msi database at run time
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