Oracle学习笔记之五,Oracle 11g的PL/SQL入门
1. PL/SQL概述
PL/SQL(Procedural Language/SQL)是Oracle的专用语言,是对标准SQL语言的扩展,它允许在其内部嵌套普通的SQL语句,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误。SQL语句的数据库操纵能力、数据查询能力和PL/SQL的过程处理能力结合在一起,可以实现比较复杂的业务逻辑。
1.1 PL/SQL块结构
PL/SQL程序都是以块(BLOCK)为单位,整个PL/SQL块分3部分:声明部分(DECLARE)、执行部分(BEGIN...END)和异常处理部分(EXCEPTION)。其中执行部分是必需的。
标准PL/SQL块的语法格式如下:
[DECLARE]
--声明部分,可选
BEGIN
--执行部分,必须
[EXCEPTION]
--异常处理部分,可选
END
1.2 代码注释和标识符
1).单行注释
单行注释由连个连接字符“--”开始,后面紧跟着注释内容。
2).多行注释
多行注释由/*开头,由*/结尾,这个大多数编程语言是相同的。
3).PL/SQL字符集
所有的PL/SQL程序元素(比如,关键字、变量名、常量名等)都是由一些字符序列组合而成的,而这些字符序列中的字符都必须取自PL/SQL语言所允许使用的字符集,那么这些合法的字符集主要包括以下内容:
- 大写和小写字母:A-Z或a-z。
- 数字:0-9。
- 非显示的字符:制表符、空格和回车。
- 数学符号:+,-,*,/,>,<,=等。
- 间隔符:包括(),{},[],?,!,;,:,@,#,%,$,&等。
2. 数据类型与定义变量、常量
2.1 基本数据类型
1).数值类型
数值类型主要包括NUMBER、PLS_INTEGER和BINARY_INTEGER三种基本类型
2).字符类型
字符类型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。这些类型的变量用来存储字符串或字符数据。
3).日期类型
日期类型只有一个种——即DATE类型,用来存储日期和时间信息,DATE类型的存储空间是7个字节,分别使用一个字节存储世纪、年、月、天、小时、分钟和秒。
4).布尔类型
布尔类型也只有一种——即BOOLEAN,主要用于程序的流程控制和业务逻辑判断,其变量值可以是TRUE、FALSE或NULL中的一种。
2.2 特殊数据类型
1).%TYPE类型
使用%TYPE关键字可以声明一个与指定列名称相同的数据类型,它通常紧跟在指定列名的后面。示范:
var_address BBSDETAIL.ADDRESS%type; --定义变量
2).RECORD类型,语法如下:
type record_type is record ( var_member1 data_type [not null] [:=default_value], … var_membern data_type [not null] [:=default_value]) );
3).%ROWTYPE类型,语法如下:
rowVar_name table_name%rowtype;
2.3 定义变量和常量
1).定义变量
<变量名> <数据类型> [(长度):=<初始值>];
2).定义常量
<常量名> constant <数据类型>:=<常量值>;
本章节代码示范:
declare --定义一个变长字符串 var_title varchar2(200); --定义一个数值 var_browse_count number(6); --定义一个小数,并给一个初始值 var_number_of_coins number(6,2):=3.2; --定义一个日期类型的数据 var_publish_time date; --定义一个布尔变量,不能为空,初始值为false var_isvalid boolean not null default false; --定义一个%type类型的变量 var_address BBSDETAIL.ADDRESS%type; --定义一个record类型 type bbsdetail_type is record ( title BBSDETAIL.TITLE%type, browse_count BBSDETAIL.BROWSE_COUNT%type, publish_time BBSDETAIL.PUBLISH_TIME%type ); --定义一个record变量 bbsdetail_record bbsdetail_type; --定义一个rowtype bbs_detail_row BBSDETAIL%rowtype; begin select TITLE,BROWSE_COUNT,NUMBER_OF_COINS,PUBLISH_TIME,ADDRESS into var_title,var_browse_count,var_number_of_coins,var_publish_time,var_address from BBSDETAIL where DETAIL_ID=91486; dbms_output.put_line('标题:'||var_title||' 浏览次数:'||var_browse_count); dbms_output.put_line('币数:'||var_number_of_coins||' 发布时间:'||var_publish_time); dbms_output.put_line('地址:'||var_address); var_isvalid := var_browse_count>10; if var_isvalid then dbms_output.put_line('有效记录!'); end if; select TITLE,BROWSE_COUNT,PUBLISH_TIME into bbsdetail_record.title,bbsdetail_record.browse_count,bbsdetail_record.publish_time from BBSDETAIL where DETAIL_ID=91486; dbms_output.put_line('标题:'||bbsdetail_record.title); dbms_output.put_line('浏览次数:'||bbsdetail_record.browse_count); dbms_output.put_line('发布时间:'||bbsdetail_record.publish_time); select * into bbs_detail_row from BBSDETAIL where DETAIL_ID=91486; dbms_output.put_line('标题:'||bbs_detail_row.title||' 地址:'||bbs_detail_row.address); end; --------------------输出------------- --标题:上海某店纪实 浏览次数:499 --币数:8 发布时间:03-9月 -11 --地址:罗秀路 --有效记录! --标题:上海某店纪实 --浏览次数:499 --发布时间:03-9月 -11 --标题:上海某店纪实 地址:罗秀路
3. 流程控制语句
3.1 选择语句
- if...then语句
if <condition_expression> then plsql_sentence end if;
- if..then..else语句
if <condition_expression> then plsql_sentence1 else plsql_sentence2 end if;
- if...then...elseif语句
if <condition_expression1> then plsql_sentence1 elseif <condition_expression2> then plsql_sentence2 ... end if;
- case语句,执行方式与if...then...elseif语句十分相似
case <selector> when <expression1> then plsql_sentence1; when <expression2> then plsql_sentence2; ... when <expressionn> then plsql_sentencen; [else plsql_sentence;] end case;
3.2 循环语句
- loop语句
loop plsql_sentence; exit when end_condition_ exp end loop;
- while语句
while condition_expression loop plsql_sentence; end loop;
- for语句
for variable_ counter_name in [reverse] lower_limit..upper_limit loop plsql_sentence; end loop;
注意,当语句中使用reverse关键字时,计数器的值会随循环递减。
本章节代码示范:
declare --定义变量 var_browse_count BBSDETAIL.BROWSE_COUNT%type; begin --查询 select BROWSE_COUNT into var_browse_count from BBSDETAIL where DETAIL_ID=91486; --判断 if var_browse_count>1000 then dbms_output.put_line('很了不起啊!'); elsif var_browse_count>100 then dbms_output.put_line('不错了!'); else dbms_output.put_line('有待努力!'); end if; --这儿的查询结果是499 dbms_output.put_line('查询出来的var_browse_count='||var_browse_count); --WHILE循环,结束后var_browse_count=100 while var_browse_count>100 loop insert into MYTABLE values(var_browse_count); var_browse_count := var_browse_count-1; end loop; dbms_output.put_line('WHILE结束后var_browse_count='||var_browse_count); --LOOP循环,结束后var_browse_count=10 loop insert into MYTABLE values(var_browse_count); var_browse_count := var_browse_count-1; exit when var_browse_count = 10; end loop; dbms_output.put_line('LOOP循环结束后var_browse_count='||var_browse_count); --FOR循环,递减,结束后var_browse_count=10(不变) for i in reverse 1..var_browse_count loop insert into MYTABLE values(var_browse_count); end loop; dbms_output.put_line('FOR递减循环结束后var_browse_count='||var_browse_count); end; --------------------输出------------- --不错了! --查询出来的var_browse_count=499 --WHILE结束后var_browse_count=100 --LOOP循环结束后var_browse_count=10 --FOR递减循环结束后var_browse_count=10
4. PL/SQL游标
4.1 显式游标
显式游标是由用户声明和操作的一种游标,通常用于操作查询结果集(即由SELECT语句返回的查询结果),它处理数据的步骤包括:声明游标、打开游标、读取游标和关闭游标4个步骤。
- 声明游标
cursor cur_name[(input_parameter1[,input_parameter2]...)] [return ret_type] is select_sentence;
- 打开游标
open cur_name[(para_value1[,para_value2]...)]
- 读取游标
fetch cur_name into {variable};
- 关闭游标
close cur_name
4.2 游标的属性
无论是显式游标还是隐式游标,都具有%found、%notfound、%isopen和%rowcount这4个属性。
4.3 隐式游标
在执行一个SQL语句时,Oracle会自动创建一个隐式游标。当使用隐式游标的属性时,需要在属性前面加上隐式游标 的默认名称:SQL。
4.4 PL/SQL异常处理
根据异常产生的机制和原理,可将Oracle系统异常分为以下两大类:预定义异常、自定义异常。
系统预定义异常 |
说明 |
ZERO_DIVIDE |
除数为零时引发的异常 |
ACCESS_INTO_NULL |
企图为某个未初始化对象的属性赋值 |
COLLECTION_IS_NULL |
企图使用未初始化的集合元素 |
CURSOR_ALREADY_OPEN |
企图再次打开一个已经打开过的游标,但在重新打开之前,游标未关闭 |
INVALID_CURSOR |
执行一个非法的游标操作,例如,关闭一个未打开的游标 |
INVALID_NUMBER |
企图将一个字符串转换成一个无效的数字而失败 |
LOGIN_DENIED |
企图使用无效的用户名或密码连接数据库 |
NO_DATA_FOUND |
SELECT INTO语句没有返回数据 |
ROWTYPE_MISMATCH |
主游标变量与PL/SQL游标变量的返回类型不兼容 |
SELF_IS_NULL |
使用对象类型时,使用空对象调用其方法 |
注意:Oracle的异常封装体制类似于Java的异常封装体制:如果一个异常被捕捉并处理,那么此异常不再向外层抛出。这样可以设计程序在处理已知异常时,处理完异常继续程序的处理,而不是中止程序。
本章节代码示范一:
declare --定义一个record类型 type bbsdetail_type is record ( title BBSDETAIL.TITLE%type, address BBSDETAIL.ADDRESS%type, browse_count BBSDETAIL.BROWSE_COUNT%type, publish_time BBSDETAIL.PUBLISH_TIME%type ); --定义一个record变量 bbsdetail_record bbsdetail_type; --声明游标,顺便指定一默认值 cursor cursor_bbsdetail(var_district_id in number:=1001) is select TITLE,ADDRESS,BROWSE_COUNT,PUBLISH_TIME from BBSDETAIL where DISTRICT_ID=var_district_id order by PUBLISH_TIME desc; begin --打开游标(用默认值打开) open cursor_bbsdetail(); --遍历游标 fetch cursor_bbsdetail into bbsdetail_record; while cursor_bbsdetail%found loop --处理 dbms_output.put_line('标题:'||bbsdetail_record.title); dbms_output.put_line('地址:'||bbsdetail_record.address); dbms_output.put_line('浏览次数:'||bbsdetail_record.browse_count); dbms_output.put_line('发布时间:'||bbsdetail_record.browse_count); --游标指针指向下一条记录 fetch cursor_bbsdetail into bbsdetail_record; end loop; --关闭游标 close cursor_bbsdetail; ----------------------------- --遍历游标的另一种更为简便的方式(FOR语句循环游标,不用打开游标、读取游标、关闭游标) for bbsdetail_record in cursor_bbsdetail(1012) loop dbms_output.put_line('标题:'||bbsdetail_record.title); dbms_output.put_line('地址:'||bbsdetail_record.address); --其它处理 end loop; ------------------------------ --隐式游标属性 update BBSDETAIL set TITLE='新标题' where DETAIL_ID=123456789; if sql%notfound then dbms_output.put_line('更新不成功!不存在DETAIL_ID=123456789'); else dbms_output.put_line('更新成功!'); end if; end; /
本章节代码示范二:
declare var_title varchar2(200); begin --下面两条SQL语句只能选择一条执行 -- select TITLE into var_title from BBSDETAIL where DETAIL_ID=12345679; select TITLE into var_title from BBSDETAIL where DISTRICT_ID=1001; exception when no_data_found then dbms_output.put_line('不存在该记录!'); when too_many_rows then dbms_output.put_line('返回多条记录!'); end;