oracle 两个带有开始和结束的 PLSQL 语句，单独运行良好但不能一起运行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3024418/
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
Two PLSQL statements with begin and end, run fine separately but not together?
提问by Twiss
Just wondering if anyone can help with this, I have two PLSQL statements for altering tables (adding extra fields) and they are as follows:
只是想知道是否有人可以帮助解决这个问题，我有两个用于更改表（添加额外字段）的 PLSQL 语句，它们如下：
-- Make GC_NAB field for Next Action By Dropdown
begin
if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))';
elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or
'VARCHAR2' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)';
end if;
commit;
end;
-- Make GC_NABID field for Next Action By Dropdown
begin
if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))';
elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or
'NUMBER' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)';
end if;
commit;
end;
When I run these two queries separately, there are no problems. However, when run together as shown above, Oracle gives me an error when it starts the second statement:
当我分别运行这两个查询时，没有问题。但是，当如上所示一起运行时，Oracle 在启动第二个语句时给我一个错误：
Error report:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "BEGIN"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm assuming that this means the first statement is not terminated properly... is there anything I should put in between the statements to make it work properly?
我假设这意味着第一个语句没有正确终止......是否应该在语句之间添加任何内容以使其正常工作？
回答by Shannon Severance
Oracle can take one SQL statement or PL/SQL anonymous block at a time. (Unlike SQL Server that can except a batch at a time.) So, you have a couple of options.
Oracle 一次可以处理一个 SQL 语句或 PL/SQL 匿名块。（与一次只能处理一个批处理的 SQL Server 不同。）因此，您有几个选择。
Wrap the two anonymous blocks within another anonymous block:
begin -- Make GC_NAB field for Next Action By Dropdown begin if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))'; elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or 'VARCHAR2' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)'; end if; commit; end; -- Make GC_NABID field for Next Action By Dropdown begin if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))'; elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or 'NUMBER' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)'; end if; commit; end; end;Tell the tool you are using to submit the PL/SQL to Oracle to send the two block seperately. How to do this will be tool specific. In SQL*PLUS, a
/on a line by itself will accomplish this:-- Make GC_NAB field for Next Action By Dropdown begin if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))'; elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or 'VARCHAR2' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)'; end if; commit; end; / -- Make GC_NABID field for Next Action By Dropdown begin if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))'; elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or 'NUMBER' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)'; end if; commit; end; /
将两个匿名块包装在另一个匿名块中：
begin -- Make GC_NAB field for Next Action By Dropdown begin if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))'; elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or 'VARCHAR2' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)'; end if; commit; end; -- Make GC_NABID field for Next Action By Dropdown begin if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))'; elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or 'NUMBER' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)'; end if; commit; end; end;告诉你使用的向Oracle提交PL/SQL的工具分别发送这两个块。如何做到这一点将取决于工具。在 SQL*PLUS 中，
/一行上的 a 本身将完成此操作：-- Make GC_NAB field for Next Action By Dropdown begin if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))'; elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or 'VARCHAR2' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)'; end if; commit; end; / -- Make GC_NABID field for Next Action By Dropdown begin if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))'; elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or 'NUMBER' = 'VARCHAR2' then execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())'; else execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)'; end if; commit; end; /
回答by Alex Poole
Yes, you need a slash /on the line after each end;.
是的，您需要/在每个end;.
回答by Ravichandra Aj
one can achieve in two ways
一种可以通过两种方式实现
1) run the two blocks separately in pl/sql file.
1) 在 pl/sql 文件中分别运行这两个块。
2) in main block you can run two sbu-blocks in this way :
2) 在主块中，您可以通过这种方式运行两个 sbu 块：
begin // main block
begin
..... [pl/sql commands]
......
end
begin
....... [pl/sql commands]
.......
end
end // main ends
