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');