PLSQL Assignment 1 PLSQL Create or Replace XXXXXXX

PL/SQL Assignment 1

PL/SQL Create or Replace XXXXXXX Service. Order. Checkout (in_service. Order. ID IN Integer, in_statusid IN Integer, in_userid IN Integer, in_timeestimate IN Varchar 2, in_actionnotes IN Long) as begin • Procedure or function? Update Service. Order • What is the main action Set So. Status. ID = in_statusid, being performed? Assigned. User. Id = in_userid, timeestimate = in_timeestimate • How many variables are where service. Order. ID = in_service. Order. ID; being passed in? Commit; Update Service. Order. Action Set Action. Notes = in_actionnotes, checkout = sysdate where so. Action. ID = (Select max(so. Action. ID) from serviceorderaction where serviceorderid = in_service. Order. ID); Commit; Exception when NO_DATA_FOUND then return; end;

PL/SQL create or replace XXXXXX 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; • Procedure or function? • What is the main action being performed? • How many variables are being passed in? 3 9/16/2020

PL/SQL create or replace XXXXXX Calc_Contract(in_modalityid IN Integer, in_Plan. Type In Integer, in. Site. Cost IN Integer) return Number is Contract. Price Number; Base. Cost Number; Avg. Price Number; Begin SELECT round(avg(Retail. Price), 2) into Avg. Price FROM Modality, Product WHERE Modality. ID = Product. Modality. ID and Modality. ID = in_Modality. ID; IF in_Plan. Type = 1 Then Base. Cost : = 0; End IF; IF in_Plan. Type = 2 Then Base. Cost : = 8; End IF; IF in_Plan. Type = 3 Then Base. Cost : = 6; End IF; IF in_Plan. Type = 4 Then Base. Cost : = 4; End IF; Contract. Price : = (Avg. Price * (Base. Cost/100) + In. Site. Cost); return Contract. Price; end; • Procedure or function? • What is the main action being performed? • How many variables are being passed in? 4 9/16/2020

Create a Function Following the example on slide 22 of the Session 1 PPT deck, create a function. Name the function with your initials + assignment 1 (e. g. , jcassignment 1). The function will summarize the sales amount for an order based on the data in the orderitems table. To Do: 1. Create a return integer named total_sale. 2. Line amount is quantity * item_price. 3. Compile the function. 4. Run a select statement to test the function. 5 9/16/2020