/*块结构的特点
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
标识符:
不能超过30个字符
第一个字符必须为字母
其余字符可以是字母,数字,$,_,或#
不区分大小写形式
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式
无sql保留字
数据类型
数字型:
整数,实数,以及指数
字符串:
用单引号括起来
若在字符串表示单引号,则使用两个单引号
字符串长度为零(两个单引号之间没有字符),则表示null
字符:
长度为1的字符串
数据定义
语法
标识符[常数] 数据类型[not null][:=pl/sql表达式];
':='表示给变量赋值
数据类型包括
数字型 number(7,2)
字符型 char(120)
日期型 date
布尔型 boolean(取值为true,false或null,不存贮在数据库中)
日期型
anniversary date:='05-jul-95';
project_completion date;
布尔型
over_budget boolean not null:=false;
available boolean;
(初始值为null)
%type类型匹配
books_printed number(6);
books_sold book_printed%type;
manager_name emp.ename%type;
变量赋值
变量名:=pl/sql表达式
numvar:=5;
boolvar:=true;
datevar:='11-jun-87';
字符型、数字型表达式中的空值
null+<数字>=null(空值加数字仍是空值)
null><数字>=null(空值与数字进行比较,结果仍是空值)
null||'字符串'='字符串'(null即'')
(空值与字符串进行连接运算,结果为原字符串)
变量作用范围
标识符在宣言它的块中有效
标识符如果不在子块中重新定义,则在pl/sql块的所有子块中同样有效
重新定义后的标识符,作用范围仅在本子块中有效 */
declare
e_mess char(80);
begin
/*子块1*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='president';
exception
when too_many_rows then
insert into job_errors
values('more than one president');
end;
/*子块2*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='manager';
exception
when too_many_rows then
insert into job_errors
values('more than one manager');
end;
exception
when others then
e_mess:=substr(sqlerrm,1,80);
insert into general errors values(e_mess);
end;
插入
declare
my_sal number(7,2):=3040.55;
my_ename char(25):='wanda';
my_hiredate date:='08-sep-88';
begin
insert into emp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,'cab driver',my_hiredate,my_sal,20);
end;
删除
declare
bad_child_type char(20):='naughty';
begin
delete from santas_gift_list where
kid_rating=bad_child_type;
end;
/*
事务处理
commit[work];
rollback[work]; (关键字work可选,但对命令执行无任何影响)
savepoint 标记名;(保存当前点)
在事务中标记当前点
rollback [work] to [savepoint] 标记名;(回退到当前保存点)
取消savepoint命令之后的所有对数据库的修改
关键字work和savepoint为可选项,对命令执行无任何影响
函数
pl/sql块中可以使用sql命令的所有函数
insert into phonebook(lastname) value(upper(my_lastname));
select avg(sal) into avg_sal from emp;
对于非sql命令,可使用大多数个体函数
不能使用聚组函数和参数个数不定的函数,如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
赋值时的数据类型转换
4种赋值形式:
变量名:=表达式
insert into 基表名 values(表达式1,表达式2,...);
update 基表名 set 列名=表达式;
select 列名 into 变量名 from ...;
数据类型间能进行转换的有:
char转成number
number转成char
char转成date
date转成char
例
char_var:=nm_var;
数字型转换成字符型
date_var:='25-dec-88';
字符型转换成日期型
insert into 表名(num_col) values('604badnumber');
错误,无法成功地转换数据类型
*/
条件控制
declare
num_jobs number(4);
begin
select count(*) into num_jobs from auditions
where actorid=&&actor_id and called_back='yes';
if num_jobs>100 then
update actor set actor_rating='word class'
where actorid=&&actor_id;
elsif num_job=75 then
update actor set actor_rating='daytime soaps'
where actorid=&&actor_id;
else
update actor set actor_rating='waiter'
where actorid=&&actor_id;
end if;
end if;
commit;
end;
循环
loop
......
end loop;
exit;(退出循环)
exit [when];(退出循环,当满足when时)
declare
ctr number(3):=0;
begin
loop
insert into table1 values('tastes great');
insert into table2 values('less filling');
ctr:=ctr+1;
exit when ctr=100;
end loop;
end;
--(注:如果ctr取为null,循环无法结束)
for 变量<范围> loop
......
end loop;
declare
my_index char(20):='fettucini alfredo';
bowl char(20);
begin
for my_index in reverse 21..30 loop
insert into temp(coll) values(my_index);
/*循环次数从30到21*/
end loop;
bowl:=my_index;
end;
--跟在in reverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式
游标
--显式游标
打开游标
open <游标名>
例
open color_cur;
游标属性
%notfound
%found
%rowcount
%isopen
例
fetch my_cur into my_var;
while my_cur %found loop
(处理数据)
fetch my_cur into my_var;
exit when my_cur %rowcount=10;
end loop;
%notfound属性
取值情况如下:
fetch操作没有返回记录,则取值为true
fetch操作返回一条记录,则取值为false
对游标无fetch操作时,取值为null
<游标名> %notfound
例
if color_cur %notfound then...
注:如果没有fetch操作,则<游标名> %notfound将导致出错,
因为%notfound的初始值为null。
关闭游标
close <游标名>
例
close color_cur;
游标的for循环
语法
for <记录名> in <游标名> loop
<一组命令>
end loop;
其中:
索引是建立在每条记录的值之上的
记录名不必声明
每个值对应的是记录名,列名
初始化游标指打开游标
活动集合中的记录自动完成fetch操作
退出循环,关闭游标
隐式游标
隐式游标是指sql命令中用到的,没有明确定义的游标
insert,update,delete,select语句中不必明确定义游标
调用格式为sql%
存贮有关最新一条sql命令的处理信息
隐式游标的属性
隐式游标有四个属性
sql%notfound
sql%found
sql%rowcount:隐式游标包括的记录数 delete from baseball_team where batting_avg<100;
if sql%rowcount>5 thn
insert into temp
values('your team needs help');
end if;
--sql%isopen:取值总为false。sql命令执行完毕,pl/sql立即关闭隐式游标。
标号
goto语句
用法:
goto you_are_here;
其中you_are_here是要跳转的语句标号
标号必须在同一组命令,或是同一块中使用
正确的使用
<>(标号)
x:=x+1
if a>b then
b:=b+c;
goto dinner;
end if;
错误的使用
goto jail;
if a>b then
b:=b+c;
<>(标号)
x:=x+1;
end if;
标号:解决意义模糊
标号可用于定义列值的变量
<>
declare
deptno number:=20;
begin
update emp set sal=sal*1.1
where deptno=sample.deptno;
commit;
end sample;
如果不用标号和标号限制符,这条命令将修改每条记录。
异常处理
预定义的异常情况
任何oracle错误都将自动产生一个异常信息
一些异常情况已命名,如:
no_data_found 当select语句无返回记录时产生
too_many_rows 没有定义游标,而select语句返回多条记录时产生
whenever notfound 无对应的记录
用户定义的异常情况
由用户自己获取
在declare部分定义:
declare
x number;
something_isnt_right exception;
用户定义的异常情况遵循一般的作用范围规则
条件满足时,获取异常情况:raise something_isnt_right
注意:同样可以获取预定义的异常情况
exception_init语句
允许为oracle错误命名
调用格式:
pragma exception_init(<表达式>,);
例
declare
deadlock_detected exception;
pragma exception_init(deadlock_detected,-60);
raise语句
单独使用raise命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。
在异常处理中,此语句只能单独使用。
异常处理标识符
一组用于处理异常情况的语句:
exception
when <表达式> or [表达式...] then
<一组语句>
...
when others then--最后一个处理
<一组语句>
end;既结束pl/sql块部分,也结束异常处理部分