先在sqlplus中以system的身份建这两张表
create table stu(
Sno varchar(12) primary key,
Sname varchar(12) not null,
Ssex varchar(4) not null)
;
create table stu_bak(
Sno varchar(12) primary key,
Sname varchar(12) not null,
Ssex varchar(4) not null)
;
需求:建一个存储过程,能够实现调用该存储过程时,能够建stu表中的数据备份到stu_bak表中,备份要求是若在stu_bak表中已有该行数据则对该行数据进行更新,当stu_bak表中没有该行数据时,就将该行数据插入。
在完成以上任务前需要了解的基础知识:
(1)存储过程的基本语法格式:
create [or replace ] procedure pro_name[(parameter1[,parameter2...])]
is|as
begin
plsql_sentences;
[exception]
[dowith_sentences;]
end [pro_name]
以上基本语法格式中红色字体标注的是PL/SQL程序块中的关键字,蓝色字体的为用户可自定义,中括号内的内容根据实际情况可选;
pro_name存储过程的名称,已使用该名称的情况下,指定"or replace"关键字能够使新的存储过程覆盖原有的存储过程;parameter1:存储过程的参数,输入参数需要在其参数名后指定in关键字,输出参数需要在其参数名后指定out关键字;plsql_sentences:PL/SQL语句是实现功能的主体;dowith_sentence:异常处理语句。
需要注意的是is|as关键字后可定义存储过程的内部变量,并使用分号(;)结束;
写个对stu表的增、删、改的存储过程练练手
插入:
create or replace procedure stu_insert
(stu_sno stu.Sno%type,stu_sname stu.Sname%type,stu_ssex stu.Ssex%type)
as
begin
insert into stu values(stu_sno,stu_sname,stu_ssex);
end;
/
对了,可以通过语句
select text from all_source where name='大写存储过程名';来查看系统中已创建的存储过程
更新:
create or replace procedure stu_update
(stu_sno stu.Sno%type,stu_sname stu.Sname%type,stu_ssex stu.Ssex%type)
as
begin
update stu set Sname=stu_sname,Ssex=stu_ssex
where Sno=stu_sno;
end;
/
删除:
create or replace procedure stu_delete
(stu_sno stu.Sno%type)
as
begin
delete from stu where Sno=stu_sno;
end;
/
检验一下以上存储过程的正确性
到此处就会有一个问题,为什么在存储过程的参数定义时没有使用关键字in,但也能输入该变量,这是因为默认的参数模式就是in模式参数。
接下来看看如何定义并使用游标,显示游标的使用包含四个部分:声明游标、打开游标、读取游标、关闭游标。
声明游标:
declare
cursor cur_name[(input_parameter1[,input_parameter2].....)]
[return ret_type]
is select _sentence;
cur_name:表示所声明的游标名称。ret_type:表示执行游标操作后的返回值类型,为一个可选项。
select_sentence:游标所执行的select语句,它为游标的反复读取提供了结果集。
inpute_parameter1:作为游标的输入参数可以有多个,也是一个可选项.
打开游标:
open cur_name[(para_value1[,para_value2]....)];
读取游标:
fetch cur_name into {variable}
详细的解释赋上课本图片:
游标+存储过程就能解决需求一了
备份的存储过程
create or replace procedure thebak
as
CURSOR stu_cur IS select * from stu;
stu1 stu%rowtype;
count1 int;
begin
open stu_cur;
loop
fetch stu_cur into stu1;
exit when stu_cur%notfound;
select count(*) into count1 from stu_bak
where Sno=stu1.Sno;
if count1=1 then
update stu_bak set Sname=stu1.Sname,Ssex=stu1.Ssex
where Sno=stu1.Sno;
else
insert into stu_bak values(stu1.Sno,stu1.Sname,stu1.Ssex);
end if;
end loop;
close stu_cur;
end;
ok,去plsql中检验一下: