1 / 51

Advanced SQL

Advanced SQL. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Part II. Remaining Lectures. Today  Complete Advanced SQL Dec 6.  Brief Introduction to Transactions and Indexes  Due date for HW4 & Phase 4

lucy-burt
Télécharger la présentation

Advanced SQL

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 Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu Part II

  2. Remaining Lectures • Today  Complete Advanced SQL • Dec 6.  Brief Introduction to Transactions and Indexes  Due date for HW4 & Phase 4 • Dec 9.  Revision + short quiz (from the revision slides) • Dec 13.  Final exam

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

  4. Triggers & Assertions • To enforce complex constraints inside the DBMS, we use Triggers or Assertions • Assertions are part of SQL standards, but not all DBMSs support them • Triggers are more powerful

  5. Recap on Triggers • Three components • Event: When this event happens, the trigger is activated • Condition (optional): If the condition is true, the trigger executes, otherwise skipped • Action: The actions performed by the trigger Event Create Trigger <name> Before| After Insert| Update| Delete On <tableName> Referencing OLD AS oldRec, NEW AS newRec For Each Row | For Each Statement When <condition> Begin …. End; Pointers to old and new records Event Granularity Condition Action

  6. Example: Maintenance of Derived Attributes Keep the bonusattribute in Employee table always 3% of the salaryattribute Create Trigger EmpBonus Before Insert Or Update On Employee For Each Row Begin newRec.bonus := newRec.salary * 0.03; End; Indicate two events at the same time The bonus value is always computed automatically

  7. Beforevs. After • Before Event • When checking certain conditions that may cause the operation to be cancelled • E.g., if the name is null, do not insert • When modifying values before the operation • E.g., if the date is null, put the current date • After Event • When taking other actions that will not affect the current operations • The insert in table X will cause an update in table Y Before Insert Trigger: newRec.x := …. //Changing value x that will be inserted After Insert Trigger: newRec.x := … //meaningless because the value is already inserted

  8. Example For Employees with salary < 50,000, keep the EmpID and salary in table LowSalaryEmp What triggers do we need? • After Insert, After Update, After Delete on Employee Create Trigger EmpUpdate After Update Of salaryOn Employee Referencing OLD AS oldRec, NEW AS newRec For Each Row Begin IF (newRec.sal < 50,000 and oldRec.sal >=50,000) THEN Insert into LowSalaryEmp values (newRec.EmpId, newRec.sal); ELSIF(newRec.sal < 50,000 and oldRec.sal< 50,000) Update LowSalaryEmp set sal = newRec.sal Where empId = newRec.EmpId; ELSIF(newRec.sal>= 50,000 and oldRec.sal < 50,000) Delete from LowSalaryEmp Where empId = newRec.EmpId; END IF; End; Can specify which updated column

  9. cs3431 Combining Multiple Events in One Trigger CREATE TRIGGER salaryRestrictions BEFORE INSERT OR UPDATE ON Professor Referencing OLD AS oldRec, NEW AS newRec For Each Row BEGIN IF (INSERTING AND newRec.salary < 60000) THEN RAISE_APPLICATION_ERROR (-20004, 'below min salary'); END IF; IF (UPDATING AND newRec.salary < oldRec.salary) THEN RAISE_APPLICATION_ERROR (-20004, ‘Salary Decreasing !!'); END IF; END; Can write different code for different events

  10. Summary of Triggers • Powerful mechanisms to enforce constraints in the DBMS • Need to know what triggers to create • On which tables • On which events • Can have many triggers on the same table, possibly of the same type (but different trigger names)

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

  12. Assertions • An expression that should be always true • When created, the expression must be true • DBMS checks the assertion after any change that may violate the expression Must return True or False

  13. Example 1 Sum of loans taken by a customer does not exceed 100,000 Must return True or False (not a relation) Create Assertion SumLoansCheck ( 100,000 >= ALL Select Sum(amount) From borrower B , loan L Where B.loan_number = L.loan_number Group By customer_name );

  14. Example 2 Number of accounts for each customer in a given branch is at most two Create Assertion NumAccountsCheck ( 2 >= ALL Select count(*) From account A , depositor D Where A.account_number = D.account_number Group By customer_name, branch_name );

  15. Example 3 Customer city is always not null Create Assertion CityCheckCheck ( NOT EXISTS ( Select * From customer Where customer_city is null));

  16. Example 4 (Exercise) The customer city must be as the branch city to have an account or a loan in that branch

  17. Assertions vs. Triggers • Assertions do not modify the data, they only check certain conditions • Triggers are more powerful because the can check conditions and also modify the data • Assertions are not linked to specific tables in the database and not linked to specific events • Triggers are linked to specific tables and specific events • All assertions can be implemented as triggers (one or more) • Not all triggers can be implemented as assertions

  18. Example: Trigger vs. Assertion All new customers opening an account must have opening balance >= $100. However, once the account is opened their balance can fall below that amount. Trigger Event: Before Insert We need triggers, assertions cannot be used Create Trigger OpeningBal Before Insert On Customer Referencing NEW AS newRec For Each Row Begin IF (newRec.bal is null or newRec.bal < 100) Then RAISE_APPLICATION_ERROR(-20004, ‘Balance should be >= $100’); End IF; End;

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

  20. 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>;

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

  22. Example Create Trigger OpeningBal After Insert On Customer Declare p_Id int; p_name string; p_price number(7,2); cursor C1 Is //define the cursor Select productId, name, price From products where type = ‘new’; Begin open C1; //opened the cursor Loop fetch C1 into p_id, p_name, p_price; //fetched the first tuple IF (C1%Found) THEN // make sure the fetch was successful …. ELSE exit; // break the loop END IF; End Loop; close C1; // close the cursor End;

  23. Another Way Create Trigger OpeningBal After Insert On Customer Declare cursor C1 Is Select productId, name, price From products where type = ‘new’; Begin For rec in C1 Loop //opened the cursor Insert into Temp values (rec.productId, rec.name, rec.price); // fetch values End Loop;// close the cursor End;

  24. Cursor Attributes • These are attributes maintained by the system • 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

  25. Parameterized Cursor • Cursors can take parameters while opening them Create Trigger OpeningBal After Insert On Customer ReferenceNEW as newRec Declare cursor C1(type_Of_Interest string) Is Select productId, name, price From products where type = type_Of_Interest; Begin For rec in C1(newRec.type) Loop Insert into Temp values (rec.productId, rec.name, rec.price); End Loop; End; Define a parameter Pass the parameter

  26. 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

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

  28. 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

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

  30. 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}

  31. 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

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

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

  34. Features in Stored Procedures IN parameters OUT parameters • Create Procedure profiler_control(start_stop IN VARCHAR2, • run_comm IN VARCHAR2, • ret OUT BOOLEAN) AS • ret_code INTEGER; • BEGIN •  ret_code:=10; •   IF ret_code !=0 THEN •    ret:=FALSE; •   ELSIF start_stop NOT IN ('START','STOP') THEN •    ret:=FALSE; •   ELSIF start_stop = 'START' THEN •    ret:=FALSE; •    ELSE •     ret:=FALSE; •   END IF; • ENDprofiler_control; • / Variable declaration Variable assignment IF statement

  35. More Features: LOOP Statement CREATE PROCEDURE testProcedure(name string) 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;

  36. 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; /

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

  38. 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

  39. 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();

  40. 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)

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

  42. 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

  43. 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

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

  45. JDBC: Code Example (Cont’d)

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

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

  48. Subqueries in DML

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

  50. Subquery in DML UPDATE

More Related