Author |
Topic |
davewrx
Starting Member
3 Posts |
Posted - 2012-04-18 : 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 inI_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME andI_CREDIT_LIMIT, respectively. Output the contents of I_CUSTOMER_NAME andI_CREDIT_LIMIT.b. Obtain the order date, customer number, and name for the order whose number currentlyis 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 ofI_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 datecurrently 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 TABLEREP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATECUSTOMER TABLECUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUMORDERS TABLEORDER_NUM, ORDER_DATE, CUSTOMER_NUMORDER_LINE TABLEORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICEPART TABLEPART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-18 : 21:09:32
|
sorry we don't do homework here. Post what you have tried so far KH[spoiler]Time is always against us[/spoiler] |
|
|
davewrx
Starting Member
3 Posts |
Posted - 2012-04-18 : 21:49:16
|
quote: Originally posted by khtan sorry we don't do homework here. Post what you have tried so far KH[spoiler]Time is always against us[/spoiler]
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) ASI_PART_NUM PART.PART_NUM%TYPE;I_DESCRIPTION PART.DESCRIPTION%TYPE;I_WAREHOUSE PART.WAREHOUSE%TYPE;I_PRICE PART.PRICE%TYPE;CURSOR PARTGROUP ISSELECT PART.PART_NUM, DESCRIPTION, WAREHOUSE, PRICEFROM PART, ORDER_LINEWHERE PART.PART_NUM = ORDER_LINE.PART_NUMAND CLASS = I_CLASS;BEGINOPEN 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 TABLEREP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATECUSTOMER TABLECUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUMORDERS TABLEORDER_NUM, ORDER_DATE, CUSTOMER_NUMORDER_LINE TABLEORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICEPART TABLEPART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 01:27:48
|
why do you need cursors? why cant you retrieve them as a batch using select?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|