Oracle PL/SQL 存储过程

灰太狼 2024-02-17 16:40 24阅读 0赞

一、存储过程的概念  
存储过程（Stored Procedure ）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。  
通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库  
应用程序都应该用到存储过程。  
  
二、存储过程的优点  
（1）性能好  
（2）可重用PL/SQL  
（3）安全性好  
（4）解耦应用程序与物理表结构。  
（5）模块化，维护性好。  
  
三、存储过程分类  
1)系统存储过程  
就是由oracle预先提供的一组完成特定功能的存储过程,安装完oracle就有了。  
  
2)自定义存储过程  
就是存在oracle数据库里由一组plsql语句组成的自定义过程(procedure)。  
它可以供其它oracle自定义存储过程、自定义函数和job调用或者由客户端程序调用。  
  
  
四、存储过程的语法

<pre name="code" class="plain">CREATE [OR REPLACE] PROCEDURE procedure_name
 ([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
 ......
 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])
 [ AUTHID DEFINER | CURRENT_USER ]
 { IS | AS }
 <声明部分>
 BEGIN
 <执行部分>
 EXCEPTION
 <可选的异常错误处理程序>
 END procedure_name;

说明：

存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT

IN 表示输入参数，按值传递方式。   
OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。   
IN OUT 即可作输入参数，也可作输出参数。   
参数的数据类型只需要指明类型名即可，不需要指定宽度。

五、存储过程的使用

模拟java写两数求和的方法（有4种写法）

--(1)无参无返回
 public void add1(){
 int a=10;
 int b=20;
 int sum =a+b;
 System.out.println(sum);
 }
 
 --调用
 add1();

<pre name="code" class="plain">create or replace procedure add1
 is
 --声明变量
 a number default 110;
 b number default 20;
 sums number;
 begin
 sums:=a+b;
 dbms_output.put_line(sums);
 end;
 
 --调用存储过程

--（1）在SQL>提示符下调用过程，execute过程名(参数……)
 set serverout on;
 execute add1();
 
 
 --（2）在PL/SQL块中调用过程，过程名（参数……）
 begin
 add1();
 end;

--(2)有参无返回
 public void add2(int a,int b){
 int sum =a+b;
 <pre name="code" class="plain"> System.out.println(sum);

\}--调用add2();

--in 输入参数，默认值
 create or replace procedure add2(a number,b number)
 is
 sums number;
 begin
 sums:=a+b;
 dbms_output.put_line(sums);
 end;

<pre name="code" class="plain">--在PL/SQL块中调用过程，过程名（参数……）

begin add2(160,40);end;

--(3)无参有返回
 public int add3(){
 int a =10;
 int b=20;
 int sum =a+b;
 return sum;
 }
 
 --调用
 int sums=add3();

--out输出参数
 create or replace procedure add3(sums out number)
 is
 --声明变量
 a number default 190;
 b number default 20;
 begin
 sums:=a+b;
 end;
 
 
 --调用
 declare
 sums number; --声明变量
 begin
 add3(sums);
 dbms_output.put_line(sums);
 end;

--(4)有参有返回
 public int add4(int a,int b){
 return a+b;
 }
 
 --调用
 int sums=add4(10,20);

--in,out
 create or replace procedure add4(a in number,b in number,sums out number)
 is
 begin
 sums:=a+b;
 end;
 
 
 --1)按位置传递参数
 --调用
 declare
 sums number; --声明变量
 begin
 add4(10,50,sums);
 dbms_output.put_line(sums);
 end;

<pre name="code" class="plain">--2)按名称传递参数 (必须是存储过程的变量名)
 declare
 sums1 number; --声明变量
 begin
 add4(a =>150,b =>50,sums =>sums1);
 dbms_output.put_line(sums1);
 end;
 
 --3)混合方式传递参数
 declare
 sums1 number; --声明变量
 begin
 add4(10,350,sums =>sums1);
 dbms_output.put_line(sums1);
 end;

--IN OUT 即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。

如： result=2*2*2 
      --创建存储过程
     create or replace procedure pro(a in out number)
     is
      b number default 3;
     begin
        a:=power(a,b);
     end;
     
     
     --调用
     declare
        a number:=&no;
     begin
       pro(a);
       dbms_output.put_line(a);
     end;

**案例1： 删除指定员工记录**

**IN输入参数(默认值可以省略不写)**

create or replace procedure delEmps(v_empno emps.empno%type)
     is
      --1)声明变量
       ex exception;
     begin
        --执行语句
        delete from emps where empno=v_empno;
        
        --判断
        if sql%notfound  then 
           raise ex;  --2)抛出异常
        end if;
        
        --可以删除
         DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');
         
         --3)处理异常
     exception
         when ex then
             DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
         when others then
            DBMS_OUTPUT.PUT_LINE(sqlcode||'---'||sqlerrm);
    
     end;
     
     
     --调用
     begin
       delemps(1201);
     end;

**案例2：计算指定部门的工资总和，并统计其中的职工数量。**

**IN输入参数(默认值可以省略不写)，out输出参数**

CREATE OR REPLACE PROCEDURE proc_demo
    (
        dept_no NUMBER,
        sal_sum OUT NUMBER,
        emp_count OUT NUMBER
      )
    IS
    BEGIN
        SELECT SUM(sal), COUNT(1) INTO sal_sum, emp_count
        FROM emps WHERE deptno = dept_no;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
    END;
    
    --PL/SQL执行调用
    DECLARE
    v_no number:=&no;
    V_num NUMBER;
    V_sum NUMBER(8, 2);
    BEGIN
      Proc_demo(30, v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);
      Proc_demo(sal_sum => v_sum, emp_count => v_num,dept_no => v_no);
    DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);
    END;

**用命令窗口/CMD下如何调用？**

var sums number; --工资总和
 var counts number; --人数
 execute scott.proc_demo(10,:sums,:counts); --(必须写：变量名)
 --输出结果
 print sums
 print counts

**案例3：查询所有的数据**

--静态游标---》不能达到客户端;(应用程序交互)
    create or replace procedure allEmps
    is
     --声明游标
     cursor c1 is select * from emps;
    begin
      for v_row in c1 loop
        dbms_output.put_line(v_row.ename||'---->'||v_row.job||'--->'||v_row.sal);
      end loop;
    end;
    
    --调用
    begin
      allemps();
    end;

**注意：另一个用户WY如何调用SCOTT用户下的存储过程，必须授予权限方可。**

**语法：GRANT EXECUTE ON 存储过程名 TO 用户名**

**grant execute on ( 过程, 函数, 程序包或程序包体) to public;**

**在命令窗口调用:**

SQL>conn wy/wyaaa;
    SQL> set serverout on;
    SQL>execute scott.add2(10,80);

六、删除存储过程

语法：`DROP``PROCEDURE``[``user``.]Procudure_name;`

`drop procedure scott.add1;`