2、PLSQL编程基础

1、PL/SQL编程基础1目标PL/SQL字符类型和词汇单元PL/SQL变量PL/SQL保留字在PL/SQL中使用标识符使用%TYPE属性指定数据类型声明和初始化变量变量的作用范围2PL/SQL字符类型和词汇单元 2-1字符类型PL/SQL引擎支持4种类型的字符：字母、数字、符号(*、+、-、=等)和空格。把这些类型的一个或者多个字符组合起来就会创建一个词汇单元词汇单元词汇单元就是不同类型字符的组合PL/SQL词汇单元分为如下5类:标识符必须以字母开头，最多包含30个字符保留字是PL/SQL的专用词(例如BEGIN、END和SELECT等)3PL/SQL字符类型和词汇单元 2-2操作符是对PL/S

2、QL有特殊含义的字符，例如，算术操作符和引号等字面值是不代表标识符的值(字符、数值或者布尔 true/false)。如123、“Declaration of Independence”和FALSE等都是字面值的例子注释可以是单行注释(-)或者多行注释(/* */)4PL/SQL变量 4-1变量可以被用来保存临时值。变量的语法为: variable-name datatype optional default assignment变量也被称为标识符。变量必须以字母开头，最大为30个字符长。例如：v_student_idv_last_ name V_LAST_NAME apt_#注意：标识符v_l

3、ast_ name和V_LAST_NAME被认为是相同的，因为PL/SQL不区分大小写5PL/SQL变量 4-2以下是非法标识符：X+Y ：包含一个加号，而加号是PL/SQL预留使用的1st_year ：以数字开头student ID ：包含空格为了使用变量，必须在PL/SQL语句块的声明部分中声明它。必须给变量起一个名称，定义其数据类型。也可以给这个变量赋一个初始值。注意，如果没有给变量赋初始值，则值为NULL。变量必须首先被声明，然后才能被引用。可以把变量设置为一个常量，这意味着这个变量的值不能被修改6PL/SQL变量 4-3示例1：set serveroutput on;DECLARE

4、v_name VARCHAR2(35); v_dob DATE; v_us_citizen BOOLEAN;BEGIN DBMS_OUTPUT.PUT_LINE (v_name|born on|v_dob);END;A）如果在SQL *Plus中运行这个示例，会得到什么结果?只会得到输出 born on。原因是v _name和v_dob变量值null，输出显示为空白B）当 v _name和v_dob变量值为null时，希望分别输出No Name和01-Jan-1999 ，可以怎么做 ？7PL/SQL变量 4-4在DBMS_OUTPUT.PUT_LINE语句中，使用COALESCE (v_nam

5、e, No Name)替代v_name，使用COALESCE (v_dob，01-Jan-1999)替代v _dobCOALESCE函数会把表达式列表中每个表达式与NULL进行比较，并返回第1个非NULL表达式的值。在此情况下，会比较v_name变量和 No Name字符串，并返回值 No Name。这是因为v_name变量尚未被初始化，因此其值还是NULL8PL/SQL保留字 2-1保留字(如BEGIN、END和SELECT等)是专用于PL/SQL的标识符。不能把保留字用作变量名、字面值或者用户定义异常的名称示例2：set serveroutput on;DECLARE exception

6、VARCHAR2(15);BEGIN exception :=This is a test; DBMS_OUTPUT.PUT_LINE (exception);END;A)如果运行这个PL/SQL语句块，会发生什么事情呢?9PL/SQL保留字 2-2会出现一大堆错误：ORA-06550: 第 3 行, 第 3 列: PLS-00103: 出现符号 EXCEPTION在需要下列之一时： begin function pragma procedure subtype type current cursor delete exists prior符号 begin在 EXCEPTION 继续之前已插入

