Oracle PL/SQL在游标中创建表? [英] Oracle PL/SQL Creating tables in cursor?

问题描述

我对PL/SQL比较陌生，所以请多多包涵. 我正在创建两个将在FOR循环游标中使用的表.我的意图是在开始时(在游标循环之前)创建这两个表，在需要清除它时在游标循环内截断它们，并插入新值，然后在游标循环完成后将其放在. (在对SO进行了一些研究之后，我认为可以使用REF CURSOR来完成此操作，但是我不清楚如何使用它，因此我决定继续第一种方法.)

I'm relatively new to PL/SQL, so please bear with me. I'm creating two tables that will be used in the FOR loop cursor. My intention was to create these two tables at the start (before the cursor loop), truncate them inside the cursor loop whenever I need it cleared and insert new values, then drop them at after the cursor loop completes. (After looking a bit on SO, I think this can be done with REF CURSOR, but I'm not very clear on how to use that so I decided to go ahead with the first approach.)

我尝试在DECLARE节中创建表，然后在循环开始前在BEGIN节中创建表，但是会引发错误"ORA-065500和PLS-00103在期望开始函数编译时遇到符号CREATE ..." 请注意，我将在LOOP结束之后但在游标的END部分之前删除表.

I tried creating the tables in the DECLARE section, then in the BEGIN section before the loop starts, but it raises the error 'ORA-065500 and PLS-00103 Encountered the symbol CREATE when expecting begin function pragma ...' Note that I am dropping the tables after the LOOP ends but before the END section of the cursor.

我应该在游标之外的DECLARE节之前创建表，然后将它们放在游标END节之后吗?我认为应该可以在游标内创建永久性的oracle表?

Should I create the tables outside the cursor , before the DECLARE section, and drop them after the cursor END section? I thought it should be possible to create permanent oracle tables inside cursors?

更新:在此处发布代码-

Update: Posting code here-

1)

DECLARE
  CREATE TABLE T1
    (
      col1 VARCHAR2(128),
      col2  VARCHAR2(128),
      col3 NUMBER(3) NOT NULL,
      col3 FLOAT(100)
    );

  CREATE TABLE T2 AS
  SELECT * FROM other_table WHERE 1 = 0;

CURSOR CUR IS ...
BEGIN

       FOR rec IN CUR
       LOOP
       --Do stuff here
         END LOOP;

Drop table T1;
Drop table T2;
END;
/

2)

DECLARE

CURSOR CUR IS ...
BEGIN

  CREATE TABLE T1
    (
      col1 VARCHAR2(128),
      col2  VARCHAR2(128),
      col3 NUMBER(3) NOT NULL,
      col3 FLOAT(100)
    );

  CREATE TABLE T2 AS
  SELECT * FROM other_table WHERE 1 = 0;

  FOR rec IN CUR
  LOOP
  --Do stuff here
  END LOOP;

  Drop table T1;
  Drop table T2;
END;
/

(1)和(2)都不起作用.

(1) and (2) both don't work.

更新-为此我们需要立即执行吗?我怎么知道何时需要立即执行?我们还需要EXECUTE IMMEDIATE来截断游标中的表吗?

Update- Do we need EXECUTE IMMEDIATE for this? How do I know when I need EXECUTE IMMEDIATE? Do we also need EXECUTE IMMEDIATE for truncating tables in cursors?

推荐答案

通常，您不在PL/SQL过程中执行DDL(创建，更改，删除).如果您需要一个表来存储一些临时数据，则可以创建临时表.在您的情况下，我将首先创建表

Usually you don't perform DDLs (create, alter, drop) inside your PL/SQL procedure. If you need a table to store some temporary data, you can create temporary tables for this purpose. In your case I would first create the tables

CREATE GLOBAL TEMPORARY TABLE T1
  (
    col1 VARCHAR2(128),
    col2  VARCHAR2(128),
    col3 NUMBER(3) NOT NULL,
    col3 FLOAT(100)
  );

CREATE GLOBAL TEMPORARY TABLE T2 AS
SELECT * FROM other_table WHERE 1 = 0;

然后该过程将如下所示

DECLARE
CURSOR CUR IS ...
BEGIN
       FOR rec IN CUR
       LOOP
       --Do stuff here
         END LOOP;

DELETE FROM T1;
DELETE FROM T2;
END;
/

当然不会在那之后删除表，但是我想您想定期使用PL/SQL过程，而不仅仅是一次，对吧?

Of course the tables would not be dropped after that, but I suppose you want to use your PL/SQL procedure regularly, not only once, right?

如果您仍想在过程中执行DDL，则必须使用动态SQL (立即执行).但是，您必须知道DDL操作会执行隐式提交，因此您的过程将不会是单个原子事务.

If you still want to peform DDL in your procedure, then you must use dynamic sql (execute immediate). However you have to be aware that DDL operations perform implicit commits, so your procedure would not be a single atomic transaction.

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