Oracle总结第三篇【PLSQL】

绝地灬酷狼 2021-09-17 07:08 385阅读 0赞

# PLSQL介绍 #

**PLSQL是Oracle对SQL99的一种扩展**，基本每一种数据库都会对SQL进行扩展，Oracle对SQL的扩展就叫做PLSQL…

SQL99是什么

*  （1）**是操作所有关系型数据库的规则**
 *  （2）是第四代语言
 *  （3）**是一种结构化查询语言**
 *  （4）只需发出合法合理的命令，就有对应的结果显示

SQL的特点

*  （1）**交互性强，非过程化**
 *  （2）数据库操纵能力强，只需发送命令，无需关注如何实现
 *  （3）多表操作时，自动导航简单，例如：  
    
    
     *  select emp.empno,emp.sal,dept.dname
     *  from emp,dept
     *  where emp.deptno = dept.deptno
 *  （4）容易调试，错误提示，直接了当
 *  （5）**SQL强调结果**

PLSQL是什么

*  是专用于Oracle服务器，在SQL基础之上，**添加了一些过程化控制语句，叫PLSQL**
 *  过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。
 *  **PLSQL强调过程**

![这里写图片描述][SouthEast]

为什么要用PLSQL

*  因为SQL是第四代命令式语言，**无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处**，
 *  SQL和PLSQL不是替代关系，是弥补关系

## PLSQL语法 ##

declare和exception都是可以省略的，`begin和end;/`是不能省略的。

[declare]
              变量声明;
          变量声明;
         begin DML/TCL操作;
          DML/TCL操作;
         [exception]
              例外处理;
          例外处理;
         end;
         /

在PLSQL程序中：**；号表示每条语句的结束，/表示整个PLSQL程序结束**

PLSQL与SQL执行有什么不同：

*  （1）SQL是单条执行的
 *  （2）PLSQL是整体执行的，不能单条执行，整个PLSQL结束用/，其中每条语句结束用；号

--------------------

## PLSQL变量 ##

既然PLSQL是注重过程的，那么写过程的程序就肯定有基本的语法，首先我们来介绍PLSQL的变量

PLSQL的变量有4种

*  **number**
 *  **varchar2**
 *  **与列名类型相同**
 *  与整个表的列类型相同

![这里写图片描述][SouthEast 1]

### **例子** ###

写一个PLSQL程序，输出"hello world"字符串，语法：dbms_output.put_line('需要输出的字符串');
    begin --向SQLPLUS客户端工具输出字符串 dbms_output.put_line('hello 你好');
    end;
    /
    
    注意：
    dbms_output是oracle中的一个输出对象
    put_line是上述对象的一个方法，用于输出一个字符串自动换行 
    
    设置显示PLSQL程序的执行结果，默认情况下，不显示PLSQL程序的执行结果，语法：set serveroutput on/off;
    set serveroutput on;
    
    使用基本类型变量,常量和注释，求10+100的和
    declare
        --定义变量
        mysum number(3) := 0;
        tip varchar2(10) := '结果是';
    begin /*业务算法*/ mysum := 10 + 100;
        /*输出到控制器*/
        dbms_output.put_line(tip || mysum);
    end;
    /
    
    输出7369号员工姓名和工资，格式如下：7369号员工的姓名是SMITH,薪水是800，语法：使用表名.字段%type
    declare
        --定义二个变量，分别装姓名和工资
        pename emp.ename%type;
        psal   emp.sal%type;
    begin --SQL语句 --select ename,sal from emp where empno = 7369;
        --PLSQL语句，将ename的值放入pename变量中，sal的值放入psal变量中 
        select ename,sal into pename,psal from emp where empno = 7369;
        --输出
        dbms_output.put_line('7369号员工的姓名是'||pename||',薪水是'||psal);    
    end;
    /
    
    输出7788号员工姓名和工资，格式如下：7788号员工的姓名是SMITH,薪水是3000，语法：使用表名%rowtype
    declare
        emp_record emp%rowtype;
    begin select * into emp_record from emp where empno = 7788;
        dbms_output.put_line('7788号员工的姓名是'||emp_record.ename||',薪水是'||emp_record.sal);
    end;
    /

