在 PL/SQL 过程中创建数据库表 [英] Creating database table in PL/SQL procedure

问题描述

我正在尝试使用过程 CREATE_TABLE 创建表，然后使用过程 PRINT_INFO 将信息插入到创建的表中，但出现异常:

I am trying to create table with procedure CREATE_TABLE and then insert the information into the created table with procedure PRINT_INFO but I am getting an exception:

错误:PROCEDURE PRINT_INFO 行/列:4/3 PL/SQL:SQL 语句忽略的行/列:4/15 PL/SQL:ORA-00942:表或视图不存在

Errors: PROCEDURE PRINT_INFO Line/Col: 4/3 PL/SQL: SQL Statement ignored Line/Col: 4/15 PL/SQL: ORA-00942: table or view does not exist

错误:PROCEDURE CREATE_TABLE 行/列:5/3 PLS-00103:遇到符号创建"当期待以下之一时:

Errors: PROCEDURE CREATE_TABLE Line/Col: 5/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

( begin case 声明退出 for goto if loop mod null pragma raisereturn select update while with <<继续关闭电流删除获取锁定插入打开回滚保存点设置 sql 执行提交所有合并管道清除 json_exists json_value json_queryjson_object json_array

( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

这是我的代码示例:

CREATE OR REPLACE PROCEDURE PRINT_INFO
IS
BEGIN
  INSERT INTO TABLE_T (TABLE_ID, MESSAGE) VALUES (1, 'Hello World!');
END PRINT_INFO;
/

CREATE OR REPLACE PROCEDURE CREATE_TABLE
IS
BEGIN

  CREATE TABLE TABLE_T(
    TABLE_ID NUMBER NOT NULL,
    MESSAGE VARCHAR2(25), 
    PRIMARY KEY(TABLE_ID)
  );
  
  PRINT_INFO;
  
END CREATE_TABLE;
/

EXEC CREATE_TABLE;

问题出在哪里?如何摆脱异常?

Where could be the problem? How can I get rid of an exception?

推荐答案

两个过程都必须使用动态 SQL:

Both procedures have to use dynamic SQL:

• print_info 因为它插入到一个在编译时还不存在的表中
• create_table 因为它运行 DDL 并且 - 为了做到这一点 - 你需要使用动态 SQL

• print_info because it inserts into a table which - at compilation time - doesn't exist yet
• create_table because it runs DDL and - in order to do that - you need to use dynamic SQL

因此:

SQL> CREATE OR REPLACE PROCEDURE PRINT_INFO
  2  IS
  3  BEGIN
  4    execute immediate q'[INSERT INTO TABLE_T (TABLE_ID, MESSAGE) VALUES (1, 'Hello World!')]';
  5  END PRINT_INFO;
  6  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE
  2  IS
  3  BEGIN
  4    execute immediate 'CREATE TABLE TABLE_T(' ||
  5      'TABLE_ID NUMBER NOT NULL, ' ||
  6      ' MESSAGE VARCHAR2(25), '    ||
  7      ' PRIMARY KEY(TABLE_ID) '    ||
  8      ')';
  9
 10    PRINT_INFO;
 11  END CREATE_TABLE;
 12  /

Procedure created.

SQL> EXEC CREATE_TABLE;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM table_t;

  TABLE_ID MESSAGE
---------- -------------------------
         1 Hello World!

SQL>

这篇关于在 PL/SQL 过程中创建数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！