【PLSQL】PLSQL基础

拼搏现实的明天。 2023-10-15 10:29 145阅读 0赞

#### 文章目录 ####

*  一：记录类型
 *   *  1.语法
     *  2.代码实例
 *  二：字符转换
 *  三：%TYPE和%ROWTYPE
 *   *  1.%TYPE
     *  2.%ROWTYPE
 *  四：循环
 *   *  1.LOOP
     *  2.WHILE（推荐）
     *  3.数字式循环
 *  五：游标
 *   *  1.游标定义及读取
     *  2.游标属性
     *  3.NO\_DATA\_FOUND和%NOTFOUND的区别
 *  六：异常错误
 *   *  1.异常处理
     *  2.非预定义异常处理
     *  3.用户自定义的异常处理
 *  七：存储过程或函数
 *   *  1.函数
     *   *  函数调用过程：
 *  八：包
 *   *  1.包的创建
     *  2.包的调用
 *  九：触发器
 *   *  1.触发器的组成
     *  2.语法
     *  3.触发器的限制
     *  4.实例
     *  5.创建替代（INSTEAD OF）触发器

## 一：记录类型 ##

### 1.语法 ###

TYPE record_type IS RECORD(
    	column1 type,
    	colunm2 type,
    	… …
    Variable_name record_type;

### 2.代码实例 ###

declare
      type test_rec is record(    --test_rec记录类型
           l_name varchar2(30),
           d_id number(4));
      v_emp test_rec;   --v_emp变量名
    begin 
      v_emp.l_name := '张三';
      v_emp.d_id := 1234;
      dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
    end;

可以使用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相匹配即可.

create table cux.employee
    (last_name varchar2(20),
    department_id number(4));
    
    insert into cux.employee values('李四',1235,234);
    
    declare
      type test_rec is record(    --test_rec记录类型
           l_name varchar2(30),
           d_id number(4));
      v_emp test_rec;   --v_emp变量名
    begin 
      select last_name, department_id into v_emp
      from cux.employee
      where employee_id = 234;
      
      dbms_output.put_line(v_emp.l_name || ', ' || v_emp.d_id);
    end;

## 二：字符转换 ##

![在这里插入图片描述][093733ef18374f55bfb6f319d0d82ecc.png]

## 三：%TYPE和%ROWTYPE ##

### 1.%TYPE ###

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE

使用%TYPE的优点：

*  所引用的数据库列的数据类型不必知道;
 *  所引用的数据库列的数据类型可以实时改变.

declare
      type test_rec is record(
           l_name cux.employee.last_name%type,
           d_id cux.employee.department_id%type);
      v_emp test_rec;
    begin
      select last_name,
      department_id into v_emp
      from cux.employee where employee_id = 234;
      dbms_output.put_line(v_emp.l_name || ', ' || v_emp.d_id);
    end;

### 2.%ROWTYPE ###

![在这里插入图片描述][9d2561c075da4083a2386f4f5e77aec1.png]

## 四：循环 ##

### 1.LOOP ###

LOOP
    	要执行的语句;
    	EXIT WHEN<条件语句>;  --条件满足,退出
    END LOOP
    
    declare
      int NUMBER(2) := 0;
    begin
      LOOP
        int := int + 1;
        dbms_output.put_line('int的当前值为:' || int);
        EXIT WHEN int = 10;
      END LOOP;
    END;

### 2.WHILE（推荐） ###

WHILE<布尔表达式> LOOP
    	要执行的语句;
    END LOOP;
    
    DECLARE
      x NUMBER(2) := 0;
    BEGIN
      WHILE x < 10 LOOP
        x := x + 1;
        dbms_output.put_line('x的当前值为:' || x);
      END LOOP;
    END;

### 3.数字式循环 ###

FOR 循环计数器 IN[REVERSE] 下限 .. 上限 LOOP
    	要执行的语句
    END LOOP;

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或者表达式。可以使用**EXIT**退出循环。

begin
      FOR i in reverse 2 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE('i的值为' || i);
      END LOOP;
    end;

## 五：游标 ##

### 1.游标定义及读取 ###

--游标FOR读取
    declare 
      cursor c_emp(dep_id number default 1236) is 
             select last_name,employee_id epid
             from cux.employee
             where department_id = dep_id;
    begin
      for v_emp in c_emp loop
        DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ', ' || v_emp.epid);
      end loop;
    end;

### 2.游标属性 ###

*  %FOUND：布尔类型属性，当最近一次读记录时成功返回，则值为TRUE；
 *  %NOTFOUND：布尔类型属性，与%FOUND相反；
 *  %ISOPEN：布尔型属性，当游标已打开时返回TRUE；
 *  %ROWCOUNT：数字型属性，返回已从游标中读取的记录数。

