Oracle_存储过程、函数、触发器、包

た入场券 2023-07-03 02:57 85阅读 0赞

## 存储过程 ##

*  语法：

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
    {
       AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    [EXCEPTION 错误处理部分]
    END [过程名];

1.  简单例子

create or replace procedure pro_hello
    is
    begin
      dbms_output.put_line('hello world!!');
    end;
    
    -- 调用
    call pro_hello();

1.  带参数

-- 2. 带参数的过程
    /*
       存储过程的参数 输入输出类型
               in 默认类型 输入类型的参数 ，在过程中是不能进行修改的 
               out 输出类型的参数，过程中可以修改， 在过程调用后，可以获取到变化后的值的
               in out 即是输入又是输出
    */
    create or replace procedure getEmp(p_empno number, p_emp out emp%rowtype, p_type in out varchar2)
    is
    begin
      select * into p_emp from emp where empno = p_empno;
      p_type := p_type || '：查询成功';
    exception
      when others then
        p_type := p_type || '：查询失败';
    end;
    
    -- 注意： 包含输出参数的存储过程必须在plsql块中调用 不能使用call调用
    declare
       v_emp emp%rowtype;
       v_type varchar2(128) default '执行结果';
    begin
      getEmp(7369,v_emp,v_type); -- 调用存储过程
      dbms_output.put_line(v_type);
      dbms_output.put_line(v_emp.ename);
    end;

1.  默认值参数

create or replace procedure pro_person(p_name varchar2 default 'lisi', p_age number default 18)
    is 
    begin
       dbms_output.put_line(p_name||','||p_age);
    end;
    
    call pro_person(); -- 使用默认值
    call pro_person('zhagnsan');
    call pro_person('wangwu', 30); -- 位置参数 按照参数的位置传入参数
    call pro_person(p_age => 29); -- 关键字形式传入参数

## 函数 ##

函数 function 
      
      定义语法：
      CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)] －－参数是可选的
       RETURN 数据类型－－return 返回值类型，不可省略
       {
       AS|IS}
      [说明部分]
      BEGIN
      可执行部分
      RETURN (表达式)
      [EXCEPTION
      错误处理部分]
      END [函数名];
    
    -- 1. 简单例子
    create or replace function fun_sum(p_start number, p_end number)
    return number
    is 
       v_sum number := 0;
    begin
      for i in p_start..p_end loop
        v_sum := v_sum + i;
      end loop;
      return v_sum;
    end;
    
    
    -- 函数调用
    -- 注意：不能使用call调用 
    -- plsql中调用
    declare
       v_sum number;
    begin
      v_sum := fun_sum(1, 100);
      dbms_output.put_line(v_sum);
    end;
    -- sql语句中调用
    select fun_sum(1, 100) from dual;
    
    
    -- 2. 使用游标的例子 sys_refcursor 是游标的数据类型
    create or replace function fun_getemp(p_empno number) return sys_refcursor
    is
       v_emps sys_refcursor; -- 定义一个游标类型的变量
    begin
      if p_empno is null then
        
         open v_emps for select * from emp ;
      else
         open v_emps for select * from emp where empno = p_empno;
      end if;
      return v_emps;
    end;
    
    -- 调用
    select fun_getemp(null) from dual;
    
    -- plsql中调用
    declare
       v_emp emp%rowtype;
       v_emps sys_refcursor;
    begin
      v_emps := fun_getemp(null);
      
      loop
        fetch v_emps into v_emp ;
      exit when v_emps%notfound;
           dbms_output.put_line(v_emp.ename);
      end loop;
    end;

## 触发器 ##

触发器 语法：
      CREATE [OR REPLACE] TRIGGER 触发器名
      {BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]
      ON 表名
      [FOR EACH ROW]
      [WHEN ( 触发条件)]
      declare
      声明部分
      BEGIN
      主体部分
      END;
    
    -- 例子
    create or replace trigger tri_dept
    before update or delete or insert
    on dept
    for each row -- 行级触发器
    declare
    begin
      case 
        when inserting then
           dbms_output.put_line('新增部门'||:new.deptno);
        when updating then
           dbms_output.put_line('修改部门,新:'||:new.dname||',旧：'||:old.dname);
        when deleting then
          dbms_output.put_line('删除部门'||:old.deptno);
       end case;
    end;
    
    
    -- 触发器实现主键自动递增例子
    create table dept_2 as select * from dept;
    
    select * from dept_1;
    truncate table dept_1;
    --1. 创建序列对象
    create sequence seq_deptno ;
    
    -- 2.创建触发器
    -- insert into dept values (id,dname,loc)
    -- insert into dept (dname,loc) values (dname,loc)
    create or replace trigger tri_dept_1
    before insert on dept_1
    for each row
    declare
    begin
      if :new.deptno is null then
        :new.deptno := seq_deptno.nextval; -- 如果新增的记录中deptno值为空 则将新增的记录中的主键赋值为序列的一个值
      end if; 
    end;

## 包 ##

/*
      1. 包定义
      语法：
      CREATE [OR REPLACE] PACKAGE 包名
      {IS|AS}
      公有变量定义
      公有类型定义
      公有游标定义
      公有异常定义
      函数说明
      过程说明
      END;
    
    */
    create or replace package employee
    is
           v_emp emp%rowtype; -- 定义一个公共变量
           procedure showdetail; -- 定义一个过程 显示员工的 具体信息
           procedure getemp(p_empno number); -- 定义一个过程 根据编号 查询员工
           -- 定义一个函数
           function sum(p_start number,p_end number) return number;
    end;
    
    /*
           2. 包体的定义
           语法：
           CREATE [OR REPLACE] PACKAGE BODY 包名
          {IS|AS}
          私有变量定义
          私有类型定义
          私有游标定义
          私有异常定义
          函数定义
          过程定义
          END;   
    */
    create or replace package body employee
    is
           v_num number := 0; -- 私有变量 只能在包中只用
           -- 实现过程
          procedure showdetail is
          begin
            if v_emp.empno is not null then
              dbms_output.put_line('员工信息如下：');
              dbms_output.put_line(v_emp.ename);
              dbms_output.put_line(v_emp.empno);
              dbms_output.put_line(v_emp.sal);
            else 
              dbms_output.put_line('没有任何员工信息');
            end if;
          end;
          
          
          procedure getemp(p_empno number) is
          begin
            select * into v_emp from emp where empno = p_empno;
          exception
            when others then
              dbms_output.put_line('查询员工失败！');
              v_emp := null;
          end;      
          
          
          
          function sum(p_start number,p_end number) return number
          is
                 
          begin
            for i in p_start..p_end loop
              v_num := v_num + i;
            end loop;
            return v_num;
          end;
          
    end;
    
    
    -- 调用
    begin
      employee.getemp(7369);
      employee.showdetail();
      dbms_output.put_line(employee.sum(1, 100));
    end;