何时使用%type，何时使用%rowtype？

*  当定义变量时，**该变量的类型与表中某字段的类型相同时，可以使用%type**
 *  当定义变量时，该变量与整个表结构完全相同时，可以使用%rowtype，此时通过变量名.字段名，可以取值变量中对应的值
 *  项目中，常用%type

--------------------

## 判断体 ##

语法：

![这里写图片描述][SouthEast 2]

值得注意的是：**eslif**并没有写错的，它是少了一个e的

### **例子** ###

使用if-else-end if显示今天星期几，是"工作日"还是"休息日"
    declare
        pday varchar2(10);
    begin
        select to_char(sysdate,'day') into pday from dual;
        dbms_output.put_line('今天是'||pday);
        if pday in ('星期六','星期日') then
        dbms_output.put_line('休息日');
        else
        dbms_output.put_line('工作日');
        end if;
    end;
    /
    
    从键盘接收值，使用if-elsif-else-end if显示"age<16"，"age<30"，"age<60"，"age<80"
    declare
        age number(3) := &age;
    begin
        if age < 16 then
           dbms_output.put_line('你未成人');
        elsif age < 30 then
           dbms_output.put_line('你青年人');
        elsif age < 60 then
           dbms_output.put_line('你奋斗人');
        elsif age < 80 then 
           dbms_output.put_line('你享受人');
        else
           dbms_output.put_line('未完再继');
        end if;
    end;
    /

## 循环 ##

在PLSQL中，循环的语法有三种：

**WHILE循环：**

*  while后面跟的是循环条件，与java的差不多，LOOP和END LOOP是关键字\*\*

WHILE  total  <= 25000  
    
    LOOP
        total : = total + salary;
    END  LOOP;

**LOOP循环：**

*  exit后面的条件成立了才退出循环【有点绕】

Loop
       exit [when 条件成立];
       total:=total+salary;
    end loop;

**FOR循环：**

*  循环的递增只能是1，不能自定义步长

FOR   I   IN   1 . . 3  
    
    LOOP
    
    语句序列 ;
    
    END    LOOP ;

### **例子** ###

使用loop循环显示1-10
    declare
        i number(2) := 1;
    begin
        loop
            --当i>10时，退出循环
            exit when i>10;
            --输出i的值
            dbms_output.put_line(i);
            --变量自加
            i := i + 1;  
        end loop;
    end;
    /
    
    使用while循环显示1-10
    declare
        i number(2) := 1;
    begin
        while i<11 
        loop
            dbms_output.put_line(i);
            i := i + 1;
        end loop;
    end;
    /
    
    使用while循环，向emp表中插入999条记录
    declare
        i number(4) := 1;
    begin 
        while( i < 1000 )
        loop
            insert into emp(empno,ename) values(i,'哈哈');
            i := i + 1;
        end loop;   
    end;
    /
    
    使用while循环，从emp表中删除999条记录
    declare
        i number(4) := 1;
    begin 
        while i<1000
        loop
            delete from emp where empno = i;
            i := i + 1;
        end loop;
    end;
    /
    
    使用for循环显示20-30
    declare
        i number(2) := 20;
    begin
        for i in 20 .. 30
        loop
            dbms_output.put_line(i);
        end loop;
    end;
    /

--------------------

# 游标 #

Oracle中的游标其实就是类似JDBC中的resultSet，**就是一个指针的概念**。

既然是类似与resultSet，那么**游标仅仅是在查询的时候有效的**。

## 语法 ##

CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)] IS SELECT 语句；

### **例子** ###

