Oracle中的存储过程及游标

*存储过程

 

l  给用户创建存储过程的权限：grant createprocedureto cz

 

l  输出存储过程前需要打开缓存：set serveroutput on;（只要打开一次就好）

 

l  存储过程关闭缓存：set serveroutput off;

 

l  存储过程创建格式：

createprocedure myproc(存储过程名称)

as

begin

       dbms_output.put_line('张鑫儿二');

end;

 

l  修改存储过程格式：

createorreplaceprocedure myproc

as

begin

       dbms_output.put_line('张鑫儿pig');

end;

 

l  输出存储过程格式：

begin

       myproc();

end;

 

 

1：创建一个输出系统时间的存储过程

方式一：

createprocedure myproc2

as

begin

  dbms_output.put_line(to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));--输出

end;

 

输出：

begin

  myproc2();

end;

 

à结果：2017-12-0615:44:58

方式二：

createprocedure myproc3

as

begin

  dbms_output.put_line(sysdate);

end;

 

输出：

begin

  myproc3();

end;

 

à结果：06-12月-17

 

2：定义存储过程求2个数字之和

createprocedure proc_num(a int,b int)

as

begin

  dbms_output.put_line(a+b);

end;

 

输出：

begin

  proc_num(3,4);

end;

 

  à结果：7

 

3：定义存储过程求2个数字之和,并提供返回参数

createprocedure proc_num1(a int,b int,num outint)

as

begin

  num:=a+b;

end;

 

输出：

declare num int;

begin

  proc_num1(54,3,num);

  dbms_output.put_line(num);

end;

 

  à结果：57

 

4：创建更具ename查询并返回Job的存储过程

createprocedure proc_name(varnamevarchar2,outnameoutvarchar2)

as

begin

  select job intooutnamefrom scott.emp where ename=varname;

end;

 

输出：

declare outname nvarchar2(64);

begin

  proc_name('SCOTT',outname);

  dbms_output.put_line(outname);

end;

 

  à结果：ANALYST

 

 

5：返回多个字段

createorreplaceprocedure proc_name(varname varchar2,outname outvarchar2)

as

begin

  select job||ename into outname from scott.emp where ename=varname;

end;

 

输出：

declare outname nvarchar2(64);

begin

  proc_name('SCOTT',outname);

  dbms_output.put_line(outname);

end;

 

  à结果：ANALYSTSCOTT

 

 

6：创建向scott表插入的存储过程

  createorreplaceprocedure myproc

as

begin

  insertinto scott.emp(empno,ename,job) values(8004,'zxxer','aaa');

  dbms_output.put_line('成功');

  commit;

end;

 

输出：

begin

  myproc();

end;

 

à结果：成功           如果查询EMP表，能够查询到我添加的那条数据。

 

 

 

Ø  *思考：如何使用存储过程一次返回整张表的数据

 

Ø  Oracle游标cursor

 

游标可以装一个数据集

 

使用游标的步骤：

1.      定义一个游标

2.      给游标一个关联

3.      解析游标

 

方式一：

--定义一个游标

declarecursor setcur_sel

--关联游标

isselect ename,sal,job from scott.emp;

 

--解析游标

v_uname nvarchar2(64);

v_sal int;

v_job nvarchar2(64);

begin

   open setcur_sel;--打开游标

  

   loop

     --读取一行数据，往下读一行

     fetch setcur_sel into v_uname,v_sal,v_job;

     --输出

     dbms_output.put_line(v_uname||':'||v_sal||':'||v_job);

     --循环结束条件

     exitwhen setcur_sel%notfound;

   endloop;

  

   close setcur_sel;--关闭游标

end;

 

 

 

方式二：直接查询一行数据

1． --定义一个游标

2． declarecursor setcur_sel

3． --关联游标

4． isselect * from scott.emp;

5．  

6． --解析游标

7． v_row scott.emp%rowtype;

8． begin

9．    open setcur_sel;--打开游标

10．        

11．        loop

12．          --读取一行数据，往下读一行

13．          fetch setcur_sel into v_row;

14．          --输出

15．          dbms_output.put_line(v_row.ename||':'||v_row.sal||':'||v_row.job);

16．          --循环结束条件

17．          exitwhen setcur_sel%notfound;

18．        endloop;

19．        

20．        close setcur_sel;--关闭游标

21．             end;

 

 

 

方式三：

创建存储过程关联游标

1.  createorreplaceprocedure myproce(rowproc outsys_refcursor)

2.  as

3.  begin

4.    open rowproc forselect * from scott.emp;

5.      end;

 

游标代码：

1.  --定义一个游标

2.  declare rcursor sys_refcursor;

3.  --定义一个存储过程需要的参数

4.  emp_row scott.emp%rowtype;

5.   

6.  begin

7.    --引用存储过程

8.    myproce(rcursor);

9.    --循环

10. loop

11.   --循环一行数据

12.   fetch rcursor into emp_row;

13.   --输出

14.   dbms_output.put_line(emp_row.ename||':'||emp_row.sal||':'||emp_row.job);

15.   --循环结束条件

16.   exitwhen rcursor%notfound;

17. endloop;

18.  end;