[轉貼]BECKHAM發表 返回记录集过程: 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; / -------------------------------------------------------------------------------------------------------------- 返回记录集函数: 1、建立测试表 CREATE TABLE student ( id NUMBER, name VARCHAR2(30), sex VARCHAR2(10), address VARCHAR2(100), postcode VARCHAR2(10), birthday DATE, photo LONG RAW ); /2、建立带ref cursor定义的包和包体及函数： CREATE OR REPLACE package pkg_test as /* 定义ref cursor类型 不加return类型，为弱类型，允许动态sql查询， 否则为强类型，无法使用动态sql查询; */ type myrctype is ref cursor; --函数申明 function get(intID number) return myrctype; end pkg_test; /CREATE OR REPLACE package body pkg_test as --函数体 function get(intID number) return myrctype is rc myrctype; --定义ref cursor变量 sqlstr varchar2(500); begin if intID=0 then --静态测试，直接用select语句直接返回结果 open rc for select id,name,sex,address,postcode,birthday from student; else --动态sql赋值，用:w_id来申明该变量从外部获得 sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; --动态测试，用sqlstr字符串返回结果，用using关键词传递参数 open rc for sqlstr using intid; end if; return rc; end get;end pkg_test; /3、用pl/sql块进行测试： declare w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量，用于显示结果 w_id student.id%type; w_name student.name%type; w_sex student.sex%type; w_address student.address%type; w_postcode student.postcode%type; w_birthday student.birthday%type;begin --调用函数，获得记录集 w_rc := pkg_test.get(1); --fetch结果并显示 loop fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday; exit when w_rc%notfound; dbms_output.put_line(w_name); end loop; end;4、测试结果： 通过。 -------------------------------------------------------------------------------------------------------------- 返回对象类型: 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. -------------------------------------------------------------------------------------------------------------- 利用对象类型通过字符串分析出数据函数: create or replace type mytabletype as table of number; /create or replace function strtab(p_str in varchar2) return mytabletype as lstr varchar2(1000) default p_str||','; ln number; ldata mytabletype:=mytabletype(); begin loop ln:=instr(lstr,','); exit when (nvl(ln,0)=0); ldata.extend; ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1))); lstr:=substr(lstr,ln+1); end loop; return ldata; end; /SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE ------------ 11 12 13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME -- ---------- 11 张三 12 李四 13 王五 ------------------------------------------------------------------------------------------------------------------ 字段类型为嵌套表的使用方法: CREATE TYPE Course AS OBJECT ( course_no NUMBER(4), title VARCHAR2(35), credits NUMBER(1)); /CREATE TYPE CourseList AS TABLE OF Course; /CREATE TABLE division ( name VARCHAR2(20), director VARCHAR2(20), office VARCHAR2(20), courses CourseList) NESTED TABLE courses STORE AS courses_tab; INSERT INTO division VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133', CourseList(Course(1000, 'General Psychology', 5), Course(2100, 'Experimental Psychology', 4), Course(2200, 'Psychological Tests', 3), Course(2250, 'Behavior Modification', 4), Course(3540, 'Groups and Organizations', 3), Course(3552, 'Human Factors in the Workplace', 4), Course(4210, 'Theories of Learning', 4), Course(4320, 'Cognitive Processes', 4), Course(4410, 'Abnormal Psychology', 4))); INSERT INTO division VALUES('History', 'John Whalen', 'Applegate Hall 142', CourseList(Course(1011, 'History of Europe I', 4), Course(1012, 'History of Europe II', 4), Course(1202, 'American History', 5), Course(2130, 'The Renaissance', 3), Course(2132, 'The Reformation', 3), Course(3105, 'History of Ancient Greece', 4), Course(3321, 'Early Japan', 4), Course(3601, 'Latin America Since 1825', 4), Course(3702, 'Medieval Islamic History', 4))); INSERT INTO division VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(Course(1002, 'Expository Writing', 3), Course(2020, 'Film and Literature', 4), Course(2418, 'Modern Science Fiction', 3), Course(2810, 'Discursive Writing', 4), Course(3010, 'Modern English Grammar', 3), Course(3720, 'Introduction to Shakespeare', 4), Course(3760, 'Modern Drama', 4), Course(3822, 'The Short Story', 4), Course(3870, 'The American Novel', 5))); ---------------------------------------------------------------------------------------------------------------- 游标只是plsql中使用的变量并不是oracle的对象，所以它不能在过程或函数间进行传递。 下面的方法是通过table类型的对象来存储要返回的记录集。 --创建测试表： 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 procedure testrerecordnotabname (tableid in number, l_data out mytabletype) 2 3 as 4 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 12 end; 13 /Function created.SQL> commit;Commit complete.SQL> commit;Commit complete.--测试不传表名参数的procedure(testrerecorenotabname) SQL> set serveroutput on SQL> declare 2 testre mytabletype :=mytabletype(); 3 i number :=0; 4 begin 5 testrerecordnotabname(1,testre); 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.
返回记录集过程:
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;
/
--------------------------------------------------------------------------------------------------------------
返回记录集函数:
1、建立测试表
CREATE TABLE student
(
id NUMBER,
name VARCHAR2(30),
sex VARCHAR2(10),
address VARCHAR2(100),
postcode VARCHAR2(10),
birthday DATE,
photo LONG RAW
);
/2、建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor; --函数申明
function get(intID number) return myrctype;
end pkg_test;
/CREATE OR REPLACE
package body pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试，直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
open rc for sqlstr using intid;
end if; return rc;
end get;end pkg_test;
/3、用pl/sql块进行测试：
declare
w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量，用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type;begin
--调用函数，获得记录集
w_rc := pkg_test.get(1); --fetch结果并显示
loop
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;4、测试结果：
通过。
--------------------------------------------------------------------------------------------------------------
返回对象类型:
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.
--------------------------------------------------------------------------------------------------------------
利用对象类型通过字符串分析出数据函数:
create or replace type mytabletype as table of number;
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
11
12
13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五
------------------------------------------------------------------------------------------------------------------
字段类型为嵌套表的使用方法:
CREATE TYPE Course AS OBJECT (
course_no NUMBER(4),
title VARCHAR2(35),
credits NUMBER(1));
/CREATE TYPE CourseList AS TABLE OF Course;
/CREATE TABLE division (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO division
VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
CourseList(Course(1000, 'General Psychology', 5),
Course(2100, 'Experimental Psychology', 4),
Course(2200, 'Psychological Tests', 3),
Course(2250, 'Behavior Modification', 4),
Course(3540, 'Groups and Organizations', 3),
Course(3552, 'Human Factors in the Workplace', 4),
Course(4210, 'Theories of Learning', 4),
Course(4320, 'Cognitive Processes', 4),
Course(4410, 'Abnormal Psychology', 4)));
INSERT INTO division
VALUES('History', 'John Whalen', 'Applegate Hall 142',
CourseList(Course(1011, 'History of Europe I', 4),
Course(1012, 'History of Europe II', 4),
Course(1202, 'American History', 5),
Course(2130, 'The Renaissance', 3),
Course(2132, 'The Reformation', 3),
Course(3105, 'History of Ancient Greece', 4),
Course(3321, 'Early Japan', 4),
Course(3601, 'Latin America Since 1825', 4),
Course(3702, 'Medieval Islamic History', 4)));
INSERT INTO division
VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205',
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(2418, 'Modern Science Fiction', 3),
Course(2810, 'Discursive Writing', 4),
Course(3010, 'Modern English Grammar', 3),
Course(3720, 'Introduction to Shakespeare', 4),
Course(3760, 'Modern Drama', 4),
Course(3822, 'The Short Story', 4),
Course(3870, 'The American Novel', 5)));
----------------------------------------------------------------------------------------------------------------
游标只是plsql中使用的变量并不是oracle的对象，所以它不能在过程或函数间进行传递。
下面的方法是通过table类型的对象来存储要返回的记录集。
--创建测试表：
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 procedure testrerecordnotabname (tableid in number, l_data out mytabletype)
2
3 as
4
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
12 end;
13 /Function created.SQL> commit;Commit complete.SQL> commit;Commit complete.--测试不传表名参数的procedure(testrerecorenotabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testrerecordnotabname(1,testre);
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.