SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PL/SQL or T-SQL procedure help :(
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davewrx
Starting Member

3 Posts

Posted - 04/18/2012 :  20:57:42  Show Profile  Reply with Quote
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
17434 Posts

Posted - 04/18/2012 :  21:09:32  Show Profile  Reply with Quote
sorry we don't do homework here.

Post what you have tried so far


KH
Time is always against us

Go to Top of Page

davewrx
Starting Member

3 Posts

Posted - 04/18/2012 :  21:49:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/19/2012 :  01:27:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000