使用无参光标cursor，查询所有员工的姓名和工资【如果需要遍历多条记录时，使用光标cursor，无记录找到使用cemp%notfound】
    declare
        --定义游标
        cursor cemp is select ename,sal from emp;
        --定义变量
        vename emp.ename%type;
        vsal   emp.sal%type;
    begin
        --打开游标，这时游标位于第一条记录之前
        open cemp;
        --循环
        loop
           --向下移动游标一次
           fetch cemp into vename,vsal; 
           --退出循环,当游标下移一次后，找不到记录时，则退出循环
           exit when cemp%notfound;
           --输出结果
           dbms_output.put_line(vename||'--------'||vsal);
        end loop;
        --关闭游标
        close cemp;
    end;
    /
    
    使用带参光标cursor，查询10号部门的员工姓名和工资
    declare
        cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
        pename emp.ename%type;
        psal emp.sal%type; 
    begin 
        open cemp(&deptno);
        loop
            fetch cemp into pename,psal;     
            exit when cemp%notfound;
            dbms_output.put_line(pename||'的薪水是'||psal);
        end loop;
        close cemp;
    end;
    /
    
    使用无参光标cursor，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400，要求显示编号，姓名，职位，薪水
    declare
        cursor cemp is select empno,ename,job,sal from emp;
        pempno emp.empno%type;
        pename emp.ename%type;
        pjob   emp.job%type;
        psal   emp.sal%type;
    begin
        open cemp;
        loop
            fetch cemp into pempno,pename,pjob,psal;
            --循环退出条件一定要写
            exit when cemp%notfound;
            if pjob='ANALYST' then
                update emp set sal = sal + 1000 where empno = pempno;
            elsif pjob='MANAGER' then
                update emp set sal = sal + 800 where empno = pempno;
            else 
            update emp set sal = sal + 400 where empno = pempno;
            end if;
        end loop;
        commit;
        close cemp;
    end;
    /

--------------------

# 例外、异常 #

我们在上面看PLSQL中的语法已经知道，有一个exception，这个在Oracle中称为例外，我们也可以简单看成就是Java中的异常。。。

## 语法： ##

在declare节中定义例外   
    out_of   exception ;
    
     在begin节中可行语句中抛出例外  
    raise out_of ；
    
     在exception节处理例外
    when out_of then …

![这里写图片描述][SouthEast 3]

### **例子** ###

使用oracle系统内置例外，演示除0例外【zero_divide】
    declare
        myresult number;
    begin myresult := 1/0;
        dbms_output.put_line(myresult);
    exception
        when zero_divide then 
         dbms_output.put_line('除数不能为0');
         delete from emp;  
    end;
    /
    
    使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】
    declare
        pename varchar2(20);
    begin select ename into pename from emp where deptno = 100;
        dbms_output.put_line(pename);
    exception
        when NO_DATA_FOUND then 
         dbms_output.put_line('查无该部门员工');
         insert into emp(empno,ename) values(1111,'ERROR');
    end;
    /

--------------------

# 存储过程和存储函数 #

在Oracle中，存储过程和存储函数的概念其实是差不多的，一般地，我们都可以混合使用。只不过有的时候有的情况使用过程好一些，有的情况时候函数的时候好一些。下面会讲解在什么时机使用过程还是函数的。

首先，我们在学习存储过程和存储函数之前，先要明白我们**为什么要学他**….

其实存储过程和函数就是**类似与我们在Java中的函数的概念**….

到目前为止，我们的PLSQL是有几个缺点的：

*  PLSQL不能将其封装起来，**每次调用的时候都要将整片代码复制来调用**
 *  有的时候，我们想要将PLSQL的代码保存起来，**只能自己手动保存在硬盘中**，非常麻烦
 *  我们学数据库就是为了让程序能够调用的，**但是PLSQL不能让程序（java）调用**

因此，存储过程和存储函数就能解决上面的问题了，能够将代码封装起来，保存在数据库之中，让编程语言进行调用….

![这里写图片描述][SouthEast 4]

## 存储过程和函数的语法 ##

过程的语法：

create [or replace] procedure 过程名[(参数列表)] as PLSQL程序体；【begin…end;/】

