问题描述
我有一个小型SQL脚本,正在使用Oracle的SQL * Plus执行,以模拟在表上的创建或替换:
I have a small SQL script that I'm executing with Oracle's SQL*Plus to emulate create or replace on tables:
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命令行为:
SQL*Plus commandline is:
sqlplus aegsys15_owner/pass#234@MARVINUAT03 @createSymbolsTable.sql << EOF
> EOF
如果我在END之后省略正斜杠(/),似乎只处理第一个BEGIN/END块,而忽略下面的CREATE TABLE部分.而且,它根本不会打印出任何帮助-只是连接/断开连接:
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:
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
首先,将BEGIN/END异常块放在顶部,并将CREATE TABLE块放在同一个.sql文件中的最佳方法是什么?
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?
其次,从SQL * Plus中获得一些有用输出的方法是什么?我们运行的每个.sql文件可能都有多个CREATE语句(表,索引,同义词等).我们理想的输出应该是这样的:
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:
TABLE foo: Pass
SYNONYM bar: Fail
INDEX foo_1: Pass
不确定是否可以通过SQL或PL/SQL达到这样的效果-如果您认为这是更好的解决方案,那么很乐意为此编写Bash或Python包装器脚本.
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.
干杯,维克多
推荐答案
您忘记输入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;
/
,如果在开始开始之前也在第一行中也使用declare
,则更好.
and better if you use declare
also at the first line, before begin starts
这篇关于在单个.sql文件中开始/结束和创建表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!