【Oracle】存储过程简述

1. 创建存储过程

create or replace procedure NoParPro 
 as  //声明 
 ; 
 begin // 执行 
 ; 
 exception//存储过程异常 
 ; 
 end;

2. 一个存储过程的操作

Procedure  存储名字(ret out number) is

     vt_date DATE;

Begin

        LOG（‘这里是log!’start_time,end_time）;

        vt_data:=TO_DATE(TO_CHAR(SYSDATE-INTERVAL '1' DAY, 'YYYY-MM-DD'),'YYYY-MM-DD');

delete from 表名 where dataTime=vt_date; //先删除表内该时间段数据

insert into 表名

 select id ,
       name as 姓名,
       decode(custerType,
              '01',
              '客户',
              '02',
              '业务员',
              '03',
              '其它') as 类别,

case
      
         when id = '01' then
          (select c1.stu_score
             from 成绩表 c1
            where c1.id = id)
      
         when id = '02' then
          (select c2.stu_score
             from 另张表 c2
            where c2.id = id)

             else

                     '0'

end  as 成绩，

from  表名

where time between

        to_data('2018-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and

        to_data('2018-01-01 00:00:00','yyyy-mm:dd hh24:mi:ss');