比如:
create table A(a1,a2,a3,a4)
create table B(b1,a1,b2,b3)
这两个表是主从关系的表,表A的主键是表B的外键,我想用存储过程返回两个表,或者更多个表内连接后查询的结果集,这个Oracle的存储过程应该怎么写?
请大家尽量写详细点,谢谢大家!
create table A(a1,a2,a3,a4)
create table B(b1,a1,b2,b3)
这两个表是主从关系的表,表A的主键是表B的外键,我想用存储过程返回两个表,或者更多个表内连接后查询的结果集,这个Oracle的存储过程应该怎么写?
请大家尽量写详细点,谢谢大家!
比如:
create view yourview as select A.a1,A.a3,B.b1,B.b4 from A,B where A.a1=B.b1;存储过程:
create or replace procedure selecttest is
cursor select_cur is
select v.a1,v.a3,v.b1,v.b4 from yourview v where v.a1='****';
v_a1 number;
v_a3 number;
v_b1 number;
v_b4 number;
begin
open select_cur;
loop
fetch select_cur into v_a1,v_a2,v_b1_v_b4;
exit when select_cur%NOTFOUND;
dbms_output.put_line('recored:'''||v_a1||','||v_a2||'''');
end loop;
close select_cur;
return;
end select_free;
/
改为
end selecttest;
2先要定义package的。
下面是个简单例,自己更改罢CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get(p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get( p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
sqlstr:=v`/`vselect id,name from deptv`/`v;
OPEN p_rc FOR sqlstr;
END get;
END pkg_test;