• PL/SQL简介


    本节要点:

    •   什么是PL/SQL
      •   PL/SQL简介
      •   PL/SQL 的优点
      •   PL/SQL 的体系结构
      •   PL/SQL 块简介
    •   PL/SQL中的变量和常量
      •   变量和常量的定义
      •   PL/SQL 支持的数据类型
    •   表达式
      •   数值表达式
      •   关系表达式
      •   逻辑表达式
    •   异常处理
      •   处理异常的语法
      •   预定义异常
      •   预定义异常
      •   自定义异常

    1         什么是PL/SQL

    1.1         PL/SQL简介

    PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言,是对 SQL 的扩展。PL/SQL完全可以像Java语言一样实现逻辑判断、条件循环以及异常处理等,这是标准SQL很难做到的。

    1.2         PL/SQL 的优点

    PL/SQL同传统的SQL语言相比有以下几个优点:

    1)         可以提高程序的运行性能

    标准的SQL被执行时,只能一条一条地向Oracle服务器发送。PL/SQL语句块可以包含多条SQL语句,这样用户只需要一次连接就可以执行多条SQL语句,大大节省了网络资源开销,减少访问数据库次数。

    2)         可以使程序模块化

    使用块后,可以把对多张表的操作都放到一个块内,而对外只提供一个调用方式和需要传入的参数,这样减少程序员的工作量。使用块也可以把数据库数据同客户程序隔离开来,使得数据库表结构发生变化时,对调用者的影响减小到最低程度。

    3)         可以采用逻辑控制语句来控制程序结构

    使PL/SQL更加实用化,更符合现实业务逻辑的需求。

    4)         利用处理运行时的错误信息

    利用流程控制语句,可以判断一些逻辑问题,提高检错能力;并且可以对错误信息进行处理,不至于出现生硬的错误提示。

    5)         良好的可移植性

    PL/SQL可以成功的运行到不同的服务器中。

    1.3         PL/SQL 的体系结构

    PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行。

     

    1.4         PL/SQL 块简介

      PL/SQL 块是构成 PL/SQL 程序的基本单元;将逻辑上相关的声明和语句组合在一起。PL/SQL 分为三个部分,声明部分、可执行部分和异常处理部分:

       [DECLARE declarations]—声明开始关键字

                          /*声明部分,包括PL/SQL中的变量、常量以及类型等*/

        BEGIN             —执行部分开始的标志

    executable statements /*这里是执行部分,是整个PL/SQL块的主体部分*/

       [EXCEPTION handlers] –异常开始部分的关键字

                         /*这里是异常处理部分*/

    END;             --执行结束标志

    示例:

     

    2         PL/SQL中的变量和常量

    2.1         变量和常量的定义

    定义方式:

    • l  在声明部分声明,使用前必须先声明
    • l  声明时必须指定数据类型,每行声明一个标识符
    • l  在可执行部分的 SQL 语句和过程语句中使用

    声明变量和常量的语法:

    identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

    给变量赋值有两种方法:

    • l  使用赋值语句 :=
    • l  使用 SELECT INTO 语句

    示例:

    set serveroutput on

    declare

      v_name char(8);

      c_name constant char(10) := '学生姓名:';

    begin

      select f_name into v_name from martin.t_student where f_id='001';

      dbms_output.put_line(c_name||v_name);

    exception    /* 异常处理语句 */

      when others then

        dbms_output.put_line('出错:'||SQLERRM);

    end;

    /

    2.2         PL/SQL 支持的数据类型

     

    1)         数值类型

    主要用来存放数字型的数据。主要有以下几种:

    • l  NUMBER类型可以表示整数、实数和浮点数,改类型以十进制存储。其通用格式是NUMBER(precision,scale),其中precision表示精度,也就是数字的位数,最高38位;scale表示小数点后的位数。例如,NUMBER(3,1)可以存储-99.9~99.9之间的值。定义的时候precision和scale可以省略,如number按最大38位表示,number(8)表示最大8位,都不能包含小数位。
    • l  PLS_INTEGER和BINARY_INTEGER类型,BINARY_INTEGER存储有符号整数,所需存储空间少于NUMBER类型值;PLS_INTEGER存储有符号整数。通常认为PLS_INTEGER和BINARY_INTEGER是同样的数据类型,区别是BINARY_INTEGER溢出时能为其指派一个number类型而不至于发生异常,PLS_INTEGER如果遇到溢出直接发生异常。

    2)         字符数据类型

    字符数据类型用来存储单个的字符或字符串的类型。主要包括:

    • l  CHAR:用来描述固定长度的字符串
    • l  VARCHAR2:表示可变长的字符串
    • l  LONG:表示可变长的字符串,与varchar2类似
    • l  NCHAR 和NVARCHAR2 :n表示Unicode字符,即所有字符都占两个字节,nchar,nvarchar字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。

    3)         日期时间类型

    用来存储日期和时间数据。常用的两种日期时间类型:

    •   DATE:可以存储月、年、日、世纪、时、分、秒
    •   TIMESTAMP:由DATE演变而来,可以存储月、年、日、世纪、时、分和秒以及小数的秒

    4)         布尔数据类型

    此类别只有一种类型,即BOOLEAN类型,用于存储逻辑值(TRUE、FALSE和NULL),不能向数据库中插入BOOLEAN数据,即不能用作定义表中的数据类型,只能对BOOLEAN变量执行逻辑操作。

    5)         LOB  数据类型

    用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB 数据类型可存储最大 4GB的数据。LOB 类型包括:

    •   BLOB   将大型二进制对象存储在数据库中
    •   CLOB   将大型字符数据存储在数据库中
    •   NCLOB   存储大型UNICODE字符数据
    •   BFILE    将大型二进制对象存储在操作系统文件中

    LOB 类型的数据库列仅存储定位符,该定位符指向大型对象的存储位置。DBMS_LOB程序包用于操纵 LOB 数据:

    declare

      content CLOB;

      beginPosition integer;

      amount integer;

      outputString varchar(100);

    begin

           /*从表中选择 CLOB 定位符到 content变量中*/

      select f_content into content from t_booktext where f_chapterid='001';

      beginPosition := 1;

      amount := 22;

    /*从CLOB数据中读取22个字符存储到 outputString 变量中*/

      dbms_lob.read(content,amount,beginPosition,outputString);

      dbms_output.put_line(outputString);

    end;

    /

    6)         属性类型

    用于引用数据库列的数据类型,以及表示表中一列或者一行的记录类型。不需要知道被引用的表列的具体类型,如果被引用对象的数据类型发生改变,PL/SQL 变量的数据类型也随之改变。有%TYPE和%ROWTYPE:

    • l  %TYPE:为了使一个变量的数据类型与另一个已经定义了的变量(尤其是表的某一列)的数据类型相一致,Oracle提供了%TYPE定义方式。当被参照的那个变量的数据类型改变了之后,这个新定义的变量的数据类型会自动跟随其改变,容易保持一致,也不用修改PL/SQL程序了。

    示例:

    DECLARE

      V_ORG_NAME SF_ORG.ORG_NAME%TYPE; --定义V_ORG_NAMEORG_NAME类型相同

      V_PARENT_ID SF_ORG.PARENT_ID%TYPE;--定义V_PARENT_IDPARENT_ID类型相同

    BEGIN 

    /*SF_ORG中的ORG_NAME,PARENT_ID放入定义好的V_ORG_NAMEV_PARENT_ID*/

      SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID

      FROM SF_ORG SO

      WHERE SO.ORG_ID=1234;

    /*输出V_ORG_NAME和V_PARENT_ID的值*/

      DBMS_OUTPUT.PUT_LINE('部门名称:' || V_ORG_NAME);

      DBMS_OUTPUT.PUT_LINE('上级部门编码:' || TO_CHAR(V_PARENT_ID));

    END;

    • l  %ROWTYPE:如果一个表有较多的列,使用%ROWTYPE来定义一个表示表中一行记录的变量

    例:

    DECLARE

    /*定义一个变量t_emp使其与EMP 表具有一样的数据类型. 也就是说EMP 表有哪里数据类型的字段,那么这个t_ emp变量也就能够存储什么类型的数据,而且大小范围也是一样的. 形象点说,t_emp就像是一个收纳盒,能装下EMP 表的一行记录. */

      t_emp EMP  %rowtype;

    BEGIN 

    SELECT * INTO t_emp FROM EMP WHERE EMPNO=7639;

      DBMS_OUTPUT.PUT_LINE(t_emp.EMPNO);
    DBMS_OUTPUT.PUT_LINE(t_emp.SAL);

    END;

     

    3         表达式

    数据库中经常使用表达式来计算结果,尤其在变量和常量的使用过程中。它和普通编程语言的表达式很类似。表达式根据操作数据类型的不同可以分为如下几类:

    •   数值表达式
    •   关系表达式
    •   逻辑表达式

    3.1         数值表达式

    数值表达式就是对数值类型的常量、变量以及函数,由算术运算符连接而成。在PL/SQL里可以使用的算术运算符有:

    •   加号+
    •   减号-
    •   乘号*
    •   除号/
    •   乘方**

    3.2         关系表达式

    由关系运算符连接起来的字符或数值称为关系表达式。其中关系运算符主要有以下几种:

    关系运算符

    说明

    =

    比较两个变量是否相等,如果值相当,则返回 True

    <>, !=

    比较两个变量,如果不相等,则返回 True

    比较两个变量,检查值 1 是否小于值 2

    比较两个变量,检查值 1 是否大于 值 2

    <=

    比较两个变量,检查变量 1 是否小于等于变量 2

    >=

    比较两个变量,检查变量 1 是否大于等于变量 2

    关系表达式最后的结果是一个布尔类型值。

    3.3         逻辑表达式

    逻辑表达式就是由逻辑符号和常量或变量等组成的表达式。逻辑符号通常有逻辑与AND、逻辑或OR和逻辑非NOT。

    4         异常处理

    在运行程序时出现的错误叫做异常。发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分。异常有三种类型:

    •   预定义异常:当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
    •   非预定义异常:Oracle定义好名称的常用异常
    •   用户定义异常:用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发

    其中,预定义异常和非预定义异常都是和Oracle中的错误有关,比如违反数据完整性等;而用户定义异常是人为的为某种特殊情况定义的异常,比如说年龄不能大于150。

    异常演示:

    declare

      v_rslt number(10) := 0;

    begin

      v_rslt := 100 / 0;

      dbms_output.put_line('result is: ' || v_rslt);

    end;

    /

    输出:

     

    4.1         处理异常的语法

    为了让程序有更好的阅读性和健壮性,PL/SQL采用了捕获并统一处理异常的方式。语法如下:

    EXCEPTION

    WHEN exception1 [OR exception2 . . .] THEN

        --处理语句。。

    [WHEN exception3 [OR exception4 . . .] THEN

        --处理语句。。

    [WHEN OTHERS THEN --其他异常,省事

        --处理语句。。

    ]

    • l  EXCEPTION:声明,是异常处理部分开始的标志;
    • l  WHEN后面接异常名称列表,THEN后面接语句序列,也就是说发生的异常和异常列表里的异常相匹配时,执行指定的语句序列,以完成善后操作。
    • l  允许多个WHEN关键词
    • l  WHEN OTHERS THEN用来处理没有匹配成功的异常。

    4.2         预定义异常

    Oracle中为每一个错误提供一个错误号,而捕获异常则需要异常有名称。Oracle提供了一些已经定义好名称的常用异常,这就是预定义异常。

    上例中使用预定义异常进行捕获:

    declare

      v_rslt number(10) := 0;

    begin

      v_rslt := 100 / 0;

      dbms_output.put_line('result is: ' || v_rslt);

    exception

      when zero_divide then

        dbms_output.put_line('divide zero! default instead one,so the result is :' || 100 / 1);

    end;

    /

    注:如果是使用PL/SQL Developer工具的话,在SQL window的SQL里面运行,在SQL window的Output查看结果,结果如下:

     

    下面是Oracle预定义异常部分信息表供参考:

    异常

    错误

    何时出现

    ACCESS_INTO_NULL

    ORA-06530

    试图访问未初始化对象的时候出现

    CASE_NOT_FOUND

    ORA-06592

    如果定义了一个没有ELSE子句的CASE语句,而且没有CASE语句满足运行时条件时出现该异常

    COLLECTION_IS_NULL

    ORA-06531

    当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候,会出现该异常

    CURSOR_ALREADY_OPEN

    ORA-06511

    游标已经被OPEN,如果再次尝试打开该游标的时候,会出现该异常

    DUP_VAL_ON_INDEX

    ORA-00001

    如果插入一列被唯一索引约束的重复值的时候,就会引发该异常(该值被INDEX认定为冲突的)

    INVALID_CURSOR

    ORA-01001

    不允许的游标操作,比如关闭一个已经被关闭的游标,就会引发

    INVALID_NUMBER

    ORA-01722

    给数字值赋非数字值的时候,该异常就会发生,这个异常也会发生在批读取时候LIMIT子句返回非正数的时候

    LOGIN_DENIED

    ORA-01017

    程序中,使用错误的用户名和密码登录的时候,就会抛出这个异常

    NO_DATA_FOUND

    ORA_06548

    在使用SELECT INTO 结构,并且语句返回NULL值的时候;访问嵌套表中已经删除的表或者是访问INDEX BY表(联合数组)中的未初始化元素就会出现该异常

    NOT_LOGGED_ON

    ORA-01012

    当程序发出数据库调用,但是没有连接的时候(通常,在实际与会话断开连接之后)

    PROGRAM_ERROR

    ORA-06501

    当Oracle还未正式捕获的错误发生时常会发生,这是因为数据库大量的Object功能而发生

    ROWTYPE_MISMATCH

    ORA-06504

    如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常

    SELF_IS_NULL

    ORA-30625

    调用一个对象类型非静态成员方法(其中没有初始化对象类型实例)的时候发生该异常

    STORAGE_ERROR

    ORA-06500

    当内存不够分配SGA的足够配额或者是被破坏的时候,引发该异常

    SUBSCRIPT_BEYOND_COUNT

    ORA-06533

    当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候,发生该异常(类似于java的ArrayIndexOutOfBoundsException)

    SUBSCRIPT_OUTSIDE_LIMIT

    ORA-06532

    使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发

    SYS_INVALID_ROWID

    ORA-01410

    将无效的字符串转化为ROWID的时候引发

    TIMEOUT_ON_RESOURCE

    ORA-00051

    当数据库不能安全锁定资源的时候引发

    TOO_MANY_ROWS

    ORA-01422

    常见错误,在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行,而比较运算符为相等的时候也会引发该异常。

    USERENV_COMMITSCN_ERROR

    ORA-01725

    只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数

    VALUE_ERROR

    ORA-06502

    将一个变量赋给另一个不能容纳该变量的变量时引发

    ZERO_DIVIDE

    ORA-01476

    将某个数字除以0的时候,会发生该异常

    4.3         非预定义异常

    Oracle中为每一个错误提供一个错误号,而捕获异常需要有异常名称,如上例中的ZERO_DIVIDE。而Oracle中更多的是非预定义异常,也就是他们只有错误编号和相关的错误描述,Oracle并没有给他们定义名称,所以不能被捕获。Oracle允许开发人员为这样的异常添加一个名称,使得他们可以被捕获。

    为一个非预定义异常定义名称需要两步:

    • l   声明一个异常的名称
    • l   把这个名称和异常的编号相互关联

    非预定义异常示例:

    给雇员设置一个不存在的部门编号,违背了数据的完整性,也就是导致ORA-02291错误。

    DECLARE  

      e_integrity EXCEPTION;  

      PRAGMA EXCEPTION_INIT(e_integrity,-2291);  

      --2291为Oracle定义的错误号,违背了完整性约束条件

      –-给-2291定义一个异常名称叫e_integrity

    BEGIN

    UPDATE emp SET deptno = 99 WHERE empno = 7788;  --发生ORA-02291异常

      EXCEPTION  

    WHEN e_integrity THEN  --将异常与e_integrity进行匹配

         Dbms_Output.put_line('该部门不存在');  

    END;  

    4.4         自定义异常

    如果开发当中遇到与实际业务相关的错误,如产品数量不允许为负数,生产日期必须保证在质保日期之前等,这些和业务相关的问题不能算系统错误,也不能使用预定义和非预定义异常来捕获它们。如果想要用异常的方式处理这些问题,那么这样的异常需要开发人员自己编写,而且在调用的时候也需要显示的触发。

        如果M_USER表中的数据记录小于20条则抛出数量小于20的异常。示例:

    declare

      v_qunty number;

      my_exp exception;

      pragma exception_init(my_exp,-20001);

    begin

      select count(*) into v_qunty from M_USER t;

      if v_qunty < 20 then

        raise my_exp;

      end if;

    exception

      when my_exp then

        dbms_output.put_line('数量小于20');

        dbms_output.put_line(sqlerrm);

      when others then

        dbms_output.put_line('其他异常');

    end;

    注:错误号的范围是-20999~-20000的负整数,该范围内的错误是可以随便用的,不用担心被占用。其实定不定义错误信息的编号跟异常的抛出没有关系,个人认为定义编号只是让机器更容易识别和传输,也是为了更好的统一编程规则等。

  • 相关阅读:
    C#8.0——异步流(AsyncStream)
    递归,循环,尾递归
    C#7.2——编写安全高效的C#代码
    Ocelot中文文档-Route
    Ocelot中文文档-Configuration
    Ocelot中文文档-Not Supported
    Ocelot中文文档-Getting Started
    .NET 单元测试的利剑——模拟框架Moq(简述篇)
    输入五个数字,按从大到小的顺序输出
    函数和方法的区别
  • 原文地址:https://www.cnblogs.com/zhouyeqin/p/7327779.html
Copyright © 2020-2023  润新知