函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】 RETURN 返回值类型 AS PLSQL子程序体； 【begin…end;/】

**无论是过程还是函数，as关键字都代替了declare关键字。**

--------------------

### **过程例子** ###

**创建第一个过程：**

CREATE OR REPLACE PROCEDURE hello AS BEGIN dbms_output.put_line('hello world');
      END;

调用过程的三种方式：

*  exec过程名【SQLPLUS中使用】
 *  **PLSQL程序调用**
 *  Java调用

PLSQL调用

BEGIN hello();
    
    END;

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER) AS BEGIN UPDATE EMP SET sal = sal * 1.2 WHERE empno = pempno;
    
      END;

调用：

BEGIN bb(7369);
      END;

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

创建过程：**在过程中的参数，默认值是IN，如果是输出的话，那么我们要指定为OUT。**

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2) AS BEGIN SELECT ename, sal, job INTO pename, psal, pjob FROM emp WHERE empno = pempno;
      END;

调用：在调用的时候，使用到的psal，pname，pjob在调用的时候都没有定义的，因此我们**需要先定义变量后使用！**

DECLARE
    
      psal   emp.sal%TYPE;
      pename emp.ename%TYPE;
      pjob   emp.job%TYPE;
    
    BEGIN
      find(7369, psal, pename, pjob);
    
      dbms_output.put_line(psal || pename || pjob);
    
    END;/

### **函数例子** ###

创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER) --这里指定的是返回值类型 RETURN NUMBER AS income NUMBER;
      BEGIN SELECT sal * 12 INTO income FROM emp WHERE empno = pempno;
    
        /*在PLSQL中一定要有return语句*/
        RETURN income;
      END;

调用：在PLSQL中，赋值的语句不是直接“=”，而是:=

DECLARE
      income number;
    BEGIN income := findEmpIncome(7369);
      dbms_output.put_line(income);
    
    END;/

如果写的是=号，那么就会出现以下的错误：

[2017-07-11 13:58:14] [65000][6550] ORA-06550: 第 4 行, 第 10 列: 
    PLS-00103: 出现符号 "="在需要下列之一时：
     := . ( @ % ;
    ORA-06550: 第 4 行, 第 31 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     . ( ) , * % & -
       + / at mod remainder rem <an exponent (**)> and or ||
       multiset
    ORA-06550: 第 7 行, 第 4 列: 
    PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
     end
       not pragma final instantiable order overriding static member
       constructor map

--------------------

创建有参存储函数findEmpNameAndJobAndSal(编号)，查询7788号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER) --这里指定的是返回值类型 RETURN VARCHAR AS /*查询出来的字段与列名相同，就使用列名相同的类型就行了。*/ pename emp.ename%TYPE;
      BEGIN SELECT sal, ename, job INTO income, pename, pjob FROM emp WHERE empno = pempno;
    
        /*在PLSQL中一定要有return语句*/
        RETURN pename;
      END;

调用函数：

DECLARE
    
      /*输出的字段与列名的类型是相同的。*/
      income emp.sal%TYPE;
      pjob   emp.job%TYPE;
      pename emp.ename%TYPE;
    BEGIN
      pename := findEmpNameAndJobAndSal(7369, pjob, income);
      dbms_output.put_line(pename || pjob || income);
    
    END;/

## 过程与函数的使用场景 ##

我们发现过程与函数的区别其实是不大的，一般我们都可以用函数来实现的时候， 也可以使用过程来实现….

但是，总有些情况，使用函数比使用过程要好，使用过程比使用函数要好，那什么时候使用过程，什么时候使用函数呢？？？

不难发现的是，函数是必定要有一个返回值的，当我们在调用的时候，接受返回值就直接获取就行了。

也就是说

*  **当返回值只有一个参数的时候，那么就使用存储函数！**
 *  **当返回值没有参数或者多于一个参数的时候，那么就使用过程！**

## SQL与过程函数使用场景 ##

【适合使用】过程函数：

