第5章使用PLSQL

1、第3章 使用PL/SQL课程目标：1、理解PL/SQL的用法2、了解数据类型及其用法3、理解逻辑比较的内容4、理解控制结构的内容5、了解错误处理的方法一、PL/SQL简介。1、PL/SQL的优点。PL/SQL（procedural language/sql，过程语言/sql）可用于创建存储过程、触发器、程序包。与oracle服务器和oracle工具紧密集成，具备可移植性、灵活性、安全性。支持SQL、支持面向对象编程（oop）等。2、PL/SQL块简介。PL/SQL是一种块结构的语言，它将一组语句放在一个块中。构成PL/SQL程序的基本单元是逻辑块，该逻辑块可以包含任何数量的嵌套子块，每个逻辑块

2、对应要解决的问题或子问题。在PL/SQL块中可以使用select、insert、update、delete等dml语句、事务控制语句、sql函数等。PL/SQL块中不允许使用create、drop、alter等ddl语句。但可以通过动态sql来执行他们。PL/SQL块分为三个部分。DeclareBeginException end;3、PL/SQL对大小写不敏感，代码中常用到的符号如下：:= 赋值操作符号| 连接操作符号- 单行注释/* */ 多行注释* 求幂操作4、声明变量声明变量必须指定变量的数据类型，可以在声明时进行初始化。一条语句只能定义一个变量。以下都是正确的变量定义。declare

3、vcode varchar2(5); /*定义变量*/vname varchar2(25) :=jack; /*定义变量并赋值jack*/rate number(7,2); /*定义数值型变量*/start_date date; /*定义日期型变量*/status Boolean; /*定义布尔变量*/beginend;5、给变量赋值status:=true;start_date:=03-07月-07;vcode:=”003”select 某个字段 into 某变量 from 某个表 where 字段名”值”；/*注意，上述用语句赋值必须确保查询结果返回值有且仅有一个。*/6、定义常量格式：变

4、量名 constant 数据类型 := 初始值；比如：declare total constant number :=2000; /*定义常量number并赋值*/beginend;7、属性类型（1）%type类型。比如：icode item.itemcode%type/*表示icode变量的类型与表item中itemcode字段类型一致*/（2）%rowtype类型比如：emp_rec emp%rowtype;/*表示emp_rec变量可存储从emp表中提取的一整条记录。*/使用属性类型的优点。（1）不需知道被引用的列或表的具体数据类型。（2）如果更改了被引用对象（如表）的数据库定义，那么变

5、量的数据类型也会随之更改。8、比较符关系比较符：>，>=，=，<=，<，< >逻辑比较符：not and or二、控制结构1、条件控制(1) if then语句格式if 条件 then 语句体；end if ；(2) if then else语句格式if 条件 then 语句体1；else 语句体2；end if ；(3) if then elsif语句if 条件1 then语句体1；elsif 条件2 then语句体2；else语句体3；end if ；(4) case 语句格式case 表达式 when 表达式或值 then 语句体1； when 表达式

6、或值 then 语句体2； ； when 表达式或值 then 语句体n； else 语句体；end case；比如：SQL> set serveroutput on； /*让结果在屏幕输出*/SQL>beginCase &grade /*符号”&”表示提示输入*/ When A then DBMS_OUTPUT.PUT_LINE(优异)； When B then DBMS_OUTPUT.PUT_LINE(良好)； When C then DBMS_OUTPUT.PUT_LINE(一般)；When D then DBMS_OUTPUT.PUT_LINE(较差)；El

7、se DBMS_OUTPUT.PUT_LINE(无此成绩)；End case； End;/*注意*/oracle中字符或字符串比较运算时是大小写敏感，其余不敏感。Case 语句的另外一种用法是，不用选择器，而直接计算when子句中的各个比较表达式，找到第一个为true的表达式，然后执行相应的语句序列。比如：SQL> set serveroutput on；SQL>declare grade varchar2(10) : = &成绩等级;beginCase When grade=A then DBMS_OUTPUT.PUT_LINE(优异)； When grade= B th

8、en DBMS_OUTPUT.PUT_LINE(良好)； When grade= C then DBMS_OUTPUT.PUT_LINE(一般)；When grade= D then DBMS_OUTPUT.PUT_LINE(较差)；Else DBMS_OUTPUT.PUT_LINE(无此成绩)；End case； End; 2、循环语句。(1) loopend loop循环这种循环必须在其循环体中包含一条exit语句，否则是无限循环。格式loop 语句体；end loop；比如：SQL> set serveroutput on；SQL>beginLoop If &mark

9、s > 60 thenDBMS_OUTPUT.PUT_LINE(该同学考试通过)；Exit； End if；End loop； End;(2) while循环这种循环是带有条件的循环，包含有loopend loop语句。格式while 条件loop 语句体；end loop；比如：SQL> set serveroutput on；SQL>declare Monthly_value number :=0； daily_value number :=0；begin while monthly_value<=4000 loopmonthly_value:=daily_valu

