Oracle(三)存储过程与游标

1 create or replace package pck_demo --声明一个程序包头，注意：包头与包体的名称一样 2 as -- 语法 3 4 　　type mycursoris ref cursor;-- 声明一个(引用)游标类型 5 　　-- 第一个存储过程，查询所有的角色（根据关键字），需要先执行一个存储过程完成后，在去添加新的存储过程 6 　　procedure get_all_role( 7 　　　　cname in varchar2,-- 输入参数 8 　　　　vresult out mycursor --输出参数,为上面定义的游标类型。 9 　　); 10 11 　　-- 第二个存储过程，添加一个角色 12 　　procedure add_role( 13 　　　　cname in varchar2,-- 输入参数，角色名称 14 　　　　vresult out varchar2--输出参数,提示结果。 15 　　); 16 17 end pck_demo ;--结束这个程序包头

2、程序包体

1 create or replace package body pck_demo --声明一个程序包体，注意：包头与包体的名称要一模一样 2 as -- 语法 3 -- 第一个存储过程，查询所有的角色（根据关键字） 4 　　procedure get_all_role( -- 在包体中实现包头中定义的存储过程 5 　　　　cname in varchar2,-- 输入参数 6 　　　　vresult out mycursor --输出参数,为上面定义的游标类型。 7 　　) as --这里的as也可以写成is 8 9 　　begin 10 　　　　open vresult -- 打开引用游标 11 　　　　　　for select * from t_role where rname like ‘%‘||cname||‘%‘;--将查询的结果放入到游标中 12 　　end get_all_role;--程序的end 13 14 15 16 　　-- 第二个存储过程，添加一个角色 17 　　procedure add_role( 18 　　　　cname in varchar2,-- 输入参数，角色名称 19 　　　　vresult out varchar2--输出参数,提示结果。 20 　　) as 21 　　v_count number := 0;--声明变量，赋予初始值 22 　　begin 23 　　　　insert into t_role (rid,rname) values(seq_uid.nextval,cname); 24 　　　　v_count := sql%rowcount; -- 受影响的行数，给到v_count中 25 　　　　commit; 26 　　　　if v_count > 0 then 27 　　　　　　vresult :=‘插入成功‘; 28 　　　　else 29 　　　　　　vresult :=‘插入失败‘; 30 　　　　end if; -- 结束if判断 31 　　end add_role ; 32 end pck_demo ;--包的结束

在IDEA中调用存储过程
　　使用C3P0数据源

1 方法一、查询所有的角色 2 public List getAllRoles(String rname) throws Exception{ 3 　　List list = new ArrayList<>(); 4 　　//得到连接 5 　　Connection con = MyC3P0Oracle.getConnection(); 6 　　String sql = "call pck_demo.get_all_role(?,?)";//用包头名 点 存储过程名；第一个是输入参数，第二个是返回的参数 7 　　//定义一个数据集，用来接收数据 8 　　ResultSet rs = null; 9 　　//CallableStatement 专门用来处理存储过程的 10 　　CallableStatement cs = con.prepareCall(sql); 11 　　cs.setString(1,rname);//给参数赋值 12 　　//注册输出参数赋值,类型是存储过程中定义的 游标 类型 13 　　cs.registerOutParameter(2,OracleTypes.CURSOR); 14 　　//执行存储过程 15 　　cs.execute(); 16 　　//返回数据集 17 　　ts = cs.getObject(2); 18 　　while(ts.next()){ 19 　　　　MyRole myrole = new MyRole( 20 　　　　　　ts.getInt(1), 21 　　　　　　ts.getString(2); 22 　　　　　　list.add(myrole); 23 　　　　) 24 　　} 25 　　//关闭资源的方法，三个对象需要关闭，这里省略 26 　　return list; 27 } 28 方法二、新增一个角色 29 public String insertRoles(MyRole role) throws Exception{ 30 　　　//得到连接　　 31 　　Connection con = MyC3P0Oracle.getConnection(); 32 　　String sql = "call pck_demo.add_rolee(?,?)";//用包头名 点 存储过程名；第一个是输入参数，第二个是返回的参数 33 　　MyRole role = new MyRole(); 34 　　role.setRname("老师")； 35 　　//CallableStatement 专门用来处理存储过程的 36 　　CallableStatement cs = con.prepareCall(sql); 37 　　cs.setString(1,role.getRname());//给参数赋值 38 　　//注册输出参数赋值,类型是存储过程中定义的 游标 类型 39 　　cs.registerOutParameter(2,OracleTypes.VARCHAR); 40 　　//执行存储过程 41 　　cs.execute(); 42 　　//关闭资源的方法，两个对象需要关闭，这里省略 43 　　return cs.getString(2);//得到返回的参数 44 }

--创建或修改存储过程，存储过程名为findclass,输入参数为classId,输出className create or replace procedure findclass(classId in int,classStudents out sys_refcursor,className out varchar2) as --定义一个游标的方式有多种，可以显示定义CURSOR cursor_name is select * from table，也可以定义动态游标，游标关键词CURSOR TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标 students ref_cursor_type; --定义班级集合为一个游标类型 student_row student%rowtype; --定义班级类型，类型为student表行类型 --存储过程开始 begin --把查询出来的class_name 赋值给输出变量className,查询条件为classId select class_name into className from class where id = classId; --打开游标并赋值 open students for select * from student where fk_class =classId; --把查询结果赋值给输出变量，实际上可以直接open classStudents for select * from student where fk_class =classId; classStudents := students; --循环输出游标，循环有三种方式，for in循环，fetch循环，while循环 --fetch循环 loop fetch classStudents into student_row; --当循环到空跳出循环 EXIT WHEN classStudents%NOTFOUND; DBMS_OUTPUT.put_line(‘学生名：‘||student_row.student_name); end loop; DBMS_OUTPUT.put_line(‘班级名：‘||className); --存储过程结束 end findclass;

into前面写table列名，后面写需要赋值的变量名，顺序要对 select student_name,class_name into studentName,className from class;

转载自https://www.jianshu.com/p/159f3f41f4cc

Oracle(三)存储过程与游标