Oracle存储过程、函数、包的使用

Oracle存储过程

1、创建存储过程（无参数）

-- 创建存储过程
create or replace procedure proc_emp as
       cursor cur_cus is select * from emp where deptno = 10;
begin
  for v_row in cur_cus loop
    dbms_output.put_line(v_row.empno||' | '||v_row.ename);
    end loop;
end proc_emp;

PLSQL执行存储过程

-- 先在Command Window窗口打开显示输出
set serveroutput on;
-- 再执行存储过程
exec proc_emp;

JDBC调用执行存储过程(在PLSQL中执行此命令，将查询到的结果存到缓存中，当再次调用 exec proc_emp时，将缓存中存到的所有数据展示出来，总共有两次记录)

当过程无参时，也需要小括号

call proc_emp();

2、创建存储过程（带参数）

注意：形参不能写精度，否则编译错误

create or replace procedure proc_emp(dno number) as
       cursor cur_cus is select * from emp where deptno = dno;
begin
  for v_row in cur_cus loop
    dbms_output.put_line(v_row.empno||' | '||v_row.ename);
    end loop;
end proc_emp;

PLSQL执行存储过程

exec proc_emp(30);

jdbc执行存储过程

call proc_emp(30);

使用PLSQL存储过程对话框建立存储过程生成标准的sql语句

创建多个参数的存储过程

create or replace procedure proc_emp(dno number,dname varchar2) as
begin
  insert into dept values(dno,dname);
  -- DDL语句不会自动提交事务，必须手动提交事务
  commit;
end proc_emp;

注意：存储过程是没用返回值的。

3、形参的分类：

• 1- 输入类型参数： 参数名 IN 参数类型； (默认的参数类型)
• 2- 输出类型参数： 参数名 OUT 参数类型；
• 3- 输入输出类型参数： 参数名 IN OUT 参数类型；

定义带输出类型参数的过程

create or replace procedure proc_get_sal(eno number, esal out number) is
begin
  select sal into esal from emp where empno = eno;
end proc_get_sal;

— 执行带输出类型参数的过程

• 1- 定义一个变量

  SQL> var v_sal number;
  

  • 2- 使用变量承载输出类型参数的信息； 注意：变量的使用格式： :变量名

    SQL> exec proc_get_sal(7788, :v_sal);
    -- call方式调用
    SQL> call proc_get_sal(7839,:v_sal);
    

    • 3- 打印输出变量

      print v_sal;
      

定义输入输出类型参数的过程

create or replace procedure proc_get_sal(v_val in out number) is
begin
  select sal into v_val from emp where empno = v_val;
end proc_get_sal;

• 调用带输入输出类型参数

  -- 1- 定义变量
  var v_val number;
  -- 2- 赋予输入的值
  begin 
     :v_val:=7839; 
  end;
  -- 3- 执行
  exec proc_get_sal(:v_val);
  -- 再次查看结果
  print v_val;
  

4、实参的传值方式

• 1- 按位置传值

  * SQL> exec proc_add_dept('dev', 50, 'lintong');
  

  • 2- 按照名称传值

  SQL> exec proc_add_dept(p_loc => 'lintong',p_dname => 'dev',p_dno => 50);
  

• 3- 混合传递

  SQL> exec proc_add_dept('dev2',p_loc => 'lintong',p_dno => 70);
  

注意： 先进行按位置，再按名称，并且一旦开始按照名称传递就不能再使用按位置传递。

错误的示范：

SQL> exec proc_add_dept('dev3',p_dno => 80,'changan');

使用参数类型的规范：

• 1- 形参尽量不要使用 out 或者 in out 类型参数
• 2- 使用按照位置传递实参，不要使用按名称和混合传递；

存储函数

存储过程和存储函数的两个区别：

• 函数定义时必须有返回对应参数类型，存储过程则没用要求。
• 函数执行操作后必须有返回变量值，存储过程则没用要求。

创建存储函数

create or replace function fun_get_sal(eno number) return number
is
  v_result number;     
begin
  select sal into v_result from emp where empno=eno;
  return v_result;
end;

执行函数的三种方式

• 1、定义变量将函数返回值赋值给变量

  -- 1-定义接收函数返回值的变量
  SQL> variable v_sal number;
  -- 2-调用函数将返回值传递给变量
  SQL> exec :v_sal := fun_get_sal(7566);
  

• 2、直接输出的方式调用函数（记得输出语句在语句块中）

SQL> begin 
          dbms_output.put_line(fun_get_sal(7566));
     end;
     /

• 3、在SQL中调用

  查询工资占本部门总工资的百分比

  一、先创建函数

  注意：形参和返回类型不能带精度，定义的其他变量可以带精度

  create or replace function fun_get_dept_sal(esal number, dno number) return varchar2 is
    FunctionResult varchar2(7);
    
  begin
     select round(esal / (select sum(sal) from emp where deptno=dno)*100,2)||'%' into FunctionResult from dual;
    return(FunctionResult);
  end fun_get_dept_sal;
  

  二、调用函数返回结果

  select e.*,fun_get_dept_sal(e.sal,e.deptno) per_sal
  from emp e;
  

  PL/SQL包

  包就是一个PL/SQL的相关对象的逻辑分组和单个对象存储在数据库中的存储程序单元

  相关的PL/SQL对象可以是常量、变量、游标、异常、过程和函数

  包的组成：

  1、包规范部分（也成为包的声明部分）

  2、包主体部分

  包的定义，分为声明（定义）部分（packages文件夹下）

  注意：包分为声明部分和主体部分，声明部分全为public,外部能直接访问的，主体部分为私有部分，外部不能直接访问，声明部分和私有部分的名称要一致，当主体部分调用声明部分的公有方法时，公有函数内部调用主体部分的私有函数时，私有部分必须在公有方法的前面，才能调用，是面向过程调用，顺序执行

  create or replace package pak_emp is
  
    -- Public variable declarations
    fie_dno number;
    fie_sal number;
  
    -- Public function and procedure declarations
    function get_sal(eno number) return number;
  
    procedure print_sal(eno number);
  
  end pak_emp;
  

  主体（实现）部分（package bodies文件夹下）

  create or replace package body pak_emp is
  
    -- Private variable declarations
    fie_ename emp.ename%type;
  
    -- Function and procedure implementations
     function get_sal(eno number) return number is
       begin
         select sal into fie_sal from emp where empno = eno;
         return fie_sal;
       end get_sal;
  
    -- private function or procedure
    procedure print_private_sal(eno number) is
      begin
        dbms_output.put_line(get_sal(eno));
      end print_private_sal;
  
    -- public procedure
    procedure print_sal(eno number) is
      begin
        print_private_sal(eno);
      end print_sal;
      
  end pak_emp;
  

调用包中的函数或者过程、属性等

注意：只能调用声明部分的公有属性、函数、过程等，不能调用主体部分的私有数据

调用过程: 使用包名.过程名调用

exec pak_emp.print_sal(7566);

对主体部分属性进行赋值调用并输出

注意： 对变量进行赋值操作使用 := 在语句块中执行完必须写/

begin pak_field.field1 := 10;pak_field.field2 := 'abc'; 
dbms_output.put_line(pak_field.field1||' | '||pak_field.field2);
end;
/

当更改了主体部分和声明部分的sql时，就要在packages和package bodies文件夹下分别右键点击recompare重新编译

声明部分有的属性或者函数，主体部分必须实现，否则主体部分自定义的私有函数，外部无法调用，允许函数或者过程只在声明部分声明，不去主体部分实现也是可以的