Oracle数据库基础教程第9章人民邮电出版社.ppt

Oracle 10gOracle 10g数据库应用教程数据库应用教程 授课教师：授课教师： 职务：职务： 第9章 PL/SQL语言基础 课程描述 SQL语言的全称是结构化 查询语言（Structure Query Language），要学 习数据库编程技术，必须 首先了解SQL语言。 PL/SQL是一种数据库程序 设计语言，是Oracle数据 库系统提供的扩展SQL语 言。使用PL/SQL语言可以 在各种环境下对Oracle数 据库进行访问。 本章知识点 p PL/SQL简介 p PL/SQL组件 p 常用函数 9.1 PL/SQL简介 pPL/SQL语言的结构 pPL/SQL示例程序 PL/SQL语言的结构 p块（Block）是PL/SQL程序中最基本的结构。 pPL/SQL的块由变量声明、程序代码和异常处理 代码3部分组成。 DECLARE -标记声明部分 -此处用来定义常量、变量、类型和游标等 BEGIN -标记程序体部分开始 -此处用来编写各种PL/SQL语句、函数和 存储过程 EXCEPTION -标记异常处理部分开始 -此处用来编写异常处理代码 END; -标记程序体部分结束 PL/SQL示例程序 p示例程序的代码如下： SET ServerOutput ON; DECLARE /* 声明变量 */ var_UserName VARCHAR2(100); BEGIN SELECT UserName INTO var_UserName FROM Users WHERE UserId = 1; dbms_output.put_line(var_UserName); -输出变量 var_TypeName END; 9.2 PL/SQL组件 p声明部分 p执行部分 p异常处理部分 声明部分 pDECLARE对变量进行声明： DECLARE ; ; ; 声明部分 p常量名和变量名的定义规则： 标识符必须以字符开头。 标识符中可以包含数字（09）、下划 线（_）、“$”和“#”。 标识符最大长度为30。 标识符不区分大小写，TypeName和 typename是完全相同的。 不能使用PL/SQL保留字使用标识符名， 例如不能声明变量名为DECLARE。 声明部分 pPL/SQL中常用数据类型： BLOB。二进制大对象，可以用来保存图像和文档等二进 制数据。 BOOLEAN。布尔数据类型，支持TRUE/FALSE值。 CHAR。固定长度字符串。 CLOB。字符大对象，可用来保存多达4GB的字符数据。 DATE。存储全部日期的固定长度字符串。 LONG。可变长度字符串。 NUMBER。可变长度数值。 RAW。二进制数据的可变长度字符串。 VARCHAR2。可变长度字符串。 声明部分 （1）声明常量。声明常量的基本格式如下 ： constant := ; 关键字constant表示声明的是常量。要声明一个程序的 版本信息常量conVersion： Conversion constant VARCHAR2(20) := 1.0.01; 【例】定义变量conVersion，保存指定产品的版本信息。然 后调用dbms_output.put_line输出常量的值： SET ServerOutput ON; DECLARE conVersion constant VARCHAR2(20) := 1.0.01; BEGIN dbms_output.put_line(conVersion); END; 程序的运行结果为： 1.0.01 声明部分 （2）声明变量。声明变量的基本格式如下： (宽度) := ; 【例】声明一个变量Database保存数据库信息： SET ServerOutput ON; DECLARE Database VARCHAR2(50) := Oracle 10g ; BEGIN dbms_output.put_line(Database); END; 程序的运行结果为： Oracle 10g 执行部分 1赋值语句 【例】在程序的运行过程中，对变量进行赋值操 作： SET ServerOutput ON; DECLARE Database VARCHAR2(50); BEGIN Database := Oracle 10g ; dbms_output.put_line(Database); END; 执行部分 2条件语句IF IF THEN ELSIF THEN ELSE END IF; 执行部分 【例】演示IF语句的使用方法： SET ServerOutput ON; DECLARE Num INTEGER := -11; BEGIN IF Num 0 THEN dbms_output.put_line(正数); ELSE dbms_output.put_line(0); END IF; END; 执行部分 3分支语句CASE CASE WHEN THEN 值1 WHEN THEN 值2 WHEN THEN 值n ELSE 值n + 1 END; 执行部分 【例】使用CASE语句根据给定的整数输出对应的星期值： SET ServerOutput ON; DECLARE varDAY INTEGER := 3; Result VARCHAR2(20); BEGIN Result := CASE varDAY WHEN 1 THEN 星期一 WHEN 2 THEN 星期二 WHEN 3 THEN 星期三 WHEN 4 THEN 星期四 WHEN 5 THEN 星期五 WHEN 6 THEN 星期六 WHEN 7 THEN 星期七 ELSE 数据越界 END; dbms_output.put_line(Result); END; 执行部分 4循环语句LOOPEXITEND LOOP IF THEN EXIT END IF END LOOP; 执行部分 【例】LOOPEXITEND语句的示例程序： SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num = 3 THEN EXIT; END IF; dbms_output.put_line( + ); v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum); END; 执行部分 5循环语句LOOPEXIT WHENEND LOOP EXIT WHEN END LOOP; 执行部分 【例】用LOOPEXIT WHENEND语句来实现： SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); EXIT WHEN v_Num = 3; dbms_output.put_line( + ); v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum); END; 执行部分 6循环语句WHILELOOPEND LOOP WHILE LOOP END LOOP; 执行部分 【例】用WHILELOOPEND LOOP语句来实现： SET ServerOutput ON; DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0; BEGIN WHILE v_Num IN . LOOP END LOOP; 执行部分 【例】用FORINLOOPEND LOOP语句来实现，代码如下： SET ServerOutput ON; DECLARE v_Num INTEGER; v_Sum INTEGER := 0; BEGIN FOR v_Num IN 13 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num THEN WHEN THEN WHEN OTHERS THEN 异常处理部分 【例】下面是一个异常处理的例子： SET SERVEROUTPUT ON; DECLARE x NUMBER; BEGIN x:= 123;-向NUMBER类型的变量X中赋值字符串，导致异 常 EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(数据类型错误); END; 运行结果为： 数据类型错误 PL/SQL 过程已成功完成 异常处理部分 【例】下面是一段与数据库操作有关的异常处理代码 ： SET SERVEROUTPUT ON; DECLARE var_UserName VARCHAR(40); BEGIN SELECT UserName INTO var_UserName FROM Users WHERE UserType = 1; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有数据); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回多行匹配的数据); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(错误情况不明); END; 9.3 常用函数 p 数值型函数 p 字符型函数 p 日期型函数 p 统计函数 数值型函数 pABS函数返回给定数字表达式的绝对值。 【例】如果要计算4的绝对值： SET ServerOutput ON; BEGIN dbms_output.put_line(ABS(-4); END; 数值型函数 pCEIL函数返回大于或等于所给数字表达式的 最小整数。 【例】分别对正数、负数和0计算CEIL： SET ServerOutput ON; BEGIN dbms_output.put_line(CEIL(116.24); dbms_output.put_line(CEIL(-112.75); dbms_output.put_line(CEIL(0); END; 数值型函数 pFLOOR函数返回小于或等于所给数字表达 式的最大整数。 【例】分别对正数、负数和0计算FLOOR： SET ServerOutput ON; BEGIN dbms_output.put_line(FLOOR(116.24); dbms_output.put_line(FLOOR(-112.75); dbms_output.put_line(FLOOR(0); END; 数值型函数 p POWER函数返回给定表达式乘指定次方的值 。 【例】执行以下命令，计算15的4次方： SET ServerOutput ON; BEGIN dbms_output.put_line(POWER(15, 4); END; 数值型函数 pROUND函数返回数字表达式并四舍五入为指定的长度 或精度。 【例】请执行以下命令，注意观察长度变化对结果的 影响： SET ServerOutput ON; BEGIN dbms_output.put_line(ROUND(123.456, 2); dbms_output.put_line(ROUND(123.456, 1); dbms_output.put_line(ROUND(123.456, 0); dbms_output.put_line(ROUND(123.456, -1); dbms_output.put_line(ROUND(123.456, -2); dbms_output.put_line(ROUND(123.456, -3); END; 字符型函数 pASCII函数返回字符表达式最左端字符的 ASCII 代码值。 【例】执行以下命令，输出字符A的ASCII码。 SET ServerOutput ON; BEGIN dbms_output.put_line(ASCII(ABC); END; 字符型函数 p使用flashback table语句可以对表进行闪 回操作： Flashback table To before drop rename to | scn | timestamp Enable | disable triggers; pLENGTH函数返回给定字符串表达式的字符 （而不是字节）个数，其中不包含尾随空格 。 【例】返回部门名称的字符串长度： SELECT UserName, LENGTH(UserName) FROM Users WHERE UserType = 1; 字符型函数 pUPPER函数返回将小写字符数据转换为大写 的字符表达式。 【例】将字符串 abc 转换为大写字母： SET ServerOutput ON; BEGIN dbms_output.put_line(UPPER(abc); END; 日期型函数 pSYSDATE。返回当前日期和时间。 【例】输出当前的日期信息： SET ServerOutput ON; BEGIN dbms_output.put_line(SYSDATE); END; pTO_CHAR。转换日期为字符串。 【例】将当前日期转换为字符串后再输出： SET ServerOutput ON; BEGIN dbms_output.put_line(TO_CHAR(SYSDATE); END; 日期型函数 pLAST_DAY。返回