database - 带有创建和游标的 PL/SQL 过程

Question

我们可以有一个程序吗

假设首先创建一个表

  create table INCOME_GROUP(income_compare_groups varchar(100)) ;

然后向这个表中插入数据。

 insert into INCOME_GROUP values (10-20);

然后将这个表用到游标中。

 CURSOR c1 IS(select *from INCOME_GROUP);

例如我正在做这个。

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;

但是我遇到了一些错误。

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

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

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;

但它似乎没有创建表。!!!!

Answer 1

你可以这样做:

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;

并使用以下代码进行测试:

begin
  cpy_inc_comp_grps;
end;

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