Advanced SQL Cursors Stored Procedures Instructor Mohamed Eltabakh

Advanced SQL: Cursors & Stored Procedures Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1

Today’s Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures

Cursors: Introduction Select statement may return many records l Select emp. ID, name, salary From Employee Where salary > 120, 000; l Get 0 or more records What if inside a trigger: l l l Want to execute a select statement Get one record at a time Do something with each record This’s what a cursor does for you…

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 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 Creating Cursor <name> IS <SQL query>; Cursor High. Sal. Emp IS Select emp. ID, name, salary From Employee Where salary > 120, 000;

Creating a Cursor name Any query can go here Cursor <name> IS <SQL query>; Cursor High. Sal. Emp IS Select emp. ID, name, salary From Employee Where salary > 120, 000;

Cursor Operations l l Create cursor Open cursor l l l Open High. Sal. Emp; Execute the query and put the pointer at the first tuple Fetch next tuple l Cursor High. Sal. Emp IS Select emp. ID, name, salary From Employee Where salary > 120, 000; Fetch High. Sal. Emp into <variable>; Pointer moves automatically when a tuple is fetched Close cursor Close High. Sal. Emp;

Example 1 l l Have two tables: Customer & Product When insert a new customer l Put in Marketing table, the customer ID along with the products labeled ‘On. Sale’ Create Trigger New. Cust After Insert On Customer Define the cursor in ‘Declare’ section For Each Row Declare pid number; cursor C 1 is Select product_id From Product Where label = 'On. Sale'; Begin Open the cursor open C 1; Loop over each record at a time Fetch C 1 Into pid; If the fetch returned a record IF (C 1%Found) Then Insert into Marketing(Cust_id, Product_id) values (: new. Id, pid); END IF; Exit When C 1%Not. Found; Customer ID END Loop; close C 1; Close the cursor End; /

Example 2: Another way l Use of the FOR loop with cursors Create Trigger New. Cust After Insert On Customer For Each Row Declare cursor C 1 is Select product_id From Product Where label = 'On. Sale'; Automatically opens the cursor and fetches Begin a record in each iteration For rec In C 1 Loop Insert into Marketing(Cust_id, Product_id) values (: new. Id, rec. product_id); End Loop; End; / Automatically closes the cursor

Cursor Attributes l These are attributes maintained by the system l Assume C 1 is the cursor name l Attributes include: l C 1%ROWCOUNT: The number of tuples in C 1 l C 1%FOUND: TRUE if the last fetch was successful l C 1%NOTFOUND: TRUE if the last fetch was not successful l C 1%ISOPEN: TRUE if C 1 is open

Parameterized Cursor l l l Cursors can take parameters while opening them Very powerful to customize their execution each time Example: Like the previous example, but select products with price < customer’s budget Create Trigger New. Cust After Insert On Customer Define the cursor with a parameter For Each Row Declare cursor C 1 (budget number) is Select product_id From Product p Where p. label = 'On. Sale' and p. price < budget; Begin Pass the value at open time For rec In C 1(: new. budget) Loop Insert into Marketing(Cust_id, Product_id) values (: new. Id, rec. product_id); End Loop; End; /

Summary of Cursors l Efficient mechanism to iterate over a relation tuple-bytuple l Main operations l l l Open, fetch, close Usually used inside loops Cursors can be parameterized l What they return depends on the passed parameters

Today’s Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures

Stored Procedures l What is stored procedure? l l l Piece of code stored inside the DBMS SQL allows you to define procedures and functions and store them inside DBMS Advantages l l Reusability: do not need to write the code again and again Programming language-like environment l l Assignment, Loop, For, IF statements Call it whenever needed l From select statement, another procedure or function

