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

,Oracle10g数据库应用教程,授课教师：职务：,第9章PL/SQL语言基础,课程描述SQL语言的全称是结构化查询语言（StructureQueryLanguage），要学习数据库编程技术，必须首先了解SQL语言。PL/SQL是一种数据库程序设计语言，是Oracle数据库系统提供的扩展SQL语言。使用PL/SQL语言可以在各种环境下对Oracle数据库进行访问。,本章知识点,PL/SQL简介PL/SQL组件常用函数,9.1PL/SQL简介,PL/SQL语言的结构PL/SQL示例程序,PL/SQL语言的结构,块（Block）是PL/SQL程序中最基本的结构。PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成。DECLARE-标记声明部分-此处用来定义常量、变量、类型和游标等BEGIN-标记程序体部分开始-此处用来编写各种PL/SQL语句、函数和存储过程EXCEPTION-标记异常处理部分开始-此处用来编写异常处理代码END;-标记程序体部分结束,PL/SQL示例程序,示例程序的代码如下：SETServerOutputON;DECLARE/*声明变量*/var_UserNameVARCHAR2(100);BEGINSELECTUserNameINTOvar_UserNameFROMUsersWHEREUserId=1;dbms_output.put_line(var_UserName);-输出变量var_TypeNameEND;,9.2PL/SQL组件,声明部分执行部分异常处理部分,声明部分,DECLARE对变量进行声明：DECLARE;,声明部分,常量名和变量名的定义规则：标识符必须以字符开头。标识符中可以包含数字（09）、下划线（_）、“$”和“#”。标识符最大长度为30。标识符不区分大小写，TypeName和typename是完全相同的。不能使用PL/SQL保留字使用标识符名，例如不能声明变量名为DECLARE。,声明部分,PL/SQL中常用数据类型：BLOB。二进制大对象，可以用来保存图像和文档等二进制数据。BOOLEAN。布尔数据类型，支持TRUE/FALSE值。CHAR。固定长度字符串。CLOB。字符大对象，可用来保存多达4GB的字符数据。DATE。存储全部日期的固定长度字符串。LONG。可变长度字符串。NUMBER。可变长度数值。RAW。二进制数据的可变长度字符串。VARCHAR2。可变长度字符串。,声明部分,（1）声明常量。声明常量的基本格式如下：constant:=;关键字constant表示声明的是常量。要声明一个程序的版本信息常量conVersion：ConversionconstantVARCHAR2(20):=1.0.01;【例】定义变量conVersion，保存指定产品的版本信息。然后调用dbms_output.put_line输出常量的值：SETServerOutputON;DECLAREconVersionconstantVARCHAR2(20):=1.0.01;BEGINdbms_output.put_line(conVersion);END;程序的运行结果为：1.0.01,声明部分,（2）声明变量。声明变量的基本格式如下：(宽度):=;【例】声明一个变量Database保存数据库信息：SETServerOutputON;DECLAREDatabaseVARCHAR2(50):=Oracle10g;BEGINdbms_output.put_line(Database);END;程序的运行结果为：Oracle10g,执行部分,1赋值语句【例】在程序的运行过程中，对变量进行赋值操作：SETServerOutputON;DECLAREDatabaseVARCHAR2(50);BEGINDatabase:=Oracle10g;dbms_output.put_line(Database);END;,执行部分,2条件语句IFIFTHENELSIFTHENELSEENDIF;,执行部分,【例】演示IF语句的使用方法：SETServerOutputON;DECLARENumINTEGER:=-11;BEGINIFNum0THENdbms_output.put_line(正数);ELSEdbms_output.put_line(0);ENDIF;END;,执行部分,3分支语句CASECASEWHENTHEN值1WHENTHEN值2WHENTHEN值nELSE值n+1END;,执行部分,【例】使用CASE语句根据给定的整数输出对应的星期值：SETServerOutputON;DECLAREvarDAYINTEGER:=3;ResultVARCHAR2(20);BEGINResult:=CASEvarDAYWHEN1THEN星期一WHEN2THEN星期二WHEN3THEN星期三WHEN4THEN星期四WHEN5THEN星期五WHEN6THEN星期六WHEN7THEN星期七ELSE数据越界END;dbms_output.put_line(Result);END;,执行部分,4循环语句LOOPEXITENDLOOPIFTHENEXITENDIFENDLOOP;,执行部分,【例】LOOPEXITEND语句的示例程序：SETServerOutputON;DECLAREv_NumINTEGER:=1;v_SumINTEGER:=0;BEGINLOOPv_Sum:=v_Sum+v_Num;dbms_output.put_line(v_Num);IFv_Num=3THENEXIT;ENDIF;dbms_output.put_line(+);v_Num:=v_Num+1;ENDLOOP;dbms_output.put_line(=);dbms_output.put_line(v_Sum);END;,执行部分,5循环语句LOOPEXITWHENENDLOOPEXITWHENENDLOOP;,执行部分,【例】用LOOPEXITWHENEND语句来实现：SETServerOutputON;DECLAREv_NumINTEGER:=1;v_SumINTEGER:=0;BEGINLOOPv_Sum:=v_Sum+v_Num;dbms_output.put_line(v_Num);EXITWHENv_Num=3;dbms_output.put_line(+);v_Num:=v_Num+1;ENDLOOP;dbms_output.put_line(=);dbms_output.put_line(v_Sum);END;,执行部分,6循环语句WHILELOOPENDLOOPWHILELOOPENDLOOP;,执行部分,【例】用WHILELOOPENDLOOP语句来实现：SETServerOutputON;DECLAREv_NumINTEGER:=1;v_SumINTEGER:=0;BEGINWHILEv_Num=3LOOPv_Sum:=v_Sum+v_Num;dbms_output.put_line(v_Num);IFv_Num3THENdbms_output.put_line(+);ENDIF;v_Num:=v_Num+1;ENDLOOP;dbms_output.put_line(=);dbms_output.put_line(v_Sum);END;,执行部分,7循环语句FORINLOOPENDLOOPFORIN.LOOPENDLOOP;,执行部分,【例】用FORINLOOPENDLOOP语句来实现，代码如下：SETServerOutputON;DECLAREv_NumINTEGER;v_SumINTEGER:=0;BEGINFORv_NumIN1.3LOOPv_Sum:=v_Sum+v_Num;dbms_output.put_line(v_Num);IFv_Num3THENdbms_output.put_line(+);ENDIF;ENDLOOP;dbms_output.put_line(=);dbms_output.put_line(v_Sum);END;,异常处理部分,WHEN语句来定义异常处理：EXCEPTIONWHENTHENWHENTHENWHENOTHERSTHEN,异常处理部分,【例】下面是一个异常处理的例子：SETSERVEROUTPUTON;DECLARExNUMBER;BEGINx:=123;-向NUMBER类型的变量X中赋值字符串，导致异常EXCEPTIONWHENVALUE_ERRORTHENDBMS_OUTPUT.PUT_LINE(数据类型错误);END;运行结果为：数据类型错误PL/SQL过程已成功完成,异常处理部分,【例】下面是一段与数据库操作有关的异常处理代码：SETSERVEROUTPUTON;DECLAREvar_UserNameVARCHAR(40);BEGINSELECTUserNameINTOvar_UserNameFROMUsersWHEREUserType=1;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(没有数据);WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(返回多行匹配的数据);WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(错误情况不明);END;,9.3常用函数,数值型函数字符型函数日期型函数统计函数,数值型函数,ABS函数返回给定数字表达式的绝对值。【例】如果要计算4的绝对值：SETServerOutputON;BEGINdbms_output.put_line(ABS(-4);END;,数值型函数,CEIL函数返回大于或等于所给数字表达式的最小整数。【例】分别对正数、负数和0计算CEIL：SETServerOutputON;BEGINdbms_output.put_line(CEIL(116.24);dbms_output.put_line(CEIL(-112.75);dbms_output.put_line(CEIL(0);END;,数值型函数,FLOOR函数返回小于或等于所给数字表达式的最大整数。【例】分别对正数、负数和0计算FLOOR：SETServerOutputON;BEGINdbms_output.put_line(FLOOR(116.24);dbms_output.put_line(FLOOR(-112.75);dbms_output.put_line(FLOOR(0);END;,数值型函数,POWER函数返回给定表达式乘指定次方的值。【例】执行以下命令，计算15的4次方：SETServerOutputON;BEGINdbms_output.put_line(POWER(15,4);END;,数值型函数,ROUND函数返回数字表达式并四舍五入为指定的长度或精度。【例】请执行以下命令，注意观察长度变化对结果的影响：SETServerOutputON;BEGINdbms_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;,字符型函数,ASCII函数返回字符表达式最左端字符的ASCII代码值。【例】执行以下命令，输出字符A的ASCII码。SETServerOutputON;BEGINdbms_output.put_line(ASCII(ABC);END;,字符型函数,使用flashbacktable语句可以对表进行闪回操作：FlashbacktableTobeforedroprenameto|scn|timestampEnable|disabletriggers;LENGTH函数返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。【例】返回部门名称的字符串长度：SELECTUserName,LENGTH(UserName)FROMUsersWHEREUserType=1;,字符型函数,UPPER函数返回将小写字符数据转换为大写的字符表达式。【例】将字符串abc转换为大写字母：SETServerOutputON;BEGINdbms_output.put_line(UPPER(abc);END;,日期型函数,SYSDATE。返回当前日期和时间。【例】输出当前的日期信息：SETServerOutputON;BEGINdbms_output.put_line(SYSDATE);END;TO_CHAR。转换日期为字符串。【例】将当前日期转换为字符串后再输出：SETServerOutputON;BEGINdbms_output.put_line(TO_CHAR(SYSDATE);END;,日期