oracle PL/SQL 块内的过程调用

Procedure call inside a PL/SQL block

提问by álvaro González

I have some SQL code to populate a database, which works just fine:

我有一些 SQL 代码来填充数据库，它工作得很好：

INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (1, 1, 1);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (2, 1, 1);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (3, 1, 2);

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);

To enhance maintainability, I'm trying to replace some magic numbers with variables. I've never used variables before but I've read I need to use a PL/SQL block so:

为了增强可维护性，我试图用变量替换一些幻数。我以前从未使用过变量，但我读过我需要使用 PL/SQL 块，因此：

DECLARE
    ELEMENT_TEXT SMALLINT := 1;
    ELEMENT_IMAGE SMALLINT := 2;
BEGIN

INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (1, 1, ELEMENT_TEXT);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (2, 1, ELEMENT_TEXT);
INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID) VALUES (3, 1, ELEMENT_IMAGE);

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);

END;
/

This works just fine, except the procedure call:

这工作得很好，除了过程调用：

EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
           *
ERROR en lYnea 45:
ORA-06550: lYnea 45, columna 9:
PLS-00103: Se ha encontrado el sYmbolo "RESET_SEQUENCE" cuando se esperaba uno
de los siguientes:
:= . ( @ % ; immediate
El sYmbolo ":=" ha sido sustituido por "RESET_SEQUENCE" para continuar.

The PLS-00103 error translates as:

PLS-00103 错误转换为：

PLS-00103: found 'string' but expected one of the following: 'string'

PLS-00103：找到“字符串”，但需要以下内容之一：“字符串”

If I change the call to:

如果我将呼叫更改为：

EXECUTE IMMEDIATE RESET_SEQUENCE('DOCUMENTO_ELEMENTO_ID_SEQ', 4);

... I get this:

......我明白了：

PLS-00222: en este ?mbito no existe ninguna funci?n cuyo nombre sea
'RESET_SEQUENCE'
ORA-06550: lYnea 45, columna 1:
PL/SQL: Statement ignored

... that translates as:

...翻译为：

PLS-00222: no function with name 'string' exists in this scope

PLS-00222：此范围内不存在名称为“string”的函数

My questions are:

我的问题是：

1. Am I using variables correctly or there's a better solution for my initial goal?
2. What do I need to fix in my procedure call syntax and why?

1. 我是正确使用变量还是有更好的解决方案来实现我的初始目标？
2. 我需要在我的过程调用语法中修复什么，为什么？

回答by Teshte

1. IMO, it would be better if you had a procedure with 3 parameters, where you would have 1 insert...and then use that procedure to make the inserts. That way you can add exception handling, and if something happens you would know.

2. Try deleting the EXECUTE when using the block version of your code. I don't think you need it :)

1. IMO，如果您有一个带有 3 个参数的程序，您将有 1 个插入……然后使用该程序进行插入，那就更好了。这样你就可以添加异常处理，如果发生什么事情你就会知道。

2. 尝试在使用代码块版本时删除 EXECUTE。我认为你不需要它:)

回答by álvaro González

A procedure call is not plain SQL, it's PL/SQL. As such, you need to run it from PL/SQL context. Options include:

过程调用不是普通的 SQL，而是 PL/SQL。因此，您需要从 PL/SQL 上下文运行它。选项包括：

• Create a PL/SQL block and invoke the procedure directly:

  BEGIN
      RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
  END;
  /
  

• If you are at SQL*Plus, you can use the EXECUTE internal command:

  EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
  

• 创建一个 PL/SQL 块并直接调用该过程：

  BEGIN
      RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
  END;
  /
  

• 如果您使用 SQL*Plus，则可以使用EXECUTE 内部命令：

  EXECUTE RESET_SEQUENCE('DOCUMENT_ELEMENT_ID_SEQ', 4);
  

My problem was that I was issuing a SQL*Plus command inside a PL/SQL block.

我的问题是我在 PL/SQL 块中发出 SQL*Plus 命令。

(As of EXECUTE IMMEDIATE, it's a PL/SQL commandto run a dynamically generated SQL statement or anonymous PL/SQL block. It expects a string and has nothing to do with this question. Oracle only suggested it because its name resembles the SQL*Plus command.)

（截至EXECUTE IMMEDIATE，它是一个PL/SQL 命令来运行动态生成的 SQL 语句或匿名 PL/SQL 块。它需要一个字符串，与这个问题无关。Oracle 只是建议它，因为它的名称类似于 SQL*Plus 命令.)

回答by David Aldridge

If the initial goal is to avoid the use of magic numbers then the solution is to use a table with the meaning of those numbers and to reference that.

如果最初的目标是避免使用幻数，那么解决方案是使用具有这些数字含义的表格并引用它。

So ...

所以 ...

 create table element (
   element_id   integer primary key,
   element_name varchar2(30) not null unique);

 insert into element values (1,'Text');
 insert into element values (2,'Image');

 INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID)
 Select 1, 1, element_id
 from   elements
 where  element_name = 'Text';

 INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID)
 Select 1, 1, element_id
 from   elements
 where  element_name = 'Text';

 INSERT INTO PDF_DOCUMENT_ELEMENT (DOCUMENT_ELEMENT_ID, DOCUMENT_ID, ELEMENT_ID)
 Select 1, 1, element_id
 from   elements
 where  element_name = 'Image';