使用创建和游标的PL/SQL过程。

Can we have a Procedure with

我们可以做个手术吗

First create a table suppose

首先创建一个表假设

  create table INCOME_GROUP(income_compare_groups varchar(100)) ;

Then insert data into this table.

然后将数据插入到该表中。

 insert into INCOME_GROUP values (10-20);

Then Use this table into a cursor.

然后将该表使用到游标中。

 CURSOR c1 IS(select *from INCOME_GROUP);

For Example I am doing this.

例如，我正在做这个。

BEGIN 
create table INCOME_GROUP(income_compare_groups varchar(100)) ;
DECLARE

   CURSOR c1 IS(select * income_Group);
BEGIN
   FOR acct IN c1 LOOP  -- process each row one at a time

      INSERT INTO temp_test
            VALUES (acct.income_compare_groups);


   END LOOP;
   COMMIT;
END;
END;

But I am getting some Error.

但是我得到了一些错误。

ORA-06550: line 2, column 4:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge

After reading the comments I tried this -

读了评论后，我尝试了这个-

BEGIN 
     EXECUTE IMMEDIATE 'create table INCOME_GROUP
 (
  income_compare_groups varchar(100)
  )'; 
DECLARE

   CURSOR c1 IS
     (select * from
       INCOME_GROUP
    );



BEGIN

 FOR acct IN c1 LOOP  -- process each row one at a time



      INSERT INTO temp_test
            VALUES (acct.income_compare_groups, null);


   END LOOP;
   COMMIT;
END;
END;

But seems it is not creating table.!!!!

但似乎它不是在创造桌子。

3 个解决方案

#1

You can do it like this:

你可以这样做:

create or replace procedure cpy_inc_comp_grps
as
cur_1 sys_refcursor;
compare_group varchar2(100);
begin
  execute immediate 'create table income_group(income_compare_groups varchar2(100))';
  open cur_1 for 'select income_compare_groups from income_group';

  LOOP
  FETCH cur_1 INTO compare_group;
    DBMS_OUTPUT.PUT_LINE('INSERT INTO temp_test VALUES (rec.income_compare_groups');
  EXIT WHEN cur_1%NOTFOUND;
  END LOOP;
  close cur_1;
  execute immediate 'drop table income_group';
end;

And test it with the following code:

测试代码如下:

begin
  cpy_inc_comp_grps;
end;

You have to replace the dbms_output.put_line(...) part with whatever inserts you want to do.

您必须用您想要做的任何插入来替换dbms_output.put_line(…)部分。

#2

It must be like this:

一定是这样:

DECLARE
    cur SYS_REFCURSOR;
     v_income_compare_groups VARCHAR(100);
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE INCOME_GROUP(income_compare_groups VARCHAR(100))';
    OPEN cur FOR 'SELECT * income_Group';
    LOOP
        FETCH cur INTO v_income_compare_groups;
        EXIT WHEN cur%NOTFOUND;
        INSERT INTO temp_test VALUES (v_income_compare_groups);
    END LOOP;
    CLOSE cur;
    COMMIT;
END;

You have to use dynamic Cursor because when you compile the package then the table INCOME_GROUP does not exist yet and you would get an error at CURSOR c1 IS(select * income_Group);

您必须使用动态游标，因为当您编译包时，表incom_group还不存在，您将在游标c1处得到一个错误(选择* incom_group);

However, there are several issue: You will get an error if the table already exist. You have to check this first or write an exception handler. The procedure is useless because you first create an (empty) table and then you select it - it will never select anything!

但是，有几个问题:如果该表已经存在，您将会得到一个错误。您必须首先检查这个问题，或者编写一个异常处理程序。这个过程是无用的，因为您首先创建一个(空的)表，然后您选择它——它将永远不会选择任何东西!

#3

Try this.

试试这个。

execute immediate 'create table INCOME_GROUP(income_compare_groups varchar(100))';

#1