Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PL/SQL or T-SQL procedure help :(

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

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]

Go to Top of Page

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) 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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -