OraFAQ Forum: SQL & PL/SQL » assign variable a calculated value in pl/sql

Home » SQL & PL/SQL » SQL & PL/SQL » assign variable a calculated value in pl/sql (sql plus , windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

assign variable a calculated value in pl/sql [message #610512]

Thu, 20 March 2014 16:22

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

I have a created a code that uses the SUM function to calculate a value from other variables, but how do I assign my declared variable the value of that calculated value, keep getting errors. I want to declare "total" as the variable that should be assigned the value of the calculation of SUM(ol_quantity * inv_price). Without total assigned my variable "discount" won't have a basis to go off of. What should I insert and where?

SQL> DECLARE
2 total NUMBER(6,2);
3 discount NUMBER(7,3);
4 CURSOR c_cursor IS
5 SELECT orders.o_id, SUM(ol_quantity * inv_price)
6 FROM orders, order_line, inventory, item
7 WHERE orders.o_id = order_line.o_id
8 AND order_line.inv_id = inventory.inv_id
9 AND inventory.item_id = item.item_id
10 GROUP BY orders.o_id;
11 o_row c_cursor%ROWTYPE;
12 BEGIN
13 DBMS_OUTPUT.PUT_LINE('o_id' || CHR(9) || 'Total Order' || CHR(9) || CHR(9) || 'Discount');
14 DBMS_OUTPUT.PUT_LINE('----' || CHR(9) || '-----------' || CHR(9) || CHR(9) || '--------');
15 OPEN c_cursor;
16 LOOP
17 IF total > 100 AND total < 200
18 THEN discount := total * .1;
19 ELSIF total > 200
20 THEN discount := total * .2;
21 ELSE discount := 0;
22 END IF;
23 FETCH c_cursor INTO o_row;
24 EXIT WHEN c_cursor%NOTFOUND;
25 DBMS_OUTPUT.PUT_LINE(o_row.o_id || CHR(9) || total || CHR(9) || CHR(9) || discount);
26 END LOOP;
27 CLOSE c_cursor;
28 END;
29 /
o_id Total Order Discount
---- ----------- --------
1 0
6 0
2 0
5 0
4 0
3 0

PL/SQL procedure successfully completed.

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610514 is a reply to message #610512]

Thu, 20 March 2014 16:45

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

With regard to your question, are you making it a lot more complicated than necessary? Do you need PL/SQL at all, rather than pure SQL? For example, using the SCOTT demo schema:

orclz>
orclz> select dname,sum(sal) total,case when sum(sal) <10000 then 10 else 20 end as discount
  2  from emp natural join dept group by dname;

DNAME               TOTAL   DISCOUNT
-------------- ---------- ----------
ACCOUNTING           8750         10
RESEARCH            10875         20
SALES                9400         10

orclz>

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610517 is a reply to message #610514]

Thu, 20 March 2014 18:34

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

if simple was allowed, I would have used it. PL/SQL with CURSOR is required, just need a way to assign "total" the value of "SUM(ol_quanity * inv_price)", can't use the SUM equation as it isn't allowed in PL/SQL. Thanks for the reply though

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610518 is a reply to message #610517]

Thu, 20 March 2014 19:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post code is fatally flawed for multiple reasons.
>DBMS_OUTPUT.PUT_LINE(o_row.o_id || CHR(9) || total || CHR(9) || CHR(9) || discount);
O_ID & DISCOUNT are never for the same row; due to the FETCH between where each get assigned a value.
>2 total NUMBER(6,2);
TOTAL is never assigned any value.

You seem to be overly challenged by this homework assignment.

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610519 is a reply to message #610518]

Thu, 20 March 2014 19:07

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

I know that total is never assigned a value, I was asking how to assign it, since my assignments all resulted in errors
I did have "total := SUM(ol_quantity * inv_price"
How would you correct the code, while still being PL/SQL with cursor?

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610520 is a reply to message #610519]

Thu, 20 March 2014 19:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

to start with, rearrange lines like below

LOOP
FETCH c_cursor INTO o_row;
EXIT WHEN c_cursor%NOTFOUND;
-- now when here new record has been read

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610521 is a reply to message #610520]

Thu, 20 March 2014 19:18

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

DECLARE
total NUMBER(6,2);
discount NUMBER(7,3);
CURSOR c_cursor IS
SELECT orders.o_id, SUM(ol_quantity * inv_price)
FROM orders, order_line, inventory, item
WHERE orders.o_id = order_line.o_id
AND order_line.inv_id = inventory.inv_id
AND inventory.item_id = item.item_id
GROUP BY orders.o_id;
o_row c_cursor%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('o_id' || CHR(9) || 'Total Order' || CHR(9) || CHR(9) || 'Discount');
DBMS_OUTPUT.PUT_LINE('----' || CHR(9) || '-----------' || CHR(9) || CHR(9) || '--------');
OPEN c_cursor;
LOOP
FETCH c_cursor INTO o_row;
EXIT WHEN c_cursor%NOTFOUND;
IF total > 100 AND total < 200
THEN discount := total * .1;
ELSIF total > 200
THEN discount := total * .2;
ELSE discount := 0;
END IF;
DBMS_OUTPUT.PUT_LINE(o_row.o_id || CHR(9) || total || CHR(9) || CHR(9) || discount);
END LOOP;
CLOSE c_cursor;
END;

correct?

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610522 is a reply to message #610521]

Thu, 20 March 2014 19:20

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

still, how to assign total the calculation value now?

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610523 is a reply to message #610522]

Thu, 20 March 2014 20:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  DECLARE
  2      v_total  NUMBER(19, 2);
  3      discount NUMBER(19, 3);
  4      CURSOR c_cursor IS
  5        SELECT order_id,
  6               SUM(unit_price * quantity) total
  7        FROM   order_items
  8        GROUP  BY order_id;
  9      o_row    c_cursor%ROWTYPE;
 10  BEGIN
 11      dbms_output.Put_line('o_id'
 12                           || Chr(9)
 13                           || 'Total Order'
 14                           || Chr(9)
 15                           || Chr(9)
 16                           || 'Discount');
 17      dbms_output.Put_line('----'
 18                           || Chr(9)
 19                           || '-----------'
 20                           || Chr(9)
 21                           || Chr(9)
 22                           || '--------');
 23      OPEN c_cursor;
 24      LOOP
 25          FETCH c_cursor INTO o_row;
 26          EXIT WHEN c_cursor%NOTFOUND;
 27          v_total := o_row.total;
 28          IF v_total > 100
 29             AND v_total < 200 THEN
 30            discount := v_total * .1;
 31          ELSIF v_total > 200 THEN
 32            discount := v_total * .2;
 33          ELSE
 34            discount := 0;
 35          END IF;
 36          dbms_output.Put_line(o_row.order_id
 37                               || Chr(9)
 38                               || v_total
 39                               || Chr(9)
 40                               || Chr(9)
 41                               || discount);
 42      END LOOP;
 43      CLOSE c_cursor;
 44* END;
 45  /
o_id    Total Order             Discount
----    -----------             --------
2357    59872.4         11974.48
2358    7826            1565.2
2369    11097.4         2219.48
2383    36374.7         7274.94
2388    282694.3                56538.86
2389    17620           3524
2398    7110.3          1422.06
2403    220             44
2414    10794.6         2158.92
2428    14685.8         2937.16
2430    29669.9         5933.98
2432    10523           2104.6
2433    78              0
2434    268651.8                53730.36
2449    86              0

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610525 is a reply to message #610523]

Thu, 20 March 2014 20:34

kevinb85
Messages: 6
Registered: March 2014
Location: Effingham, IL

Junior Member

SQL> DECLARE
2 v_total NUMBER(6,2);
3 discount NUMBER(7,3);
4 CURSOR c_cursor IS
5 SELECT orders.o_id, SUM(ol_quantity * inv_price) total
6 FROM orders, order_line, inventory, item
7 WHERE orders.o_id = order_line.o_id
8 AND order_line.inv_id = inventory.inv_id
9 AND inventory.item_id = item.item_id
10 GROUP BY orders.o_id;
11 o_row c_cursor%ROWTYPE;
12 BEGIN
13 DBMS_OUTPUT.PUT_LINE('o_id' || CHR(9) || 'Total Order' || CHR(9) || CHR(9) || 'Discount');
14 DBMS_OUTPUT.PUT_LINE('----' || CHR(9) || '-----------' || CHR(9) || CHR(9) || '--------');
15 OPEN c_cursor;
16 LOOP
17 FETCH c_cursor INTO o_row;
18 EXIT WHEN c_cursor%NOTFOUND;
19 v_total := o_row.total;
20 IF v_total > 100 AND v_total < 200
21 THEN discount := v_total * .1;
22 ELSIF v_total > 200
23 THEN discount := v_total * .2;
24 ELSE discount := 0;
25 END IF;
26 DBMS_OUTPUT.PUT_LINE(o_row.o_id || CHR(9) || v_total || CHR(9) || CHR(9) || discount);
27 END LOOP;
28 CLOSE c_cursor;
29 END;
30 /
o_id Total Order Discount
---- ----------- --------
1 379.89 75.978
6 349.84 69.968
2 15.99 0
5 89.9 0
4 119.9 11.99
3 409.9 81.98

PL/SQL procedure successfully completed.

it works, finally!

THANKS BlackSwan!

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610532 is a reply to message #610525]

Fri, 21 March 2014 01:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Note that you don't need PL/SQL to do this, you can do it in a single SQL statement.

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610559 is a reply to message #610532]

Fri, 21 March 2014 04:13

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You should read up on for loops as well. This:

15 OPEN c_cursor;
 16 LOOP
 17 FETCH c_cursor INTO o_row;
 18 EXIT WHEN c_cursor%NOTFOUND;

Can just be:

FOR o_row IN c_cursor LOOP

Then the close cursor statement can be removed along with the declaration of o_row (for loops declare record variables implicitly).

Report message to a moderator

Re: assign variable a calculated value in pl/sql [message #610562 is a reply to message #610559]

Fri, 21 March 2014 04:57

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And then you should read up on the case statement. As the others have pointed out that IF logic can be very easily done in the select itself.

Report message to a moderator

Previous Topic:	Capturing OLD and NEW values from the Parameter of a sored procedure
Next Topic:	count rows grouped by column name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:34:55 CDT 2024