sql语句的编程手册(3)
2 PL/SQL的块结构和数据类型
块结构的特点
嵌套
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;
---------
22 SQL和PL/SQL
插入
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');
错误，无法成功地转换数据类型
---------
4 条件控制
例
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;
--------
5 循环
语法
loop
......
end loop;
exit;（退出循环）
exit [when];（退出循环，当满足WHEN时）
例1
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，循环无法结束）
例2
FOR语法
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后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式
----------
6 游标
显式游标
打开游标
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立即关闭隐式游标。
---------
7 标号
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;
如果不用标号和标号限制符，这条命令将修改每条记录。
----------
8 异常处理
预定义的异常情况
任何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块部分，也结束异常处理部分
--------
练习与答案
1:
接收contract_no和item_no值，在inventory表中查找，如果产品：
已发货，在arrival_date中赋值为今天后的7天
已订货，在arrival_date中赋值为今天后的一个月
既无订货又无发货，则在arrival_date中赋值为今天后的两个月，
并在order表中增加一条新的订单记录。
product_status的列值为'shipped'和'ordered'
inventory:
product_id number(6)
product_description char(30)
product_status char(20)
std_shipping_qty number(3)
contract_item:
contract_no number(12)
item_no number(6)
arrival_date date
order:
order_id number(6)
product_id number(6)
qty number(3)
答案：
declare
i_product_id inventory.product_id%type;
i_product_description inventory.product_description%type;
i_product_status inventory.product_status%type;
i_std_shipping_qty inventory.std_shipping_qty%type;
begin
select product_id,product_description,product_status,std_shipping_qty
into i_product_id,i_product_description,
i_product_status,i_std_shipping_qty
from inventory
where product_id=(
select product_id
from contract_item
where contract_no=&&contractno and item_no=&&itemno);
if i_product_status='shipped' then
update contract_item
set arrival_date=sysdate+7
where item_no=&&itemno and contract_no=&&contractno;
elsif i_product_status='ordered' then
update contract_item
set arrival_date=add_months(sysdate,1)
where item_no=&&itemno and contract_no=&&contractno;
else
update contract_item
set arrival_date=add_months(sysdate,2)
where item_no=&&itemno and contract_no=&&contractno;
insert into orders
values(100,i_product_id,i_std_shipping_qty);
end if;
end if;
commit;
end;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
22.把雇员姓名及工资存入prnttable表中，基结构为：
create table prnttable
(seq number(7),line char(80));
4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。
答案：
declare
cursor emp_cur is
select ename,sal,comm
from emp where deptno=&dno;
emp_rec emp_cur%rowtype;
null_commission exception;
begin
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is null then
begin
close emp_cur;
raise null_commission;
end;
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_sur;
exception
when null_commission then
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is not null then
insert into temp values(emp_rec.sal,emp_rec.ename);
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_cur;
commit;
end;
块结构的特点
嵌套
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;
---------
22 SQL和PL/SQL
插入
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');
错误，无法成功地转换数据类型
---------
4 条件控制
例
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;
--------
5 循环
语法
loop
......
end loop;
exit;（退出循环）
exit [when];（退出循环，当满足WHEN时）
例1
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，循环无法结束）
例2
FOR语法
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后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式
----------
6 游标
显式游标
打开游标
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立即关闭隐式游标。
---------
7 标号
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;
如果不用标号和标号限制符，这条命令将修改每条记录。
----------
8 异常处理
预定义的异常情况
任何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块部分，也结束异常处理部分
--------
练习与答案
1:
接收contract_no和item_no值，在inventory表中查找，如果产品：
已发货，在arrival_date中赋值为今天后的7天
已订货，在arrival_date中赋值为今天后的一个月
既无订货又无发货，则在arrival_date中赋值为今天后的两个月，
并在order表中增加一条新的订单记录。
product_status的列值为'shipped'和'ordered'
inventory:
product_id number(6)
product_description char(30)
product_status char(20)
std_shipping_qty number(3)
contract_item:
contract_no number(12)
item_no number(6)
arrival_date date
order:
order_id number(6)
product_id number(6)
qty number(3)
答案：
declare
i_product_id inventory.product_id%type;
i_product_description inventory.product_description%type;
i_product_status inventory.product_status%type;
i_std_shipping_qty inventory.std_shipping_qty%type;
begin
select product_id,product_description,product_status,std_shipping_qty
into i_product_id,i_product_description,
i_product_status,i_std_shipping_qty
from inventory
where product_id=(
select product_id
from contract_item
where contract_no=&&contractno and item_no=&&itemno);
if i_product_status='shipped' then
update contract_item
set arrival_date=sysdate+7
where item_no=&&itemno and contract_no=&&contractno;
elsif i_product_status='ordered' then
update contract_item
set arrival_date=add_months(sysdate,1)
where item_no=&&itemno and contract_no=&&contractno;
else
update contract_item
set arrival_date=add_months(sysdate,2)
where item_no=&&itemno and contract_no=&&contractno;
insert into orders
values(100,i_product_id,i_std_shipping_qty);
end if;
end if;
commit;
end;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
22.把雇员姓名及工资存入prnttable表中，基结构为：
create table prnttable
(seq number(7),line char(80));
4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。
答案：
declare
cursor emp_cur is
select ename,sal,comm
from emp where deptno=&dno;
emp_rec emp_cur%rowtype;
null_commission exception;
begin
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is null then
begin
close emp_cur;
raise null_commission;
end;
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_sur;
exception
when null_commission then
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is not null then
insert into temp values(emp_rec.sal,emp_rec.ename);
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_cur;
commit;
end;
