一. PL/SQL块
1. 命名的PL/SQL块:将一个完整业务逻辑的PL/SQL块起一个名字,然后进行独立的编译之后保存到数据库中,然后在后续使用时直接可以通过名称调用
2. 匿名的PL/SQL块:重用性不高,并且执行效率也不高
优点:代码重用性和效率比较高
常见的命名的PL/SQL块:函数,存储过程,触发器,程序包等等
二. 函数
自定义函数:
语法:create [or replace] function 函数名[(参数列表)]
return 数据类型
is
[变量声明]
begin
需要执行的语句;
return 返回的值;
end [函数名];
使用场景:如果业务逻辑最终需要返回一个结果则可以使用函数。
例子:
create or replace function avg_sal return number
is
emp_avg_sal number;
begin
select avg(sal) into emp_avg_sal from emp;
return emp_avg_sal;
end avg_sal;
调用方式:
可以在select语句中调用,也可以在PL/SQL块中使用
删除函数:
drop function 函数名;
三. 存储过程
存储过程:procedure
(1)创建存储过程
语法:create [or replace] procedure 存储过程名[(参数名 数据类型,参数名 数据类型,…)]
is/as
[变量的定义]
begin
需要执行的代码
[exception
异常处理]
end;
例子:
create or replace procedure pro_insert_dept
is
begin
insert into dept values(21,’销售部’,’深圳’);
end;
(2)存储过程的调用
在PL/SQL块调用
语法:
begin
存储过程名;
end;
例子:(接上面)
begin
pro_insert_dept;
end;
在sqlplus中调用
execute/exec 存储过程名[(参数值,参数值,…)];
(3)存储过程的参数
形参:在函数或者是存储过程创建的时候规定的参数,没有具体的值
实参:在函数或这是存储过程调用时传入的值或带有值的变量
参数传递方式:
1. 按照位置传参
实参的顺序和形参的顺序要一一对应
2. 按照形参传参
语法:形参名 => 实参值
例子:name => ‘张三’
3. 混合传参
既有按位置传参又有名称传参,但是如果第一个参数就是按照名称传参,后续一般不建议使用位置传参
(4)存储过程的参数模式
1. in模式:参数的值只能从存储过程外部传入,并且在存储过程内部不能对数据进行修改
语法: 参数名 [in] 数据类型
注意: 在存储过程创建时定义的参数模式默认为 in 模式
in模式参数的默认值设置:
语法:参数名 in 数据类型 default 默认值
如果给输入参数设置了默认值,则在调用的时候可以不用输入参数,但是要注意参数顺序
2. out模式:输出参数,相当于函数中的return,out参数的值只能在存储过程内部被赋值,不能从外部传入
语法: 参数名 out 数据类型
例子:
create or rplace procedure pro_select_emp (e_no number,e_name out varchar2,e_job out varchar2)
is
begin
select ename,job into e_name,e_job from emp where empno = e_no;
end;
调用:
declare
e_name emp.ename%type;
e_job emp.job%type;
begin
pro_select_emp(7369,e_name,e_job);
dbms_output.put_line(‘姓名:’||e_name||’ 职位’||e_job);
end;
3. in out 模式: 输入输出参数,参数的值可以从外部传入到存储过程内部,并且在内部进行重新赋值后返回
语法:参数名 in out 数据类型
(5)删除存储过程
drop procedure 存储过程名
(6)存储过程和函数的区别
1. 关键字不同
2. 函数必须使用 return 来返回数据,但是存储过程要使用 out 参数进行返回数据
3. 函数可以用 select 语句进行调用,但是存储过程不能使用 select 语句进行调用
4. 函数只能返回一个值,但是存储过程可以返回多个值
5. 函数只有一种模式的参数,但是存储过程有3种模式的参数
四. 序列:sequence
在 Oracle 数据库中,序列是可以用来生成一系列唯一的数字的一种数据库对象
1. 序列的创建:
create sequence 序列名
[start with n –序列产生的起始位置,默认为1
increment by n –序列增长的步长,默认为1
minvalue n –最小值,默认为1
maxvalue n –最大值,默认为99999999999999999999
cache n|nocache –缓存设置,默认为20
]
2. 序列的使用
nextval:生成下一条数字
currval:获取当前的数字
注意:在创建好一个序列之后,在使用currval之前必须先执行一次nextval来生成数字
语法:select 序列名.nextval/currval from dual;
使用:create table user_t(
u_id number,
u_name varchar2(20)
)
insert into uesr_t values (序列名.nextval,’小明’);
五. 程序包
将数据库中的对象封装到一起,方便后期的管理和维护,并且可以提高程序的效率
程序包的组成
1.程序包规范:package
规范中主要保存一些声明操作,比如:游标、类型、变量、存储过程的声明,函数的声明等
语法:create [or replace] package 包名
is
[游标声明]
[变量声明]
[类型声明]
[存储过程声明]
[函数声明]
end [包名];
例子:
create or replace package pack_age
is
procedure pro_emp(e_no number,e_name out varchar2,e_sal out number);
function fun_sal(e_deptno number) return number;
end pack_age;
2.程序包主体:package body
主要就是对规范中的声明的子程序进行一个具体的实现
语法:create or replace package body 包名
is
[存储过程的实现]
[函数的实现]
…
end 包名;
例子:
create or replace package body pack_age
procedure pro_emp(e_no number,e_name out varchar2,e_sal out number)
is
begin
select ename,sal into e_name,e_sal from emp where empno = e_no;
end pack_emp;
function fun_sal(e_deptno number) return number
is
v_sal number;
begin
select max(sal) into v_sal from emp where deptno = e_deptno;
return v_sal;
end fun_sal;
end pack_age;
3. 程序包中的子程序的引用
包名.子程序名
4. 引用游标的使用(ref游标):可以使用引用游标来返回一个查询的结果集
使用步骤:
(1)定义一个引用游标类型
type 类型名 is ref cursor;
(2)在存储过程中使用out参数将游标类型返回
参数名 out 包名.游标类型名;
(3)在存储过程实现的时候,将查询结果放入到引用游标的输出参数中
open 参数名 for select 查询语句;
(4)在调用存储过程的时候,也需要定义引用类型的游标对输出参数进行接受
编写规范:
create or replace package pack_emp
is
type ref_cur is ref cursor;
procedure pro_emp(d_no number,row_emp out pack_emp.ref_cur);
end pack_emp;
create or replace package body pack_emp
is
procedure pro_emp(d_no number,row_emp out pack_emp.ref_cur);
is
begin
open row_emp for select * from emp where deptno = d_no;
end pro_emp;
end pack_emp;
调用:
declare
r_emp pack_emp.ref_cur;
row_e emp%rowtype;
begin
pack_emp.pro_emp(30,r_emp);
loop
fetch r_emp into row_e;
exit when r_emp %notfound;
dbms_output.put_line(row_e.ename||’,’||row_e.sal);
end loop;
end;
六. 定时任务调度
Oracle中可以在系统中设置一些定时任务,并且规定好执行的时间,然后系统会自动去执行该操作
Oracle数据库中定时任务的操作需要使用一个程序包:dbms_job:
1. 设置定时任务
dbms_job.submit(
job out number, –定时任务生成的编号
what in varchar2, –设置定时任务要执行的存储过程
next_date in date, –设置任务下一次执行的时间
interval in varchar2 –设置任务执行的时间间隔
)
例子:每隔1分钟向数据库添加一条数据
create sequence test_pk nocache;
create or replace procedure pro_insert_user
is
begin
insert into user_t values(test_pk.nextnvl,’小明’);
commit;
end pro_insert_user;
declare
jobid number;
begin
dbms_job.submit( job=>jobid,what=>’pro_insert_user;’,next_date=>sysdate,interval=>’sysdate+(1/24/60)’);
dbms_output.put_line(jobid);
end;
2.查询系统中当前的所有定时任务
数据字典:dba_jobs
select * from dba_jobs;
注意:只能在管理员模式下进行查询
3.禁用/启用定时任务
dbms_job.broken(任务编号,true/false);
4.删除定时任务
dbms_job.remove(任务编号);
注意删除定时任务之前先将定时任务禁用
原创文章,作者:wure,如若转载,请注明出处:https://blog.ytso.com/tech/database/267243.html