1 / 28

Introduction to PL/SQL

Introduction to PL/SQL. CIS 310 Fall,2013. SQL:1999 and SQL:200 N 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

tamber
Download Presentation

Introduction to PL/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. 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;

More Related