sql - 这里发生了什么？当我尝试运行此代码时，出现以下错误

Question

          -- Creating a procedure to convert DB to XML 
        CREATE OR REPLACE PROCEDURE XML_OUTPUT AS 
        BEGIN
-- Declaring a ref cursor and a variable of type XMLTYPE
DECLARE
  refcursor SYS_REFCURSOR;
  wxmltype XMLTYPE;
BEGIN
-- Prompting the user to input the range of database to be converted
dbms_output.put_line('Input the min value of MESSID');
-- Declaring the ref cursor and pointing it to a select variable
  OPEN refcursor FOR SELECT messname,
                            studentname 
                            FROM studentsmessdata
                            -- Accepting user input for range
                            WHERE messid > &messid; 
  wxmltype:= XMLTYPE(refcursor);
  -- printing the database as XML
  dbms_output.put_line(wxmltype.getClobVal);
END;
  NULL;
END XML_OUTPUT;

出现如下错误:

Error: PLS-00801: internal error [ph2csql_strdef_to_diana:bind] Error(9,22): PL/SQL: SQL Statement ignored Error(12,34): PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
[ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

Answer 1

禁用替换变量时会出现该错误:

set define off

CREATE OR REPLACE PROCEDURE
...
/

Procedure XML_OUTPUT compiled
Errors: check compiler log

show errors

Errors for PROCEDURE STACKOVERFLOW.XML_OUTPUT:

LINE/COL ERROR
-------- ----------------------------------------------------------------------------------------------------------------
0/0      PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
11/22    PL/SQL: SQL Statement ignored
13/34    PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

将 define 设置为默认 &(假设您使用的是支持替换变量的客户端；这是在 SQL Developer 中)，系统会提示您输入 &messid 值在编译时，该值然后固定在过程定义中。

如果你想在运行时提供值，你需要将它作为参数传递，调用类似 p_messid 的东西:

CREATE OR REPLACE PROCEDURE XML_OUTPUT(p_messid studentsmessdata.messid%TYPE) AS 
  refcursor SYS_REFCURSOR;
  wxmltype XMLTYPE;
BEGIN
  -- Declaring the ref cursor and pointing it to a select variable
  OPEN refcursor FOR SELECT messname,
                            studentname 
                            FROM studentsmessdata
                            -- Accepting user input for range
                            WHERE messid > p_messid; 
  wxmltype:= XMLTYPE(refcursor);
  -- printing the database as XML
  dbms_output.put_line(wxmltype.getClobVal);
END XML_OUTPUT;
/

我删除了多余的嵌套 block 以使其更短一些。然后你会称它为，例如:

set serveroutput on
exec XML_OUTPUT(42);