【Oracle数据库】存储过程

• 语法

create [or replace<] procedure < procedure name> 

            
[(<parameter list>)]

      
is|as 

      
<localvariable declaration>

      
begin

           
<executable statements>

          
[exception  <exceptionhandlers>]

      
end;

• 方法

             方法1：
              exec +存储名      
                        注：这个是sqlpluse 中的调用方式

              方法2：
              begin
                   存储名
              end;

• IN 定义一个输入参数变量，用于传递参数给存储过程。

• OUT 定义一个输出参数变量，用于从存储过程获取数据。

• IN OUT 定义一个输入、输出参数变量，兼有以上2者的功能

• DEFAULT 关键字为可选项，用来设定参数的默认值
  -例如：v_empno in number default 7777;

无参

• 创建无参存储过程

CREATE or replace PROCEDURE proc6
as
BEGIN
  DBMS_OUTPUT.PUT_LINE('我是存储过程!!!!!');
  DBMS_OUTPUT.PUT_LINE('Hello Everyone!');
END;

create or replace PROCEDURE pro111
as
BEGIN
  dbms_output.put_line('存储过程！');
  dbms_output.put_line('hello world');
end;

• 调用无参的存储过程

begin
proc6;
end;

drop procedure proc6

begin
  pro111;
end;

drop procedure pro111

输入参数

• 创建带输入参数的存储过程

create or replace procedure proc2
(v_empno in emp.empno%type)  --存储过程名字后（入参名字 in 约束入参的数据类型，既可以用%type,也可以直接用number，char）
--多个入参：(入参一 in 数据类型，入参二 in 数据类型)
is

begin
      --根据员工编号，删除指定员工信息
      delete from emp where emp.empno=v_empno;
      
      --判断是否删除成功
      if (sql%notfound) then   
         --错误编号 -20000   ——————   -29999 之间
         --reise_application_error 内置的   指定错误的存储过程
         raise_application_error(-20008,'删除失败，员工不存在！');
      else
         dbms_output.put_line('删除成功');      
      end if ;         
end;

注：创建存储过程时，即使存储过程代码有误，也会被创建成功，
但是procedures文件夹下存储过程名字会有叉叉，可以打开sql窗口中的program windows-procedures窗口写存储过程

• 调用带有输入参数的存储过程

begin
proc2(7788);
end;

begin
pro123(7788);
end;

select * from emp02;

输出参数

• 创建带输出参数的存储过程

create or replace procedure proc3
(v_deptno in number,v_sal out number,v_cnt out number)

as

begin
   select avg(sal),count(*) into v_sal,v_cnt
          from emp where deptno=v_deptno;
exception
when no_data_found then
dbms_output.put_line('没有找到部门');          
when others then
dbms_output.put_line(SQLERRM);    
  
end;

• 调用带有输出参数的存储过程
  注意，调用带有出参数据的存储过程要另外定义两个变量接收创建存储
  过程创建的出参，整体：创建存储过程时创建两个出参v_sal，v_cnt 接收emp表中查到的数据，调用proc3时，还要在定义两个变量v_avgsal，v_count来接收v_sal，v_cnt的数据

declare
       v_avgsal number;
       v_count number;
begin
       proc3(&a,v_avgsal,v_count);
       dbms_output.put_line(trunc(v_avgsal)||'====='||v_count);
end;
select * from user_procedures
declare
  e_avgsal number;
  e_count number;
begin
  pro111(&a,e_avgsal,e_count);
  dbms_output.put_line(trunc(e_avgsal)||'====='||e_count);
end;

输入输出参数

• 创建输入输出参数的存储过程

create or replace procedure proc4
(v_num1 in out number,v_num2 in out number)
is 
        v_temp number:= 0;        
begin
        v_temp := v_num1;
        v_num1 := v_num2;
        v_num2 := v_temp;
end;

• 调用输入输入参数的存储过程

declare
       v_n1 number:=1;
       v_n2 number:=4;
begin
       proc4(v_n1,v_n2);
       dbms_output.put_line(v_n1||'======'||v_n2);
end;

删除存储过程

• 语法：drop procedures + 存储过程名
• 存放存储过程的表为：user_source

存储过程时如何实现批量插入数据

create table emp_copy as select * from emp
select * from emp_copy for update
drop table emp_copy

• 创建存储过程 背下

create or replace procedure ab
is 
begin
  /*循环*/
  for i in 1..100 loop
  /*操作*/
  insert into emp02 values (
  /*empno的值*/
  i+100,
  /*ename*/
  dbms_random.string('a',5),
  /*job*/
  'CLERK',
  /*mgr*/
  i,
  /*hiredate*/
  trunc(sysdate)-i,
  /*sal*/
  101+i,
  /*comm*/
  202,
  /*deptno*/
  21);
  end loop;
end;

• 执行

begin
  ab;
end;

select * from emp02