一个关于存储过程的问题(高分相赠，不够可以加)

Question

我想用存储过程创建一个表，命令如下：
CREATE TABLE tablename (PKID NUMBER(12,0) NOT NULL, PC NUMBER(4,0) NULL, ZD NUMBER(4,3) NULL, SD NUMBER(4,2) NULL, JJ NUMBER(4,2) NULL, ZZ NUMBER(4,2) NULL, YM NUMBER(4,2) NULL, FJ NUMBER(4,2) NULL ,CO NUMBER(4,3) NULL, ZS NUMBER(4,3) NULL, SYX NUMBER(4,3) NULL, KWZ NUMBER(4,2) NULL, RL NUMBER(4,0) NULL, AIR NUMBER(4,2) NULL, RJ NUMBER(4,3) NULL, DC VARCHAR2(10) NULL, XJ NUMBER(4,0) NULL,  FOREIGN KEY (PKID) REFERENCES "LAB"."BH"(PKID))PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50) TABLESPACE "USER_DATA";其中 tablename 为一个从外面传送进来的变量表名（字符串），LAB为用户名，BH为企业编号表，"USER_DATA"为表空间。   帮我写一个存储过程实现这样一个功能。

Answer 1

不就是组装一个字符串然后用execute immediate来执行吗？
CREATE OR REPLACE PROCEDURE myproc (p_tblname VARCHAR2) IS
   w_sql_txt   VARCHAR2 (2000);
BEGIN
   w_sql_txt :=
          'CREATE TABLE :1 (PKID NUMBER(12,0) not null, PC NUMBER(4,0), '
       || 'ZD NUMBER(4,3), SD NUMBER(4,2), JJ NUMBER(4,2), ZZ NUMBER(4,2),'
       || ' YM NUMBER(4,2), FJ NUMBER(4,2) ,CO NUMBER(4,3), ZS NUMBER(4,3),'
       || ' SYX NUMBER(4,3), KWZ NUMBER(4,2), RL NUMBER(4,0), AIR NUMBER(4,2),'
       || ' RJ NUMBER(4,3), DC VARCHAR2(10), XJ NUMBER(4,0),'
       || ' FOREIGN KEY (PKID) REFERENCES LAB.BH(PKID))'
       || ' PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE'
       || ' ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50)'
       || ' TABLESPACE USER_DATA;';
   EXECUTE IMMEDIATE w_sql_txt
      USING          p_tblname;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END myproc;
/

Answer 2

create procedure name_f(p_tblname in VARCHAR2,p_lab in varchar2,p_BH in varchar2,p_USER_DATA in varchar2)
as
str varchar2(2000);
begin
str:='CREATE TABLE '||p_tblname||' (PKID NUMBER(12,0) NOT NULL, PC NUMBER(4,0)   NULL, ZD NUMBER(4,3) NULL, SD NUMBER(4,2) NULL, JJ NUMBER(4,2) NULL, ZZ NUMBER(4,2) NULL, YM NUMBER(4,2) NULL, FJ NUMBER(4,2) NULL ,CO NUMBER(4,3) NULL, ZS NUMBER(4,3) NULL, SYX NUMBER(4,3) NULL, KWZ NUMBER(4,2) NULL, RL NUMBER(4,0) NULL, AIR NUMBER(4,2) NULL, RJ NUMBER(4,3) NULL, DC VARCHAR2(10) NULL, XJ NUMBER(4,0) NULL,  FOREIGN KEY (PKID) REFERENCES '||p_lab||'.'||p_BH||'(PKID))PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50) TABLESPACE '||p_USER_DATA;
execute immediate str;
end;
/

Answer 3

EXECUTE IMMEDIATE str USING 是8i中的新特征。
当然我们应该也可以使用DBMS-SQL程序包来实现动态SQL。

Answer 4

To developer2002(开发者2002)
我在SQL*PLUS下编译提示
“警告：已创建的过程出现编译错误。”
TO beckhambobo(beckham)  
你的指点，在创建时也有上述提示。
所以，我想问一下是否是因为我用的ORACLE是8.0.5版本的问题，不是8I，如果是版本的问题，是否必须升级数据库，到8i或9i才能解决。再次感谢两位的支持。也要感谢ykliu1(river) 的宝贵提示。

Answer 5

Sorry!刚才更应该KingSunSha(弱水三千)表示感谢。

Answer 6

那最好升级吧，8.05不支持execute immediate

Answer 7

居然要在过程中创建表，不是太理解
805用dbms_sql包一样可以

Answer 8

To penitent(只取一瓢) :
能不能请你帮我写一个应用DBMS_SQl的例子，救救急，谢谢！

Answer 9

declare
  c number;
  d number;
  col_cnt integer;
  f boolean;
  rec_tab dbms_sql.desc_tab;
  col_num number;
  procedure print_rec(rec in dbms_sql.desc_rec) is
  begin
    dbms_output.new_line;
    dbms_output.put_line('col_type            =    '
                         || rec.col_type);
    dbms_output.put_line('col_maxlen          =    '
                         || rec.col_max_len);
    dbms_output.put_line('col_name            =    '
                         || rec.col_name);
    dbms_output.put_line('col_name_len        =    '
                         || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    '
                         || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    '
                         || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    '
                         || rec.col_precision);
    dbms_output.put_line('col_scale           =    '
                         || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    if (rec.col_null_ok) then
      dbms_output.put_line('true');
    else
      dbms_output.put_line('false');
    end if;
  end;
begin
  c := dbms_sql.open_cursor;  dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql);
 
  d := dbms_sql.execute(c);
 
  dbms_sql.describe_columns(c, col_cnt, rec_tab);/*
 * Following loop could simply be for j in 1..col_cnt loop.
 * Here we are simply illustrating some of the PL/SQL table
 * features.
 */
  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      exit when (col_num is null);
    end loop;
  end if;
 
  dbms_sql.close_cursor(c);
end;
/

Answer 10

嘿，楼主，该结贴了吧，很简单的问题呀，多看看书吧，动态SQL一章，就什么都明白了。
总是看到你这个帖子，搞的我总登进来看一次，浪费不少时间的说。呵呵！！！

Answer 11

楼上的：
什么书的动态SQL一章！能否提供一下！