程序环境中的 SQL语句
在程序中使用 SQL
?SQL语言的程序使用方式就是嵌入
某种高级语言程序中 （ 例如 C）,
该语言称为宿主语言 。 在这样的程
序中, 由 SQL语句实现对数据库的
访问, 而宿主语言的语句则完成对
数据的各种处理功能, 还可担当与
用户的交互 。 很多数据库应用程序
就是采用此种编程方式实现的 。
包含 SQL的 宿主语言程序
主语言＋嵌入的 SQL 语句
SQL 库
预处理程序
主语言＋函数调用
主语言编译程序
主语言程序
SQL与宿主语言
?信息沟通 －用能被 SQL语句使用的主
语言变量来传递信息 。 这些共享的变
量在主语言中使用时同其他变量一样,
但在 SQL语句中引用时, 要在变量名
前加上冒号 。
?语法形式 －当在主语言中书写 SQL代
码时, 为了便于预处理程序识别它,
我们要在 SQL语句前加上 EXEC SQL
关键字 。
使用无返回值的 SQL语句
void getdep()
{ EXEC SQL BEGIN DECLARE SECTION ;
char depno[3],depname[10] ;
char deptel[4] ;
EXEC SQL END DECLARE SECTION ;
/* 程序读入 depno(系号 ),depname(系名 )
和 deptel(电话 ） 的值 */
EXEC SQL INSERT INTO dep
VALUES(:depno,:depname,:deptel) ;
}
返回单行的 select语句
void printage()
{ EXEC SQL BEGIN DECLARE SECTION ;
int eage;
floatesal;
char empname[6] ;
char SQLSTATE[6] ;
EXEC SQL END DECLARE SECTION ;
/* 程序读入要查询的职工姓名 (读入值
放到变量 empname中 )*/
EXEC SQL SELECT age,sal INTO,eage,:esal
FROM emp WHERE ename=:empname;
IF SQLSTATE == '00000' …
使用 select返回结果的更一般方法－光标
① 定义光标
EXEC SQL DECLARE <光标名 > CURSOR FOR
<查询语句 >
该语句属说明语句, 其核心是定义了一个光标标识
名和一个查询语句 。
② 打开光标
EXEC SQL OPEN <光标名 >
该语句实际上是执行了光标定义中的查询语句, 查
询结果 （ 结果关系 ） 存放在光标区中 。 该语句为
访问光标区中的第一个元组做好了准备 。
使用 select返回结果的更一般方法－光标
③ 读光标区中的当前元组
EXEC SQL FETCH FROM <光标名 > INTO <变量
列表 >
该语句读取光标区中下一个元组的值，并将它的
各分量依次赋值给指定的共享变量。
如果光标区的元组已经读完，那么系统变量
SQLSTATE的值被设为‘ 02000’，意为,no
tuple found”。
④ 关闭光标
EXEC SQL CLOSE <光标名 >
光标应用例－返回多行的 select
?例,上级部门为职工普调工资, 调资的办法是：从
最低工资调起, 每人长 10％, 但工资总额不能超
过 50万元 。 程序代码如下：
void addsalary()
{ EXEC SQL BEGIN DECLARE SECTION;
char empno[3],SQLSTATE[6];
float s_sal,e_sal ;
EXEC SQL END DECLARE SECTION ;
EXEC SQL DECLARE c1 CURSOR FOR
SELECT eno,sal FROM emp ORDER BY sal ASC ;
光标应用例－返回多行的 select
EXEC SQL OPEN c1 ;
EXEC SQL SELECT SUM(sal) into,s_sal from emp ;
while (s_sal < 500000.00) {
EXEC SQL FETCH FROM c1 INTO,e_sno,:e_sal ;
if(SQLSTATE=='02000') BREAK ; /*读完职工记录，
退出循环 */
EXEC SQL UPDATE emp SET sal=sal*1.1
WHERE eno=:e_eno;
s_sal = s_sal + e_sal * 0.1 ; };
EXEC SQL CLOSE c1 ;
}
PL/SQL程序设计
什么是 PL/SQL
?对 SQL的扩充
?过程化
?后台技术
PL/SQL 程序结构
Declare
begin
exception
end
/
变量类型
v_ename char(10) ;
married boolean,=true ;
v_ename emp.ename %TYPE ;
emp_rec emp % ROWTYPE ;
变量赋值
an_sal,=mon_sal *12 + nvl(comm,0) ;
Select ename INTO v_ename from emp
where eno=` e01 ` ;
程序控制结构
IF e1 THEN
ELSIF e2 THEN
ELSIF e3 THEN
ELSE
END IF;
WHILE e
LOOP
END LOOP ;
FOR i IN 1..10
LOOP
END LOOP ;
EXIT [WHEN e] ;
例 外
EXCEPTION
WHEN 例外名 1 THEN
…
WHEN 例外名 2 THEN
… …
WHEN OTHERS THEN
… …
END ；
系统预定义例外
? dup_val_on_index
? no_data_found
? too_many_rows
select into
用户自定义例外
?例外名定义
ex1 exception ;
?引起例外
raise ex1
例 一
检查用户注册
tin,
tout,
userlog,
ud ps
msg
userid passwd,.,
例 一
DECLARE
tin_rec tin % rowtype ;
v_passwd userlog.passwd % type ;
errps EXCEPTION ;
BEGIN
select * into tin_rec from tin ;
select passwd into v_passwd from userlog
where userid = tin_rec.ud ;
例 一
if tin_rec.ps = v_passwd then
insert into tout values(‘ login ok’ );
else
raise errps ;
end if ;
exception
when errps then
insert into tout values(‘ password error’〕 ；
when no_data_found then
insert into tout values(‘ userid error’〕 ；
end;
光 标
?Oracle 使用 Private SQL Area的
工作区执行 SQL语句，并保存处理
结果。
?光标是一个 PL/SQL结构，可命名这
些工作区，并通过光标访问工作区
里的信息。
隐式光标
隐式光标名,SQL
访问：
SQL％ ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
显式光标
?定义
delcare
cursor c1 is select …
?打开 open c1
?读 fetch c1 into 变量 /记录
?close c1
显式光标
读状态信息
c1%found
c1%rowcound
用光标定义记录
temp c1%rowtype
例 二
问题：
为职工长工资。从最低工资长，每人长 10 ％，
工资总额限制在 50万元。
Declare
cursor c1 is select eno,sal from emp
order by sal for update;
emp_num number,=0 ;
s_sal number ;
e_rec c1%rowtype ;
例 二
Begin
open c1;
select sum(sal) into s_sal from emp ;
while s_sal<500000
loop
fetch c1 into e_rec ;
exit when c1%notfound ;
update emp set sal=sal*1.1
where eno=e_rec.eno ;
s_sal:=s_sal + e_rec.sal*0.1;
emp_num:=emp_num+1 ;
end loop ;
例 二
Close c1;
insert into msg values(emp_num,s_sal) ;
commit ;
end;
光标 for循环
For r in c1
loop
r.sal （ 循环变量永远是记录型）
end loop;
带参数光标
cursor c1 (depno char(3)) is
select * from emp
where dno=depno ;
open c1(v_depno) ;
表 clog
例 三
练习要点：
光标 FOR循环，带参数的光标
问题：
收发室收文件。文件分类，每类中文件依收到日期先后顺
序编号。值班人员收到文件后，要在系统中登录文件类别，
编号，收到日期。
（注,一天中收到的文件可能不止一份）
编一 PL/SQL 程序，查询每一类中最后一天收到的文件）
Kno 类别 pno编号 day收发日期
例 三
Declare
cursor c1 is select distinct kno from clog;
cursor c2 (kindno char(3)) is
select * from clog where kno=kindno
order by day desc ;
i number ;
l_d date ;
begin
例 三
For r1 in c1
loop
I,= 0 ;
for r2 in c2(r1.kno)
loop
I,= I + 1 ;
if I=1 then
insert into tout(r2.kno,r2.pno,r2.day);
l_d:=r2.day;
elsif r2.day:=l_d then
insert into tout(r2.kno,r2.pno,r2.day);
else exit;
end if ;
end loop;
end loop ;
建立数据库对象－触发器
Create trigger emp_check
before
after insert or update or delete
instead of
on emp
begin
if to_char(sysdate,’dy’)=‘星期日’ then
raise_application_error(-20000,`error `) ;
end if ;
end ;
建立数据库对象－触发器
SQL> create or replace trigger temp_t
2 before update on temp
3 begin
4 if to_char(sysdate,'dy')='星期二 ' then
5 raise_application_error(-20000,
‘今天不能修改 ') ;
6 end if ;
7 end ;
SQL> /
触发器已创建
建立数据库对象－触发器
SQL> update temp set a=2;
update temp set a=2
*
ERROR 位于第 1 行,
ORA-20000,今天不能修改
ORA-06512,在 "WXY.TEMP_T",line 3
ORA-04088,触发器 'WXY.TEMP_T' 执行
过程中出错
Create trigger emp_t
before update of sal
on emp
for each row
when (old.dno is not null)
begin
if (:new.sal < =,old.sal) then
raise_application_error(… );
end if ;
end ;
建立数据库对象－触发器
建立存储过程
Create procedure raise_salary
(emp_id integer,increase real)
is
begin
update emp set sal=sal+increase
where eno=emp_id ;
end ;
存储过程调用
?在 SQL*Plus中
execute raise_salary(12,46.50)
?在 PL*SQL中
begin
raise_salary(12,46.50)；
end ;
在程序中使用 SQL
?SQL语言的程序使用方式就是嵌入
某种高级语言程序中 （ 例如 C）,
该语言称为宿主语言 。 在这样的程
序中, 由 SQL语句实现对数据库的
访问, 而宿主语言的语句则完成对
数据的各种处理功能, 还可担当与
用户的交互 。 很多数据库应用程序
就是采用此种编程方式实现的 。
包含 SQL的 宿主语言程序
主语言＋嵌入的 SQL 语句
SQL 库
预处理程序
主语言＋函数调用
主语言编译程序
主语言程序
SQL与宿主语言
?信息沟通 －用能被 SQL语句使用的主
语言变量来传递信息 。 这些共享的变
量在主语言中使用时同其他变量一样,
但在 SQL语句中引用时, 要在变量名
前加上冒号 。
?语法形式 －当在主语言中书写 SQL代
码时, 为了便于预处理程序识别它,
我们要在 SQL语句前加上 EXEC SQL
关键字 。
使用无返回值的 SQL语句
void getdep()
{ EXEC SQL BEGIN DECLARE SECTION ;
char depno[3],depname[10] ;
char deptel[4] ;
EXEC SQL END DECLARE SECTION ;
/* 程序读入 depno(系号 ),depname(系名 )
和 deptel(电话 ） 的值 */
EXEC SQL INSERT INTO dep
VALUES(:depno,:depname,:deptel) ;
}
返回单行的 select语句
void printage()
{ EXEC SQL BEGIN DECLARE SECTION ;
int eage;
floatesal;
char empname[6] ;
char SQLSTATE[6] ;
EXEC SQL END DECLARE SECTION ;
/* 程序读入要查询的职工姓名 (读入值
放到变量 empname中 )*/
EXEC SQL SELECT age,sal INTO,eage,:esal
FROM emp WHERE ename=:empname;
IF SQLSTATE == '00000' …
使用 select返回结果的更一般方法－光标
① 定义光标
EXEC SQL DECLARE <光标名 > CURSOR FOR
<查询语句 >
该语句属说明语句, 其核心是定义了一个光标标识
名和一个查询语句 。
② 打开光标
EXEC SQL OPEN <光标名 >
该语句实际上是执行了光标定义中的查询语句, 查
询结果 （ 结果关系 ） 存放在光标区中 。 该语句为
访问光标区中的第一个元组做好了准备 。
使用 select返回结果的更一般方法－光标
③ 读光标区中的当前元组
EXEC SQL FETCH FROM <光标名 > INTO <变量
列表 >
该语句读取光标区中下一个元组的值，并将它的
各分量依次赋值给指定的共享变量。
如果光标区的元组已经读完，那么系统变量
SQLSTATE的值被设为‘ 02000’，意为,no
tuple found”。
④ 关闭光标
EXEC SQL CLOSE <光标名 >
光标应用例－返回多行的 select
?例,上级部门为职工普调工资, 调资的办法是：从
最低工资调起, 每人长 10％, 但工资总额不能超
过 50万元 。 程序代码如下：
void addsalary()
{ EXEC SQL BEGIN DECLARE SECTION;
char empno[3],SQLSTATE[6];
float s_sal,e_sal ;
EXEC SQL END DECLARE SECTION ;
EXEC SQL DECLARE c1 CURSOR FOR
SELECT eno,sal FROM emp ORDER BY sal ASC ;
光标应用例－返回多行的 select
EXEC SQL OPEN c1 ;
EXEC SQL SELECT SUM(sal) into,s_sal from emp ;
while (s_sal < 500000.00) {
EXEC SQL FETCH FROM c1 INTO,e_sno,:e_sal ;
if(SQLSTATE=='02000') BREAK ; /*读完职工记录，
退出循环 */
EXEC SQL UPDATE emp SET sal=sal*1.1
WHERE eno=:e_eno;
s_sal = s_sal + e_sal * 0.1 ; };
EXEC SQL CLOSE c1 ;
}
PL/SQL程序设计
什么是 PL/SQL
?对 SQL的扩充
?过程化
?后台技术
PL/SQL 程序结构
Declare
begin
exception
end
/
变量类型
v_ename char(10) ;
married boolean,=true ;
v_ename emp.ename %TYPE ;
emp_rec emp % ROWTYPE ;
变量赋值
an_sal,=mon_sal *12 + nvl(comm,0) ;
Select ename INTO v_ename from emp
where eno=` e01 ` ;
程序控制结构
IF e1 THEN
ELSIF e2 THEN
ELSIF e3 THEN
ELSE
END IF;
WHILE e
LOOP
END LOOP ;
FOR i IN 1..10
LOOP
END LOOP ;
EXIT [WHEN e] ;
例 外
EXCEPTION
WHEN 例外名 1 THEN
…
WHEN 例外名 2 THEN
… …
WHEN OTHERS THEN
… …
END ；
系统预定义例外
? dup_val_on_index
? no_data_found
? too_many_rows
select into
用户自定义例外
?例外名定义
ex1 exception ;
?引起例外
raise ex1
例 一
检查用户注册
tin,
tout,
userlog,
ud ps
msg
userid passwd,.,
例 一
DECLARE
tin_rec tin % rowtype ;
v_passwd userlog.passwd % type ;
errps EXCEPTION ;
BEGIN
select * into tin_rec from tin ;
select passwd into v_passwd from userlog
where userid = tin_rec.ud ;
例 一
if tin_rec.ps = v_passwd then
insert into tout values(‘ login ok’ );
else
raise errps ;
end if ;
exception
when errps then
insert into tout values(‘ password error’〕 ；
when no_data_found then
insert into tout values(‘ userid error’〕 ；
end;
光 标
?Oracle 使用 Private SQL Area的
工作区执行 SQL语句，并保存处理
结果。
?光标是一个 PL/SQL结构，可命名这
些工作区，并通过光标访问工作区
里的信息。
隐式光标
隐式光标名,SQL
访问：
SQL％ ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
显式光标
?定义
delcare
cursor c1 is select …
?打开 open c1
?读 fetch c1 into 变量 /记录
?close c1
显式光标
读状态信息
c1%found
c1%rowcound
用光标定义记录
temp c1%rowtype
例 二
问题：
为职工长工资。从最低工资长，每人长 10 ％，
工资总额限制在 50万元。
Declare
cursor c1 is select eno,sal from emp
order by sal for update;
emp_num number,=0 ;
s_sal number ;
e_rec c1%rowtype ;
例 二
Begin
open c1;
select sum(sal) into s_sal from emp ;
while s_sal<500000
loop
fetch c1 into e_rec ;
exit when c1%notfound ;
update emp set sal=sal*1.1
where eno=e_rec.eno ;
s_sal:=s_sal + e_rec.sal*0.1;
emp_num:=emp_num+1 ;
end loop ;
例 二
Close c1;
insert into msg values(emp_num,s_sal) ;
commit ;
end;
光标 for循环
For r in c1
loop
r.sal （ 循环变量永远是记录型）
end loop;
带参数光标
cursor c1 (depno char(3)) is
select * from emp
where dno=depno ;
open c1(v_depno) ;
表 clog
例 三
练习要点：
光标 FOR循环，带参数的光标
问题：
收发室收文件。文件分类，每类中文件依收到日期先后顺
序编号。值班人员收到文件后，要在系统中登录文件类别，
编号，收到日期。
（注,一天中收到的文件可能不止一份）
编一 PL/SQL 程序，查询每一类中最后一天收到的文件）
Kno 类别 pno编号 day收发日期
例 三
Declare
cursor c1 is select distinct kno from clog;
cursor c2 (kindno char(3)) is
select * from clog where kno=kindno
order by day desc ;
i number ;
l_d date ;
begin
例 三
For r1 in c1
loop
I,= 0 ;
for r2 in c2(r1.kno)
loop
I,= I + 1 ;
if I=1 then
insert into tout(r2.kno,r2.pno,r2.day);
l_d:=r2.day;
elsif r2.day:=l_d then
insert into tout(r2.kno,r2.pno,r2.day);
else exit;
end if ;
end loop;
end loop ;
建立数据库对象－触发器
Create trigger emp_check
before
after insert or update or delete
instead of
on emp
begin
if to_char(sysdate,’dy’)=‘星期日’ then
raise_application_error(-20000,`error `) ;
end if ;
end ;
建立数据库对象－触发器
SQL> create or replace trigger temp_t
2 before update on temp
3 begin
4 if to_char(sysdate,'dy')='星期二 ' then
5 raise_application_error(-20000,
‘今天不能修改 ') ;
6 end if ;
7 end ;
SQL> /
触发器已创建
建立数据库对象－触发器
SQL> update temp set a=2;
update temp set a=2
*
ERROR 位于第 1 行,
ORA-20000,今天不能修改
ORA-06512,在 "WXY.TEMP_T",line 3
ORA-04088,触发器 'WXY.TEMP_T' 执行
过程中出错
Create trigger emp_t
before update of sal
on emp
for each row
when (old.dno is not null)
begin
if (:new.sal < =,old.sal) then
raise_application_error(… );
end if ;
end ;
建立数据库对象－触发器
建立存储过程
Create procedure raise_salary
(emp_id integer,increase real)
is
begin
update emp set sal=sal+increase
where eno=emp_id ;
end ;
存储过程调用
?在 SQL*Plus中
execute raise_salary(12,46.50)
?在 PL*SQL中
begin
raise_salary(12,46.50)；
end ;
