我想在这里使用PLSQL动态创建表，但它不起作用，它显示了此错误

Question

CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE(D_NAME in VARCHAR2, D_TABLE_SPECS in VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE' || D_NAME || '(' || D_TABLE_SPECS || ')' ;

结尾;

Answer 1

动态SQL是...好吧，如果需要的话，应该使用的选项。我建议您不要动态创建表。

需要

SQL> CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE (D_NAME         IN VARCHAR2,
  2                                                    D_TABLE_SPECS  IN VARCHAR2)
  3  IS
  4     l_str  VARCHAR2 (200);
  5  BEGIN
  6     l_str := 'CREATE TABLE' || D_NAME || '(' || D_TABLE_SPECS || ')';
  7
  8     DBMS_OUTPUT.put_line (l_str);
  9  --EXECUTE IMMEDIATE l_str;
 10  END;
 11  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC dynamic_table_create('super_man', 'id number');
CREATE TABLEsuper_man(id number)

PL/SQL procedure successfully completed.

SQL>

如果 尝试运行此语句：

SQL> CREATE TABLEsuper_man(id number)
  2  /
CREATE TABLEsuper_man(id number)
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL>

表和表名称之间缺少空间。因此，请修复它，然后uncomment 立即执行：

SQL> CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE (D_NAME         IN VARCHAR2,
  2                                                    D_TABLE_SPECS  IN VARCHAR2)
  3  IS
  4     l_str  VARCHAR2 (200);
  5  BEGIN
  6     l_str := 'CREATE TABLE ' || D_NAME || '(' || D_TABLE_SPECS || ')';
  7     --                    ^
  8     --           add space here
  9     DBMS_OUTPUT.put_line (l_str);
 10
 11     EXECUTE IMMEDIATE l_str;
 12  END;
 13  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC dynamic_table_create('super_man', 'id number');
CREATE TABLE super_man(id number)

PL/SQL procedure successfully completed.

SQL> SELECT * FROM super_man;

no rows selected

SQL>

现在起作用。