oracle PL/SQL的基本构成,块结构和基本语法要求,数据类型,变量定义,运算符和函数

oracle PL/SQL的基本构成,块结构和基本语法要求,数据类型,变量定义,运算符和函数

特点
PL/SQL语言是SQL语言的扩展，具有为程序开发而设计的特性，如数据封装、异常处理、面向对象等特性。PL/SQL是嵌入到Oracle服务器和开发工具中的，所以具有很高的执行效率和同Oracle数据库的完美结合。在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，这样就可以编写具有数据库事务处理功能的模块。
至于数据定义(DDL)和数据控制(DCL)命令的处理，需要通过Oracle提供的特殊的DMBS_SQL包来进行。PL/SQL还可以用来编写过程、函数、包及数据库触发器。过程和函数也称为子程序，在定义时要给出相应的过程名和函数名。它们可以存储在数据库中成为存储过程和存储函数，并可以由程序来调用，它们在结构上同程序模块类似。
PL/SQL过程化结构的特点是：可将逻辑上相关的语句组织在一个程序块内；通过嵌入或调用子块，构造功能强大的程序；可将一个复杂的问题分解成为一组便于管理、定义和实现的小块。
块结构和基本语法要求
PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。
块中各部分的作用解释如下：
(1)  DECLARE：声明部分标志。
(2)  BEGIN：可执行部分标志。
(3)  EXCEPTION：异常处理部分标志。
(4)  END；：程序结束标志。
在以下的训练中，将使用函数DBMS_OUTPUT.PUT_LINE显示输出结果。DBMS_OUTPUT是Oracle提供的包，该包有如下三个用于输出的函数，用于显示PL/SQL程序模块的输出信息。
第一种形式：
DBMS_OUTPUT.PUT(字符串表达式)；
用于输出字符串，但不换行，括号中的参数是要输出的字符串表达式。
第二种形式：
DBMS_OUTPUT.PUT_LINE(字符串表达式)；
用于输出一行字符串信息，并换行，括号中的参数是要输出的字符串表达式。
第三种形式：
DBMS_OUTPUT.NEW_LINE；
用来输出一个换行，没有参数。
调用函数时，在包名后面用一个点“.”和函数名分隔，表示隶属关系。
要使用该方法显示输出数据，在SQL*Plus环境下要先执行一次如下的环境设置命令：
SET SERVEROUTPUT ON [SIZE n]
用来打开DBMS_OUTPUT.PUT_LINE函数的屏幕输出功能，系统默认状态是OFF。其中，n表示输出缓冲区的大小。n的范围在2000～1 000 000之间，默认为2000。如果输出内容较多，需要使用SIZE n来设置较大的输出缓冲区。
在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，所以PL/SQL程序是同SQL语言紧密结合在一起的。在PL/SQL程序中，最常见的是使用SELECT语句从数据库中获取信息，同直接执行SELECT语句不同，在程序中的SELECT语句总是和INTO相配合，INTO后跟用于接收查询结果的变量，形式如下：
SELECT 列名1，列名2... INTO 变量1，变量2... FROM 表名 WHERE 条件；
注意：接收查询结果的变量类型、顺序和个数同SELECT语句的字段的类型、顺序和个数应该完全一致。并且SELECT语句返回的数据必须是一行，否则将引发系统错误。当程序要接收返回的多行结果时，可以采用后面介绍的游标的方法。
使用INSERT、DELETE和UPDATE的语法没有变化，但在程序中要注意判断语句执行的状态，并使用COMMIT或ROLLBACK进行事务处理。
以下训练包含了按照标准结构书写的一个包含SELECT语句的PL/SQL程序示例。
【训练1】  查询雇员编号为7788的雇员姓名和工资。
步骤1：用SCOTT账户登录SQL*Plus。
步骤2：在输入区输入以下程序：

 /*这是一个简单的示例程序*/
        SET SERVEROUTPUT ON
        DECLARE--定义部分标识
         v_name  VARCHAR2(10);  --定义字符串变量v_name
         v_sal   NUMBER(5); --定义数值变量v_sal
        BEGIN           --可执行部分标识
