如何建立及调用oracle存储过程-实例讲解

Oracle SQL Developer定制每天执行一次存储过程的计划任务

 

1.获取昨天一天的所有数据

select     pk_information,problemno,problemname,productid,partno,createUser,createTime,dutygroup,dutyuser,batch,problemtype,emergencydegree from  brsj_kms_problem
where       createtime
between    to_char(sysdate-1,'yyyy-mm-dd')
and           to_char(sysdate,'yyyy-mm-dd');

2.将查询到的数据插入到对应的表中 

insert into brsj_kms_distributeproblem (
             pk_information
             ,problemno
             ,problemname
             ,productid
             ,partno
             ,createUser
             ,createTime
             ,dutygroup
             ,dutyuser
             ,batch
             ,problemtype
             ,emergencydegree
        )
    select pk_information,problemno,problemname,productid,partno,createUser,createTime,dutygroup,dutyuser,batch,problemtype,emergencydegree from
    brsj_kms_problem where createtime between to_char(sysdate-1,'yyyy-mm-dd') and to_char(sysdate,'yyyy-mm-dd');

这里特别说明一下，第一个括号后面没有values关键字，加上values关键字执行就会报错，错误的表达式。 

3.测试上面的SQL语句无误后，开始创建存储过程 

create procedure ScheduledTasks
is
begin
insert into brsj_kms_distributeproblem (
             pk_information
             ,problemno
             ,problemname
             ,productid
             ,partno
             ,createUser
             ,createTime
             ,dutygroup
             ,dutyuser
             ,batch
             ,problemtype
             ,emergencydegree
        )
    select pk_information,problemno,problemname,productid,partno,createUser,createTime,dutygroup,dutyuser,batch,problemtype,emergencydegree from
    brsj_kms_problem where createtime between to_char(sysdate-1,'yyyy-mm-dd') and to_char(sysdate,'yyyy-mm-dd');
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
     ROLLBACK;
END;

 创建好了之后，刷新一下，在左侧的过程文件夹中就可以看到对应的过程名称，检查图标上是否有红叉，如果有，说明语句错误，重新检查哪里出错。如果没有错误，直接右击，选择运行，运行成功后，可以查看对应的表中是否成功插入了数据，需要注意的是要确保执行过程的SQL语句逻辑上是能产生数据的，例如查询昨日的所有记录，如果昨日没有数据产生，即便SQL语句无误也不会产生结果，所以需要提前添加一些测试数据，确保能看到结果。

     运行到这里，能看到新产生的数据，说明创建的过程是没有问题的，接下来就是如何每天让它运行一次的问题了。 

Oracle存储过程的调用

如何执行oracle存储过程，就exec一下？

不单单是exec一下，还是得分情况：

1.如果是命令窗口就用exec 存储过程名，举个栗子：

EXEC  procedure;--procedure是存储过程名

2.如果是PL/SQL窗口就用 begin  存储过程名  end; 举个栗子：

begin
  procedure;--procedure是存储过程名
end;

3.如果是程序中调用就用 call 存储过程名 ，举个栗子：

hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");//存储过程proc_stuInfo

另附 存储过程创建方法：

create or replace procedure pro_test--pro_test为存储过程名
is
temp varchar2(128);--temp为存储过程临时变量
bengin
    select count(*) into temp from TEST;--这里为什么会使用temp变量，下面会提到
    insert into TEST values(3,'sss',25,'asd');
    commit;--切记commit一下（提交）
end;

注意：在存储过程中是不能直接出现"select * from test",这种简单查询，必须将查询出来的数据放入存储过程变量中，如上所示的temp变量。