10、e*31;daily_value := daily_value + 10;dbms_output.put_line(每日销量：| daily_value); end loop;dbms_output.put_line(每月销量：| monthly_value); end;(3) for 循环在执行语句前，for循环中的循环次数是已知的。循环计数变量无需事先声明，可将循环计数器作为常量引用。在for循环语句序列中，不能给计数器变量赋值。格式for 计数器变量 in reverse 值1 . . 值2 loop语句体；end loop；/*注意*/ 值1必须小于值2；当需要对值从大到小执行循环时，

11、才使用reverse关键字。比如：SQL> set serveroutput onSQL> beginFor even_number in 1 . . 25 Loop Dbms_output.put_line(even_number*2) ; End loop;End; 二、动态sql1、动态SQL动态SQL是指在PL/SQL块编译时SQL语句是不确定的，例如根据用户输入参数的 而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态的创建语句、对语句进行语法分析并执行该语句。格式execute immediate 动态sql语句字符串into 定义的变量using

12、 参数比如：declare sql_stmt varchar(200);emp_id number(4):=7566emp_rec emp%rowtype;begin execute immediate create table bonus ( id number, amt number); /*创建表*/ sql_stmt := select * from emp where empno= :id ; /*查找记录*/ execute immediate sql_stmt into emp_rec using emp_id; /*使用参数执行sql语句*/end;/*注意*/ execute

13、 immediate语句 用于处理返回单行或没有返回的sql语句。要处理返回多行的动态sql请使用游标。2、错误处理在运行程序时出现的错误叫做异常。发生异常后，将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。预定义的异常是在运行时由系统自动引发的，而用户定义的异常必须使用RAISE语句显式引发。3、预定义异常PL/SQL程序违反Oracle规则或超越系统限制时，将隐式引发内部异常。三、预定义异常1、PL/SQL程序违反oracle规则或超越系统限制时，将隐式引发内部异常。Oracle把一些常见错误预定义为异常。每个异常都必须通过名字

14、来捕获。比如：将一个数除以零，就会引发预定义异常ZERO_DIVIDE下表列出了PL/SQL支持的预定义异常。用于为预定义异常编写异常处理程序。异常说明ACCESS_INTO_NULL在未初始化对象时出现CASE_NOT_FOUND在case语句中的选项与用户输入的数据不匹配时出现COLLECTION_IS_NULL在给尚未初始化的表或数据赋值时出现CURSOR_ALREADY_OPEN在用户试图打开已经打开的游标时出现。INVALID_CURSOR执行非法游标运算时出现TOO_MANY_ROWS在执行select into语句后返回多行时出现。ZERO_DIVIDE以零作除数时出现VALUE

15、_ERROR产生大小限制错误时出现。如：变量中的列值超出变量的大小。NO_DATA_FOUND在表中不存在请求的行时出现。异常处理程序的语法如下。Begin语句1；exceptionwhen 异常名 then 语句2；when others then 语句3；end;例如：SQL>set serveroutput onSQL>declare ordernum varchar2(5);BeginSelect orderno into ordernum from order_master;Exception when TOO_MANY_ROWS thenDbms_output.put_

16、line(返回多行);End; 上例中Select into语句只允许返回一行，由于没有where子句限制条件，该语句从表order_master中检索所有orderno的值，并存储到变量ordernum中，将引发异常。2、用户预定义异常。上面介绍了系统已经定义的异常，下面介绍用户自定义异常。定义异常的语法如下。Declare my_exception exception;My_exception是自定义异常的名字，可以是任何合法的标示符。用户自定义异常必须使用RAISE语句显示引发。显式引发异常的语法如下。Raise my_exception;比如：SQL>set serveroutp

17、ut onSQL>declare abc exception ;Category varchar2(10);BeginCategory := &category;If category not in (附件,顶盖,备件) then Raise abc;Else Dbms_output.put_line(输入的类别是|category);End if;Exception when abc then Dbms_output.put_line (无法识别该类别);作业1、PL/SQL块中不能直接使用的sql命令是_A.select B.insert C.update D.drop2、以

18、_数据类型存储的数据可用于执行计算。A.标量 B.数字 C.LOB D.属性类型3、_用于比较字符A.逻辑 B.数字布尔型 C.字符布尔型 D.日期布尔型4、_语句用于在执行语句前先计算选择器的值A.if then B.if then else C.for D.case5、以零作除数会引发_异常A.VALUE_ERROR B.ZERO_DIVIDE C.STORAGE_ERROR D.SELF_IS_NULL6、编写一个程序，用于接受用户输入的数字，将该数左右反转，然后显示反转后的数。7、编写一个程序，往一个表中查询数据，如果没有查到任何数据，将引发no_data_found异常，显式一则消息。