*  》需要长期保存在数据库中
 *  》需要被多个用户重复调用
 *  》业务逻辑相同，只是参数不一样
 *  》批操作大量数据，例如：批量插入很多数据

【适合使用】SQL：

*  》凡是上述反面，都可使用SQL
 *  》对表，视图，序列，索引，等这些还是要用SQL

--------------------

# 触发器 #

在PLSQL中也有个类似与我们Java Web中过滤器的概念，就是触发器…触发器的思想和Filter的思想几乎是一样的….

![这里写图片描述][SouthEast 5]

值得注意的是：对于触发器而言，是不针对查询操作的。也就是说：**触发器只针对删除、修改、插入操作！**

## 触发器语法 ##

CREATE [or REPLACE] TRIGGER 触发器名 { BEFORE | AFTER} { INSERT | DELETE|-----语句级 UPDATE OF 列名}----行级 ON 表名 -- 遍历每一行记录 [FOR EACH ROW] PLSQL 块【declare…begin…end;/】

![这里写图片描述][SouthEast 6]

### **例子** ###

创建语句级触发器insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】，显示”hello world”

CREATE OR REPLACE TRIGGER insertempTiriger BEFORE INSERT ON EMP BEGIN dbms_output.put_line('helloword');
    
      END;

调用：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

结果：

![这里写图片描述][SouthEast 7]

--------------------

星期一到星期五，且9-20点能向数据库emp表插入数据，否则使用函数抛出异常,  
语法：raise\_application\_error(‘-20000’,’例外原因’)

CREATE OR REPLACE TRIGGER securityTrigger BEFORE INSERT ON EMP DECLARE pday VARCHAR2(10);
        ptime NUMBER;
      BEGIN /*得到星期几*/ SELECT to_char(sysdate, 'day') INTO pday FROM dual;
    
        /*得到时间*/
        SELECT to_char(sysdate, 'hh24') INTO ptime FROM dual;
    
        IF pday IN ('星期六', '星期日') OR ptime NOT BETWEEN 7 AND 23
        THEN
          RAISE_APPLICATION_ERROR('-20000', '非工作事件，请工作时间再来！');
    
        END IF;
    
      END;

插入数据、响应触发器：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (3, '2', '3', 4, NULL, NULL, NULL, 10);

![这里写图片描述][SouthEast 8]

--------------------

创建行级触发器checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，语法：for each row/:new.sal/:old.sal

可以使用:new.sal/:old.sal来对比插入之前的值和插入之后的值

CREATE OR REPLACE TRIGGER checkSalTrigger BEFORE UPDATE OF sal ON EMP FOR EACH ROW BEGIN IF :new.sal <= :old.sal THEN RAISE_APPLICATION_ERROR('-20001', '你涨的工资也太少了把！！！！');
    
        END IF;
    
      END;

调用：

UPDATE emp SET sal = sal - 1 WHERE empno = 7369;

![这里写图片描述][SouthEast 9]

[SouthEast]: /images/20210827/3f6fab72d5d44f05b108ebc6f72fb129.png
[SouthEast 1]: /images/20210827/84908b82e9454469aa491951b8bb7788.png
[SouthEast 2]: /images/20210827/21570038181d42159665f96b69e378c7.png
[SouthEast 3]: /images/20210827/4270c8e4e3ce4ec68b841c71921fbc36.png
[SouthEast 4]: /images/20210827/004d1d451979424fa6888fe92cc62259.png
[SouthEast 5]: /images/20210827/75c45b312dc54042b38f6f4986585c64.png
[SouthEast 6]: /images/20210827/b74a6a7532884ac5be46177c63b2128a.png
[SouthEast 7]: /images/20210827/068b2611a0d44a6a9c545a0abc6e55ba.png
[SouthEast 8]: /images/20210827/d14f05e685734dbba3b139d9ea2ac26d.png
[SouthEast 9]: /images/20210827/15d0114c7d3f4403a9cad11478d4da92.png