存储过程与存储函数学习笔记

存储在数据库中供所有用户程序调用的子程序，叫做存储过程、存储函数。

本文的存储过程和存储函数以oracle为主。

存储过程

创建一个存储过程：create [ or replace ] procedure 过程名(参数名) as plsql子程序体;

存储过程只能创建或替换，不能修改

创建一个简单的无参存储过程

create or replace procedure test
as
begin
  dbms_output.put_line('hello world');
end;

存储过程的名字为TEST(会自动转为大写)，不带参数，子程序体只是打印一个"hello world"。

然后执行一次，在plsql的procedures目录下，会多出一个名为TEST的存储过程。

调用存储过程：

1、exec TEST();

2、begin

　　 TEST();--调用一次

　　 TEST();--调用两次

　　end;

创建一个带入参的存储过程

create or replace procedure test(orgno2 in varchar2)
as
  str yzyj_org.orgname%type;
begin
  select orgname into str from yzyj_org where orgno = orgno2;

  dbms_output.put_line('hello'||str);
end;

入参用in表示，只需要指定类型，不需要指定类型的大小。

需要注意的是，存储过程中可以commit和rollback，但是不需要。因为这只是一个子程序，commit或rollback会无法确保整个函数是在一个事务中。

创建一个带出参的存储过程

create or replace procedure queryOrgno(org in varchar2, org2 out varchar2, orgname2 out varchar2, norgname2 out varchar2)
as
begin
  select t.orgno, t.orgname, t.norgname into org2, orgname2, norgname2 from yzyj_org t where orgno = org;
end;

出参用out表示，也只需要指定类型，不指定类型的大小。

其中上面的org和org2其实可以合并，作为一个出入参，即如下

create or replace procedure queryOrgno(org in out varchar2, orgname2 out varchar2, norgname2 out varchar2)
as
begin
  select t.orgno, t.orgname, t.norgname into org, orgname2, norgname2 from yzyj_org t where orgno = org;
end;

 在应用程序中调用存储过程

1、首先写一个sql语句

String sql = "{call queryOrgno(?,?,?,?)}"

2、java中有一个类callableStatement，实例化一个对象命名为call，并且执行

call = conn.prepareCall(sql);

3、开始插入存储过程中的参数。

1、输入参数可以setXXX方法插入。占位符的位置从1开始。
call.setInt(1，123);
2、输出参数只需要声明。
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(3,OracleTypes.NUMBER);
call.registerOutParameter(4,OracleTypes.VARCHAR);
3、执行调用
call.execute();
4、取出结果
String orgno = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);

存储函数

创建一个存储函数：create or replace function 函数名(参数)

return 函数值类型

as

plsql子程序体

创建一个存储函数

create or replace function test(orgno2 in varchar2)
return varchar2
as
  str yzyj_org.orgname%type;
begin
  select orgname into str from yzyj_org where orgno = orgno2;

  dbms_output.put_line('hello'||str);
  return str;
end;

执行一个存数函数

declare 
  str varchar2(20);
begin
  str := test('43410');
  dbms_output.put_line(str);
end;

在应用程序中调用存储函数

1、首先写一个sql语句

String sql = "{?=call test(?)}"

2、java中有一个类callableStatement，实例化一个对象命名为call，并且执行

CallableStatement call = null;
call = conn.prepareCall(sql);

3、开始插入存储函数中的参数。

1、输入参数可以setXXX方法插入。占位符的位置从1开始。
call.setString(2，"444");
2、输出参数只需要声明。
call.registerOutParameter(1,OracleTypes.NUMBER);
3、执行调用
call.execute();
4、取出结果
double sal = call.getDouble(1);

光标

在我们需要通过存储过程或存储函数返回一个集合的时候，可以定义一个包。

定义一个包头

create or replace package test1 as

  type orgcursor is ref cursor;--orgcursor是我们自己定义的一个光标类型
  procedure queryorgno(orgno in number, org out orgcursor);--在out参数中使用我们定义的光标类型
  
end test1;

创建包体

create or replace package body test1 as

  procedure queryorgno(orgno in number, org out orgcursor) as
  begin
    open org for select * from yzyj_org;--首先需要打开光标
  end queryorgno;

end test1;

在应用程序中调用

1、首先写一个sql语句，调用的是包下面的存储过程，所以用的是调用存储过程类型的sql语句，但是由于是在包下，所以需要带上包名

String sql = "{call test1.queryOrgno(?,?)}"

2、java中有一个类CallableStatement，实例化一个对象命名为call，并且执行

CallableStatement call = null;
call = conn.prepareCall(sql);

3、开始插入存储过程中的参数。

1、输入参数可以setXXX方法插入。占位符的位置从1开始。
call.setInt(1，123);
2、输出参数只需要声明。
call.registerOutParameter(2,OracleTypes.CURSOR);
3、执行调用
call.execute();
4、获取结果
ResultSet rs = ((OracleCallableStatement)call).getCursor(2); 
5、取出结果
while(rs.next()){
　　int orgno = rs.getInt("orgno");
　　String orgname = rs.getString("orgname");
}