两个带有开始和结束的 PLSQL 语句，单独运行良好但不能一起运行?

2021-12-24 00:00:00 oracle oracle10g plsql statements

只是想知道是否有人可以帮助解决这个问题，我有两个用于更改表(添加额外字段)的 PLSQL 语句，它们如下:

Just wondering if anyone can help with this, I have two PLSQL statements for altering tables (adding extra fields) and they are as follows:

-- 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;

当我分别运行这两个查询时，没有问题.但是，如上图一起运行时，Oracle在启动第二条语句时报错:

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:

Error report:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "BEGIN" 
06550. 00000 -  "line %s, column %s:
%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?

推荐答案

Oracle 一次可以处理一个 SQL 语句或 PL/SQL 匿名块.(与 SQL Server 一次只能处理一个批处理不同.)因此，您有几个选择.

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.

  1. 将两个匿名块包裹在另一个匿名块中:

  1. 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;

  • 告诉你使用的工具将PL/SQL提交给Oracle，分别发送这两个块.如何做到这一点将取决于工具.在 SQL*PLUS 中，一行中的 / 将完成此操作:

      -- 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;
  /

    • 相关文章