2- Advanced SQL (PL-SQL).pptx

Chapter 5: Advanced SQL  Outline Structure of PL/SQL Functions and Procedures Triggers 5.2  Roadmap  Motivation for PL/SQL  Anonymous vs. Named PL/SQL Programs Basic PL/SQL Features Basic Struct...

Chapter 5: Advanced SQL  Outline Structure of PL/SQL Functions and Procedures Triggers 5.2  Roadmap  Motivation for PL/SQL  Anonymous vs. Named PL/SQL Programs Basic PL/SQL Features Basic Structure Variables and Assignment Control Structures (if … then, loop)  Using SQL in PL/SQL Insert, Update, Delete Implicit Cursor (Select) Explicit Cursor (Select) 5.3 3  Is SQL Powerful Enough?  Do you need to write multiple SQL statements for a task? E.g., to transfer a certain amount from your checking account to saving, only transfer when there is enough balance in checking  Does SQL have variables, loops, if then else …?  How can others use the SQL statements you developed?  What if some errors appear when you execute SQL queries? 5.4 4  Why PL/SQL  Solution 1: embed SQL into a procedure language such as JAVA or C E.g., JDBC, ODBC Client program sends SQL to DB server and gets back intermediate results Client program then computes credit score  Problems? JAVA program What's my SQL credit score Intermediate results 700! 5.5 5  Why PL/SQL  Oracle Procedure Language/SQL SQL + procedure language features such as variables, if…then, loop, function or procedure, etc. A named PL/SQL program is stored within the database, and can be used by other users  Benefits Communication cost Portability Security Learning curve 5.6 6  Why PL/SQL  Oracle Procedure Language/SQL Downside?  Which DMBS to use?  Does PL/SQL provide a GUI?  Memory usage? 5.7 7  Structure of PL/SQL PL/SQL is Block Structured A block is the basic unit from which all PL/SQL programs are built. A block can be named (functions and procedures) or anonymous 5.8  Anonymous PL/SQL Programs (blocks)  Program has no name  Code is NOT stored in database (discarded after execution)  Code can be stored as a script file in local file system, but you have to manually save & load it  Cannot accept or pass parameter values 5.9  Anonymous PL/SQL Programs (blocks)  Basic Structure: declare declaration of variables; begin statements; exception handle exceptions; end;  The subprogram is unnamed and can be used only once  declare section is optional  exception section is also optional  Must have begin … end; 5.10  Named PL/SQL Programs (blocks)  Stored As compiled objects in database (functions, procedures)  Can take parameters as input  Can be called by other programs  Can be executed by other users 5.11  PL/SQL Program Lines  May span multiple text editor lines  Each line ends with a semicolon  Text is NOT case sensitive 5.12 12  Comment Statements  Block of comments are delimited with  Single comment line starts with 2 hyphens -- comment on a single line 5.13 13  Displaying PL/SQL Output  Normally PL/SQL is used with other Oracle utilities such as forms or reports  You will learn to use PL/SQL in SQL Developer  Command to enable output from PL/SQL programs: SET SERVEROUTPUT ON; You need to turn on serveroutput every time you log in 5.14 14  Displaying PL/SQL Output  Use DBMS_OUTPUT package to output DBMS_OUTPUT.PUT(item); output the item without end-of-line  Item can be either varchar2 or number, integer, … DBMS_OUTPUT.PUT_LINE(item); output an end-of-line 5.15  Displaying PL/SQL Output Example declare greeting varchar2(50) := 'Hello World'; x integer := 10; Begin dbms_output.put(greeting); dbms_output.put_line(‘ Hello World'); dbms_output.put_line(x); dbms_output.put_line(10); End; Output 5.16  Variables  Typically used to store some values  Steps using a variable declare variable_name data_type; begin variable_name := value; -- assign value to a variable -- use variables (e.g., in assignment, output, SQL, etc.) dbms_output.put_line(variable_name); end;  E.g., declare x number; begin x:= 10; dbms_output.put_line('x is ' || x); end; 5.17 17  Declaration  Syntax for declaring a variable: variable_name data_type_declaration;  Example: current_s_id number; 5.18 18  Scope  Scope: the range where the variable is valid  Valid in the begin … end block after it is declared E.g., declare declare x1 number; x number; begin x1 := 10; begin declare x:= 10; x2 number; begin end; x2 := 20; end;  x is not valid outside the program end; 5.19 19  Assignment Statements  Assignment operator: :=  Variable being assigned to a new value is on the left side of the assignment operator  New value is on the right side of the operator student_name := 'John Miller'; student_name := current_student; 5.20 20  Referring to Column Data Type  Use data type of a DB column table-name.column-name%type: same data type as that column  Examples declare e_name emp.ename%type; begin e_name := 'john'; insert into emp values (5, e_name, 2, date '2006-9-1',55000); end;  Question: benefits? 5.21 21  Row Type Variable  table-name%rowtype: assumes data type of entire row, where each column as a field of this type  Use variable_name.column_name to refer to a field of the variable  Often used in insert or store results of a SQL query  Question: benefits? 5.22 22  Row Type  Examples declare rec1 dept%rowtype; begin rec1.did := 3; rec1.dname := 'service'; insert into dept values rec1; end; did dname eid ename did hiredat salary e Dept Emp 23 5.23  Common Errors  Miss % between table name and rowtype  Miss. between row type variable name and column name  dbms_output.put_line() can not take a rowtype input, so you must use row_type_variable.column_name E.g., dbms_output.put_line(new_emp.ename); 5.24 24  PL/SQL Control Structures  Conditional: if and case statements  Loop and exit 5.25 25  PL/SQL Selection Structures  IF/END IF: IF condition THEN program statements END IF; 5.26 26  PL/SQL Selection Structures  IF/ELSIF: IF condition1 THEN program statements; ELSIF condition2 THEN alternate program statements; ELSIF condition3 THEN alternate program statements;... ELSE alternate program statements; END IF; 5.27 27  PL/SQL Comparison Operators  Same as SQL =, , >=, 10 and x < 20  Comparison with NULL values: if x is null then… if x is not null then …  Can not use: if x = null then (later) 5.28 28  PL/SQL Loops  Loop: repeats one or more program statements multiple times until an exit condition is reached  Often needed when we process results of a SQL query declare total integer; i integer; begin i := 1; -- initialization before loop total := 0; -- initialization before loop loop total := total + i; -- repeated action i:= i + 1; -- update loop variable if any exit when i > 10; -- check exit condition end loop; dbms_output.put_line(' total is ' || total); end; 5.29 29  Numeric FOR Loop FOR counter_variable IN [reverse ] start_value.. end_value LOOP program statements END LOOP;  No need to declare counter_variable. If it is not declared, it is only valid in loop (can not use it before or after loop)  Can not modify counter_variable inside the loop  start_value always lower than end_value  Use reverse if you want to start from end_value  The loop repeats end_value – start_value +1 times  Using exit when statement when you want to jump out of for loop earlier 5.30 30  Numeric FOR Loop declare total integer := 0; begin FOR i IN 1..10 LOOP total := total + i; END LOOP; dbms_output.put_line(total); End; 5.31 31  Numeric FOR Loop  No need to declare counter_variable. If it is not declared, it is only valid in loop (can not use it before or after loop) declare total integer := 0; begin for i in 1..10 loop Any error? total := total + i; end loop; dbms_output.put_line(i); end; 5.32 32  Numeric FOR Loop  Can not modify counter_variable inside the loop begin for i in 1..10 loop i := i*3; Any error? dbms_output.put_line(i); end loop; end; 5.33 33  Numeric FOR Loop  Each iteration, the value of counter_variable changes by 1  There is no formal syntax to change it in PL/SQL!  But there are some coding tricks you can use…  Example: if I want to use the following counter_variable: 2, 5, 8, 11, 14 declare step integer := 3; 1 2 3 4 5 begin i*3-1 for i in 1..5 loop 2 5 8 11 14 dbms_output.put_line(i*step-1); end loop; end; 5.34 34  Numeric FOR Loop  start_value is always lower than (or equal to) end_value declare declare total integer := 0; total integer := 0; begin begin for i in 10..1 loop for i in reverse 1..10 loop total := total + i; total := total + i; end loop; end loop; dbms_output.put_line(total); dbms_output.put_line(total); end; end; No error message. But you will not get the right output. 5.35 35  Numeric FOR Loop  start_value and end_value of a FOR LOOP statement can be either numeric values, numeric variables, or numeric expressions. declare declare total integer := 0; total integer := 0; bound integer := 10; bound integer := 5; begin begin for i in 1..bound loop for i in 1..bound*2 loop total := total + i; total := total + i; end loop; end loop; dbms_output.put_line(total); end; dbms_output.put_line(total); end; 5.36 36  Numeric FOR Loop  Using exit when statement when you want to jump out of for loop earlier  Or you can use exit in an if statement declare declare total integer := 0; total integer := 0; begin begin for i in 1..10 loop for i in 1..10 loop total := total + i; total := total + i; exit when i>5; if i>5 then end loop; exit; dbms_output.put_line(total); end if; end; end loop; dbms_output.put_line(total); end; 5.37 37  Using SQL Commands in PL/SQL Programs  Can directly use insert, update, delete, commit, rollback 5.38 38  Using SQL Commands in PL/SQL Programs  Can use select through cursors Need a way to store results of select to local variables Cursor is a variable pointing to a result row A loop is often needed if results contain multiple rows  Can use Data Definition Statements through dynamic SQL DDL including create, drop, alter, grant, revoke 5.39 39  Using Insert, Update, Delete  Can use them as in SQL, i.e., not referring to any variables begin insert into Emp values (5, 'alice', 1, date '2006-9-1', 45000); end; 5.40 40  Using Insert, Update, Delete  Can refer to any initialized variables where a value is expected declare dept_id number; begin dept_id := 2; update emp set did = dept_id where eid = 4; end; 5.41 41  Insert Using Row-type Variable declare x emp%rowtype; begin x.eid := 5; x.ename := 'alice'; x.did := 1; x.hiredate := date '2006-9-1'; x.salary := 45000; insert into Emp values x; end; 5.42 42  Using Select  Must use a cursor to refer to results  Following example will get an error begin select eid, ename from Emp; end; 5.43 43  Cursor  Pointer to a server memory location  Contains information about the results of a SQL command in a PL/SQL program Called the command’s context area Number of Parsed Cursor rows command processed statement CID CALLID CNAME CCREDIT 1 MIS 101 Intro. to Info. Systems 3 2 MIS 301 Systems Analysis 3 active set 3 MIS 441 Database Management 3 4 CS 155 Programming in C++ 3 5 MIS 451 Client/Server Systems 3 5.44 44  Types of Cursors  Implicit  Easier to use  Results must be EXACTLY one row  Explicit  More complex to use  Results can be one row, multiple rows, or even empty  Cursor can not appear in assignment or expressions 5.45 45  Implicit Cursors SELECT column1, column2,… INTO variable1, variable2, … FROM tablename WHERE search_condition; Can only be used if query returns one and only one record Variables must have been declared and have the same type as results For strings, make sure the variable’s size is long enough to store the results (that’s why column type is useful) 5.46 46  Implicit Cursor Syntax (cont.) declare e_id number; e_name varchar2(50); begin select eid, ename into e_id, e_name from emp where eid = 1; dbms_output.put_line('e_id is: ' || e_id || ' e_name is: ' || e_name); end; 5.47 47  Common Errors  SQL statement does not return 1 row  Forget to define variable used to store the result  The variable’s data type does not match the result (use row type or column type will avoid this problem)  SQL statement error (use correct table-name, column-name, do not confuse with variables). You can test your SQL statement first 5.48 48  No Rows Returned declare e_id number; e_name varchar2(50); begin select eid, ename into e_id, e_name from emp where eid = 10; dbms_output.put_line('e_id is: ' || e_id || ' e_name is: ' || e_name); end; eid ename did hiredate salary 1 Jeff 1 2005-1-1 70000 did dname 2 Susan 2 2005-6-1 50000 1 IT 3 Bob 1 2000-1-1 90000 2 HR 4 Steve 1 2006-1-1 60000 Dept 5.49 Emp 49  No Rows Returned declare e_id number; e_name varchar2(50); begin select eid, ename into e_id, e_name from emp where eid = 10; dbms_output.put_line('e_id is: ' || e_id || ' e_name is: ' || e_name); end;  An exception (no data found) will be reported, solution? 5.50 50  No Rows Returned declare e_id number; e_name varchar2(50); begin select eid, ename into e_id, e_name from emp where eid = 10; dbms_output.put_line('e_id is: ' || e_id || ' e_name is: ' || e_name); end;  An exception (no data found) will be reported, solution? 5.51 51  Exception declare e_id number; e_name varchar2(50); begin select eid, ename into e_id, e_name from Emp where eid = 10; dbms_output.put_line('e_id is: ' || e_id || ' e_name is: ' || e_name); exception when no_data_found then dbms_output.put_line('no rows found'); when too_many_rows then dbms_output.put_line('too many rows'); end; 5.52 52  Exception Syntax exception when exception_name then … -- handle exception when exception_name then … -- handle exception when others then … -- hand all other exceptions 5.53 53  Exceptions for Implicit Cursor  no_data_found (case insensitive)  too_many_rows 5.54 54  Tips for Implicit Cursor  Use it only for two cases: where condition on primary key (zero or one rows) aggregations without group-by (1 row)  Otherwise use explicit cursor (next) 5.55 55  Explicit Cursors  Must be declared in program’s DECLARE section  Can be used if query returns multiple records or no records 5.56 56  Using an Explicit Cursor  Declare the cursor  Open the cursor  Fetch the cursor result into PL/SQL program variables using a loop  Make sure check exit condition in loop (what condition to check?)  Close the cursor 5.57 57  Template declare cursor cursor_name IS SQL-statements; variable-definitions; begin open cursor_name loop FETCH cursor_name INTO variable_name(s); exit when cursor_name%notfound; -- handle results, e.g., print them out end loop; close cursor_name; end; 5.58 58  Fetching Explicit Cursor Records declare cursor c1 IS select ename, dname from Emp, Dept where Dept.did = Emp.did; -- cursor definition e_name Emp.ename%type; -- variables to hold results e_dept Dept.dname%type; -- variables to hold results begin open c1; -- open cursor loop fetch c1 into e_name, e_dept; -- fetch exit when c1%NOTFOUND; --- exit check dbms_output.put_line('name = ' || e_name || ', dept = ' || e_dept); -- print end loop; close c1; -- close cursor end; 5.59 59  Common Errors  Cursor definition error: make sure you try out the SQL statement before defining the cursor Do NOT use variable names that are the same as tables or column names  Forget to check exit condition (never do this!)  Forget to open cursor  Forget to fetch cursor in the loop body  Forget to use a loop  Forget to close cursor  Wrong order in the loop (recommended to be (1) fetch; (2) exit test; and (3) process results;) 5.60 60  Stick to the Order of Fetch, Exit, and Print declare cursor cursor_name IS SQL-statements; variable-definitions; begin open cursor_name loop FETCH cursor_name INTO variable_name(s); exit when cursor_name%notfound; -- handle results, e.g., print them out end loop; close cursor_name; end; 5.61 61 Functions and Procedures 5.62  Functions and Procedures Functions and procedures allow “business logic” to be stored in the database and executed from SQL statements. These can be defined either by the procedural component of SQL or by an external programming language such as Java, C, or C++. 5.63  SQL Functions The syntax for creating a function is as follows: CREATE [OR REPLACE] FUNCTION function_name (parameter datatype) RETURN datatype IS BEGIN function body RETURN return_value; END; 5.64  Declaring SQL Functions Define a function that, given the ID of an instructor, returns the salary of that instructor. create or replace function return_salary (instructor_id number) return number is sal number; begin select salary into sal from instructor where id = instructor_id ; return sal; end; 5.65  Invoking SQL Functions functions can be invoked (called) either from an SQL procedure or from anonymous program: Example of invoking the function return_salary from anonymous program with id 10101: declare i_sal number; begin i_sal := return_salary(10101); dbms_output.put_line(i_sal); end; 5.66  SQL Procedures  A procedure is a named (stored) module that performs one or more actions; it does not need to return any values.  The syntax for creating a procedure is as follows: CREATE OR REPLACE PROCEDURE procedure_name (parameter datatype) IS | AS local variable declarations BEGIN procedure body END; 5.67  Declaring SQL Procedures  Using the instructor table, create a procedure get_salary with an input parameter id, and print the salary of the instructor with that id. create or replace procedure get_salary (instructor_id int ) is instr_salary int; begin select salary into instr_salary from instructor where id = instructor_id; dbms_output.put_line('salary of ' || instructor_id || ' is ' || instr_salary); end; 5.68  Invoking SQL Procedures  Procedures can be invoked (called) either from an SQL procedure, an anonymous block or from embedded SQL, using the call statement.  Example of invoking the procedure get_salary from anonymous program with id 10101: begin get_salary(10101); end;  Example of invoking the procedure get_salary with id 10101, using the call statement: call get_salary(10101); 5.69 Triggers 5.70  Triggers A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. To design a trigger mechanism, we must:  Specify the conditions under which the trigger is to be executed.  Specify the actions to be taken when the trigger executes. Use of Database Triggers  Enforce complex constraints or business rules  E.g., an account cannot have a negative balance  Audit the changes to the tables  A trigger is similar to SQL procedures but, triggers CANNOT accept parameters 5.71  Triggering Events and Actions in SQL  Triggering event can be insert, delete or update  Triggers on update can be restricted to specific attributes For example, after update of takes on grade  Two types of triggers in terms of timing Before: the trigger is executed before the modification occurs in database After: the trigger is executed after the modification 5.72  Two Types of Triggers  Statement Level: trigger fires once, regardless of how many rows are modified Typically for auditing purpose The trigger program can see the whole table being modified  Row Level: trigger fires once for each row that is affected For most other situations The trigger program can see the current row being modified, but NOT other rows  Through two system variables: new and old 5.73  Creating a Statement-Level Trigger CREATE OR REPLACE TRIGGER trigger_name {BEFORE|AFTER} {INSERT or UPDATE or DELETE} ON table_name [DECLARE declare local variables here] BEGIN trigger body END; -- {} is must -- [] is optional -- X | Y means X OR Y Use OR if you want to fire the trigger in multiple types 5.74  a Statement-Level Trigger Example  Create a trigger on the instructor table, and fires it when someone update, insert, or delete the instructor table. The trigger will print out the user who does the modification and the time. The ‘user’ system variable holds the current user. create or replace trigger audit_instructor after insert or update or delete on instructor begin dbms_output.put_line(user||' modifies the table instructor on '||sysdate); end;  We can test the trigger audit_instructor by updating the instructor table update instructor set salary = salary + 500; 5.75  Creating a Row-Level Trigger CREATE OR REPLACE TRIGGER trigger_name {BEFORE|AFTER} {INSERT or UPDATE or DELETE} ON table_name FOR EACH ROW [WHEN (condition)] [DECLARE …] BEGIN trigger body END; --{} is must --[] is optional --X | Y means X or Y 5.76  a Row-Level Trigger Example  Create a row-level trigger on instructor such that if the department name (dept_name) gets changed, print out: name + ‘has changed dept from’ + dept_name + ‘to’ dept_name. create or replace trigger change_inst_dept after update on instructor for each row when (new.dept_name old.dept_name) begin dbms_output.put_line(:new.name||' has changed dept from ' ||:old.dept_name || ' to '||:new.dept_name) ; end;  We can test the trigger change_inst_dept by updating the instructor table update instructor set dept_name='Finance' where instructor.id=10101; 5.77 End of Chapter 5 5.78