### 3.NO\_DATA\_FOUND和%NOTFOUND的区别 ###

SELECT … INTO 语句触发NO\_DATA\_FOUND；

当一个显示游标的WHERE子句未找到时触发%NOTFOUND；当UPATE或DELETE语句的WHERE子句未找到时触发SQL%NOTFOUND；在提取循环中要用%NOTFOUND或%FOUND来确定循环退出条件，不要用NO\_DATA\_FOUND。

## 六：异常错误 ##

![在这里插入图片描述][9a2344bfec034c03bc46439523ce16b8.png]

### 1.异常处理 ###

EXCEPTION 
    	WHEN first_exception THEN <code to handle first exception>
    	WHEN second_exception THEN <code to handle second exception>
    	WHEN OTHERS THEN <code to handle others exception>
    END;

异常处理可以按照任意次序排列，但OTHERS必须放在最后。

declare 
      -- Local variables here
      v_empid cux.employee.employee_id%type := &v_empid;
      v_sal cux.employee.salary%type;
    /* 预定义异常处理 */
    begin
      -- Test statements here
      select salary into v_sal
      from cux.employee
      where employee_id = v_empid
      for update;
      if v_sal <= 3000 then 
        update cux.employee set salary = salary+1000
        where employee_id = v_empid;
        DBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '工资已更新');
      else 
        DBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '工资不需更新');
      end if;
    exception
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('编号为：'|| v_empid || '员工不存在');
      WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('数据行数太多，请使用游标');
      WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他错误');
    end;

### 2.非预定义异常处理 ###

1.  在PL/SQL块定义部分定义异常情况 <异常情况> EXCEPTION
2.  将其定义好的异常情况，与标准的ORACLE错误联系起来，使用PRAGMA EXCEPTION\_INIT语句；
    
    PRAGMA EXCEPTION(<异常情况>,<错误代码>)；
3.  在PL/SQL异常情况处理部分对异常情况做出相应处理。

### 3.用户自定义的异常处理 ###

用户定义的异常错误是通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。

步骤：

1.  在PL/SQL块的定义部分定义异常情况；
2.  <异常情况> EXCEPTION
    
    RAISE<异常情况>;

在PL/SQL块的异常情况处理部分对异常情况做出相应处理。

## 七：存储过程或函数 ##

把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

### 1.函数 ###

IN参数标记表示传递给函数的值在该函数执行中不改变；OUT标记表示一个值在函数中进行计算并通过该参数传递给调用语句；IN OUT标记表示传递给函数的值可以变化并传递给调用语句。若省略标记，则参数隐含为IN。因为函数需要一个返回值，所以RETURN包含返回结果的数据类型。

create or replace function get_salary(
           dep_id cux.employee.department_id%type (default 1235),
           emp_count out number)
           return number
           is
           v_sum number;
    begin
      select sum(salary), count(*) into v_sum, emp_count
      from cux.employee
      where department_id = dep_id;
      
      return v_sum;
    exception
      when no_data_found then 
        DBMS_OUTPUT.PUT_LINE('查询的数据不存在');
      when others then 
        DBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);
        
    end;

#### 函数调用过程： ####

1.位置表示法；

declare
      v_num number;
      v_sum number;
    begin
      v_sum := get_salary(1237,  v_num);
      
      DBMS_OUTPUT.PUT_LINE('1237号部门的工资总和：' || v_sum || ' 人数：' || v_num);
    end;

2.名称表示法

形式参数必须和函数定义时声明的形式参数名称相同，顺序可以任意排列。

v_sum := get_salary(dep_id => 1237, emp_count => v_num);

3.混合表示法

使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

v_sum := get_salary(1237, emp_count => v_num);

## 八：包 ##

### 1.包的创建 ###

create or replace package demo_pack is
    
      -- Author  : 11313321
      -- Created : 2023/8/22 8:45:06
      -- Purpose : 练习测试
      
      -- Public type declarations
      EmpRec cux.employee%ROWTYPE;
    
      -- Public function and procedure declarations
      function add_emp(
        last_name VARCHAR2, dept_id number, emp_id NUMBER, salary number)
        return number;
      function remove_emp(emp_id number)
        return number;
      procedure query_empl(emp_id number);
    
    end demo_pack;

**包主体的创建方法，它实现上面所声明的包定义：**

