Question

PL/SQL 存储过程出错

create or replace procedure p_index_check is

v_sql varchar2(2048);
v_bt timestamp;
v_ft timestamp;

cursor c1 is select * from index_check_log;

Begin
for r1 in c1 loop
v_sql := r1.sql_statement;
update index_check_log aa
set aa.v_begin_time = (to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff'))
where aa.id_log = r1.id_log;

v_bt := systimestamp;
execute immediate v_sql;
v_ft := systimestamp;
update index_check_log bb
set bb.v_finish_time = (to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff'))
where bb.id_log = r1.id_log;

update index_check_log cc
set cc.diff_time = (v_ft - v_bt)
where cc.id_log = r1.id_log;

End loop;
End;

PL/SQL 7.15
服务器端oracle 9i
出错代码:
Compilation errors for PROCEDURE CCSP.P_INDEX_CHECK

Error: PL/SQL: ORA-06553: PLS-103: Encountered the symbol ")" when expecting one of the following:( - + case mod new null <anidentifier><a double-quoted delimited-identifier> <a bind variable> avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date<a string literal with character set specification><a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ")" to continue.
Line: 7
Text: cursor c1 is select * from index_check_log;

Error: PL/SQL: SQL Statement ignored
Line: 7
Text: cursor c1 is select * from index_check_log;

Error: PLS-00364: loop index variable 'R1' use is invalid
Line: 11
Text: for r1 in c1 loop

Error: PL/SQL: Statement ignored
Line: 11
Text: for r1 in c1 loop

Line: 21
Text: where bb.id_log = r1.id_log;

Error: PL/SQL: SQL Statement ignored
Line: 21
Text: where bb.id_log = r1.id_log;

请Orale高手帮忙分析分析原因

Answer 1

我试验了，你的代码写的大体没有错误，只有一点点问题。

改成下面这样后，在我的电脑上过了，你试试吧。
update index_check_log cc
set cc.diff_time = to_date(v_ft - v_bt)
where cc.id_log = r1.id_log;

***********
试试log：
***********
[TEST@ORA1] SQL>create or replace procedure p_index_check is
2 v_sql varchar2(2048);
3 v_bt timestamp;
4 v_ft timestamp;
5 cursor c1 is select * from index_check_log;
6 Begin
7 for rr in c1 loop
8 v_sql := rr.sql_statement;
9 update index_check_log aa
10 set aa.v_begin_time = (to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff'))
11 where aa.id_log = rr.id_log;
12
13 v_bt := systimestamp;
14 execute immediate v_sql;
15 v_ft := systimestamp;
16 update index_check_log bb
17 set bb.v_finish_time = (to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff'))
18 where bb.id_log = rr.id_log;
19
20 update index_check_log cc
21 set cc.diff_time = to_date(v_ft - v_bt)
22 where cc.id_log = rr.id_log;
23 End loop;
24 End;
25 /

Procedure created.

---
以上，希望对你有所帮助。

Answer 2

13及就是 13及 牛比啊 select 没into在存储过程里竟然还能跑 .....赞一个