SQL Training Procedures & Functions

1. SQL Training Procedures & Functions

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

3. Procedures

4. PL/SQL –DB Procedure Sample Create or Replace Procedure DistributorUpdate (in_DistributorID IN Integer, in_Distributorname IN VarChar2, in_DistributorFirstName IN VarChar2, in_DistributorLastName IN VarChar2, in_Phone IN VarChar2, in_Fax IN VarChar2, in_City IN VarChar2, in_PostalCode IN VarChar2, in_DiscountRate IN Number) as begin Update distributor Set distributorname = in_DistributorName, distributorfirstname = in_DistributorFirstName, distributorlastname = in_DistributorLastName, phone = in_Phone, fax = in_Fax, city = in_City, postalcode = in_PostalCode, discountrate = in_DiscountRate where distributorid = in_DistributorID; Commit; end; /

5. Using a DB Procedure Create or Replace Procedure UpdateAddress (in_distributorid In Integer, in_street IN VarChar2, in_city IN VarChar2, in_statecode IN VarChar2, in_zip IN VarChar2, in_phone IN VarChar2, in_fax IN VarChar2) as begin Update Distributor Set address1 = in_street, city = in_city, provinceabbreviation = in_statecode, postalcode = in_zip, phone = in_phone, fax = in_fax Where distributorid = in_distributorid; Commit; end;

6. PL/SQL – A Definition Create or Replace Procedure name AS PL/SQL is an Oracle language that extends the capabilities of SQL. 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 Declaration Section Variables and Cursors Executable Section Procedural & SQL statements Exception Section Error handling statements End;

7. PL/SQL Procedures – A Definition The syntax for the Create Procedure command is as follows: Create Procedure Syntax: create [or replace] procedure procedurename [ (argument IN | OUT | IN OUT datatype) ] … as variable datatype; … begin … … exception … … end;

8. 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 Can be assigned the constants True or False. Binary_Integer Integers in the range –2,147,483,647 to 2,147,483,647. Natural Integers from 0 to 2,147,483,647. Positive Integers from 1 to 2,147,483,647. %Type 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 AddDistributor (in_distributorid IN Integer, in_name IN String) as my_address Distributor.Address1%Type; begin My_Address := 'Unknown'; Insert into Distributor(DistributorID, DistributorName, Address1) values (in_distributorid, in_name, my_address); end; /

9. PL/SQL – Operators & Delimiters + 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> Space <tab> Tab <cr> Carriage return Examples: MyUser_Nm := 'Temp' || MyUser_ID; My_Address := 'Unknown'; My_discount := My_discount * 0.75; If MySalary < 50000 then RAISE Salary_Too_Low; End If;

10. PL/SQL – If /Then / Else Syntax IF condition THEN statement; … statement; [ELSIF 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.

11. PL/SQL – IS Null Condition v_Number1 Number; v_Number2 Number, v_Result Varchar2(5); begin; … IF v_Number1 IS NULL or v_Number2 IS NULL then v_Result := ‘Unknown’; ELSIF v_Number1 < v_Number2 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.

12. PL/SQL – Loop / End Loop Syntax LOOP statement; … statement; END LOOP; Example: create or replace procedure LoopSample 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; /

13. PL/SQL – While / Loop Syntax WHILE condition LOOP statement; … statement; END LOOP; Example: create or replace procedure WhileLoopSample as MyUser_ID Positive := 1; MyUser_Nm VarChar2(30); begin WHILE MyUser_ID < 101 LOOP MyUser_Nm := ‘Temp’ || MyUser_ID; insert into Users (UserID, UserName, RoleID, Password) values (MyUser_ID, MyUser_Nm,5,'ChangeMe'); MyUser_ID := MyUserID + 1; END LOOP; end; /

14. PL/SQL – For / Loop Syntax FOR loop-variable IN [REVERSE] lower..upper LOOP statement; … statement; END LOOP; Example: create or replace procedure ForLoopSample as MyUser_Nm VarChar2(30); Max_Loops Positive := 100; begin FOR MyUser_ID IN 1..Max_Loops LOOP MyUser_Nm := 'Temp' || MyUser_ID; INSERT INTO Users (UserID, UserName, RoleID, Password) VALUES (MyUser_ID, MyUser_Nm,5,'ChangeMe'); END LOOP; end; /

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

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

17. PL/SQL – Cursor Processing 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. BASE TABLE RecordSet CURSOR SQL Statement BASE TABLE Cursor Functions: DECLARE the recordset OPEN the recordset FETCH a row and move through the recordset CLOSE the recordset

18. 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 unconfirmedOrders as my_orderid Orders.OrderID%type; cursor my_cursor is select orderID 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(OrderID) values (my_orderid); end loop; commit; close my_cursor; end; /

19. 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 VarChar2 fields, there is no need to enclose them in quotes.

20. 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 address1 = in_street, city = in_city, provinceabbreviation = in_state, postalcode = in_zip Where distributorID = in_distributorid;

21. 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 OrderID = 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.

22. Functions

23. 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] function functionname [ (argument IN | OUT | IN OUT datatype) ] … return datatype is argumentdatatype; begin … return (argument); exception … end; Note: Every Function must have a return clause.

