PL/SQL学习笔记二：PL/SQL块儿结构和组成元素

PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。

PL/SQL块的结构如下：

    DECLARE

    /* 声明部分：在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数 */

    BEGIN

    /* 执行部分：过程及SQL语句，即程序的主要部分 */

    EXCEPTION

    /* 执行异常部分：错误处理 */

    END；

其中执行部分是必须的。

PL/SQL可以分为三类：

1、无名块：动态构造，只能执行一次。

2、子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好可以在其它程序中调用它们。

3、触发器：当数据库发生操作时，会触发一些事情，从而自动执行相应的程序。

 

PL/SQL的结构

PL/SQL块中可以包含子块；

子块可以位于PL/SQL中的任何部分；

子块也即PL/SQL中的一条命令。

 

标识符

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：

1、标识符名不能超过30字符；

2、第一个字符必须为字母；

3、不分大小写；

4、不能用“-”（减号）；

5、不能使SQL保留字。

注意：一般不要把变量名声明与表中字段名完全一致，如果这样可能得到不正确的结果。

 

PL/SQL变量类型

1、基本类型

    1>、CHAR：子类有Character、String、Rowid、Nchar（民族语言字符集）；

    2>、VARCHAR2：子类有Varchar、String、NVARCHAR2（民族语言字符集）；

    3>、BINARY_INTEGER（带符号整数，为整数计算优化性能）；

    4>、NUMBER（p,s）：子类有Dec、Double、precision、Integer、Int、Numberic、Real、Small int；

    5>、LONG；

    6>、DATE；

    7>、BOOLEAN；

    8>、ROWID（存放数据库行号）；

    9>、UROWID（通用行标识符，字符类型）。

2、复合类型，ORALCE在PL/SQL中除了提供像前面介绍的各种类型外，还提供一种称为复合类型的类型——记录和标。

    1>、记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称为PL/SQL RECORD的域     
     （FIELD），其作用是存放互不相同但逻辑相关的信息。

           定义记录类型语法如下：

           TYPE record_type IS RECORD(

               Field1 type1 [NOT NULL] [:=EXP1],

               Field2 type2 [NOT NULL] [:=EXP2],

               ...  ...

               Fieldn typen [NOT NULL] [:=EXPn]

           );

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

           使用%TYPE特性的优点在于：所引用的数据库列的数据类型可以不必知道；所引用的数据库列的数据类型可以实时改变。

           eg：

           DECLARE

               --用%TYPE类型定义与表相配的字段

               TYPE t_Record IS RECORD(

                   T_no emp.empno%TYPE,

                   T_name emp.ename%TYPE,

                   T_sal emp.sal%TYPE

               );

               --声明接收数据的变量

               v_empt_Record;

           BEGIN

               SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno = 7788;

               DBMS_OUTPUT_PUT_LINE(TO_CHAR(v_emp.t_no) || v_emp.t_name || TO_CHAR(v_emp.t_sal));

           END;

    3>、使用%ROWTYPE：PL/SQL提供%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致。

           使用%ROWTYPE特性的优点在于：所引用的数据库中列的个数和数据类型可以不必知道；所引用的数据库中列的个数和数据类型可以实时改变。

           eg：

           DECLARE

               v_empno emp.empno%TYPE := &empno;

               rec emp%ROWTYPE;

           BEGIN

               SELECT & INTO rec FROM emp WHERE empno = v_empno;

               DBMS_OUTPUT.PUT_LINE('姓名：' || rec.ename || '工资：' || rec.sal || '工作时间：' || rec.hiredate);

           END;

    4>、LOB类型：ORACLE提供了LOB（Large OBject）类型，用于存储大数据对象的类型。ORACLE目前只支持BFILE，BLOB，CLOB及NCLOB类型。

               BFILE（Movie）：存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里面值存放文件的目录。

               BLOB（Photo）：存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB.

               CLOB（Book）：存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。

               NCLOB：存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。

    5>、Bind变量：绑定变量是在主机环境中定义的变量。在PL/SQL程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL环境中声明绑定变量，

    使用命令VARIABLE。eg：VARIABLE retum_code NUMBER。

    6>、INDEX BY TABLES包括两个基本成分：数据处理类型为BINARY_INTEGER主键；标量或记录数据类型的列。

           TYPE TYPE_NAME is TABLE OF

               {column_type | variable%TYPE | table.column%TYPE}[NOT NULL]|table%ROWTYPE[index by binary_integer];

 