7、。ORA-06550: 第 5 行, 第 3 列: PLS-00103: 出现符号 EXCEPTION在需要下列之一时： ( begin case declare exit for goto if loop mod null pragma raise return select update while with con10在PL/SQL中使用标识符 4-1示例3：DECLARE v_var1 VARCHAR2(20); v_var2 VARCHAR2(6); v_var3 NUMBER(5,3);BEGIN v_var1 := string literal; v_var2 := 12.345

8、; v_var3 := 12.345; DBMS_OUTPUT.PUT_LINE (v_var1: |v_var1); DBMS_OUTPUT.PUT_LINE (v_var2: |v_var2); DBMS_OUTPUT.PUT_LINE (v_var3: |v_var3);END;当运行这个脚本时，会得到什么结果？11在PL/SQL中使用标识符 4-2分析：在本例中，我们声明和初始化了3个变量。给变量分配的值都是字面值。因为使用单引号括起来了，所以前2个值string literal和12.345都是字符串字面值。第3个值12.345是数字字面值。当运行这个脚本，会得到如下结果：v_var

9、1: string literalv_var2: 12.345v_var3: 12.345 PL/SQL procedure successfully completed12在PL/SQL中使用标识符 4-3示例4：DECLARE v_var1 NUMBER(2) := 123; v_var2 NUMBER(3) := 123; v_var3 NUMBER(5,3) := 123456.123;BEGIN DBMS_OUTPUT.PUT_LINE (v_var1: |v_var1); DBMS_OUTPUT.PUT_LINE (v_var2: |v_var2); DBMS_OUTPUT.PUT

10、_LINE (v_var3: |v_var3);END;A)如果执行这个PL/SQL语句块，会得到什么结果?13在PL/SQL中使用标识符 4-4在本例中，我们声明和初始化了3个数字变量。第1个声明和初始化语句(v_var1 NUMBER (2) : = 123)会报错，因为值123超出了指定的精度范围。第2个声明和初始化语句(v_var2 NUMBER (3) : = 123)不会报错，因为值123符合指定的精度要求。最后一个声明和初始化语句(v_var3 NUMBER (5 , 3): = 123456 .1 23)会报错，因为值123456.123超出了指定精度范围。因此，本例会得到如下

11、输出: ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高ORA-06512: 在 line 314使用%TYPE属性指定数据类型 2-1给变量指定的数据类型可以基于某种数据库对象，这样使得变量的数据类型依赖于底层对象的数据类型。应该尽可能使用这种方式，这使得当底层对象的数据类型发生变化时，不需要更改自己的PL/SQL代码。语法如下所示: variable_name type-attribute%TYPEtype-attribute是对数据库列的直接引用示例5：set serveroutput on;DECLARE v_name student.first_name%type

12、; v_grade grade.numeric_grade%type;15使用%TYPE属性指定数据类型 2-2BEGIN DBMS_OUTPUT.PUT_LINE (nvl(v_name,no name)| has grade of |nvl(v_grade,0);END;A)在本例中，声明了什么？阐述所声明变量的数据类型和值变量v _name声明时所使用的数据类型与数据库表STUDENT的first _name列的类型完全相同。换句话说，变量v_ name被定义为VARCHAR2 (25)。除此之外，声明变量v_grade的数据类型与数据库表GRADE的grade_numeric列的类型完

13、全相同。这也就是说， v_grade变量被定义为NUMBER (3)。每个变量的值都是NULL16PL/SQL中使用的主要数据类型 6-1VARCHAR2(max_length)存储变长的字符数据参数是必须的，指定字符数据的最大长度，最大32767字节不使用常量或者变量来指定最大长度，必须使用数字型的字面值VARCHAR2数据库列的最大宽度是4000字节CHAR(max_length)存储定长的字符数据（不足时使用空格填充）参数是可选的，指定字符数据的最大长度，最大32767字节不使用常量或者变量来指定最大长度，必须使用数字型的字面值。如果没有指定最大长度，则默认为1CHAR数据库列的最大宽度

14、是2000字节，默认值是1字节17PL/SQL中使用的主要数据类型 6-2NUMBER(precision,scale)存储任何大小的整数或者浮点数precision精度是指数字的总位数，不含小数点scale数值范围决定是否进行四舍五入NUMBER值的最大精度是38个十进制位常量或者变量不能被用于指定精度和数值范围，必须使用数字型的字面值数值范围为0到127。当sacle=0时（默认值），表示只允许使用整数，在小数点后面第1位上进行四舍五入，例如123.45变成123；当sacle=2时，表示在小数点后面第3位上进行四舍五入，例如3.456变成3.46数值范围可以是负数。表示在小数点的前面进行

