《Oracle数据库基础教程第9章人民邮电出版社.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库基础教程第9章人民邮电出版社.ppt（40页珍藏版）》请在金锄头文库上搜索。
1、Oracle 10gOracle 10g数据库应用教程数据库应用教程数据库应用教程数据库应用教程 授课教师：授课教师：职务：职务：第第9章章 PL/SQL语言基础语言基础 课程描述课程描述课程描述课程描述SQLSQLSQLSQL语言的全称是结构化语言的全称是结构化语言的全称是结构化语言的全称是结构化查询语言（查询语言（查询语言（查询语言（Structure Structure Structure Structure Query LanguageQuery LanguageQuery LanguageQuery Language），要学），要学），要学），要学习数据库编程技术，必须习数据库编程技
2、术，必须习数据库编程技术，必须习数据库编程技术，必须首先了解首先了解首先了解首先了解SQLSQLSQLSQL语言。语言。语言。语言。PL/SQLPL/SQLPL/SQLPL/SQL是一种数据库程序是一种数据库程序是一种数据库程序是一种数据库程序设计语言，是设计语言，是设计语言，是设计语言，是OracleOracleOracleOracle数据数据数据数据库系统提供的扩展库系统提供的扩展库系统提供的扩展库系统提供的扩展SQLSQLSQLSQL语语语语言。使用言。使用言。使用言。使用PL/SQLPL/SQLPL/SQLPL/SQL语言可以语言可以语言可以语言可以在各种环境下对在各种环境下对在各种环
3、境下对在各种环境下对OracleOracleOracleOracle数数数数据库进行访问。据库进行访问。据库进行访问。据库进行访问。 本章知识点本章知识点p PL/SQLPL/SQL简介简介 p PL/SQLPL/SQL组件组件 p 常用函数常用函数 9.1PL/SQL简介简介 pPL/SQLPL/SQL语言的结构语言的结构pPL/SQLPL/SQL示例程序示例程序 PL/SQL语言的结构语言的结构 p块（块（BlockBlock）是）是PL/SQLPL/SQL程序中最基本的结构。程序中最基本的结构。pPL/SQLPL/SQL的块由变量声明、程序代码和异常处理代的块由变量声明、程序代码和异常处
4、理代码码3 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 WHE
5、RE UserId = 1; dbms_output.put_line(var_UserName); -输出变量var_TypeNameEND;9.2 PL/SQL组件组件 p声明部分声明部分 p执行部分执行部分 p异常处理部分异常处理部分 声明部分声明部分 pDECLAREDECLARE对变量进行声明：对变量进行声明：DECLARE ; ; ;声明部分声明部分 p常量名和变量名的定义规则：常量名和变量名的定义规则：标识符必须以字符开头。标识符中可以包含数字（09）、下划线（_）、“$”和“#”。标识符最大长度为30。标识符不区分大小写，TypeName和typename是完全相同的。不能使用
6、PL/SQL保留字使用标识符名，例如不能声明变量名为DECLARE。声明部分声明部分pPL/SQLPL/SQL中常用数据类型：中常用数据类型：BLOB。二进制大对象，可以用来保存图像和文档等二进制数据。BOOLEAN。布尔数据类型，支持TRUE/FALSE值。CHAR。固定长度字符串。CLOB。字符大对象，可用来保存多达4GB的字符数据。DATE。存储全部日期的固定长度字符串。LONG。可变长度字符串。NUMBER。可变长度数值。RAW。二进制数据的可变长度字符串。VARCHAR2。可变长度字符串。声明部分声明部分（1 1）声明常量。声明常量的基本格式如下：）声明常量。声明常量的基本格式如下：
7、 constant := ; 关键字关键字constantconstant表示声明的是常量。要声明一个程序的版本信表示声明的是常量。要声明一个程序的版本信息常量息常量conVersionconVersion：Conversion constant VARCHAR2(20) := 1.0.01;【例例】定义变量定义变量conVersionconVersion，保存指定产品的版本信息。然后调用，保存指定产品的版本信息。然后调用dbms_output.put_linedbms_output.put_line输出常量的值：输出常量的值：SET ServerOutput ON;DECLARE conVe
8、rsion constant VARCHAR2(20) := 1.0.01;BEGIN dbms_output.put_line(conVersion);END;程序的运行结果为：1.0.01声明部分声明部分（2 2）声明变量。声明变量的基本格式如下：）声明变量。声明变量的基本格式如下： (宽度) := ;【例例】声明一个变量声明一个变量DatabaseDatabase保存数据库信息：保存数据库信息：SET ServerOutput ON;DECLARE Database VARCHAR2(50) := Oracle 10g ;BEGIN dbms_output.put_line(Databa
9、se);END;程序的运行结果为：Oracle 10g 执行部分执行部分 1 1赋值语句赋值语句 【例例】在程序的运行过程中，对变量进行赋值操作：在程序的运行过程中，对变量进行赋值操作：SET ServerOutput ON;DECLARE Database VARCHAR2(50); BEGIN Database := Oracle 10g ; dbms_output.put_line(Database);END;执行部分执行部分2 2条件语句条件语句IF IF IF THEN ELSIF THEN ELSE END IF;执行部分执行部分【例例】演示演示IFIF语句的使用方法：语句的使用方
10、法：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 3分支语句分支语句CASE CASE CASE WHEN THEN 值1 WHEN THEN 值2 WHEN THEN 值n ELSE 值n + 1END;执行部分执行部分【例例】使用使用CASECASE语句根据给定的整数输出对应的星期值：语句根据给定的整数输出对应的星期值：SET ServerOutpu
11、t 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 4循环语句循环语句LOOPEXITEND LOOPEXITEND LOOP IF THEN EX
12、IT END IF END LOOP;执行部分执行部分【例例】LOOPEXITENDLOOPEXITEND语句的示例程序：语句的示例程序：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
13、.put_line( = ); dbms_output.put_line(v_Sum);END;执行部分执行部分5 5循环语句循环语句LOOPEXIT WHENEND LOOPEXIT WHENEND LOOP EXIT WHEN END LOOP;执行部分执行部分【例例】用用LOOPEXIT WHENENDLOOPEXIT WHENEND语句来实现：语句来实现：SET ServerOutput ON;DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0;BEGIN LOOP v_Sum := v_Sum + v_Num; dbms_output.put
14、_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 6循环语句循环语句WHILELOOPEND LOOP WHILELOOPEND LOOP WHILE LOOP END LOOP;执行部分执行部分【例例】用用WHILELOOPEND LOOPWHILELOOPEND LOOP语句来实现：语句来实现：SET ServerOut
15、put ON;DECLARE v_Num INTEGER := 1; v_Sum INTEGER := 0;BEGIN WHILE v_Num = 3 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num 3 THEN dbms_output.put_line( + ); END IF; v_Num := v_Num + 1; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;执行部分执行部分7 7循环语句循环语句FORINLO
16、OPEND LOOP FORINLOOPEND LOOP FOR IN .LOOP END LOOP;执行部分执行部分【例例】用用FORINLOOPEND LOOPFORINLOOPEND LOOP语句来实现，代码如下：语句来实现，代码如下：SET ServerOutput ON;DECLARE v_Num INTEGER; v_Sum INTEGER := 0;BEGIN FOR v_Num IN 1.3 LOOP v_Sum := v_Sum + v_Num; dbms_output.put_line(v_Num); IF v_Num 3 THEN dbms_output.put_line
17、( + ); END IF; END LOOP; dbms_output.put_line( = ); dbms_output.put_line(v_Sum);END;异常处理部分异常处理部分 pWHENWHEN语句来定义异常处理：语句来定义异常处理：EXCEPTION WHEN THEN WHEN THEN WHEN OTHERS THEN 异常处理部分异常处理部分 【例例】下面是一个异常处理的例子：下面是一个异常处理的例子：SET SERVEROUTPUT ON;DECLARE x NUMBER;BEGIN x:= 123;-向NUMBER类型的变量X中赋值字符串，导致异常EXCEPTIO
18、N 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
19、 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 统计函数统计函数 数值型函数数值型函数pABSABS函数返回给定数字表达式的绝对值。函数返回给定数字表达式的绝对值。【例例】如果要计算如果要计算4 4的绝对值：的绝对值：SET ServerOutput ON;B
20、EGIN dbms_output.put_line(ABS(-4);END;数值型函数数值型函数pCEILCEIL函数返回大于或等于所给数字表达式的最函数返回大于或等于所给数字表达式的最小整数。小整数。【例例】分别对正数、负数和分别对正数、负数和0 0计算计算CEILCEIL：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;数值型函数数值型函数pFLOORFLOOR函数返回小于或等于所
21、给数字表达式函数返回小于或等于所给数字表达式的最大整数。的最大整数。【例例】分别对正数、负数和分别对正数、负数和0 0计算计算FLOORFLOOR：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 POWERPOWER函数返回给定表达式乘指定次方的值。函数返回给定表达式乘指定次方的值。【例例】执行以下命令，计算执行以下命令，计算1515的的4 4次方：次方：
22、SET ServerOutput ON;BEGIN dbms_output.put_line(POWER(15, 4);END;数值型函数数值型函数pROUNDROUND函数返回数字表达式并四舍五入为指定的长度或函数返回数字表达式并四舍五入为指定的长度或精度。精度。【例例】请执行以下命令，注意观察长度变化对结果的影响：请执行以下命令，注意观察长度变化对结果的影响：SET ServerOutput ON;BEGIN dbms_output.put_line(ROUND(123.456, 2); dbms_output.put_line(ROUND(123.456, 1); dbms_output
23、.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;字符型函数字符型函数 pASCIIASCII函数返回字符表达式最左端字符的函数返回字符表达式最左端字符的 ASCII ASCII 代码值。代码值。【例例】执行以下命令，输出字符执行以下命令，输出字符A A的的ASCIIASCII码。码。SET ServerOutput ON;BEGIN d
24、bms_output.put_line(ASCII(ABC);END;字符型函数字符型函数 p使用使用flashback tableflashback table语句可以对表进行闪回操语句可以对表进行闪回操作：作：Flashback table To before drop rename to | scn | timestamp Enable | disable triggers;pLENGTHLENGTH函数返回给定字符串表达式的字符（而函数返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。不是字节）个数，其中不包含尾随空格。【例例】返回部门名称的字符串长度：返回部门名称的字
25、符串长度：SELECT UserName, LENGTH(UserName) FROM UsersWHERE UserType = 1;字符型函数字符型函数 pUPPERUPPER函数返回将小写字符数据转换为大写的字函数返回将小写字符数据转换为大写的字符表达式。符表达式。【例例】将字符串将字符串 abcabc 转换为大写字母：转换为大写字母：SET ServerOutput ON;BEGIN dbms_output.put_line(UPPER(abc);END;日期型函数日期型函数 pSYSDATESYSDATE。返回当前日期和时间。返回当前日期和时间。【例例】输出当前的日期信息：输出当前的
26、日期信息：SET ServerOutput ON;BEGIN dbms_output.put_line(SYSDATE);END;pTO_CHARTO_CHAR。转换日期为字符串。转换日期为字符串。【例例】将当前日期转换为字符串后再输出：将当前日期转换为字符串后再输出：SET ServerOutput ON;BEGIN dbms_output.put_line(TO_CHAR(SYSDATE);END;日期型函数日期型函数 pLAST_DAYLAST_DAY。返回包含日期。返回包含日期d d的月份的最后一天的日期。这个函数可以被的月份的最后一天的日期。这个函数可以被用来确定当前月中还剩下多少天
27、。用来确定当前月中还剩下多少天。【例例】输出当前月份的最后一天：输出当前月份的最后一天：SET ServerOutput ON;BEGIN dbms_output.put_line(LAST_DAY(SYSDATE);END;pMONTHS_BETWEENMONTHS_BETWEEN。返回两个日期之间月的数目。返回两个日期之间月的数目。【例例】计算计算2008-06-052008-06-05到到2008-10-052008-10-05之间的月份数目：之间的月份数目：SET ServerOutput ON;DECLARE date1 VARCHAR2(20) := 2008-06-05; dat
28、e2 VARCHAR2(20) := 2008-10-05;BEGIN dbms_output.put_line(MONTHS_BETWEEN(TO_DATE(date2,yyyy-mm-dd), TO_DATE(date1, yyyy-mm-dd);END;统计函数统计函数 pCOUNTCOUNT函数返回组中项目的数量。函数返回组中项目的数量。 【例】统计表Users中用户记录的数量：SELECT COUNT(UserName) FROM UserMan.Users;pMAXMAX。MAXMAX函数返回表达式的最大值。函数返回表达式的最大值。 【例】统计表Users中最大的用户编号：SELECT MAX(UserId) FROM UserMan.Users;pMINMIN。MINMIN函数返回表达式的最小值。函数返回表达式的最小值。 【例】统计表Users中最小的用户编号：SELECT MIN(UserId) FROM UserMan.Users;
