1 / 36

Advanced SQL: Cursors & Stored Procedures

Advanced SQL: Cursors & Stored Procedures. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Today ’ s Roadmap. Views Triggers Assertions Cursors Stored Procedures. Cursors: Introduction. Select statement may return many records. Select e mpID , name, salary From Employee

Télécharger la présentation

Advanced SQL: Cursors & Stored Procedures

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. Today’s Roadmap • Views • Triggers • Assertions • Cursors • Stored Procedures

  3. Cursors: Introduction • Select statement may return many records SelectempID, name, salary From Employee Wheresalary > 120,000; Get 0 or more records • What if inside a trigger: • 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…

  4. What is a Cursor • A mechanism to navigate tuple-by-tuple over a relation • Typically used inside triggers, stored procedures, or stored functions • Main Idea • When we execute a query, a relation is returned • It is stored in private work area for the query • Cursor is a pointer to this area • Move the cursor to navigate over the tuples • Creating Cursor CursorHighSalEmpIS Select empID, name, salary From Employee Where salary > 120,000; Cursor<name>IS<SQL query>;

  5. Creating a Cursor Cursor name Any query can go here Cursor<name>IS<SQL query>; CursorHighSalEmpIS Select empID, name, salary From Employee Where salary > 120,000;

  6. Cursor Operations CursorHighSalEmpIS Select empID, name, salary From Employee Where salary > 120,000; • Create cursor • Open cursor • Execute the query and put the pointer at the first tuple • Fetch next tuple • Pointer moves automatically when a tuple is fetched • Close cursor Open HighSalEmp; Fetch HighSalEmpinto <variable>; Close HighSalEmp;

  7. Example 1 • Have two tables: Customer & Product • When insert a new customer • Put in Marketing table, the customer ID along with the products labeled ‘OnSale’ Create Trigger NewCust After Insert On Customer For Each Row Declare pid number; cursor C1 isSelectproduct_idFrom Product Where label = 'OnSale'; Begin open C1; Loop Fetch C1 Intopid; IF (C1%Found) Then Insert into Marketing(Cust_id, Product_id) values (:new.Id, pid); END IF; Exit WhenC1%NotFound; END Loop; closeC1; End; / Define the cursor in ‘Declare’ section Open the cursor Loop over each record at a time If the fetch returned a record Customer ID Close the cursor

  8. Example 2: Another way • Use of the FOR loop with cursors Create Trigger NewCust After Insert On Customer For Each Row Declare cursor C1 isSelectproduct_idFrom Product Where label = 'OnSale'; Begin For rec In C1 Loop Insert into Marketing(Cust_id, Product_id) values (:new.Id, rec.product_id); End Loop; End; / Automatically opens the cursor and fetches a record in each iteration Automatically closes the cursor

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

  10. Parameterized Cursor • 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 Define the cursor with a parameter Create Trigger NewCust After Insert On Customer For Each Row Declare cursor C1 (budget number) isSelectproduct_idFrom Product p Wherep.label = 'OnSale' and p.price < budget; Begin For rec In C1(:new.budget) Loop Insert into Marketing(Cust_id, Product_id) values (:new.Id, rec.product_id); End Loop; End; / Pass the value at open time

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

  12. Today’s Roadmap • Views • Triggers • Assertions • Cursors • Stored Procedures

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

  14. cs3431 Stored Procedures in Oracle • Stored procedures in Oracle follow a language called PL/SQL • PL/SQL: Procedural Language SQL

  15. cs3431 Creating A Stored Procedure Example: Create Procedure test (id in int, name out string) As Begin …. End; CREATE [OR REPLACE] PROCEDURE <procedureName> [(<paramList>)] AS <localDeclarations> <procedureBody>; A parameter in the paramList is specified as: <name> <mode> <type> <mode> is one of {IN, OUT, INOUT}

  16. Example Define a variable By default, it is IN In PL/SQL a ‘;’ ends a line without execution Execute the command and create the procedure

  17. Calling a Stored Procedure • SQL>exec <procedureName> [(<paramList>)]; SQL > exec remove_emp (10);

  18. Printing From Stored Procedures Taking three parameters Printing them to screen

  19. Features in Stored Procedures • Create Procedure profiler_control(start_stop IN VARCHAR2, • run_comm IN VARCHAR2, • 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; • ENDprofiler_control; • / IN parameters OUT parameters Variable declaration Variable assignment IF statement

  20. More Features: LOOP Statement CREATE PROCEDURE testProcedure(name varchar2(20)) AS num1 int; BEGIN num1 := 10; LOOP INSERT INTO Student VALUES (num1, name); num1 := num1 + 1; IF (num1 > 15) THEN EXIT; END IF; END LOOP; END;

  21. More Features: CURSOR & FOR Statement Create Procedure OpeningBal (p_type IN string) AS cursor C1 Is Select productId, name, price From products where type = p_type; Begin For rec in C1 Loop Insert into Temp values (rec.productId, rec.name, rec.price); End Loop; End; /

  22. Stored Functions • Similar to stored procedures except that they return value CREATE [OR REPLACE] FUNCTION <functionName> RETURN<type> [(<paramList>)] AS <localDeclarations> <functionBody>;

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

  24. Example CREATE FUNCTION MaxNum() RETURN int AS num1 int; BEGIN SELECT MAX (sNumber) INTO num1 FROM Student; RETURN num1; END; / SQL> Select * from Student where sNumber = MaxNum();

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

  26. Today’s Roadmap • Triggers • Assertions • Cursors • Stored Procedures • ODBC/JDBC

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

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

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

  30. JDBC: Code Example (Cont’d)

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

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

  33. Subqueries in DML

  34. Subquery in DML DELETE • DML: Data Manipulation Language

  35. Subquery in DML UPDATE

  36. Subquery in DML INSERT Any order of columns Follows the order in CREATE TABLE command

More Related