Oracle数据库(六)——PL/SQL上

PL/SQL基础

什么是PL/SQL

结构化查询语言(structured query language,SQL)是用来访问和操作关系型数据库的一种标准通用语言，它属于第四代语言(4GL)，简单易学，特点是方便，非过程化。使用的时候不用明确指明执行的具体方法和途径，不用关注任何实现的细节。但这种语言也有一个问题，就是满足不了复杂的流程需求。

Oracle中的PL/SQL语言正是为了解决这一问题，它属于第三代的语言，也是过程化的语言，同Java,C#一样可以关注细节，用它可以实现复杂的业务逻辑，是数据库开发人员的利器

PL/SQL(Procedural Language/Structured Query Language)是Oracle公司在标准SQL语言基础上进行扩展而形成的一种可以再数据库上进行设计编程的语言，通过Oracle的PL/SQL引擎执行，可以实现逻辑判断，条件循环等与语句。

PL/SQL的特点：

• 支持事务控制和SQL数据操作命令
• 它支持SQL的所有数据类型，并且在此基础上扩展了新的数据类型，也支持SQL的函数和运算符
• PL/SQL可以存储在oracle服务器中
• 服务器上的PL/SQL程序可以使用权限进行控制
• Oracle有自己的DBMS包，可以处理数据的控制和定义命令

PL/SQL优点：

• 可以提高程序的运行性能，语句块中可以包含多条SQL语句，只用连接一次数据库
• 可以使用程序模块化：可以包装一个功能
• 可以采用逻辑控制语句来控制程序结构
• 利用处理运行时的错误信息
• 良好的可移植性

PL/SQL的结构

PL/SQL程序的基本单位是块(block),而PL/SQL块很明确地分三部分，其中包括声明部分，执行部分和异常处理部分

[DECLARE]  --声明开始的关键字
            /*这里是声明部分，包括了变量，常量和类型等*/
BEGIN   --执行部分开始的标志
            /*这里是执行部分，是整个plsql的主题部分，该部分必须存在，可以是SQL语句或者是程序流程控制语句等*/
[EXCEPTION] --异常开始部分的关键字
            /*这里是异常处理部分，当出现异常时程序流程可以进入此处*/
END;    --执行结束标志

DECLARE
v_categoryid VARCHAR2(12);
BEGIN
    SELECT CATEGORYID
        INTO V_CATEGORYID
            FROM CATEGORYINFO
        WHERE CATEGORYINFO.CATEGORYNAME = '雨具'；
    DBMS_OUTPUT.PUT_LINE('雨具对应的编码是：'|| v_categoryid);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有对应的编码! ');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！ ');

END;

--select...into 是PL/SQL特有赋值语句，该类型语句一次只能返回一条记录，超过一条会报错

PL/SQL的基本规则

• 标识符不分大小写，所有的名称在存储时都被修改成大写
• 标识符中能有字母，数字，下划线，并且以字母开头
• 标识符最多30个字符
• 不能用保留字，如果与保留字需要用双引号括住
• 语句使用分号结束
• 语句中的关键字，标识符，字段的名称以及表名等都需要空格的分隔
• 字符类型和日期类型需要使用单引号括起

PL/SQL 变量的使用

variable_name datetype
[
  [ NOT NULL ]
  {:= | DEFAULT} expression --当使用not NULL 属性是，赋值和默认值选一
];

数值类型：

• NUMBER类型可以表示整数和浮点数，十进制。格式NUMBER(precision,scale).precision表示精度，也就是位数，最多38位，scale表示小数点后的位数，例如NUMBER(3,1)可以储存-99.9~99.9之间的数
• PLS_INTEGER和BINARY_INTENER类型通常可以认为是一样的类型，表示的是-2147483628~2147483647之间。不同是BINARY_INTENER 发生溢出时指派一个NUMBER类型而不发生异常，而PLS_INTEGER会发生异常
• SIMPLE_INTEGER属于PLS_INTEGER的子类型，取值范围一样，只是改类型不允许为空。如果数据本身不需要溢出检查而且也不可能是空，可以选择该类型

字符类型

• CHAR类型，用来描述固定长度的字符串，最长为32 767个字节，默认为1，如果长度达不到会以空格补齐长度，CHAR(maximum_size).
• VARCHAR类型 作为变量为32 767字节，但存储时最大4000个字节，该类型表示可以变的长度字符串，当没到定义的最大长度的时候不会补齐
• NCHAR,NVARCHAR 与国家的字符集有关
• LONG类型 可以变的方式存储数据，PL/SQL中该类型作为变量最长为32760字节的字符串，如果作为存储字段则可达2GB

