存储过程

存储过程

1，创建存储过程：

·简单的存储过程

create or replace procedure sp_pcd_1 is -- 定义一个存储过程，不要忘了写 is 了
begin
     update emp set sal =sal - 200 where empno = 7788;
end;

·有参数的存储过程

--参数只要写名类型就可以，不要定义长度，就像函数的参数一样
create or replace procedure sp_pcd_2(v_ename varchar) is
begin
     update emp set sal = sal - 200 where ename = v_ename;
end;

·有返回值存储过程

--需要注意的是在参数列表里面，如果是返回值，就要写out ,默认是in ,所以作为
--传入参数，可以不写
create or replace procedure sp_pcd_3(v_eno number,v_ename out varchar) is
begin
     select ename into v_ename from emp where empno = v_eno;
     dbms_output.put_line(v_ename);
end;

·复杂的存储过程
--先创建一个包，里面包含复杂的数据类型
--这个主要是针对在传入穿出参数列表时候要引用这些类型
create or replace package my_types as -- 注意这里是as ，注意这里
type emp_cur is ref cursor; --里面可以指定多个type 
end my_types; --end 包名

--分页的存储过程
create or replace procedure paging
(tablename varchar,pagenum number,maxnum number,
pages out number,sizes out number,rs out my_types.emp_cur) is
--在is 和 begin 之间声明变量
v_firstNum number:=(pagenum-1)*maxnum + 1 ;--rownum 是从 1 开始的
v_endNum number:=pagenum*maxnum;
v_sql varchar2(1000); -- 用来存放sql语句，动态执行sql
begin
     v_sql := ' select * from (select rownum rn, e.* from (select * from '||tablename||') e 
              where rownum <=' || v_endNum || ') where rn >='||v_firstNum;
     open rs for v_sql; --打开游标
     
     --求总条数
     v_sql := 'select count(*) from '||tablename;
     execute immediate v_sql into sizes;
     --求总页数
     if mod(sizes,maxnum)=0 then
        pages := sizes/maxnum;
     else 
        pages := sizes/maxnum + 1;
     end if;
end;

2，删除存储过程

drop  procedure  prc_name;

3，管理存储过程

SQL> select * from user_procedures; 可以通过数据字典来查询当前用户创建的存储过程

当然更容易的就是在pl*sql development这个工具里面查询

4，jdbc调用存储过程

以上面的分页为例

public static void main(String[] args) {

try{

//加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取连接

Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:HWT","hwt","hwt");

CallableStatement cs = connection.prepareCall("{call paging(?,?,?,?,?,?)}");

cs.setString(1, "emp");

cs.setInt(2,1);

cs.setInt(3, 5);

cs.registerOutParameter(4, OracleTypes.NUMBER);

cs.registerOutParameter(5, OracleTypes.NUMBER);

cs.registerOutParameter(6, OracleTypes.CURSOR);

cs.execute();

Integer pages = cs.getInt(4);

Integer size  = cs.getInt(5);

System.out.println("总条数："+size+",总页数："+pages);

ResultSet rs = (ResultSet)cs.getObject(6);

while (rs.next()) {

String name = rs.getString(3);

System.out.println(name);

}

}catch (Exception e) {

e.printStackTrace();

}

}

5，hibernate调用存储过程

使用本地sqlQuery

public static void main(String[] args) {

Session session = HibernateSessionFactory.getSession();

Transaction tr = session.beginTransaction();

//使用本地的sql

SQLQuery sqlQuery = session.createSQLQuery("call sp_pcd_2(?)");

sqlQuery.setParameter(0, "SCOTT");

sqlQuery.executeUpdate();

tr.commit();

}

也可以使用配置文件来实现

此处略。。

本人觉得hibernate在存储过程上的实现不够好，如果有返回值，hibernate就不怎么好搞，

所以，可以用hibernate来使用jdbc来调用存储过程

1，只要先得到sessionFactory对象

2，然后通过sessionFactory的子类SessionFactoryImplementor 的 getConnectionProvider() 来 得到ConnectionProvider 

3，connectionProvider。getConnection();

ConnectionProvider cp = ((SessionFactoryImplementor)factory).getConnectionProvider();

Connection connection = cp.getConnection();

得到了connection对象，下面就是jdbc代码了

记得关闭资源