DECLARE
c_id c_customer.customer_name%type;
coin_id1 i_invoice.coin_id%type;
coin_price c_coin.price%type;
delivery i_invoice.delivery_date%type;
CURSOR i_invoice ,c_coin ,c_customer is
SELECT customer_name, coin_id, price ,delivery_date
FROM ((invoice
INNER JOIN customer on invoice.customer_id = customer.customer_id)
INNER JOIN coin on invoice coin_id = coin.coin_id)
WHERE delivery_date BETWEEN '18 MAY 2021' AND '20 MAY 2021'
BEGIN
OPEN i_invoice,c_customer,c_coin;
LOOP
FETCH c_customers into customer_id ,
c_coin into coin_id,
c_coin into price,
i_invoice into delivery_date;
EXIT WHEN i_invloice%notfound;
dbms_output.put_line(customer_id || ' ' || coin_id || ' '
|| price || ' ' || delivery_date);
END LOOP;
CLOSE i_invoice;
END;
/
CodePudding user response：
You are using incorrect syntax for the cursor declaration. The correct code should look alike -
DECLARE
c_id c_customer.customer_name%type;
coin_id1 i_invoice.coin_id%type;
coin_price c_coin.price%type;
delivery i_invoice.delivery_date%type;
-- CURSOR i_invoice ,c_coin ,c_customer is --> This is incorrect declaration.
CURSOR invoice_dtls IS
SELECT customer_name, coin_id, price ,delivery_date
FROM ((invoice INNER JOIN customer on invoice.customer_id = customer.customer_id)
INNER JOIN coin on invoice coin_id = coin.coin_id)
WHERE delivery_date BETWEEN '2021-05-18' AND '2021-05-20'
-- You are missing a semi-colon after this
;
BEGIN
-- OPEN i_invoice,c_customer,c_coin;
OPEN invoice_dtls;
LOOP
/*FETCH c_customers into customer_id ,
c_coin into coin_id,
c_coin into price,
i_invoice into delivery_date;*/
FETCH invoice_dtls
INTO c_id, coin_id1, coin_price, delivery;
EXIT WHEN i_invloice%notfound;
dbms_output.put_line(c_id || ' ' || coin_id1 || ' '
|| coin_price || ' ' || delivery);
END LOOP;
CLOSE i_invoice;
END;
/
CodePudding user response：
You produced quite a big mess. Let me try to pick some errors:
- in
declaresection, you're using one table names (i_invoice,c_coin) while cursor'sfromclause suggest different (invoice,coin) - cursor should have a name, for example
c1. Its name can't bei_invoice ,c_coin ,c_customer. You'd actually fetch cursor into those variables whereclause is wrong - ifdelivery_datecolumn's datatype isdate(I presume it is, as it should be), then don't compare it to strings.'18 MAY 2021'is a string. Date literal (I used) is notopenis as wrong as cursor declaration; you'dopen c1fetchwould fail as well, as there's nocustomer_idin itsselectcolumn list. Also, why are you fetchingc_coininto bothcoin_idandprice?
I'd suggest you to use a cursor for loop instead. You don't have to explicitly declare any variables, open a cursor, fetch into variables, take care about exiting the loop nor closing the cursor. Something like this (with dummy tables I created, just to show that code compiles):
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
Description:
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_NAME VARCHAR2(10)
CUSTOMER_ID NUMBER
SQL> desc invoice
Name Null? Type
----------------------------------------- -------- ----------------------------
COIN_ID NUMBER
DELIVERY_DATE DATE
CUSTOMER_ID NUMBER
SQL> desc coin
Name Null? Type
----------------------------------------- -------- ----------------------------
PRICE NUMBER
COIN_ID NUMBER
Contents:
SQL> select * from customer;
CUSTOMER_N CUSTOMER_ID
---------- -----------
Littlefoot 1
SQL> select * from invoice;
COIN_ID DELIVERY_D CUSTOMER_ID
---------- ---------- -----------
1 19.05.2021 1
SQL> select * from coin;
PRICE COIN_ID
---------- ----------
100 1
Code itself:
SQL> set serveroutput on
SQL> begin
2 for cur_r in (select t.customer_id,
3 t.customer_name,
4 c.coin_id,
5 c.price,
6 i.delivery_date
7 from invoice i join customer t on i.customer_id = t.customer_id
8 join coin c on i.coin_id = c.coin_id
9 where i.delivery_date between date '2021-05-18' and date '2021-05-20'
10 )
11 loop
12 dbms_output.put_line(cur_r.customer_id
13 || ' '
14 || cur_r.coin_id
15 || ' '
16 || cur_r.price
17 || ' '
18 || cur_r.delivery_date);
19 end loop;
20 end;
21 /
1 1 100 19.05.2021
PL/SQL procedure successfully completed.
SQL>
If you insist on doing it your way, at least declare one cursor variable instead of separate variables.
SQL> declare
2 cursor c1 is
3 select t.customer_id,
4 t.customer_name,
5 c.coin_id,
6 c.price,
7 i.delivery_date
8 from invoice i join customer t on i.customer_id = t.customer_id
9 join coin c on i.coin_id = c.coin_id
10 where i.delivery_date between date '2021-05-18' and date '2021-05-20';
11 cur_r c1%rowtype;
12 begin
13 open c1;
14 loop
15 fetch c1 into cur_r;
16 exit when c1%notfound;
17
18 dbms_output.put_line(cur_r.customer_id
19 || ' '
20 || cur_r.coin_id
21 || ' '
22 || cur_r.price
23 || ' '
24 || cur_r.delivery_date);
25 end loop;
26 end;
27 /
1 1 100 19.05.2021
PL/SQL procedure successfully completed.
SQL>