Stored Procedures in Oracle l Stored procedures in Oracle follow a language called PL/SQL l PL/SQL: Procedural Language SQL cs 3431
![Creating A Stored Procedure CREATE [OR REPLACE] PROCEDURE <procedure. Name> [(<param. List>)] AS <local. Creating A Stored Procedure CREATE [OR REPLACE] PROCEDURE <procedure. Name> [(<param. List>)] AS <local.](http://slidetodoc.com/presentation_image_h/0ab45c74bd1865293ff2b5927e77696e/image-15.jpg)
Creating A Stored Procedure CREATE [OR REPLACE] PROCEDURE <procedure. Name> [(<param. List>)] AS <local. Declarations> <procedure. Body>; A parameter in the param. List is specified as: <name> <mode> <type> <mode> is one of {IN, OUT, INOUT} Example: Create Procedure test (id in int, name out string) As Begin …. End; cs 3431

Example Define a variable By default, it is IN In PL/SQL a ‘; ’ ends a line without execution Execute the command create the procedure
![Calling a Stored Procedure l SQL> exec <procedure. Name> [(<param. List>)]; SQL > exec Calling a Stored Procedure l SQL> exec <procedure. Name> [(<param. List>)]; SQL > exec](http://slidetodoc.com/presentation_image_h/0ab45c74bd1865293ff2b5927e77696e/image-17.jpg)
Calling a Stored Procedure l SQL> exec <procedure. Name> [(<param. List>)]; SQL > exec remove_emp (10);

Printing From Stored Procedures Taking three parameters Printing them to screen

Features in Stored Procedures IN parameters Create Procedure profiler_control(start_stop IN VARCHAR 2, run_comm IN VARCHAR 2, ret OUT number) AS ret_code INTEGER; BEGIN ret_code : = 10; IF start_stop NOT IN ('START', 'STOP') THEN ret: = 0; ELSIF start_stop = 'START' THEN ret: = 1; ELSE ret: = ret_code; END IF; END profiler_control; / OUT parameters Variable declaration Variable assignment IF statement

More Features: LOOP Statement CREATE PROCEDURE test. Procedure (name varchar 2(20)) AS num 1 int; BEGIN num 1 : = 10; LOOP INSERT INTO Student VALUES (num 1, name); num 1 : = num 1 + 1; IF (num 1 > 15) THEN EXIT; END IF; END LOOP; END;

More Features: CURSOR & FOR Statement Create Procedure Opening. Bal (p_type IN string) AS cursor C 1 Is Select product. Id, name, price From products where type = p_type; Begin For rec in C 1 Loop Insert into Temp values (rec. product. Id, rec. name, rec. price); End Loop; End; /

Stored Functions l Similar to stored procedures except that they return value CREATE [OR REPLACE] FUNCTION <function. Name> RETURN <type> [(<param. List>)] AS <local. Declarations> <function. Body>;

Using Stored Procedures or Functions l Stored Procedures l l Called from other procedures, functions, triggers, or standalone Stored Functions l In addition to above, can be used inside SELECT statement l In WHERE, HAVING, or projection list

Example CREATE FUNCTION Max. Num() RETURN int AS num 1 int; BEGIN SELECT MAX (s. Number) INTO num 1 FROM Student; RETURN num 1; END; / SQL> Select * from Student where s. Number = Max. Num();

Summary of Stored Procedures/Functions l Code modules that are stored inside the DBMS l Used and called repeatedly l Powerful programing language style l Can be called from other procedures, functions, triggers, or from select statement (only functions)

Today’s Roadmap l Triggers l Assertions l Cursors l Stored Procedures l ODBC/JDBC

ODBC/JDBC l Interfaces that allow applications to connect to a database and execute queries l Applications can be java, C, C++, C# programs l Application makes calls to l l l Connect with the database server Send SQL commands to the database server Get the results back in your program l ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic l JDBC (Java Database Connectivity) works with Java

JDBC l JDBC is a Java API for communicating with database systems supporting SQL l JDBC supports a variety of features for querying and updating data, and for retrieving query results l Model for communicating with the database: l l Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors

JDBC: Code Example Connecting to Oracle DB DB name, port number, user. Id, password Holder for SQL statement

JDBC: Code Example (Cont’d)

ODBC l Similar to JDBC, but has its own syntax l Works with C, C++, C# languages

End of Advanced SQL l Triggers l Assertions l Cursors l Stored Procedures l ODBC/JDBC To check any syntax Google is you friend !!!

Subqueries in DML

Subquery in DML DELETE l DML: Data Manipulation Language 34

Subquery in DML UPDATE 35

Subquery in DML INSERT Any order of columns Follows the order in CREATE TABLE command 36
- Slides: 36