资源描述
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程序由一个或多个块组成。块有固定的结构,也可以嵌套。一个块可以 包括三个部分,每个部分由一个关键字标识。块中各部分的作用解释如下: DECLARE :声明部分标志。(2) BEGIN :可执行部分标志。 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的范围在20001 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 :在输入区输入以下程序:Sql代码1. /*这是一个简单的示例程序*/2. SET SERVEROUTPUT ON3. DECLARE-定义部分标识4. v_name VARCHAR2(10);-定义字符串变量 v_name5. v_sal NUMBER(5);-定义数值变量 v_sal6. BEGIN-可执行部分标识7. SELECT en ame,sal8. INTO v_name,v_sal9. FROM emp10. WHEREemp no=7788;11. -在程序中插入的SQL语句12. DBMS_OUTPUT.PUT_LINE(7788 号雇员是:|v_name|,工资为:|to_char(v_sal);13. -输両员名斥口工资14. END;-结束标识步骤3:按执行按钮或F5快捷键执行程序。 输出的结果是:Sql代码1.7788号雇员是:SCOTT,工 资为:30002.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】变量的定义和初始化。输入和运行以下程序:Sql代码1. SET SERVEROUTPUT ON2. DECLARE -声明部分标识3. v_job VARCHAR2(9);4. v_count BINARY_INTEGERDEFAULT0;5. v_total_sal NUMBER(9,2) := 0;6. v_dateDATE := SYSDATE + 7;7. c_tax_rate CONSTANT NUMBER(3,2) := 8.25;8. v_valid BOOLEANNOT NULL := TRUE;9. BEGIN10. v_job:=MANAGER;11. -在程序中赋值12. DBMS_OUTPUT.PUT_LINE(v_job);13. -输出变量v_job 的值14. DBMS_OUTPUT.PUT_LINE(v_c oun t);15. -输出变量v_count 的值16. DBMS_OUTPUT.PUT_LINE(v_date);17. -输出变量v_date 的值18. DBMS_OUTPUT.PUT_LINE(c_tax_rate);19. -输出变量c_tax_rate 的值20. END-执行结果:Sql代码1.MANAGER2.03.18-4月-034.8.255.PL/SQL过程已成功完成。说明:本训练共定义了 6个变量,分别用“:颯值运算符或DEFAULT关键字对 变量进行了初始化或赋值。其中:c_tax_rate为常量,在数据类型前加了“CONSTANT关键字;v_valid变量在赋值运算符前面加了关键字 “ NOT NULL, 强制不能为空。如果变量是布尔型,它的值只能是 “TRU”、“FALSE或 “NUL, 本练习中的变量v_valid布尔变量的值只能取 “TRUE或 “FALSE。2 根据表的字段定义变量变量的声明还可以根据数据库表的字段进行定义或根据已经定义的变量进行定 义。方法是在表的字段名或已经定义的变量名后加%TYPE ,将其当作数据类型。定义字段变量的方法如下:变量名 表名字段名TYPE;【训练2】 根据表的字段定义变量 输入并执行以下程序:Sql代码1.SET SERVEROUTPUT ON2.DECLARE3.v_ename emp.ename%TYPE;-根据字段定义变量4.BEGIN5.SELECT en ame6.INTOv_en ame7.FROM emp8.WHERE emp no = 7788;9.DBMS_OUTPUT.PUT_LINE(v_e name);10.-输出变量的值11.END执行结果:Sql代码1.SCOTT2.PL/SQL过程已成功完成。说明:变量v_ename是根据表emp的ename字段定义的,两者的数据类型总 是一致的。如果我们根据数据库的字段定义了某一变量,后来数据库的字段数据类型又进行 了修改,那么程序中的该变量的定义也自动使用新的数据类型。 使用该种变量定 义方法,变量的数据类型和大小是在编译执行时决定的, 这为书写和维护程序提 供了很大的便利。3 结合变量的定义和使用我们还可以定义SQL*Plus环境下使用的变量,称为结合变量。结合变量也可以 在程序中使用,该变量是在整个SQL*Plus环境下有效的变量,在退出SQL*Plus 之前始终有效,所以可以使用该变量在不同的程序之间传递信息。结合变量不是由程序定义的,而是使用系统命令 VARIABLE定义的。在SQL*Plus环境下显 示该变量要用系统的PRINT命令。在SQL*Plus环境下定义结合变量的方法如下:VARIABLE 变量名数据类型【训练3】定义并使用结合变量。步骤1 :输入和执行下列命令,定义结合变量 g_ename :Sql代码1. VARIABLE g_ename VARCHAR2(100)步骤2:删掉刚才输入的语句,输入和执行下列程序:Sql代码1. SET SERVEROUTPUT ON2. BEGIN3.:g_e name:=:g_e name|Hello ;4.-在程序中使用结合变量5.DBMS_OUTPUT.PUT_LINE(:g_e name);6.-输出结合变量的值7.ENU输出结果:Sql代码1. Hello2. PL/SQL过程已成功完成。步骤3 :重新执行程序。输出结果:Sql代码1. Hello Hello2. PL/SQL过程已成功完成。步骤4:程序结束后用命令显示结合变量的内容:Sql代码1. PRINT g_en ame输出结果:Sql代码1. G_ENAME2. 3. Hello Hello说明:g_ename为结合变量,可以在程序中引用或赋值,引用时在结合变量前 面要加上:”。在程序结束后该变量的值仍然存在,其他程序可以继续引用。4 记录变量的定义还可以根据表或视图的一个记录中的所有字段定义变量,称为记录变量。记录变量包含若干个字段,在结构上同表的一个记录相同,定义方法是在表名后跟ROWTYPE。记录变量的字段名就是表的字段名,数据类型也一致。记录变量的定义方法是:记录变量名表名%ROWTYPE ;获得记录变量的字段的方法是:记录变量名.字段名,女口 emp_record.ename 。 如下练习中定义并使用了记录变量。【训练4】根据表定义记录变量。输入并执行如下程序:Sql代码1. SET SERVEROUTPUT ON2. DECLARE3. emp_record emp%ROWTYPE;-定义记录变量4. BEGIN5. SELECT *INTOemp_record6. FROMemp7. WHEREmpno =7788;-取出一条记录8. DBMS_OUTPUT.PUT_LINE(emp_record.ename);-输出记录变量的某个字段9. ENU执行结果为:Sql代码1. SCOTT2. PL/SQL过程已成功完成 说明:在以上的练习中定义了记录变量 emp_record,它是根据表emp的全部字 段定义的。SELECT语句将编号为7788的雇员的全部字段对应地存入该记录变 量,最后输出记录变量的雇员名称字段 emp_record.e name的内容。如果要获 得其他字段的内容,比如要获得编号为 7788的雇员的工资,可以通过变量 emp_record.sal5. TABLE类型变量在PL/SQL中可以定义TABLE类型的变量。TABLE数据类型用来存储可变长 度的一维数组数据,即数组中的数据动态地增长。要定义 TABLE变量,需要先 定义TABLE数据类型。通过使用下标来引用 TABLE变量的元素。TABLE数据类型的定义形式如下:TYPE 类型名 IS TABLE OF 数据类型NOT NULL INDEX BYBINARY_INTEGER;此数据类型自动带有BINARY_INTEGER 型的索引。【训练5】 定义和使用TABLE变量:Sql代码1. SET SERVEROUTPUT ON2. DECLARE3. TYPE type_tableIS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; -类型说明4.v_t type_table;-定义TABLE变量5.BEGIN6.v_t(1):=MONDAY;7.v_t(2):=TUESDAY;8.v_t(3):=WEDNESDAY;9.v_t(4):=THURSDAY;10.v_t( 5):=FRIDAY;11.DBMS_OUTPUT.PUT_LINE(v_t(3);-输出变量的内容12.END执行结果为:Sql代码1. WEDNESDAY2. PL/SQL过程已成功完成。说明:本例定义了长度为10的字符型TABLE变量,通过赋值语句为前五个元 素赋值,最后输出第三个元素。运算符和函数PL/SQL常见的运算符和函数包括以下方面(这里只做简单的总结,可参见 SQL 部分的例子):*算术运算:加(+)、减、乘(*)、除(/)、指数(*)。*关系运算:小于()、小于等于()、大于等于(=)、等于(=)、不等 于(匸或)。*字符运算:连接(|)。*逻辑运算:与(AND)、或(OR)、非(NOT)。还有如下所示的特殊运算。IS NULL:用来判断运算对象是否为空,为空则返回 TRUELIKE:用来判断字符串是否与模式匹配BETWEENAND:判断值是否位于一个区间IN():测试运算对象是否在一组值的列表中IS NULL或IS NOT NULL用来判断运算对象的值是否为空,不能用 “=去判断。 另外,对空值的运算也必须注意,对空值的算术和比较运算的结果都是空, 但对 空值可以进行连接运算,结果是另外一部分的字符串。例如:NULL+5的结果为NULL。NULL5的结果为NULL。NULL| ABC的结果为ABC。在PL/SQL中可以使用绝大部分 Oracle函数,但是组函数(如AVG( )、MIN()、 MAX()等)只能出现在SQL语句中,不能在其他语句中使用。还有GREATEST()、 LEAST()也不能使用。类型转换在很多情况下是自动的,在不能进行自动类型 转换的场合需要使用转换函数。
展开阅读全文