Home » SQL & PL/SQL » SQL & PL/SQL » Explict Cursor
Explict Cursor [message #204172] Sat, 18 November 2006 10:04 Go to next message
powder
Messages: 7
Registered: November 2006
Junior Member
I need to display:

PS3 Deluxe, Blue, $200.99, 10, $2009.90
PS3 Deluxe, Black, $200.99, 20, $4019.80
PS3 Deluxe, Red, $200.99, 30, $6029.70
Total Amount: $12,059.40

from the following data using explicit cursor:

SQL> select * from inventory;

INV_ID P_ID COLOR INV_COST INV_QT
---------- ---------- -------------------- ---------- ----------
1 1 Blue 200.99 10
2 1 Black 200.99 20
3 1 Red 200.99 30
4 2 Blue 190.99 10

SQL> select * from color;

COLOR
--------------------
Black
Blue
Red

SQL> select * from product;

P_ID P_DESC
---------- ------------------------------
1 PS3 Deluxe
2 Xbox Deluxe


This is my code:

DECLARE
v_p_id product.p_id%TYPE:= 1;
v_p_desc product.p_desc%TYPE;
v_inv_id inventory.inv_id%TYPE;
v_inv_cost inventory.inv_cost%TYPE;
v_inv_qt inventory.inv_qt%TYPE;
v_color color.color%TYPE;
CURSOR inventory_cursor IS
SELECT p_desc, color.color, inv_cost, inv_qt
FROM product, inventory, color
WHERE product.p_id=inventory.p_id;
inventory_row inventory_cursor%ROWTYPE;
v_amount VARCHAR2(10);
v_total VARCHAR2(10);
BEGIN
OPEN inventory_cursor;
LOOP
FETCH inventory_cursor INTO inventory_row;
EXIT WHEN inventory_cursor%NOTFOUND;
v_amount := inventory_row.inv_cost*inventory_row.inv_qt;
DBMS_OUTPUT.PUT_LINE(v_p_desc || ' ' || v_color || ' ' || TO_CHAR(inventory_row.inv_cost, '$99.99') || ' ' || inventory_row.inv_qt || ' ' || TO_CHAR(v_amount, '$9,999.99'));
END LOOP;
v_total := v_amount;
DBMS_OUTPUT.PUT_LINE('Total Value: ' || TO_CHAR(v_amount, '$9,999.99'));
CLOSE inventory_cursor;
END;
/

This is the incorrect output from the code:

####### 10 $2,009.90
####### 10 $2,009.90
####### 10 $2,009.90
####### 20 $4,019.80
####### 20 $4,019.80
####### 20 $4,019.80
####### 30 $6,029.70
####### 30 $6,029.70
####### 30 $6,029.70
####### 10 $1,909.90
####### 10 $1,909.90
####### 10 $1,909.90
Total Value: $1,909.90

PL/SQL procedure successfully completed.

Appreciate any help on fixing the code. Thanks!
Re: Explict Cursor [message #204180 is a reply to message #204172] Sat, 18 November 2006 12:19 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi powder,

I am sending you the corrected version of you pl/sql. You have made some errors in taking the join between table.I have removed the '$' sign because it is not working in here rest is correct.

DECLARE
v_p_id product.p_id%TYPE:= 1;
v_p_desc product.p_desc%TYPE;
v_inv_id inventory.inv_id%TYPE;
v_inv_cost inventory.inv_cost%TYPE;
v_inv_qt inventory.inv_qty%TYPE;
v_color color.color%TYPE;
CURSOR inventory_cursor IS
select pro.p_desc,col.color,inv.inv_cost,inv.inv_qty
from inventory inv,color col,product pro
where inv.p_id = pro.p_id
and inv.color= col.color
and inv.p_id = v_p_id
order by inv.inv_qty;
inventory_row inventory_cursor%ROWTYPE;
v_amount VARCHAR2(10);
v_total VARCHAR2(10);
BEGIN
OPEN inventory_cursor;
LOOP
FETCH inventory_cursor INTO inventory_row;
EXIT WHEN inventory_cursor%NOTFOUND;
v_amount := inventory_row.inv_cost*inventory_row.inv_qty;
DBMS_OUTPUT.PUT_LINE(inventory_row.p_desc || ' ' || inventory_row.color || ' ' || inventory_row.inv_cost
|| ' '|| inventory_row.inv_qty || ' ' || v_amount );
END LOOP;
v_total := v_amount;
DBMS_OUTPUT.PUT_LINE('Total Value: ' || v_amount);
CLOSE inventory_cursor;
END;
/

Where are you from?

Bye,
Ashu
Re: Explict Cursor [message #204198 is a reply to message #204180] Sat, 18 November 2006 16:19 Go to previous messageGo to next message
powder
Messages: 7
Registered: November 2006
Junior Member
Thanks Ashu. I'm in Los Angeles. This is the output after the change.

PS3 Deluxe Blue 200.99 10 2009.9
PS3 Deluxe Black 200.99 20 4019.8
PS3 Deluxe Red 200.99 30 6029.7
Total Value: 6029.7

The Total Value is not correct though. This code

DBMS_OUTPUT.PUT_LINE('Total Value: ' || sum(v_amount));

generate an error message of:

ERROR at line 28:
ORA-06550: line 28, column 42:
PLS-00204: function or pseudo-column 'SUM' may be used inside a SQL statement
only
ORA-06550: line 28, column 2:
PL/SQL: Statement ignored

Appreciate your advise.
Re: Explict Cursor [message #204225 is a reply to message #204198] Sun, 19 November 2006 05:21 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why would you bother with PL/SQL when the same can be done using SQL*Plus?
SQL> set numformat 999,990.00
SQL> break on p_desc skip 1
SQL> compute sum of amount on p_desc
SQL>
SQL> SELECT p.p_desc, i.color, i.inv_cost, i.inv_cost * i.inv_qt amount
  2  FROM PRODUCT p, INVENTORY i
  3  WHERE p.p_id = i.p_id
  4  ORDER BY p.p_desc;

P_DESC               COLOR                   INV_COST      AMOUNT
-------------------- -------------------- ----------- -----------
ps3 deluxe           blue                      200.99    2,009.90
                     black                     200.99    4,019.80
                     red                       200.99    6,029.70
********************                                  -----------
sum                                                     12,059.40

xbox deluxe          blue                      190.99    1,909.90
********************                                  -----------
sum                                                      1,909.90


SQL>

But, if you insist on PL/SQL procedure, try this:
DECLARE
  l_sum_amount NUMBER;
BEGIN
  FOR cur_product IN (SELECT p.p_id, p.p_desc FROM PRODUCT p)
  LOOP
    l_sum_amount := 0;
    FOR cur_inv IN (SELECT i.color, i.inv_cost, i.inv_cost * i.inv_qt amount
                    FROM INVENTORY i
                    WHERE i.p_id = cur_product.p_id)
    LOOP
      dbms_output.put_line
        (LPAD(cur_product.p_desc, 20, ' ')       ||', '|| 
         LPAD(cur_inv.color, 10, ' ')            ||', '||
         TO_CHAR(cur_inv.inv_cost, '999,990.00') ||', '||
         TO_CHAR(cur_inv.amount, '999,990.00'));

      l_sum_amount := l_sum_amount + cur_inv.amount;
    END LOOP;

    dbms_output.put_line
      ('Total: ' || LPAD(TO_CHAR(l_sum_amount, '999,990.00'), 51, ' ') 
       || CHR(10));

  END LOOP;
END;
/

ps3 deluxe,        blue,      200.99,    2,009.90
ps3 deluxe,       black,      200.99,    4,019.80
ps3 deluxe,         red,      200.99,    6,029.70
Total:                                  12,059.40

xbox deluxe,       blue,      190.99,    1,909.90
Total:                                   1,909.90


PL/SQL procedure successfully completed.
Re: Explict Cursor [message #204233 is a reply to message #204225] Sun, 19 November 2006 10:30 Go to previous message
powder
Messages: 7
Registered: November 2006
Junior Member
Thank you all very much!
Previous Topic: Grouping and Merging Data
Next Topic: how to insert data into a column
Goto Forum:
  


Current Time: Fri Dec 02 19:01:54 CST 2016

Total time taken to generate the page: 0.22019 seconds