create or replace package body demo_pack is
    
      function add_emp(last_name VARCHAR2, dept_id number, emp_id NUMBER, salary number)
        return number
        is
        empno_remaining exception;
        pragma exception_init(empno_remaining, -1);
      begin
        insert into cux.employee values(last_name, dept_id, emp_id, salary,TO_DATE('2023,5,20','yyyy-mm-dd'));
        if sql%found then
           return 1;
        end if;
      exception
        when empno_remaining then 
           return 0;
        when others then 
           return -1;
      end add_emp;
    
      function remove_emp(emp_id number)
        return number
        is
      begin
        delete from cux.employee where employee_id = emp_id;
        if sql%found then 
          return 1;
        else
          return 0;
        end if;
      exception
        when others then
          return -1;
      end remove_emp;
      
      procedure query_empl(emp_id number)
        is
      begin
        select * into EmpRec from cux.employee where employee_id = emp_id;
      exception
        when no_data_found then
          DBMS_OUTPUT.PUT_LINE('数据库中没有该员工');
        when too_many_rows then
          DBMS_OUTPUT.PUT_LINE('程序运行错误！请使用游标');
        when others then
          DBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);
      end query_empl;
      
    begin
      -- Initialization
      null;
    end demo_pack;

### 2.包的调用 ###

对包内共有元素的调用格式为：报名.元素名称

declare
      var number;
    begin
      var := demo_pack.add_emp('老马', 1476, 789, 3800);
      if var=-1 then
        DBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);
      elsif var=0 then
        DBMS_OUTPUT.PUT_LINE('该记录已存在');
      else 
        DBMS_OUTPUT.PUT_LINE('添加记录成功');
        demo_pack.query_empl(789);
        DBMS_OUTPUT.PUT_LINE(demo_pack.EmpRec.employee_id||'--'||
           demo_pack.EmpRec.last_name||'--'||demo_pack.EmpRec.department_id);
        var := demo_pack.remove_emp(788);
        if var=-1 then
          DBMS_OUTPUT.PUT_LINE(sqlcode || '--' || sqlerrm);
        elsif var=0 then
          DBMS_OUTPUT.PUT_LINE('该记录不存在');
        else
          DBMS_OUTPUT.PUT_LINE('删除记录成功');
        end if;
      end if;
    end;

## 九：触发器 ##

### 1.触发器的组成 ###

*  触发事件：在何种情况下触发TRIGGER，例如：INSERT，UPDATE，DELETE
 *  触发时间：触发之前（BEFORE）、之后（AFTER）
 *  触发器本身：触发之后的目的和意图
 *  触发频率：语句级(STATEMENT)触发器和行级(ROW)触发器。
    
     *  语句级：当触发某事件时，该触发器只执行一次
     *  行级：当某事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
     *  行触发器和语句触发器的区别表现在：行触发器要求当一 个 DML 语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激 活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。

### 2.语法 ###

CREATE [OR REPLACE] TRIGGER trigger_name
    	{BEFORE | AFTER}
    	{INSERT|DELETE|UPDATE[OF column[,column...]]}
    	ON [schema.] table_name
    	[FOR EACH ROW]
    	[WHEN condition]
    	trigger body;

FOR EACH ROW选项说明触发器为多行触发器。当省略FOR EACH ROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF触发器则为行触发器。

### 3.触发器的限制 ###

*  触发器体内的SELECT语句只能为SELECT…INTO…结构，或者为定义游标所使用的SELECT语句。
 *  触发器中不能使用数据库事务控制语句COMMIT；ROLLBACK；SAVEPOINT语句。
 *  由触发器所调用的过程或函数也不能使用数据库事务控制语句。

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值

*  :NEW 修饰符访问操作完成后列的值
 *  :OLD 修饰符访问操作完成前列的值

### 4.实例 ###

--创建表
    create table cux.emp_his as
    select * from cux.employee
    where 1 = 2;
    
    --创建触发器
    create or replace trigger del_emp_trigger
           before delete on cux.employee for each row
    begin
      insert into cux.emp_his(last_name, department_id, employee_id, salary)
      values(:old.last_name, :old.department_id, :old.employee_id, :old.salary);
    end;

### 5.创建替代（INSTEAD OF）触发器 ###

CREATE [OR REPLACE] TRIGGER trigger_name
    INSTEAD OF
    {INSERT | DELETE | UPDATE [OF column [, column …]]}
    ON [schema.] view_name
    [FOR EACH ROW ]
    [WHEN condition]
    trigger_body;

INSTEAD OF用于对视图的DML触发。

[093733ef18374f55bfb6f319d0d82ecc.png]: https://img-blog.csdnimg.cn/093733ef18374f55bfb6f319d0d82ecc.png
[9d2561c075da4083a2386f4f5e77aec1.png]: https://img-blog.csdnimg.cn/9d2561c075da4083a2386f4f5e77aec1.png
[9a2344bfec034c03bc46439523ce16b8.png]: https://img-blog.csdnimg.cn/9a2344bfec034c03bc46439523ce16b8.png