oracle如何直接使用存储过程,Oracle 存储过程直接使用

结构1：

不一定要创建一个存储过程使用，有时后为处理一些脏数据，直接在SQL Window 中也可以直接选中执行。

declare

v_date date := sysdate-6;

begin

while v_date <= sysdate loop

dbms_output.put_line(to_char(v_date+1,'yyyy-mm-dd'));

end loop;

end;

结构2：

创建一个有规则存储过程

create or replace procedure procedureName

is

v_udate VARCHAR2(20) := to_char(sysdate-1,'yyyy-mm-dd');

begin

for cur in (select * from tableName) loop

begin

dbms_output.put_line(cur.v_udate);

exception

when others then

dbms_output.put_line(cur.udate);

end;

end loop;

end;

执行：

call procedureName()

3.实例

create or replace procedure mc_established_day

is

v_udate VARCHAR2(20)  := to_char(sysdate-1,'yyyy-mm-dd');

v_beginDate number(11) := ORACLE_TO_UNIX(to_date(v_udate,'yyyy-mm-dd'));

v_endDate number(11) := v_beginDate+86400;

begin

for cur in (select v_udate as udate, 'DAY' as date_type, min(value_min) as value_min,avg(value_avg) as value_avg, max(value_max) as value_max,count(num) as value_count from mc_rep_trends where itemName = 'Established connections' and clock between v_beginDate and v_endDate) loop

begin

insert into mc_rep_established(udate,date_type,value_min,value_max,value_avg,value_count) values(to_date(cur.udate,'yyyy-mm-dd'),cur.date_type,cur.value_min,cur.value_max,cur.value_avg,cur.value_count);

commit;

--dbms_output.put_line(cur.udate);

exception

when others then

dbms_output.put_line(cur.udate);

end;

end loop;

end;