109 积分	0 好友	9 主题

发消息

[SQL调优] 一个简单的循环脚本问题

1^#

发表于 2013-3-3 17:10:54 | 查看: 3823| 回复: 3

测试环境为Redhat 4 U4,oracle 10.2.0.1
在/home/oracle下写了一个简单的脚本check.sh，内容如下
select to_char(sysdate,'HH24:MI:SS') now from dual;
select CPHBT from X$KCCCP where CPHBT>0;
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
在sqlplus下运行正常
SQL> @check.sh

NOW
--------
16:19:42

CPHBT
----------
809090683

ac99dc73241f11ad92cb415c61a9bb59 /u0/oracle/oradata/TESTwis/control01.ctl

为了多次运行这一脚本，想将其修改为循环结构，于是修改check.sh内容如下
declare
i number;
begin
for i in 1..20 loop
select to_char(sysdate,'HH24:MI:SS') now from dual;
select CPHBT from X$KCCCP where CPHBT>0;
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
end loop;
end;
/

运行该脚本后出错，内容如下
SQL> @check.sh
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "!" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
The symbol "case was inserted before "!" to continue.
ORA-06550: line 7, column 49:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * @ % & = - + < / > at in is mod remainder not rem when
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member
貌似循环结构下识别不了操作系统的命令？
那要想多次自动运行上述三个命令，请教一下，该如何操作？

分享0