创建存储过程:
create or replace procedure 自定义过程名称 as
begin
--过程中要执行的语句
insert into table_name values ('');
commit;
end;
--调用无参存储过程
begin
过程名;
end;
--创建定时任务:每分钟运行一次存储过程
declare
jobid number;
begin
dbms_job.submit(jobid, 'p_test;', sysdate, 'sysdate+1/(24*60)');
end;
--sysdate+1 每天
--sysdate+1/24 每小时
--sysdate+2/(24*60) 每2分钟
--sysdate+30/(24*60*60) 每30秒
--sysdate+5 每5天
--sysdate+6/24 每6小时
--查看定时任务
select * from dba_jobs;
select * from user_jobs;
--运行定时任务
begin
dbms_job.run(job);--job:user_jobs表中的job字段(主键)
end;
--停止定时任务
begin
dbms_job.broken(job,false);--true:开启
end;
--删除定时任务
begin
dbms_job.remove(job);
end;
创建有输入输出的存储过程:
create or replace procedure pjd_test
(
v_state in number,
v_type in number,
v_num1 out number,
v_num2 out number
)
as
--定义变量
v_count number(8);
begin
if v_state = 0 then
--过程中要执行的语句
SELECT COUNT(*) INTO v_count FROM usera.table_name A, userb.table_name B WHERE A.id = b.id AND a.type = v_type;
if v_count = 0 then
--过程中要执行的语句
v_num1 := 0;
v_num2 := 0;
elseif v_count = 1 then
--过程中要执行的语句
v_num1 := 1;
v_num2 := 1;
elseif v_count = 2 then
--过程中要执行的语句
v_num1 := 2;
v_num2 := 2;
else v_count = 3 then
--过程中要执行的语句
v_num1 := 3;
v_num2 := 3;
end if;
else
--过程中要执行的语句
v_num1 := 4;
v_num2 := 4;
end if;
end;
--运行
begin
pjd_test(1,1,1,1);
end;
--创建定时任务
declare
job number;
begin
dbms_job.submit(job,
'declare v_state number; v_type number; v_num1 number; v_num2 number; begin pjd_test(v_state,v_type,v_num1,v_num2); commit; end;',
sysdate, 'sysdate+1/(24*60)');
commit;
end;
删除存储过程
drop procedure 存储过程名称;
问题出现
在Oracle中,在USERA下编写一个存储过程,该存储过程中引用了另一个用户USERB下的表或视图对象。编译该存储过程,会出现编译错误。报ORA-00942: table or view does not exist。
查找分析问题
该表或视图的确在USERB下存在,而且已经授予了USERA DBA角色的权限,即可以查询任何用户下的表或视图。将存储过程中的该报错语句select * from userb.table_name复制粘贴到SQL窗口中执行,不会报错。为什么放在存储过程中会报上述错误呢?经过研究,发现ORACLE对于在存储过程中引用其他用户下的表或视图对象有特别的限制,要求USERA对这个表或视图具有直接的SELECT权限,而不是通过角色,如DBA间接获取的权限。否则就会报上述错误导致编译不通过。
问题解决
所以我们先登录到USERB用户下,执行GRANT SELECT ON 表名(或视图名) TO USERA ,再切换到USERA用户下,重新编译存储过程,发现编译就可以通过了,问题解决。