mohamed saber
asked on
how to fetch data from multiple row variable that returned from procedure
/*that's procedure*/
create or replace procedure updatesalary(did in number , sumsalary out employee%rowtype)
is
begin
update employee set salary=salary*2 where dno=did;
select * into sumsalary from employee where dno=did;
end;
/*------------------------
/*-----------------*/
SET SERVEROUTPUT ON;
declare
out_ employee%rowtype;
tmp employee%rowtype;
cursor cur;
begin
UPDATESALARY(1,out_);
/*how to fetch data from out_*/
end;
/*-----------------*/
create or replace procedure updatesalary(did in number , sumsalary out employee%rowtype)
is
begin
update employee set salary=salary*2 where dno=did;
select * into sumsalary from employee where dno=did;
end;
/*------------------------
/*-----------------*/
SET SERVEROUTPUT ON;
declare
out_ employee%rowtype;
tmp employee%rowtype;
cursor cur;
begin
UPDATESALARY(1,out_);
/*how to fetch data from out_*/
end;
/*-----------------*/
ASKER
@hainkurt
error occured :out_ is multiple rows
error occured :out_ is multiple rows
select * into sumsalary from employee where dno=did;
so, it means you dont have a unique index or PK on sumsalary.dno
the proc updates multiple records and try to return multiple rows, but your out paramater is just row!
you should fix your db structure or use syscurcor as out param and return multiple records...
or use rownum=1 in select query and return only top 1 record...
not sure what is right way, you should know this
so, it means you dont have a unique index or PK on sumsalary.dno
the proc updates multiple records and try to return multiple rows, but your out paramater is just row!
you should fix your db structure or use syscurcor as out param and return multiple records...
or use rownum=1 in select query and return only top 1 record...
not sure what is right way, you should know this
ASKER
@HainKurt
how to declare curor as parameter of procedure?
how to declare curor as parameter of procedure?
Here is a complete test case showing how to create a procedure with an OUT cursor.
I don't know how you are calling the procedure so I cannot provide examples for that. I used sqlplus variables and print statement to show it.
I don't know how you are calling the procedure so I cannot provide examples for that. I used sqlplus variables and print statement to show it.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
insert into tab1 values('b');
commit;
create or replace procedure myproc( out_cur out sys_refcursor)
is
begin
open out_cur for select * from tab1;
end;
/
show errors
--sqlplus testing of above procedure
var mycur refcursor
exec myproc(:mycur);
print :mycur
so, what did you decide?
you proc will update multiple records and it should return all affected rows fro that id?
it means, you want to return a cursor, not a row?
you proc will update multiple records and it should return all affected rows fro that id?
it means, you want to return a cursor, not a row?
ASKER
@HainKurt
Exactly I mean that?
Exactly I mean that?
so, use
then use it like
create or replace procedure updatesalary(did in number, cur_salary out sys_refcursor)
is
begin
update employee set salary=salary*2 where dno=did;
open cur_salary for select * from employee where dno=did;
end;then use it like
SET SERVEROUTPUT ON;
declare
cur sys_refcursor;
rec employee%rowtype;
begin
UPDATESALARY (1, ret_cur);
LOOP
FETCH cur INTO rec;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.Put_Line ('Salary : ' || rec.salary);
END LOOP;
end;This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
out_.col_name