BOOL类型，不能定义表中的存储数据，可以用来存储逻辑上的值，它有3个值可以选：TRUE FALSE NULL

日期类型

• DATE类型可以存储月，年，日，世纪，时，分，秒
• TIMESTAMP类型由DATE演变，可以存储月，年，日，世纪，时，分，秒以及小数的秒

使用%TYPE方式定义变量类型。

它利用以及存在的数据类型来定义数据的数据类型。例如，当定义多个变量时，只要使用过的数据类型，后面的变量就可以用%TYPE引用。 productinfo.productid%TYPE;

复合类型的变量

• PL/SQL ‘记录类型’

该类型可以包括一个或多个成员，每个成员的类型可以不同。该类型比较适合处理 查询语句中有多个列的情况。最常见的就是在调用某张表中的记录时，利用该类型存储这行记录

TYPE type_name IS RECORD
(
  field_name datetype
  [
  [ NOT NULL ]
  { := | DEFAULT } expression
  ]
    [, field_name datatype [[ NOT NULL ] { := | DEFAULT } expression]]...
);

• %ROWTYPE声明记录类型数据

这种声明方式可以直接引用表中的行作为变量类型。可以避免因表中字段的数据改变而导致PL/SQL块出错的问题

v_product productinfo%ROWTYPE;

• PL/SQL索引表类型(关联数组)

该类型和数组相似，它利用键值查找对应值。这里键值同真正数组的下标不同，索引表中下标允许字符串。数组的长度不是固定值，它可以根据需要自动增长。其中键值是整数或字符串。而其中的值就是普通的标量类型，也可以是记录类型。可以利用“变量名称（键值）”为其赋值或取值，如果某个键值的指向已经有数据了，那么该操作就是更改已有的数据。

TYPE type_name IS TABLE OF
{
column_type |
variable_name%TYPE |
table_name.column_name%TYPE|
table_name%ROWTYPE
}
[NOT NULL]
INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) }

以上的语法只是索引表类型本身的定义语法，并没有包含变量的定义。如果想把某个变量声明成索引表类型：

variable_name type_name;

DECLARE
TYPE prodt_tab_fst IS TABLE OF productinfo%ROWTYPE
    INDEX BY BINARY_INTEGER;
TYPE prodt_tab_sec IS TABLE OF VARCHAR2(8)
    INDEX BY PLS_INTEGER;
    v_ptr_row   prodt_tab_fst;
        v_ptr   prodt_tab_sec;

BEGIN 
    v_prt(1) := '正数';
    v_prt(-1) := '负数';

    SELECT * INTO v_prt_row(1);
    FROM productinfo
    WHERE productid = '0240040001';

    DBMS_OUTPUT.PUT_LINE('行数据-v_prt_row(1) = ' || v_prt_row(1).productid || '---' || v_ptr_row(1).productname);
    DBMS_OUTPUT.PUT_LINE('v_prt(1)= '           || v_prt(1));
    DBMS_OUTPUT.PUT_LINE('v_prt(-1) = '         ||v_prt('-1'));
END;
/

DECLARE
TYPE prodt_tab_thd IS TABLE OF NUMBER(8)
    INDEX BY VARCHAR2(20);
    v_ptr_chr  prodt_tab_thd;
BEGIN
    v_ptr_chr('test') := 123;
    v_ptr_chr('test1') := 0;
    DBMS_OUTPUT.PUT_LINE('v_prt_chr(123) = ' || v_ptr_chr('test')); --123
    DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' || v_ptr_chr('test1')); --0
    DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' || v_ptr_chr.first); --test
    DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' || v_ptr_chr(v_prt_chr.first)); --123
END;
/

• VARRAY变长数组

该类型的元素个数是需要限制的，它是一个存储有序元素的集合。集合下标从1开始，比较适合较少的数据使用。声明语法如下：

TYPE type_name IS { VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ]

DECLARE
    TYPE varr IS VARRAY(100) OF VARCHAR2(20);
    v_product varr := varr('1','2');
BEGIN
    v_product(1) := 'THIS IS A';
    v_product(2) := 'TEST';
    DBMS_OUTPUT.PUT_LINE('productid = ' || v_product(1));
    DBMS_OUTPUT.PUT_LINE('productid = ' || v_product(2));
END;
/

表达式

• 数值表达式

乘方**

--计算根号的58+25*3+(19-9)^2
DECLARE
    v_result NUMBER(10,4);
BEGIN
    v_result := SQRT(58+25*3+(19-9)**2);
    DBMS_OUTPUT.PUT_LINE('v_result = ' || v_result);
END;

• 关系表达式

不等号 != 和 <>

• 逻辑表达式

NOT OR AND