--程序单元:数据库中命名的PL/SQL块,作为数据对象保存在数据库中
--分类:
1.过程:执行特定操作,无返回值,相当于PL/SQL块起个名字
2.函数:进行复杂计算,有返回值,相当于PL/SQL块起个名字
3.包:逻辑上相关的过程和函数组织在一起
4.触发器:事件触发,执行相关操作
--结构:2345称为匿名块
1.header--必须的,了程序的名称、类型和参数
2.declare--局部变量声明
3.executable--必须的,sql和控制语句
4.exception--异常处理
5.end;--必须
-- 参数模式
1.in 默认传递,值被传送给子程序,子程序上可以改变参数值
可以是表达式,变量(有值或无值)
2.out 必须指定,值被返回调用环境,子程序不能改变参数值
必须是变量(有值或无值)
3.in out 必须指定,值被返回调用环境,子程序上可以改变参数值
必须是变量(有值)
--建的存储过程位于Procedure文件夹中
--举例一.1:新建无参数存储过程:向学生表中插入一条数据
create or replace procedure firstpro
is --定义变量的地方
v_sex varchar2:='男';
begin
insert into tb_student(id,name,age,sex)
values(seq_tb_student.nextval,'npl','21',v_sex) ;
commit;
end firstpro;
注:第一次F8不是执行,是编译并存储在数据库中
第二次F8是执行
--举例一.2:调用存储过程
begin
fristpro;
end;
--举例二.1:新建带参数的存储过程
应用场景:用户从注册页面填写信息,通过jdbc调用存储过程,效率高
create or replace procedure parampro
(
v_name varchar2,
v_age varchar2,
v_sex varchar2
)
is --定义变量的地方
v_sex varchar2:='男';
begin
insert into tb_student(id,name,age,sex)
values(seq_tb_student.nextval,v_name,v_age,v_sex) ;
commit;
end parampro;
注:第一次F8不是执行,是编译并存储在数据库中
第二次F8是执行
--举例二.2:调用带参数的存储过程
begin
parampro('npl','25','女');
end;
--存储过程更多情况下是在数据库方做数据整合等复杂的工作
--举例四.1:需求:第一天备份80万条记录,第二份每次备份新增80万条数
备份在晚上12点进行,
1.每天备份完之后,max(id)存到tb_maxid表
2.下次备份时select max(id)
分析:select * from table where id >max(id)则备份
步骤:1.提取_student表所有数据
2.循环插入到tb_student_bak表中
3.完成后记录max(id)到tb_maxid表
create or replace procedure backpro (v_maxid number) is
cursor c_tb_student is
select * from tb_student where id>v_maxid;
r_tb_student tb_student%rowtype;
--判断循环次数
v_index number:=0;
v_max number;
begin
--打开游标
if c_tb_student%isopen then--判断游标是否打开
null;--只是维护语句的完整性,没有任何效果
else
open c_tb_student;
end if
--循环插入数据
loop
fetch c_tb_student into r_tb_student;
exit when c_tb_student%notfound;
insert tb_student_back(id,name,sex,age)
values(r_tb_student.id,r_tb_student.name,r_tb_student.sex,r_tb_student.age);
v_index number:=v_index+1;
--分段提交
if(v_index=2000) then
commit;--问题:放在循环内,效率低,但放到循环外,当回滚段(内存)不足时,会抛异常
--可以要求管理员加大回滚段---不现实
--可以分段提交,批量提交
v_index :=0;
end if;
end loop;
commit;--提交剩余的数据
--实现增量备份
select max(id) into v_max from tb_student_back;
update tb_maxid set id =v_max;
commit;
--关闭游标
close c_tb_student;
end backpro;
--举例四.2调用存储过程
create or replace procedure invokdbackpro
is
v_maxid number;
begin
select id into v_maxid from tb_maxid;
bachpro(v_maxid);
end invokdbackpro;
--举例四.3:测试
begin
invokdbackpro;
end;
--举例四.4:定时调度存储过程
--创建调度任务定时器:运行后会在DBMS_job文件夹下生成一个job
declare
jobno number;
begin
dbms_job.submit(
jobno,
what =>'invokdbackpro',--存储过程的名称
Interval => 'TRUNC(sysdate,''mi'')+1/24*60'--定义时间间隔为每分钟
)
end;
--分类:
1.过程:执行特定操作,无返回值,相当于PL/SQL块起个名字
2.函数:进行复杂计算,有返回值,相当于PL/SQL块起个名字
3.包:逻辑上相关的过程和函数组织在一起
4.触发器:事件触发,执行相关操作
--结构:2345称为匿名块
1.header--必须的,了程序的名称、类型和参数
2.declare--局部变量声明
3.executable--必须的,sql和控制语句
4.exception--异常处理
5.end;--必须
-- 参数模式
1.in 默认传递,值被传送给子程序,子程序上可以改变参数值
可以是表达式,变量(有值或无值)
2.out 必须指定,值被返回调用环境,子程序不能改变参数值
必须是变量(有值或无值)
3.in out 必须指定,值被返回调用环境,子程序上可以改变参数值
必须是变量(有值)
--建的存储过程位于Procedure文件夹中
--举例一.1:新建无参数存储过程:向学生表中插入一条数据
create or replace procedure firstpro
is --定义变量的地方
v_sex varchar2:='男';
begin
insert into tb_student(id,name,age,sex)
values(seq_tb_student.nextval,'npl','21',v_sex) ;
commit;
end firstpro;
注:第一次F8不是执行,是编译并存储在数据库中
第二次F8是执行
--举例一.2:调用存储过程
begin
fristpro;
end;
--举例二.1:新建带参数的存储过程
应用场景:用户从注册页面填写信息,通过jdbc调用存储过程,效率高
create or replace procedure parampro
(
v_name varchar2,
v_age varchar2,
v_sex varchar2
)
is --定义变量的地方
v_sex varchar2:='男';
begin
insert into tb_student(id,name,age,sex)
values(seq_tb_student.nextval,v_name,v_age,v_sex) ;
commit;
end parampro;
注:第一次F8不是执行,是编译并存储在数据库中
第二次F8是执行
--举例二.2:调用带参数的存储过程
begin
parampro('npl','25','女');
end;
--存储过程更多情况下是在数据库方做数据整合等复杂的工作
--举例四.1:需求:第一天备份80万条记录,第二份每次备份新增80万条数
备份在晚上12点进行,
1.每天备份完之后,max(id)存到tb_maxid表
2.下次备份时select max(id)
分析:select * from table where id >max(id)则备份
步骤:1.提取_student表所有数据
2.循环插入到tb_student_bak表中
3.完成后记录max(id)到tb_maxid表
create or replace procedure backpro (v_maxid number) is
cursor c_tb_student is
select * from tb_student where id>v_maxid;
r_tb_student tb_student%rowtype;
--判断循环次数
v_index number:=0;
v_max number;
begin
--打开游标
if c_tb_student%isopen then--判断游标是否打开
null;--只是维护语句的完整性,没有任何效果
else
open c_tb_student;
end if
--循环插入数据
loop
fetch c_tb_student into r_tb_student;
exit when c_tb_student%notfound;
insert tb_student_back(id,name,sex,age)
values(r_tb_student.id,r_tb_student.name,r_tb_student.sex,r_tb_student.age);
v_index number:=v_index+1;
--分段提交
if(v_index=2000) then
commit;--问题:放在循环内,效率低,但放到循环外,当回滚段(内存)不足时,会抛异常
--可以要求管理员加大回滚段---不现实
--可以分段提交,批量提交
v_index :=0;
end if;
end loop;
commit;--提交剩余的数据
--实现增量备份
select max(id) into v_max from tb_student_back;
update tb_maxid set id =v_max;
commit;
--关闭游标
close c_tb_student;
end backpro;
--举例四.2调用存储过程
create or replace procedure invokdbackpro
is
v_maxid number;
begin
select id into v_maxid from tb_maxid;
bachpro(v_maxid);
end invokdbackpro;
--举例四.3:测试
begin
invokdbackpro;
end;
--举例四.4:定时调度存储过程
--创建调度任务定时器:运行后会在DBMS_job文件夹下生成一个job
declare
jobno number;
begin
dbms_job.submit(
jobno,
what =>'invokdbackpro',--存储过程的名称
Interval => 'TRUNC(sysdate,''mi'')+1/24*60'--定义时间间隔为每分钟
)
end;