Oracle中定义package以及存储过程的使用
使用scott账户下的dept表;
select * from dept order by deptno;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
为了演示方便,插入一条数据:
insert into dept(deptno, dname, loc) values(50,'SYSTEM', 'NEW YORK');
新插入的记录为:50 SYSTEM NEW YORK
我们主要演示在package中存储过程的返回类型为pipelined table,cursor 和 value三种。
1.返回类型为pipelined table。
create or REPLACE type dept_obj is OBJECT( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14 BYTE) );
create or REPLACE type dept_obj_type AS table of dept_obj;
2.定义package 和package body。
1 create or replace package SPTest
2 is
3 /*return a pipelined demo start*/
4 type dept_data_rec_type is RECORD(
5 DEPTNO NUMBER(2,0),
6 DNAME VARCHAR2(14)
7 );
8
9 type dept_ref_type is REF CURSOR;
10
11 function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined;
12 /*return a pipelined demo end*/
13
14 /*return a cursor demo start*/
15 FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type;
16 /*return a cursor demo end*/
17
18 /* return a varchar value start */
19 function getName(in_deptno in number) RETURN VARCHAR2;
20 /* return a varchar value end */
21 end SPTest;
22 /
23 -----------------------------------------------------------------------------------------------
24 create or replace package body SPTest
25 is
26 /*return a pipelined demo start*/
27 function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined is
28 l_dept_obj dept_obj :=dept_obj(null, null);
29 dept_ref_type_cursor dept_ref_type;
30 dept_data_rec dept_data_rec_type;
31 begin
32 open dept_ref_type_cursor
33 for select deptno, dname from dept where loc = in_loc;
34
35 loop
36 fetch dept_ref_type_cursor into dept_data_rec;
37 exit when dept_ref_type_cursor%NOTFOUND;
38 l_dept_obj.DEPTNO := dept_data_rec.DEPTNO;
39 l_dept_obj.DNAME := dept_data_rec.DNAME;
40
41 pipe row(l_dept_obj);
42 end loop;
43 close dept_ref_type_cursor;
44 RETURN ;
45 end getDept;
46 /*return a pipelined demo end*/
47
48 /*return a cursor demo start*/
49 FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type
50 AS
51 dept_ref_type_cursor dept_ref_type;
52 BEGIN
53
54 OPEN dept_ref_type_cursor FOR
55 SELECT deptno, dname, loc FROM dept where deptno = in_deptno;
56
57 RETURN dept_ref_type_cursor;
58
59 END getDeptInfo;
60 /*return a cursor demo end*/
61
62 /* return a varchar value start */
63 function getName(in_deptno in number) RETURN VARCHAR2
64 as rtn_deptname VARCHAR2(100);
65 begin
66 select dname into rtn_deptname from dept where deptno = in_deptno;
67 RETURN rtn_deptname;
68 end getName;
69 /* return a varchar value start */
70
71 end SPTest;
72 /
最后,执行存储过程。
select deptno, dname from table(SPTest.getDept('NEW YORK')) order by deptno;
select SPTest.getDeptInfo(10) from dual;
select SPTest.getName(50) from dual;
标签: Oracle
好文要顶 关注我 收藏该文
林本托
关注 - 9
粉丝 - 289
+加关注
1
« 上一篇:不同系统平台下Java默认的安装路径
» 下一篇:《编写高质量代码:改善Java程序的151个建议》笔记
posted @ 2015-02-20 00:39 林本托 阅读(3298) 评论(0) 编辑 收藏
https://www.cnblogs.com/IcanFixIt/p/4296379.html