关于RETURNING

RETURNING通常结合DML语句使用（INSERT、UPDATE、DELETE）

INSERT：返回的是添加后的值；

UPDATE：返回的是更新后的值；

DELETE：返回的是删除前的值。

另：INSERTE INTO SELECT和MERGE语句不支持RETURNING；RETURNING可以与BULK COLLECT结合。

使用RETURNING的限制：

1、不能并行DML语句和远程对象一起使用；

2、不能检索LONG类型信息；

3、当通过视图向基表中插入数据时，只能与单基表视图一起使用。

 

运算符和表达式

关系运算符：=（等于）、<>/!=/~=/^=（不等于）、<（小于）、>（大于）、<=（小于或等于）、>=（大于或等于）。

一般运算符：+（加号）、-（减号）、*（乘号）、/（除号）、:=（赋值号）、=>关系号、..（范围远算符）、||（字符连接符）。

逻辑运算符：IS NULL（是空值）、BETWEEN（介于两者之间）、IN（在一列值中间）、AND（逻辑与）、OR（罗辑或）、NOT（取反，如IS NOT NULL, NOT

                 IN）。

 

变量赋值

在PL/SQL编程中，变量赋值的语法：variable := expression。其中variable是一个PL/SQL变量，expression是一个PL/SQL表达式。 

    1>、字符及数字运算特点：空值加数字仍是空值（NULL + <数字> = NULL）；空值加（连接）字符，结果为字符（NULL || <字符串> = <字符串>。

    2>、BOOLEAN赋值：布尔值只有TRUE，FALSE及NULL三个值。

    3>、数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。eg：

           DECLARE

               emp_id emp.empno%TYPE := 7788;

               emp_name emp.ename%TYPE;

               wages emp.sal%TYPE;

           BEGIN

               SELECT ename, NVL(sal, 0) + NVL(comm, 0) INTO emp_name, wages FROM emp WHERE empno = emp_id;

               DBMS_OUTPUT.PUT_LINE(emp_name || '----' || to_char(wages));

           END;

           注意：不能将SELECT语句中的列赋值给布尔变量。

    4>、可转换的类型赋值

               CHAR转换为NUMBER：使用TO_NUMBER函数来完成字符到数字的转换，eg：v_total := TO_NUMBER('100.0') + sal;。

               NUMBER转换为CHAR：使用TO_CHAR函数可以实现数字到字符的转换，eg：v_comm := TO_CHAR('123.45' || '元');。

               字符转换为日期：使用TO_DATE函数可以实现字符到日期的转换，eg：v_date := TO_DATE('2001.07.03', 'yyyy.mm.dd');。

               日期转换为字符：使用TO_CHAR函数可以实现日期到字符的转换，eg：v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss');。

 

变量作用范围及可见性

在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这个的原因主要是变量的作用范围所致。与其它高级语言类似，PL/SQL的变量作用范围特点是：

    1>、变量的作用范围是在你所引用的程序单元（块、子程序、包）内，即从声明变量开始到该块的结束；

    2>、一个变量（标识）只能在你所引用的块内是可见的；

    3>、当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。

    4>、在子块中重新定义该变量后，它的作用仅在该块内。

 

注释

在PL/SQL里，可以使用两种符号来写注释，即：使用双'-'（减号）加注释；使用/**/来加以行或多行的注视。

转载于:https://www.cnblogs.com/flyingAngle/archive/2012/09/25/2702033.html