SQL Training Procedures Functions DB Procedures Functions Procedures
SQL Training Procedures & Functions
DB Procedures & Functions Procedures and Functions are pre-defined pieces of code that perform database work (like a view). Procedures are typically used to perform and action - insert, update and delete statements. They do not return data. Functions run select statements and return a single* value. Functions can be run from a select statement. Procedures cannot. * Functions can be modified to return multiple values but then cannot be used in a select statement which is how they provide their value. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Procedures
PL/SQL –DB Procedure Sample Create or Replace Procedure Distributor. Update (in_Distributor. ID IN Integer, in_Distributorname IN Var. Char 2, in_Distributor. First. Name IN Var. Char 2, in_Distributor. Last. Name IN Var. Char 2, in_Phone IN Var. Char 2, in_Fax IN Var. Char 2, in_City IN Var. Char 2, in_Postal. Code IN Var. Char 2, in_Discount. Rate IN Number) as begin Update distributor Set distributorname = in_Distributor. Name, distributorfirstname = in_Distributor. First. Name, distributorlastname = in_Distributor. Last. Name, phone = in_Phone, fax = in_Fax, city = in_City, postalcode = in_Postal. Code, discountrate = in_Discount. Rate where distributorid = in_Distributor. ID; Commit; end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Using a DB Procedure Create or Replace Procedure Update. Address (in_distributorid In Integer, in_street IN Var. Char 2, in_city IN Var. Char 2, in_statecode IN Var. Char 2, in_zip IN Var. Char 2, in_phone IN Var. Char 2, in_fax IN Var. Char 2) as begin Update Distributor Set address 1 = in_street, city = in_city, provinceabbreviation = in_statecode, postalcode = in_zip, phone = in_phone, fax = in_fax Where distributorid = in_distributorid; Commit; end; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – A Definition PL/SQL is an Oracle language that extends the capabilities of SQL. Create or Replace Procedure name AS Declaration Section Variables and Cursors A PL/SQL procedure contains three sections: Declaration, Executable, and Exception. In PL/SQL you can develop logic that can be executed in: SQL*Plus A Stored Procedure A Stored Function Database Trigger Package Executable Section Procedural & SQL statements Exception Section Error handling statements End; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL Procedures – A Definition The syntax for the Create Procedure command is as follows: Create Procedure Syntax: create [or replace] procedurename [ (argument IN | OUT | IN OUT datatype) ] … as variable datatype; … begin … … exception … … end; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Datatypes In addition to the normal Oracle SQL datatypes, PL/SQL allows you to declare variables with these datatypes. The maximum length of a variable name is 30 characters. Boolean Binary_Integer Natural Positive %Type Can be assigned the constants True or False. Integers in the range – 2, 147, 483, 647 to 2, 147, 483, 647. Integers from 0 to 2, 147, 483, 647. Integers from 1 to 2, 147, 483, 647. A Table. Column’s datatype. You must declare all variables and constants that are referenced in the PL/SQL statements. SQL Cursors must also be declared. Example: Create or Replace Procedure Add. Distributor (in_distributorid IN Integer, in_name IN String) as my_address Distributor. Address 1%Type; begin My_Address : = 'Unknown'; Insert into Distributor(Distributor. ID, Distributor. Name, Address 1) values (in_distributorid, in_name, my_address); end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Operators & Delimiters + * / = < > ; ' " <> != <= >= : = || -/* */ << >> <space> <tab> <cr> Addition Operator Subtraction Operator Multiplication Operator Division Operator Equality Operator Less than Operator Greater than Operator Statement terminator Character String Delimiter Quoted String Delimiter Not Equal To Operator Same as <> Less Than Or Equal To Operator Greater Than or Equal To Operator Assignment Operator Concatenation Operator Single Line Comment Multiple Line Comments Label Delimiters Space Tab Carriage return Examples: My. User_Nm : = 'Temp' || My. User_ID; My_Address : = 'Unknown'; My_discount : = My_discount * 0. 75; If My. Salary < 50000 then RAISE Salary_Too_Low; End If; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – If /Then / Else Syntax IF condition THEN statement; … statement; [ELSIF condition THEN statement; … statement; ] [ELSE statement; … statement; ] END IF; Example: IF My_price < 5000 THEN My_price : = My_price * 1; ELSE My_price : = My_price * 0. 75; END IF; • Special Notes: The ELSIF and ELSE clauses are optional. An IF statement can have multiple ELSIF clauses but only one ELSE clause. ELSIF is valid. ELSEIF is invalid. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – IS Null Condition v_Number 1 Number; v_Number 2 Number, v_Result Varchar 2(5); begin; … IF v_Number 1 IS NULL or v_Number 2 IS NULL then v_Result : = ‘Unknown’; ELSIF v_Number 1 < v_Number 2 then v_Result : = ‘Yes”; ELSE v_Result : = ‘No’; END IF; END; The IS NULL condition will evaluate to TRUE only if the variable it is checking is NULL. If the variable IS NOT NULL, the condition will evaluate to FALSE. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Loop / End Loop Syntax LOOP statement; … statement; END LOOP; Example: create or replace procedure Loop. Sample as My_Count Positive : = 1; Max_Loops constant positive : =100; begin LOOP My_Count : = My_Count + 1; EXIT WHEN My_Count > Max_Loops; END LOOP; end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – While / Loop Syntax WHILE condition LOOP statement; … statement; END LOOP; Example: create or replace procedure While. Loop. Sample as My. User_ID Positive : = 1; My. User_Nm Var. Char 2(30); begin WHILE My. User_ID < 101 LOOP My. User_Nm : = ‘Temp’ || My. User_ID; insert into Users (User. ID, User. Name, Role. ID, Password) values (My. User_ID, My. User_Nm, 5, 'Change. Me'); My. User_ID : = My. User. ID + 1; END LOOP; end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – For / Loop Syntax FOR loop-variable IN [REVERSE] lower. . upper LOOP statement; … statement; END LOOP; Example: create or replace procedure For. Loop. Sample as My. User_Nm Var. Char 2(30); Max_Loops Positive : = 100; begin FOR My. User_ID IN 1. . Max_Loops LOOP My. User_Nm : = 'Temp' || My. User_ID; INSERT INTO Users (User. ID, User. Name, Role. ID, Password) VALUES (My. User_ID, My. User_Nm, 5, 'Change. Me'); END LOOP; end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – GOTO Statement GOTO Label; Example: cur_value positive : = 1; max_loops positive : = 10; begin loop if cur_value > max_loops then GOTO blast_off; else cur_value : = cur_value + 1; end if; end loop; <<blast_off>> …. end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – SQL Syntax (Select) SELECT A SELECT statement retrieves data from the database into PL/SQL variables. The form of a SELECT statement is as follows: SELECT select-list INTO variable-list FROM table-reference WHERE where-clause GROUP BY group-by-clause ORDER BY order-by-clause; Example: select orderdate into in_orderdate from orders where orderid = in_orderid; Note: This form of the SELECT statement should return not more than one row. If more than one row must be return, you must use a CURSOR to retrieve each row individually. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Cursor Processing BASE TABLE A Cursor is used to process multiple rows retrieved from the database. Using a cursor, your program can step through the set of returned rows one at a time, processing each row in turn. Record. Set CURSOR SQL Statement BASE TABLE Cursor Functions: DECLARE the recordset OPEN the recordset FETCH a row and move through the recordset CLOSE the recordset • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Cursor Processing Example Use a Procedure with a Cursor to save all work in progress (or unshipped orders). Note: WIP Work in Progress Create or Replace Procedure unconfirmed. Orders as my_orderid Orders. Order. ID%type; cursor my_cursor is select order. ID from orders where orderstatuscode = 'O'; begin -- Delete all records in the WIP table -- Then Open the cursor. -- Insert all cursor rows into the WIP Table delete from WIP; open my_cursor; loop fetch my_cursor into my_orderid; exit when my_cursor%notfound; insert into WIP(Order. ID) values (my_orderid); end loop; commit; close my_cursor; end; / • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – SQL Syntax (Insert) INSERT The form of a INSERT statement is as follows: INSERT INTO table-reference ( column-name, column-name…) VALUES (expression, expression. . . ); Example: Insert Into distributor (distributorid, distributorname, distributorfirstname, distributorlastname) Values (in_distributorid, in_distributorname, in_firstname, in_lastname); Note: Since all character fields are declared as Var. Char 2 fields, there is no need to enclose them in quotes. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – SQL Syntax (Update) UPDATE The form of a UPDATE statement is as follows: Update table-reference SET column-name = expression, … WHERE where-clause; Example: Update Distributor Set address 1 = in_street, city = in_city, provinceabbreviation = in_state, postalcode = in_zip Where distributor. ID = in_distributorid; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – SQL Syntax (Delete) DELETE The form of a DELETE statement is as follows: DELETE FROM table-reference WHERE [where-clause] [CURRENT OF cursor-name]; Example: Delete from Orders Where Order. ID = in_orderid; Note: The Where Current of cursor-name option allows you to delete the record that is being pointed to by the Cursor. For this to work, the Cursor must be declared as an updateable Cursor. See Cursor Processing. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Functions
PL/SQL Functions – A Definition A Function can return a value to the caller. The value is returned to the caller through the use of a RETURN keyword within the function. Functions can be referenced directly in SQL Queries. Create Function Syntax: create [or replace] functionname [ (argument IN | OUT | IN OUT datatype) ] … return datatype is argument datatype; begin … return (argument); exception … end; Note: Every Function must have a return clause. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL Function Example Create or Replace Function Shipping. Charge(in_orderid IN Integer) return Integer is Shipping. Charge integer; begin SELECT Sum(Weight*Order. Qty*Carrier. Mileage. Rate*Mileage)+ (Fixed. Cost. Amount+ Delivery. Charge. Rate) AS Shipping. Cost into Shipping. Charge FROM Delivery, CARRIER, Orders, Order. Detail, Distributor, Product, Distance, v_weight WHERE Delivery. Code = Orders. Delivery. Code AND CARRIER. Carrier. ID = Orders. Carrier. ID AND Orders. Order. ID = Order. Detail. Order. ID AND Orders. Distributor. ID = Distributor. ID AND Distributor. Warehouse. ID = Distance. Warehouse. ID AND Orders. PRovince. ID = Distance. Province. ID AND Order. Detail. Product. ID = Product. ID AND Product. Code = v_weight. Model AND Orders. Order. ID = in_orderid Group by Fixed. Cost. Amount, Delivery. Charge. Rate; return Shipping. Charge; end; Select Order. ID, Shipping. Charge(Orderid) as Shipping. Charge from Orders Where Order. ID = 1001; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
PL/SQL – Handling Exceptions Predefined Exceptions: DUP_VAL_ON_INDEX INVALID_NUMBER NO_DATA_FOUND TOO_MANY_ROWS VALUE_ERROR Duplicate value for index column. SQL statement specifies an invalid number. Select statement doesn’t return any rows. A Select statement has retrieved more than 1 row. Truncation or conversion error. User-Defined Exceptions: Salary_Too_Low exception; My. Salary NUMBER(10, 2); begin Select Salary from Person Into My. Salary Where person = in_person; If My. Salary < 50000 then RAISE Salary_Too_Low; End If; return (‘Salary OK’); exception When Salary_Too_Low then return (‘Salary too low’); end; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Exception Handling Example Create or Replace Function Shipping. Charge(in_orderid IN Integer) return Integer is Shipping. Charge integer; begin SELECT Sum(Weight*Order. Qty*Carrier. Mileage. Rate*Mileage)+ (Fixed. Cost. Amount+ Delivery. Charge. Rate) AS Shipping. Cost into Shipping. Charge FROM Delivery, CARRIER, Orders, Order. Detail, Distributor, Product, Distance, v_weight WHERE Delivery. Code = Orders. Delivery. Code AND CARRIER. Carrier. ID = Orders. Carrier. ID AND Orders. Order. ID = Order. Detail. Order. ID AND Orders. Distributor. ID = Distributor. ID AND Distributor. Warehouse. ID = Distance. Warehouse. ID AND Orders. PRovince. ID = Distance. Province. ID AND Order. Detail. Product. ID = Product. ID AND Product. Code = v_weight. Model AND Orders. Order. ID = in_orderid Group by Fixed. Cost. Amount, Delivery. Charge. Rate; return Shipping. Charge; exception when NO_DATA_FOUND then return(0); Note: This code uses a view named v_weight. This view is supplied in the _Create. Views. sql file within the student’s starter database folder. end; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Showing Errors Create or Replace Procedure Update. Address (in_distributorid In Integer, in_street IN Var. Char 2, in_city IN Var. Char 2, in_statecode IN Var. Char 2, in_zip IN Var. Charxxx, in_phone IN Var. Char 2, in_fax IN Var. Char 2) as begin Update Distributor Set address 1 = in_street, city = in_city, provinceabbreviation = in_statecode, postalcode = in_zip, phone = in_phone, fax = in_fax Where distributorid = in_distributorid; Commit; end; / show errors • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Test Procedures • Create or Replace Procedure Update. Address (in_distributorid In Integer, in_street IN Var. Char 2, in_city IN Var. Char 2, in_statecode IN Var. Char 2, in_zip IN Var. Char 2, in_phone IN Var. Char 2, in_fax IN Var. Char 2) as • begin • Update Distributor Set • address 1 = in_street, • city = in_city, • provinceabbreviation = in_statecode, • postalcode = in_zip, • phone = in_phone, • fax = in_fax • Where distributorid = in_distributorid; • Commit; • end; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Test & Use the Function Create or Replace Function Shipping. Charge(in_orderid IN Integer) return Integer is Shipping. Charge integer; begin SELECT Sum(20*Order. Qty*Carrier. Mileage. Rate*Mileage) +(Fixed. Cost. Amount+ Delivery. Charge. Rate) AS Shipping. Cost into Shipping. Charge FROM Delivery, CARRIER, Orders, Order. Detail, Distributor, Product, distance WHERE Delivery. Code = Orders. Delivery. Code AND CARRIER. Carrier. ID = Orders. Carrier. ID AND Orders. Order. ID = Order. Detail. Order. ID AND Orders. Distributor. ID = Distributor. ID AND Distributor. Warehouse. ID = Distance. Warehouse. ID AND Orders. PRovince. ID = Distance. Province. ID AND Order. Detail. Product. ID = Product. ID AND Orders. Order. ID = in_orderid GROUP BY Fixed. Cost. Amount, Delivery. Charge. Rate; return Shipping. Charge; exception when NO_DATA_FOUND then return(0); end; / show errors; • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Test & Use the Function • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
Workshop
Exercise: Creating Procedures 1. Create a procedure to add an orderdetail line. • Use any existing orderid • Populate all orderdetail columns 2. Create an orderdetail update procedure • Change the order quantity 3. Create a function to return the line amount (productprice * orderqty) for any order detail line. 4. Create a function to return the total order amount for any order. • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.
- Slides: 32