oracle 单个 .sql 文件中的 BEGIN/END 和 CREATE 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7397449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BEGIN/END and CREATE Table in single .sql file
提问by victorhooi
I have a small SQL script that I'm executing with Oracle's SQL*Plus to emulate create or replace on tables:
我有一个小的 SQL 脚本，我正在使用 Oracle 的 SQL*Plus 执行它来模拟表上的创建或替换：
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE symbols';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
END IF;
END;
/
CREATE TABLE symbols (
blah blah,
blah blah,
);
EXIT;
SQL*Plus commandline is:
SQL*Plus 命令行是：
sqlplus aegsys15_owner/pass#234@MARVINUAT03 @createSymbolsTable.sql << EOF
> EOF
If I omit the forward slash (/) after END, it seems to only process the first BEGIN/END block, and ignores the CREATE TABLE section underneath. Also, it doesn't print anything help out at all - just connecting/disconnecting:
如果我在 END 之后省略正斜杠 (/)，它似乎只处理第一个 BEGIN/END 块，而忽略下面的 CREATE TABLE 部分。此外，它根本不打印任何帮助 - 只是连接/断开连接：
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 15:49:34 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
78 Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
However, if I do have the forward slash it gives me an error:
但是，如果我确实有正斜杠，它会给我一个错误：
END IF;
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
CREATE TABLE symbols (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Firstly, what's the best way of having both the BEGIN/END exception block at the top, and the CREATE TABLE block in the same .sql file?
首先，将 BEGIN/END 异常块放在顶部并将 CREATE TABLE 块放在同一个 .sql 文件中的最佳方法是什么？
And secondly, what's some way of getting some helpful output out of SQL*Plus? Each .sql file we run may have multiple CREATE statements (tables, indexes, synonyms etc.). Our ideal output would be something like:
其次，有什么方法可以从 SQL*Plus 中获得一些有用的输出？我们运行的每个 .sql 文件可能有多个 CREATE 语句（表、索引、同义词等）。我们理想的输出是这样的：
TABLE foo: Pass
SYNONYM bar: Fail
INDEX foo_1: Pass
Not sure if something like that is achievable with SQL or PL/SQL though - happy to write a Bash or Python wrapper script around this, if you guys think that's a better solution.
不确定这样的事情是否可以通过 SQL 或 PL/SQL 实现 - 如果你们认为这是一个更好的解决方案，很高兴围绕这个编写 Bash 或 Python 包装器脚本。
Cheers, Victor
干杯，维克多
回答by pratik garg
you forgot to put in your if statement..
你忘了把你的 if 语句..
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE symbols';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
--here you have to write something for this exception
-- if you don't have any activity to do then you can use NULL (atleast)
-- you can't put this if statement body empty in oracle
NULL;
END IF;
END;
/
and better if you use declarealso at the first line, before begin starts
如果你declare也在第一行使用，在开始之前更好
回答by Kay Marczinzik
For output in slqplus use the prompt command. For output from pl/sql (i.e. inside a begin/end block) use the dbms_output.put_line() function.
对于 slqplus 中的输出，请使用 prompt 命令。对于 pl/sql 的输出（即在开始/结束块内）使用 dbms_output.put_line() 函数。
prompt Creating foo table
begin
create table foo...;
dbms_output.put_line('success');
exception
when others then
dbms_output.put_line('fail');
end;
/
