| Author |
 Topic  |
|
|
davewrx
Starting Member
3 Posts |
 Posted - 04/18/2012 : 20:57:42
|
I'm not sure if I am posting in the right section, but I need help on these >_< Thank you.
Write PL/SQL or T-SQL procedures to accomplish the following tasks:
a. Obtain the name and credit limit of the customer whose number currently is stored in
I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and
I_CREDIT_LIMIT, respectively. Output the contents of I_CUSTOMER_NAME and
I_CREDIT_LIMIT.
b. Obtain the order date, customer number, and name for the order whose number currently
is stored in I_ORDER_NUM. Place these values in the variables I_ORDER_DATE,
I_CUSTOMER_NUM and I_CUSTOMER_NAME, respectively. Output the contents of
I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME.
c. Add a row to the ORDERS table.
d. Change the date of the order whose number is stored in I_ORDER_NUM to the date
currently found in I_ORDER_DATE.
e. Delete the order whose number is stored in I_ORDER_NUM.
6. Write a PL/SQL or T-SQL procedure to retrieve and output the part number, part description, warehouse number, and unit price of every part in the item class stored in I_CLASS.
Here are all the tables that will help you answer these questions.
REP TABLE
REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE
CUSTOMER TABLE
CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUM
ORDERS TABLE
ORDER_NUM, ORDER_DATE, CUSTOMER_NUM
ORDER_LINE TABLE
ORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICE
PART TABLE
PART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE |
Edited by - davewrx on 04/18/2012 21:06:29
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 04/18/2012 : 21:09:32
|
sorry we don't do homework here.
Post what you have tried so far
KH
Time is always against us
|
 |
|
|
davewrx
Starting Member
3 Posts |
Posted - 04/18/2012 : 21:49:16
|
quote:
Originally posted by khtan
sorry we don't do homework here.
Post what you have tried so far
KH
Time is always against us
Okay. I did question number 6. Please check if there are any errors. I did my best.
6. Write a PL/SQL or T-SQL procedure to retrieve and output the part number, part description, warehouse number, and unit price of every part in the item class stored in I_CLASS.
CREATE OR REPLACE PROCEDURE DISP_CLASS_ORDERS (I_CLASS IN CLASS%TYPE) AS
I_PART_NUM PART.PART_NUM%TYPE;
I_DESCRIPTION PART.DESCRIPTION%TYPE;
I_WAREHOUSE PART.WAREHOUSE%TYPE;
I_PRICE PART.PRICE%TYPE;
CURSOR PARTGROUP IS
SELECT PART.PART_NUM, DESCRIPTION, WAREHOUSE, PRICE
FROM PART, ORDER_LINE
WHERE PART.PART_NUM = ORDER_LINE.PART_NUM
AND CLASS = I_CLASS;
BEGIN
OPEN PARTGROUP;
LOOP
FETCH PARTGROUP INTO I_PART_NUM, I_DESCRIPTION,
I_WAREHOUSE, I_PRICE;
EXIT WHEN PARTGROUP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (I_PART_NUM);
DBMS_OUTPUT.PUT_LINE (I_DESCRIPTION);
DBMS_OUTPUT.PUT_LINE (I_WAREHOUSE);
DBMS_OUTPUT.PUT_LINE (I_PRICE);
END LOOP;
CLOSE PARTGROUP;
END;
/
..................................................................
These are tables used to answer this question.
REP TABLE
REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE
CUSTOMER TABLE
CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUM
ORDERS TABLE
ORDER_NUM, ORDER_DATE, CUSTOMER_NUM
ORDER_LINE TABLE
ORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICE
PART TABLE
PART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE |
Edited by - davewrx on 04/18/2012 21:51:00 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 04/19/2012 : 01:27:48
|
why do you need cursors? why cant you retrieve them as a batch using select?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
PL/SQL or T-SQL procedure help :(
