提供参考!!CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/直接调用存储过程
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/直接调用存储过程
解决方案 »
- oracle 多字段全完检索 怎么实现索引的自动更新
- oracle 11g创建数据库出现问题!!求救
- 谁来帮我优化下这个语句
- 为什么select * from 都能查到的内容而在user_tab_columns里面查不到呢?
- 网络服务名配置问题,问题比较奇怪,请各位大侠指点,在线等待----------
- Oracle中的自动增量值如何恢复使之同步??急!在线等待!一定给分!
- 如何从oracle的本地网络服务名(在客户端配置)得到其对应的机器名和数据库名??
- 问:关于一个引用对象以及对象类型表的插入、更新问题!!!
- oracle8和oracle8i有什么区别,这个i代表什么(初学者)???
- java中用jdbc获取oracle数据分两次返回,两次返回的时间间隔为两个小时
- pl/sql中的一个问题
- 看看这个SQL语句错在哪里?在线等待!
怎么样定义一个record,能不能给个例子
在存储过程中能不能定义一数组,如果可以,请给个例子说明一下,
谢谢!
SQL> create table a (id number,name varchar2(50),doctime date);
Table created.
--插入六条测试数据:
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));
1 row created.
SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));
1 row created.
SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));
1 row created.
SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));
1 row created.
SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));
1 row created.
SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
2 /
Type created.
SQL> create or replace type mytabletype as table of myobjectype
2 /
Type created.
--创建可以返回纪录集的函数(不传入表名参数)
SQL> create or replace function testrerecordnotabname (tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 begin
6 for i in (select * from a where id>=tableid) loop
7 l_data.extend;
8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
9 exit when i.id = 62;
10 end loop;
11 return l_data;
12 end;
13 /
Function created.
SQL> commit;
Commit complete.
--创建可以返回纪录集的函数(可以传入表名参数)
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 strsql varchar2(50);
6 type v_cursor is ref cursor;
7 v_tempcursor v_cursor;
8 i1 number;
9 i2 varchar2(50);
10 i3 date;
11 begin
12 strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13 open v_tempcursor for strsql;
14 loop
15 fetch v_tempcursor into i1,i2,i3;
16 l_data.extend;
17 l_data(l_data.count) := myobjectype(i1,i3,i2);
18 exit when v_tempcursor%NOTFOUND;
19 end loop;
20 return l_data;
21 end;
22 /
Function created.
SQL> commit;
Commit complete.
--测试不传表名参数的function(testrerecorenotabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordnotabname(1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
PL/SQL procedure successfully completed.
--测试传表名参数的function(testrerecoretabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordtabname('a',1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
;6;06-7?? -02;fff;
PL/SQL procedure successfully completed.