在SQLPlus中运行循环 [英] Running Loops in SQLPlus

问题描述

我制作了一个bash脚本，该脚本通过SQLPlus连接到数据库，并运行一个包含如下所示的For循环的SQL脚本.但是一旦运行它，它就会陷入循环的BEGIN中，如下所示.我试图直接通过SQLPlus运行它，它是相同的.所以任何人都可以建议在这里做错了什么.

I made a bash script that connects to a database by SQLPlus and runs a SQL Script which contains a For loop as below. But once running it, it stuck in the BEGIN of the loop as below. I tried to run it directly through SQLPlus and it is the same. So can anyone advise what is done wrong here.

BEGIN
  FOR l_counter IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END

结果:

   BEGIN
  2  FOR l_counter IN 1..5
  3  LOOP
  4  DBMS_OUTPUT.PUT_LINE( l_counter );
  5  END LOOP;
  6  END;
  7
  8

重击:

SPOOL $FILE

@/home/genesys/scripts/Counter.sql
SPOOL OFF
EXIT
EOF

这是我在末尾添加斜杠后得到的结果.

This is the result I am getting after adding a slash at the end.

SQL> set serveroutput on;
SQL> BEGIN
  FOR l_counter IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter || ' finished ');
  END LOOP;
END;/  2    3    4    5    6
  7
  8
  9
 10

推荐答案

为了将 DBMS_OUTPUT.PUT_LINE 命令的结果放入文件中，您更喜欢使用以下两种方法之一./p>

You can prefer using one of the following two methods in order to put the results of the DBMS_OUTPUT.PUT_LINE command into a file

1. 通过使用 .sql 文件

$ cd /home/genesys/scripts
$ sqlplus /nolog
SQL> conn un/pwd
SQL> @Counter.sql

其中 Counter.sql 文件具有以下内容:

where Counter.sql file has the following content :

SET FEEDBACK OFF
SET SERVEROUTPUT ON
SPOOL output2.txt
BEGIN
FOR l_counter IN 1..10
 LOOP
  DBMS_OUTPUT.PUT_LINE(l_counter || ' finished ');
 END LOOP; 
END;
/
SPOOL OFF

1. 通过创建 .sh ( Counter.sh )文件:

record=`sqlplus -S /nolog << EOF
conn hr/hr
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SPOOL output.txt
BEGIN
 FOR l_counter IN 1..10
 LOOP
   DBMS_OUTPUT.PUT_LINE(l_counter || ' finished ');
 END LOOP; 
END;
/
SPOOL OFF
EOF`

在创建文件的命令提示符处调用

call from the command prompt where you created the file

$ cd /home/genesys/scripts
$ . Counter.sh 

这篇关于在SQLPlus中运行循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！