15、四舍五入。当scale= -3时，表示在小数点前面的第3位上进行四舍五入，例如3 456变成3 000，3 556变成4 00018PL/SQL中使用的主要数据类型 6-3BINARY_INTEGER存储正负整数与NUMBER类型比较，该类型的值被存储为二进制格式，占用空间更少计算速度更快可以存储 -2 147 483 747到2 147 483 747之间的任何整数值（几十亿）该数据类型主要用于索引PL/SQL表，不能在数据库表中创建类型为BINARY_INTEGER的列BOOLEAN该类型只有3个值：TRUE、FALSE、NULL不能在数据库表中创建类型为BOOLEAN的列19PL/SQL

16、中使用的主要数据类型 6-4DATE存储定长的日期值，7个字节DATE变量的有效日期在January 1 4712 BC和December 31 9999 AD的范围内当在数据库列中存储时，包含从零时以来，以秒为单位的时间信息日期部分的默认值是当月的第1天；时间部分的默认值是零时日期实际存储为二进制格式，并按照默认格式进行显示TIMESTAMP该类型是date类型的扩展，存储定长的日期值，精度到秒的小数位，最高为十进制小数位的第9位(默认是6)。例如，12-jan-2012 09.51.44.000000借助于WITH TIME ZONE或者WITH LOCAL TIME ZONE选项，能够把

17、TIMESTAMP绑定到特定时区20PL/SQL中使用的主要数据类型 6-5LONG存储变长的字符串类似于VARCHAR2类型，唯一的区别是long值的最大长度为2GB不能从LONG列选择大于4000字节的值填充到一个long变量可以在update、insert、select语句中引用LONG列，但是不能在表达式、SQL函数调用或者特定SQL子句（例如where、group by、connect by等）中引用LONG RAW存储二进制数据，最大不能超过2GBLOB（大对象）LOB的四种类型是BLOB、CLOB、NLOB、BFILE。可以存储二进制对象，例如图像或者视频文件，最大为4GBBFI

18、LE是存储在数据库之外的大型二进制文件，最大为4GB21PL/SQL中使用的主要数据类型 6-6ROWID每个Oracle数据库表都有一个ROWID伪列，存储二进制值Rowid值用于唯一的标识每行数据，提供访问特定数据行的最快捷方式当检索ROWID伪列的值并存入ROWID类型变量时，可以使用函数ROWIDTOCHAR，把二进制值转换为18字节的字符串22PL/SQL中使用的操作符算术运算符(* , * , / , + , )比较运算符(=, , !=, , =, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL,NOT IN)逻辑运算符(AND, OR,NOT)字

19、符串运算符(|, LIKE)23声明和初始化变量在PL/SQL中，变量必须被声明，以便于被引用。这是在P L/SQL语句块中最初的声明部分所完成的每个声明语句必须以分号结束可以使用赋值操作符（:=）对变量进行赋值。如果把某变量声明为常量，必须在声明时进行赋值，该变量的值会在整个语句块中都保持不变24变量的作用域 4-1标签和嵌套语句块标签可以添加到语句块，以改进代码的可读性，以及限定嵌套语句块中存在相同名称的元素。标签的名称必须在可执行代码第1行(或者BEGIN或者DECLARE)的前面，如下所示:SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(Th

20、e procedure find_stu_num has been executed.);END find_stu_num;标签也可以出现在END的后面，目的是为了注释25变量的作用域 4-2嵌套语句块是完全位于其它语句块中的语句块，例如：BEGIN -outer block BEGIN -inner block END; -end of inner block END; -end of outer block 嵌套语句块会对变量的作用域带来影响26变量的作用域 4-3变量的作用域变量的作用域是可以访问变量或者变量可见的程序块范围。通常，变量的作用域就是声明该变量的当前语句块。在外部块中声明的变量其作用域就是整个外部块（从BEGIN开始一直到END），在嵌套块中声明的变量其作用域就是整个嵌套块（从BEGIN开始一直到END）。嵌套块可以直接访问外部块中声明的变量。但是当在嵌套块中声明了一个和外部块中同名的变量时，嵌套块就不能直接访问外部块中的同名变量了，因为这时它看不见外部块中的同名变量。只能通过标签名来引用外部同名变量27变量的作用域 4-4示