oracle的存储过程

为了方便学习自己整理了下，在里面的案例在oracle数据库中都可以直接运行，分享给大家

存储过程

创建存储过程

优点：存储过程是已经编译好的代码，所以其被调用或者引用的时候执行效率非常高

语法格式如下：

create [orreplace]procedure pro_name[(parameter1[,parameter2]…)]is|as

begin

 plsql_sentences;

[exception]

  [dowith _sentences;]

end [pro_name];

参数说明：

pro_name :存储过程的名称，如果数据库中已经存在了此名称，则可以指定”or replace”关键字，这样的存储过程将覆盖原来的存储过程

parameter1:存储过程的参数，若是输入参数，则需要在其后指定”in”关键字；若是输出参数，则需要在其后面指定”out”关键字。在int和out关键字的后面是参数的数据类型，但不能指定该类型的长度。

plsql_sentences：PL/SQL语句，它是存储过程功能实现的主体

dowith _ sentences：异常处理语句，也是PL/SQL语句，这是一个可选项

案例:

创建一个存储过程，该存储过程实现向dept表中插入一条记录

createprocedure pro_insertDept is

      begin

            insert into dept values(77,'市场拓展部','JILIN');

     commit;

            dbms_output.put_line('插入数据记录成功');

     endpro_insertDept;

 /

1)  SQL:Plus环境中，使用execute执行:

 executepro_insertDept;

2)   PL/SQL块中调用:

set serverout on

begin

    pro_insertDept;

end;

   /

存储过程的参数

IN模式参数

案例：创建一个存储过程，并定义3个in模式的变量

create or replace procedure insert_dept(

   num_deptno in number,

   var_ename in varchar2,

   var_loc in varchar2) is 

 begin

   insert into dept values(num_deptno,var_ename,var_loc);

   commit;

 endinsert_dept;

/

 

向存储过程中添加参数的方法

1)      PL/SQL按指定名称传递

pro_name(parameter1=>value1[,parameter2=>value2]...)

parameter1：参数名称，在传递参数值是，这个参数名称与存储过程中定义的参数顺序无关

value1：参数值，在他的左侧不是常规的赋值符号”=”，而是一种新的赋值符号”=>”，需要注意参数值的类型和与参数定义类型兼容。

begin

 insert_dept(var_ename=>'采购部',var_loc=>'成都',num_deptno=>15);

end;

/

2)      PL/SQL按位置传递：用户提供的数值顺序必须与存储过程中定义的参数顺序相同

begin

 insert_dept(28,'工程部','洛阳');

end;

/

说明：用户可以通过desc命令查看存储过程的定义信息，这些信息包括参数名、参数定义顺序、参数模式等

3)      混奈何方式传递:

exec insert_dept(38,var_loc => '济南',var_ename => '测试部');

说明：使用混合模式传入参数值，需要注意的是：在某个位置使用”指定名称传递”方式传入参数值后，其后面的参数值也要使用”指定名称传递”，因为”指定名称传递”的方式有可能已经破坏了参数原始定义顺序。

out模式参数

create or replace procedure select_dept(

 num_deptno in number,               --定义in模式变量，要求输入部门编号

 var_dname out dept.dname%type,    --定义out模式变量，可以存储部门名称并输出

 var_locout dept.loc%type) is

begin

  selectdname,loc into var_dname,var_loc from dept where deptno=num_deptno;

exception

  whenno_data_found then            --若无返回记录

   dbms_output.put_line('该部门编号不存在');

end select_dept;

/

1)  PL/SQL执行：

set serverout on

declare

 var_dname dept.dname%type; --声明变量，对应过程中的out模式的var_dname

 var_locdept.loc%type;     --声明变量，对应过程中的out模式的var_loc

begin

 select_dept(77,var_dname,var_loc); --传入部门编号，然后输出名称和位置信息

 dbms_output.put_line(var_dname||'位于:'||var_loc); --输出信息

end;

/

2)  使用exec命令执行out模式的存储过程

 

variable var_dname varchar2(50);

variable var_loc varchar2(50);

exec select_dept(10,:var_dname,:var_loc);

使用select语句检索并输出数据变量var_dname和var_loc的值

select :var_dname,:var_loc;

IN OUT模式参数

在执行那个存储过程时，IN参数不能被修改，它只能被传入的指定值（或者是默认值）为存储过程提供数据，而OUT类型的参数只能等待被赋值，而不能像IN参数那样为存储过程本身提供数据，但是IN OUT参数可以兼顾其他两种参数的特点，在调用存储过程是，可以从外界向该类型的参数传入值，在执行完存储过程之后，可以将该参数的返回值传给外界。

案例：

创建一个存储过程，在其中定义一个"int out"参数，该存储过程用来计算这个参数的平方或者平方根

create orreplace procedure pro_square(

 num in out number,      --计算它的平方根或者平法，这是一个"inout"参数

 flag in boolean) is     --计算平方或平方根的表示，这是一个"in"参数

 i int :=2               --表示计算平方，这是一个内部变量

begin

  if flag then           --若为true

    num :=power(num,i);  --计算平方

  else

    num :=sqrt(num);     --计算平方根

  end if;

end;

调用存储过程

declare

 var_number number;                          --存储要进行运算的值和运算后的结构

 var_temp number;                            --存数要进行运算的值

 boo_flag boolean;                           --true代表平方根，false代表立方

begin

 var_temp :=3;                               

 var_number :=var_temp;

 boo_flag :=true;                             --true代表调用平方

 pro_square(var_number,boo_flag);         --调用存储过程

  ifboo_flag then

   dbms_output.put_line(var_temp||'平方是:'||var_number);--输出计算结果

   else

   dbms_output.put_line(var_temp||'平方根是:'||var_number);

   endif;

end;

/

IN参数默认值

Oracle支持在声明IN参数的同时给其初始化默认值，这样在存储过程调用的时候，如果没有向IN参数中传入值，则存储过程可以使用默认值进行操作。

create or replace procedure insert_dept(

  num_deptno in number,                                  --存储部门编号

  var_dname in varchar2 default '综合部',             --定义存储部门名称的IN参数，并初始化默认值

  var_loc in varchar2 default '北京') is

begin

  insertinto dept values(num_deptno,var_dname,var_loc);--插入一条数据

end;

/

先添加数据，再运行

declare

 row_dept dept%rowtype;            --自定义行变量，与dept表的一行类型相同

begin

 insert_dept(9,var_loc=>'太原');    --调用存储过过程，传入参数

 commit;                           --提交数据库        

  select* into row_dept from dept where deptno=57; --重新插入的记录

 dbms_output.put_line('部门名称为:'||row_dept.dname);  

end;