24. PL/SQL Function Example Create or Replace Function ShippingCharge(in_orderid IN Integer) return Integer is ShippingCharge integer; begin SELECT Sum(Weight*OrderQty*CarrierMileageRate*Mileage)+ (FixedCostAmount+ DeliveryChargeRate) AS ShippingCost into ShippingCharge FROM Delivery, CARRIER, Orders, OrderDetail, Distributor, Product, Distance, v_weight WHERE Delivery.DeliveryCode = Orders.DeliveryCode AND CARRIER.CarrierID = Orders.CarrierID AND Orders.OrderID = OrderDetail.OrderID AND Orders.DistributorID = Distributor.DistributorID AND Distributor.WarehouseID = Distance.WarehouseID AND Orders.PRovinceID = Distance.ProvinceID AND OrderDetail.ProductID = Product.ProductID AND Product.ProductCode = v_weight.Model AND Orders.OrderID = in_orderid Group by FixedCostAmount,DeliveryChargeRate; return ShippingCharge; end; Select OrderID, ShippingCharge(Orderid) as ShippingCharge from Orders Where OrderID = 1001;

25. PL/SQL – Handling Exceptions Predefined Exceptions: DUP_VAL_ON_INDEX Duplicate value for index column. INVALID_NUMBER SQL statement specifies an invalid number.   NO_DATA_FOUND Select statement doesn’t return any rows. TOO_MANY_ROWS A Select statement has retrieved more than 1 row. VALUE_ERROR Truncation or conversion error. User-Defined Exceptions: Salary_Too_Low exception; MySalary NUMBER(10,2); begin Select Salary from Person Into MySalary Where person = in_person; If MySalary < 50000 then RAISE Salary_Too_Low; End If; return (‘Salary OK’);   exception When Salary_Too_Low then return (‘Salary too low’); end;

26. Exception Handling Example Create or Replace Function ShippingCharge(in_orderid IN Integer) return Integer is ShippingCharge integer; begin SELECT Sum(Weight*OrderQty*CarrierMileageRate*Mileage)+ (FixedCostAmount+ DeliveryChargeRate) AS ShippingCost into ShippingCharge FROM Delivery, CARRIER, Orders, OrderDetail, Distributor, Product, Distance, v_weight WHERE Delivery.DeliveryCode = Orders.DeliveryCode AND CARRIER.CarrierID = Orders.CarrierID AND Orders.OrderID = OrderDetail.OrderID AND Orders.DistributorID = Distributor.DistributorID AND Distributor.WarehouseID = Distance.WarehouseID AND Orders.PRovinceID = Distance.ProvinceID AND OrderDetail.ProductID = Product.ProductID AND Product.ProductCode = v_weight.Model AND Orders.OrderID = in_orderid Group by FixedCostAmount,DeliveryChargeRate; return ShippingCharge; exception when NO_DATA_FOUND then return(0); end; Note: This code uses a view named v_weight. This view is supplied in the _CreateViews.sql file within the student’s starter database folder.

27. Showing Errors Create or Replace Procedure UpdateAddress (in_distributorid In Integer, in_street IN VarChar2, in_city IN VarChar2, in_statecode IN VarChar2, in_zip IN VarCharxxx, in_phone IN VarChar2, in_fax IN VarChar2) as begin Update Distributor Set address1 = 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

28. Test Procedures • Create or Replace Procedure UpdateAddress (in_distributorid In Integer, in_street IN VarChar2, in_city IN VarChar2, in_statecode IN VarChar2, in_zip IN VarChar2, in_phone IN VarChar2, in_fax IN VarChar2) as • begin • Update Distributor Set • address1 = in_street, • city = in_city, • provinceabbreviation = in_statecode, • postalcode = in_zip, • phone = in_phone, • fax = in_fax • Where distributorid = in_distributorid; • Commit; • end;

29. Test & Use the Function Create or Replace Function ShippingCharge(in_orderid IN Integer) return Integer is ShippingCharge integer; begin SELECT Sum(20*OrderQty*CarrierMileageRate*Mileage) +(FixedCostAmount+ DeliveryChargeRate) AS ShippingCost into ShippingCharge FROM Delivery, CARRIER, Orders, OrderDetail, Distributor, Product, distance WHERE Delivery.DeliveryCode = Orders.DeliveryCode AND CARRIER.CarrierID = Orders.CarrierID AND Orders.OrderID = OrderDetail.OrderID AND Orders.DistributorID = Distributor.DistributorID AND Distributor.WarehouseID = Distance.WarehouseID AND Orders.PRovinceID = Distance.ProvinceID AND OrderDetail.ProductID = Product.ProductID AND Orders.OrderID = in_orderid GROUP BY FixedCostAmount, DeliveryChargeRate; return ShippingCharge; exception when NO_DATA_FOUND then return(0); end; / show errors;

30. Test & Use the Function

31. Workshop

32. Exercise: Creating Procedures • Create a procedure to add an orderdetail line. • Use any existing orderid • Populate all orderdetail columns • Create an orderdetail update procedure • Change the order quantity • Create a function to return the line amount (productprice * orderqty) for any order detail line. • Create a function to return the total order amount for any order.