Oracle PL/SQL 存储过程

一、存储过程的概念
   存储过程（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 [<span style="color:#ff99ff;">OR REPLACE</span>] 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 |<span style="color:#6666cc;"> CURRENT_USER </span>]
{ IS | AS }
  <声明部分>
BEGIN
  <执行部分>
EXCEPTION
  <可选的异常错误处理程序>
END procedure_name;

 说明：

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

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

五、存储过程的使用 

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

<strong>--(1)无参无返回</strong>
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;

--调用存储过程

<span style="color:#ff0000;">--（1）在SQL>提示符下调用过程，execute过程名(参数……)</span>
set serverout on;
execute add1();


<span style="color:#ff0000;">--（2）在PL/SQL块中调用过程，过程名（参数……）</span>
begin
 add1();
end;

<strong>--(2)有参无返回</strong>
public void add2(int a,int b){
  int sum =a+b;
<pre name="code" class="plain"> System.out.println<span style="font-family: Arial, Helvetica, sans-serif;">(sum);</span>

}--调用add2();

<strong>--in 输入参数，默认值</strong>
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"><span style="color: rgb(255, 0, 0);">--在PL/SQL块中调用过程，过程名（参数……）</span>

begin add2(160,40);end;

<strong>--(3)无参有返回</strong>
public int add3(){
  int a =10;
  int b=20;
  int sum =a+b;
  return sum;
}

--调用
int sums=add3();

<strong>--out输出参数</strong>
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;

<strong>--(4)有参有返回</strong>
public int add4(int a,int b){
  return a+b;
}

--调用
int sums=add4(10,20);

<strong>--in,out</strong>
create or replace procedure add4(a in number,b in number,sums out number)
is
begin
    sums:=a+b;
end;


<span style="color:#3366ff;"><strong>--1)按位置传递参数</strong></span>
--调用
declare
  sums number;  --声明变量
begin
  add4(10,50,sums);
  dbms_output.put_line(sums);
end;

<pre name="code" class="plain"><span style="color:#6633ff;"><strong>--2)按名称传递参数 (必须是存储过程的变量名)</strong></span>
declare
  sums1 number;  --声明变量
begin
  add4(a =>150,b =>50,sums =>sums1);
  dbms_output.put_line(sums1);
end;

<span style="color:#6666cc;"><strong>--3)混合方式传递参数</strong></span>
declare
  sums1 number;  --声明变量
begin
  add4(10,350,sums =>sums1);
  dbms_output.put_line(sums1);
end;

--<strong>IN OUT <span style="color: rgb(51, 51, 51); font-family: 宋体; font-size: 14.399999618530273px; line-height: 20.799999237060547px;">即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。</span></strong>  

如： 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;  --<span style="font-family: Arial, Helvetica, sans-serif;">工资总和</span>
 var counts number;  --人数
<span style="font-size:14px;"> execute scott.proc_demo(10,:sums,:counts); --(必须写<span style="color:#ff0000;"><strong>：变量名)</strong></span></span>
 --输出结果
 print sums
 print counts<strong>
</strong>

 案例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;