--in/out 类型变量的使用 以及过程中调用过程,获取out类型的返回值
--建一张表books
- create table books(bookid number,bookname varchar(50),pubhouse varchar(50));
-
-
- create or replace procedure sub_pro1
- (bookid in number,bookname in varchar2,pubhouse in varchar2) is
- begin
- insert into books values(bookid,bookname,pubhouse);
- end
测试:
- SQL> exec sub_pro1(1,'wudi','安徽文艺出版社');
-
- PL/SQL procedure successfully completed
--简单过程二 由id获取一条记录的的name
- create or replace procedure sub_pro2
- (bookid in number,bookname out varchar2) is
- begin
- select bookname into bookname from books where bookid=bookid;
- end;
-
-
- create or replace procedure sub_pro22
- (bookno in number) is
-
- v_bookname varchar(50);
- begin
-
- sub_pro2(bookno,v_bookname);
-
- dbms_output.put_line(bookno||' 的图书为'||v_bookname);
- end;
测试:
- SQL> exec sub_pro22(1);
-
- 1 的图书为wudi
--简单过程三 得到一个用户集合的结果集,取出同一个出版社的书的id和name
- books表中插入这么几条数据
- BOOKID BOOKNAME PUBHOUSE
-
- 1 wudi 安徽文艺出版社
- 2 haorn 10
- 3 angle 20
- 4 anglele 10
-
- create or replace package testpackage as
- type test_cursor is ref cursor;
- end testpackage;
-
- create or replace procedure sub_pro3
- (l_pubhouse in varchar2,l_cursor out testpackage.test_cursor) is
- begin
- open l_cursor for select bookid,bookname from books where pubhouse=l_pubhouse;
- end;
-
- create or replace procedure sub_pro33(l_pubhouse in varchar2) is
- books_cursor testpackage.test_cursor;
- v_bookid books.bookid%type;
- v_bookname books.bookname%type;
- begin
- sub_pro3(l_pubhouse,books_cursor);
- loop
- fetch books_cursor into v_bookid,v_bookname;
-
- exit when books_cursor%notfound;
- dbms_output.put_line('图书ID是:'||v_bookid||' 名称是:'||v_bookname);
- end loop;
- end;
看起来还能用:
- SQL> exec sub_pro33('10');
-
- 图书ID是:2 名称是:haorn
- 图书ID是:4 名称是:anglele
-
- PL/SQL procedure successfully completed
注意!1 过程中的调用只要直接写过程名就好了,不要什么调用关键字。
2 对于out类型的值直接声明一个相同类型的变量来接收就好了。
3 为了好测试才用另一个过程来显示结果的,以后会通过jdbc来调用过程,获取函数。
本文出自 “orangleliu笔记本” 博客,请务必保留此出处http://blog.csdn.net/orangleliu/article/details/38309425