Information required and the question is attached. OrderDetails PK FK1 QrderlD P
ID: 3807016 • Letter: I
Question
Information required and the question is attached.
OrderDetails PK FK1 QrderlD PK,FK2 ProductD Unit Price Quantity Discount has Products PKK ProductD FK1 SupplierID CategoryID ProductName EnglishName Quantity PerUnit UnitPrice Unitsinstock UnitsOnorder ReorderLevel Discontinued has Suppliers PK SupplierID. Address City Province Orders 1 customerID FK FK2 EmployeelD ShipAddress ShipCity ShipPostalCode FK3 ShipperID Required Date Shipped Date Freigh has Shippers Company Name Customers PK CustomerID Company Name ContactTite -has--H Region PostalCode Country Phone Fax Employees PK Emplo LastName FirstName City Province PostalCode Phone BirthDateExplanation / Answer
Lets start by creating a procedure which takes two IN parameters.
We will write a query to get the productid,product name,price whre price is between parameter1 and parameter two.
save this into a cursor.
Now we will iterate through this cursor to display the values.
Here is the code.
Firstly for testing purpose, i will create a table products
create table products(
product_id number,
product_name varchar2(20),
unit_price number
);
Now lets insert some values
insert into products values(1,'A',6);
insert into products values(2,'B',2.6);
insert into products values(3,'C',6.5);
Now, we will create a procedure.
CREATE OR REPLACE PROCEDURE sp_unit_price(p1 IN NUMBER,p2 IN NUMBER) IS
unit_price1 products.unit_price%TYPE:=p1;
unit_price2 products.unit_price%TYPE:=p2;
CURSOR c1 IS
SELECT product_id,product_name,unit_price
FROM products
WHERE unit_price BETWEEN unit_price1 AND unit_price2;
BEGIN
dbms_output.put_line('ProdID '||'ProdName '||'Unit Price');
FOR i IN c1
LOOP
dbms_output.put_line(i.product_id||' '||i.product_name||' '||i.unit_price);
END LOOP;
END;
Now lets execute it.
exec sp_unit_price(5.99,7.99);
And here is the output:
ProdID ProdName Unit Price
3 C 6.5
1 A 6
PRODUCT_ID PRODUCT_NAME UNIT_PRICE 2 B 2.6 3 C 6.5 1 A 6