Introduction to PL/SQL

1. Introduction to PL/SQL CIS 310 Fall,2013

2. SQL:1999 and SQL:200N Enhancements/Extensions (cont.) • Persistent Stored Modules (SQL/PSM) • Capability to create and drop code modules • New statements: • CASE, IF, LOOP, FOR, WHILE, etc. • Makes SQL into a procedural language • Oracle has propriety version called PL/SQL, and Microsoft SQL Server has Transact/SQL

3. Routines and Triggers • Routines • Program modules that execute on demand • Functions–routines that return values and take input parameters • Procedures–routines that do not return values and can take input or output parameters • Triggers–routines that execute in response to a database event (INSERT, UPDATE, or DELETE)

4. What is PL/SQL • Procedural Language SQL • Transaction Processing Language • Uses Program Blocks • Similar to COBOL • Syntax similar to C# and to JAVA • Provides Control Structures • IF logic, Looping, etc. • Used to Build: Functions, Triggers & Procedures • Limited I/O Support – Utility Focus

5. PL/SQL Structure Functions & Procedures • Header CREATE OR REPLACE _____ . . . • Contains parameter specifications, Return type if a function • IS – Optional Section – Contains • Declarations of: temporary variables BEGIN -- Mandatory • Executable block, Contains • SQL statements • SELECT . . . INTO, INSERT, UPDATE, DELETE • PL/SQL Statements • Assignment statements, IF statements, • RETURN statement for functions • END; -- Mandatory

6. DECLARE (IS) SECTION • Contains definitions (variable name & data type) for any temporary variables needed while processing the PL/SQL block) • Standard Oracle data types plus • Ability to map a temporary variable to the data type of a database table column.

7. Variable Declarations • Form is variable_namedata_type(length); t_prod_Descr varchar2(20); t_date date; t_unit_price number (6,2); t_total number (5) := 0;

8. Reference Data Types • Used to declare a temporary variable that References a database column • Takes on the data type of that column • Format is: Variable_nameDB_Table_Name.Column_Name:%TYPE; • For Example: t_qty_orditem_Sold.item_qty_ord%type; t_Prod_descrPRODUCT.Prod_Descrip%TYPE;

9. Executable Section(After the BEGIN Keyword) • Statements in this section are much like the code of a programming language function or procedure • Can contain • Assignment statements • Conditional logic (IF statements) • Loop structures (not covered in this class)

10. Assignment Statements • Assignment operator: := • Variable being assigned to a new value is on left side of assignment operator • New value is on right side of operator student_name := ‘John Miller’; student_name := current_student;

11. Implicit Cursors – SELECT . . . INTO . . . • Created automatically every time you use a SELECT command that returns a single row • Can be used to assign the output of a SELECT command to one or more PL/SQL variables • Can only be used if query returns one and only one record

12. Implicit Cursor Select Statement • Must return only a single row • Uses SELECT col_list INTO variable_list FROM table_name WHERE … • E.g. Select Prod_Code, Standard_Price INTO t_prod_code, t_std_price from PRODUCT Where Prod_Code = ‘RAM9’;

13. Function USE • Extend functionality of Built-In single row functions • Define functions for frequently used computations that return a single value for each row of data processed from a table • Structure like the built-in functions • Supply 0 to many input parameter values • Return exactly 1 value

14. Built-In Funcion Example SELECT to_char(Standard_Price, ‘$9,990.00’), . . . You supply 2 parameter values • Standard_Price a number and • ‘$9,990.00’ a string • The function returns a single value • Its datatype is string • Code has been written to take those parameters and compute the value returned.

15. RETURN Statement • Every function must have a RETURN Statement • Its form is simply: RETURN value • Where value must be a literal or variable that contains a single piece of data of the type that was specified in the Function Header

16. Functions - Syntax CREATE OR REPLACE FUNCTION fn_name [(param_name IN datatype, . . .) RETURN datatype IS scratch variable declarations [if needed] BEGIN function body concluding with RETURN variable_name or value END;

17. Sample Function Using a Cursor • Apex would like its workers to be able to specify a Cust_No and have the total sales to that customer automatically displayed • Total sales to a customer equals the SUM(Sale_Tot_Bill) for all sales orders for that customer

18. Sample Function Using a Cursor CREATE OR REPLACE FUNCTION cust_spend (p_cust_no IN NUMBER) RETURN number IS t_tot_bill sale.sale_tot_bill%type; BEGIN select sum(sale_tot_bill) into t_tot_bill from SALE where Cust_No = p_cust_no; RETURN t_tot_bill; END; /

19. Executing a PL/SQL Programin SQL*Developer • Add a / After the END; at the end of your code to cause your script to execute • Add Show Errors; after that in order to see any errors in your code. • So each PL/SQL Program should end with: END; / Show Errors;

20. Testing a Function in PL/SQL • Must test in the context of a SQL Select • A pseudo table called DUAL is provided for this type of testing (dummy table 1 record no real data) SELECT cust_spend(115) from dual; • Or test in meaningful context select f_name, l_name, str_address, cust_spend(cust_no) from CUSTOMER where City = 'Flagstaff';

21. A solid web-site providing descriptions of fundamentals PL/SQL programming. The links with arrows pointing to them cover the elements we have used in lab and which I will expect you to know on the exam.

22. PL/SQL Selection Structures • Simple IF • Where actions are to be taken only if some condition is true • IF/END IF: IF condition THEN program statements END IF;

23. PL/SQL Selection Structures • IF WITH ELSE CLAUSE • Where actions are to be taken if a condition is true and different actions are to be taken if the condition is false • IF/ELSE/END IF: IF condition THEN program statements ELSE alternate program statements END IF;

24. PL/SQL Selection Structures • COMPOUND Ifs – Multiple conditions to test • 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;

25. IF Structures IF t_item_qty_ord >= 10 THEN t_std_price := t_std_price * .9; ELSE t_std_price := t_std_price * .95; END IF; NOTE: Null values always evaluate to false in an IF statement

26. IF with ELSIF IF t_item_qty_ord >= 10 THEN t_std_price := t_std_price * .9; ELSIF t_item_qty_ord >= 5 THEN t_std_price := t_std_price * .93; ELSE t_std_price := t_std_price * .95; END IF;

27. EXAMPLE • APEX is offering discounts on the products they sell • The discount will be 10% for products whose standard_price is over $100 and • 5% for all other products

28. Sample Function CREATE OR REPLACE FUNCTION apex_discnt (p_std_price IN NUMBER) RETURN NUMBER IS t_price PRODUCT.Standard_Price%TYPE; BEGIN IF p_std_price >= 100 THEN t_price := p_std_price * .9; ELSE t_price := p_std_price * .95; END IF; RETURN t_price; END;