SELECT   ename,sal
          INTO v_name,v_sal
        FROM    emp
         WHERE empno=7788;
        --在程序中插入的SQL语句
        DBMS_OUTPUT.PUT_LINE('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));
        --输出雇员名和工资
        END;

 /*这是一个简单的示例程序*/
		SET SERVEROUTPUT ON
		DECLARE--定义部分标识
 		 v_name  VARCHAR2(10);	--定义字符串变量v_name
 		 v_sal   NUMBER(5);	--定义数值变量v_sal
		BEGIN			--可执行部分标识
SELECT	 ename,sal 
		  INTO v_name,v_sal 
  		FROM	emp 
 		 WHERE empno=7788; 			
		--在程序中插入的SQL语句
  		DBMS_OUTPUT.PUT_LINE('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));
		--输出雇员名和工资
		END;			

--结束标识
步骤3：按执行按钮或F5快捷键执行程序。
输出的结果是：

7788号雇员是：SCOTT，工资为：3000
        PL/SQL 过程已成功完成。

7788号雇员是：SCOTT，工资为：3000
		PL/SQL 过程已成功完成。

以上程序的作用是，查询雇员编号为7788的雇员姓名和工资，然后显示输出。这种方法同直接在SQL环境下执行SELECT语句显示雇员的姓名和工资比较，程序变得更复杂。那么两者究竟有什么区别呢？SQL查询的方法，只限于SQL环境，并且输出的格式基本上是固定的。而程序通过把数据取到变量中，可以进行复杂的处理，完成SQL语句不能实现的功能，并通过多种方式输出。
“--”是注释符号，后边是程序的注释部分。该部分不编译执行，所以在输入程序时可以省略。/*......*/中间也是注释部分，同“--”注释方法不同，它可以跨越多行进行注释。
PL/SQL程序的可执行语句、SQL语句和END结束标识都要以分号结束。
数据类型
变量的基本数据类型同SQL部分的字段数据类型相一致，但是也有不同，具体看附件里的图片。
NUMBER和VARCHAR2是最常用的数据类型。
VARCHAR2是可变长度的字符串，定义时指明最大长度，存储数据的长度是在最大长度的范围自动调节的，数据前后的空格，Oracle 9i会自动将其删去。
NUMBER型可以定义数值的总长度和小数位，如NUMBER(10,3)表示定义一个宽度为10、小数位为3的数值。整个宽度减去小数部分的宽度为整数部分的宽度，所以整数部分的宽度为7。
CHAR数据类型为固定长度的字符串，定义时要指明宽度，如不指明，默认宽度为1。定长字符串在显示输出时，有对齐的效果。
   DATE类型用于存储日期数据，内部使用7个字节。其中包括年、月、日、小时、分钟和秒数。默认的格式为DD-MON-YY，如：07-8月-03表示2003年8月7日。
BOOLEAN为布尔型，用于存储逻辑值，可用于PL/SQL的控制结构。
LOB数据类型可以存储视频、音频或图片，支持随机访问，存储的数据可以位于数据库内或数据库外，具体有四种类型：BFILE、BLOB、CLOB、NCLOB。但是操纵大对象需要使用Oracle提供的DBMS_LOB包。
变量定义
1．变量定义
变量的作用是用来存储数据，可以在过程语句中使用。变量在声明部分可以进行初始化，即赋予初值。变量在定义的同时也可以将其说明成常量并赋予固定的值。变量的命名规则是：以字母开头，后跟其他的字符序列，字符序列中可以包含字母、数值、下划线等符号，最大长度为30个字符，不区分大小写。不能使用Oracle的保留字作为变量名。变量名不要和在程序中引用的字段名相重，如果相重，变量名会被当作列名来使用。
变量的作用范围是在定义此变量的程序范围内，如果程序中包含子块，则变量在子块中也有效。但在子块中定义的变量，仅在定义变量的子块中有效，在主程序中无效。
变量定义的方法是：
变量名 [CONSTANT] 类型标识符 [NOT NULL][:=值|DEFAULT 值];
关键字CONSTANT用来说明定义的变量是常量，如果是常量，必须有赋值部分进行赋值。
关键值NOT NULL用来说明变量不能为空。
∶=或DEFAULT用来为变量赋初值。
变量可以在程序中使用赋值语句重新赋值。通过输出语句可以查看变量的值。
在程序中为变量赋值的方法是：
变量名:=值 或 PL/SQL 表达式;
以下是有关变量定义和赋值的练习。
【训练1】  变量的定义和初始化。
输入和运行以下程序：

SET SERVEROUTPUT ON
        DECLARE     --声明部分标识
        v_job       VARCHAR2(9);
        v_count BINARY_INTEGER DEFAULT 0;
        v_total_sal NUMBER(9,2) := 0;
        v_date      DATE := SYSDATE + 7;
        c_tax_rate  CONSTANT NUMBER(3,2) := 8.25;
        v_valid     BOOLEAN NOT NULL := TRUE;
        BEGIN
        v_job:='MANAGER';
        --在程序中赋值
         DBMS_OUTPUT.PUT_LINE(v_job);
        --输出变量v_job的值
         DBMS_OUTPUT.PUT_LINE(v_count);
        --输出变量v_count的值
        DBMS_OUTPUT.PUT_LINE(v_date);
        --输出变量v_date的值
        DBMS_OUTPUT.PUT_LINE(c_tax_rate);
        --输出变量c_tax_rate的值
        END;

SET SERVEROUTPUT ON 
		DECLARE		--声明部分标识
		v_job		VARCHAR2(9);
		v_count	BINARY_INTEGER DEFAULT 0;
		v_total_sal	NUMBER(9,2) := 0;
		v_date		DATE := SYSDATE + 7;
		c_tax_rate	CONSTANT NUMBER(3,2) := 8.25;
		v_valid		BOOLEAN NOT NULL := TRUE;
		BEGIN
  		v_job:='MANAGER';		
		--在程序中赋值
 		 DBMS_OUTPUT.PUT_LINE(v_job);	
		--输出变量v_job的值
 		 DBMS_OUTPUT.PUT_LINE(v_count);
		--输出变量v_count的值
 		DBMS_OUTPUT.PUT_LINE(v_date);	
		--输出变量v_date的值
 	 	DBMS_OUTPUT.PUT_LINE(c_tax_rate);	
		--输出变量c_tax_rate的值
		END;

执行结果：

MANAGER
        0
        18-4月 -03
        8.25
        PL/SQL 过程已成功完成。

MANAGER
		0
		18-4月 -03
		8.25
		PL/SQL 过程已成功完成。

说明：本训练共定义了6个变量，分别用“:=”赋值运算符或DEFAULT 关键字对变量进行了初始化或赋值。其中：c_tax_rate为常量，在数据类型前加了 “CONSTANT” 关键字；v_valid变量在赋值运算符前面加了关键字“NOT NULL”，强制不能为空。如果变量是布尔型，它的值只能是“TRUE”、“FALSE”或“NULL”。本练习中的变量v_valid布尔变量的值只能取“TRUE”或“FALSE”。
2．根据表的字段定义变量
变量的声明还可以根据数据库表的字段进行定义或根据已经定义的变量进行定义。方法是在表的字段名或已经定义的变量名后加 %TYPE，将其当作数据类型。定义字段变量的方法如下：
变量名 表名.字段名%TYPE;
【训练2】  根据表的字段定义变量。
输入并执行以下程序：

SET SERVEROUTPUT ON
        DECLARE
        v_ename emp.ename%TYPE;--根据字段定义变量
        BEGIN
        SELECT  ename
         INTO       v_ename
         FROM       emp
         WHERE  empno = 7788;
        DBMS_OUTPUT.PUT_LINE(v_ename);
        --输出变量的值
        END;

SET SERVEROUTPUT ON 
		DECLARE
   		v_ename	emp.ename%TYPE;--根据字段定义变量
		BEGIN
   		SELECT	ename
  		 INTO		v_ename
  		 FROM		emp
  		 WHERE	empno = 7788;
		DBMS_OUTPUT.PUT_LINE(v_ename);	
		--输出变量的值
		END;

执行结果：

SCOTT
        PL/SQL 过程已成功完成。

SCOTT
		PL/SQL 过程已成功完成。

说明：变量v_ename是根据表emp的ename字段定义的，两者的数据类型总是一致的。
如果我们根据数据库的字段定义了某一变量，后来数据库的字段数据类型又进行了修改，那么程序中的该变量的定义也自动使用新的数据类型。使用该种变量定义方法，变量的数据类型和大小是在编译执行时决定的，这为书写和维护程序提供了很大的便利。
3．结合变量的定义和使用
我们还可以定义SQL*Plus环境下使用的变量，称为结合变量。结合变量也可以在程序中使用，该变量是在整个SQL*Plus环境下有效的变量，在退出SQL*Plus之前始终有效，所以可以使用该变量在不同的程序之间传递信息。结合变量不是由程序定义的，而是使用系统命令VARIABLE定义的。在SQL*Plus环境下显示该变量要用系统的PRINT命令。
在SQL*Plus环境下定义结合变量的方法如下：
VARIABLE  变量名 数据类型
【训练3】  定义并使用结合变量。
步骤1：输入和执行下列命令，定义结合变量g_ename：

VARIABLE  g_ename VARCHAR2(100)

VARIABLE  g_ename VARCHAR2(100) 

步骤2：删掉刚才输入的语句,输入和执行下列程序：

SET SERVEROUTPUT ON
        BEGIN
         :g_ename:=:g_ename|| 'Hello~ ';
        --在程序中使用结合变量
         DBMS_OUTPUT.PUT_LINE(:g_ename);
        --输出结合变量的值
        END;

SET SERVEROUTPUT ON 
		BEGIN
  		 :g_ename:=:g_ename|| 'Hello~ ';		
		--在程序中使用结合变量
  		 DBMS_OUTPUT.PUT_LINE(:g_ename);	
		--输出结合变量的值
		END;

输出结果：

Hello~
        PL/SQL 过程已成功完成。

Hello~
		PL/SQL 过程已成功完成。

步骤3：重新执行程序。
输出结果：

Hello~ Hello~
        PL/SQL 过程已成功完成。

Hello~ Hello~
		PL/SQL 过程已成功完成。

步骤4：程序结束后用命令显示结合变量的内容：
 

PRINT g_ename

PRINT g_ename 

输出结果：

G_ENAME
        -----------------------------------------------
        Hello~ Hello~

G_ENAME
		-----------------------------------------------
		Hello~ Hello~  

说明：g_ename为结合变量，可以在程序中引用或赋值，引用时在结合变量前面要加上“∶”。在程序结束后该变量的值仍然存在，其他程序可以继续引用。
4．记录变量的定义
还可以根据表或视图的一个记录中的所有字段定义变量，称为记录变量。记录变量包含若干个字段，在结构上同表的一个记录相同，定义方法是在表名后跟%ROWTYPE。记录变量的字段名就是表的字段名，数据类型也一致。
记录变量的定义方法是：
记录变量名 表名%ROWTYPE；
获得记录变量的字段的方法是：记录变量名.字段名，如emp_record.ename。
如下练习中定义并使用了记录变量。
【训练4】  根据表定义记录变量。
输入并执行如下程序：

SET SERVEROUTPUT ON
        DECLARE
        emp_record      emp%ROWTYPE;--定义记录变量
        BEGIN
         SELECT * INTO  emp_record
         FROM           emp
         WHERE      mpno = 7788;--取出一条记录
        DBMS_OUTPUT.PUT_LINE(emp_record.ename); --输出记录变量的某个字段
        END;

SET SERVEROUTPUT ON 
		DECLARE
		emp_record		emp%ROWTYPE;--定义记录变量
		BEGIN
 		 SELECT * INTO	emp_record
 		 FROM			emp
 		 WHERE		mpno = 7788;--取出一条记录
  		DBMS_OUTPUT.PUT_LINE(emp_record.ename);	--输出记录变量的某个字段 
		END;

执行结果为：

SCOTT
        PL/SQL 过程已成功完成。

SCOTT
		PL/SQL 过程已成功完成。 

说明：在以上的练习中定义了记录变量emp_record，它是根据表emp的全部字段定义的。SELECT语句将编号为7788的雇员的全部字段对应地存入该记录变量，最后输出记录变量的雇员名称字段emp_record.ename的内容。如果要获得其他字段的内容，比如要获得编号为7788的雇员的工资，可以通过变量emp_record.sal获得，依此类推。
5．TABLE类型变量
在PL/SQL中可以定义TABLE类型的变量。 TABLE数据类型用来存储可变长度的一维数组数据，即数组中的数据动态地增长。要定义TABLE变量，需要先定义TABLE数据类型。通过使用下标来引用TABLE变量的元素。
TABLE数据类型的定义形式如下：
TYPE 类型名 IS TABLE OF 数据类型[NOT NULL] INDEX BY BINARY_INTEGER;
此数据类型自动带有BINARY_INTEGER型的索引。
【训练5】  定义和使用TABLE变量：

SET SERVEROUTPUT ON
        DECLARE
        TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;  --类型说明
         v_t      type_table;     --定义TABLE变量
        BEGIN
         v_t(1):='MONDAY';
         v_t(2):='TUESDAY';
        v_t(3):='WEDNESDAY';
         v_t(4):='THURSDAY';
        v_t(5):='FRIDAY';
DBMS_OUTPUT.PUT_LINE(v_t(3));  --输出变量的内容
        END;

SET SERVEROUTPUT ON 
		DECLARE
 		TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;  --类型说明
		 v_t	  type_table;     --定义TABLE变量
		BEGIN
		 v_t(1):='MONDAY';
		 v_t(2):='TUESDAY';
 		v_t(3):='WEDNESDAY';
		 v_t(4):='THURSDAY';
 		v_t(5):='FRIDAY';
DBMS_OUTPUT.PUT_LINE(v_t(3));  --输出变量的内容
		END;

执行结果为：

WEDNESDAY
        PL/SQL 过程已成功完成。

WEDNESDAY
		PL/SQL 过程已成功完成。

说明：本例定义了长度为10的字符型TABLE变量，通过赋值语句为前五个元素赋值，最后输出第三个元素。
运算符和函数
PL/SQL常见的运算符和函数包括以下方面(这里只做简单的总结，可参见SQL部分的例子)：
* 算术运算：加(+)、减(?)、乘(*)、除(/)、指数(**)。
* 关系运算：小于(<)、小于等于(<=)、大于(>)、大于等于(>=)、等于(=)、不等于(!=或<>)。
* 字符运算：连接(||)。
* 逻辑运算：与(AND)、或(OR)、非(NOT)。
还有如下所示的特殊运算。
IS NULL:用来判断运算对象是否为空，为空则返回TRUE
LIKE:用来判断字符串是否与模式匹配
BETWEEN…AND…:判断值是否位于一个区间
IN(…):测试运算对象是否在一组值的列表中
IS NULL或IS NOT NULL用来判断运算对象的值是否为空，不能用“=”去判断。另外，对空值的运算也必须注意，对空值的算术和比较运算的结果都是空，但对空值可以进行连接运算，结果是另外一部分的字符串。例如：
NULL+5的结果为NULL。
NULL>5的结果为NULL。
NULL|| 'ABC' 的结果为'ABC'。
在PL/SQL中可以使用绝大部分Oracle函数，但是组函数(如AVG( )、MIN( )、MAX( )等)只能出现在SQL语句中，不能在其他语句中使用。还有GREATEST( )、LEAST( )也不能使用。类型转换在很多情况下是自动的，在不能进行自动类型转换的场合需要使用转换函数。