[求助】使用Oracle的存储过程动态建立数据库表

Question

oracle 的pl/sql中不支持数据操作语言，所以如果想这么做，只能通过动态sql,有2中
1。本地动态sql。
l_str:='create table a ..';
execute immediate(l_str);
2.dbms_sql包。
给你一个我的动态建立seq的脚本研究一把
 cursor_i:=dbms_sql.open_cursor;
     l_sql:='create sequence '||l_table_name||'_seq increment by 1 start with '
     ||to_char(l_max_id)||' minvalue 1 maxvalue 999999999999999999999999999 nocycle noorder cache 20';
     DBMS_SQL.PARSE(cursor_i,l_sql,DBMS_SQL.NATIVE);
        cursor_ret:=dbms_sql.execute(cursor_i);
        dbms_sql.close_cursor(cursor_i);

Answer 1

execute immediate的用法好像是Oracle8i才开始有的，我现在用的是Oralce8数据库所以只能用第二种写法？实际上，我是想在Delphi里调用存储过程来动态建立数据表，这样可能效果好一点；那个dbms_sql包能在Delphi等应用程序中调用吗？

Answer 2

我说的只是pl/sql，delphi应该也可以建表吧，要不你去delphi区问问。
dbms_sql包可以在oracle中的存储过程中调用。不知能不能帮到你

Answer 3

delphi用dbms_sql干嘛，你只要在delphi执行存储过程就可以了

Answer 4

同意beckhambobo(beckham)，这其实跟你用什么前台没有关系。

Answer 5

：：delphi用dbms_sql干嘛，你只要在delphi执行存储过程就可以了其实我本意就是这样的；我就是不太会写存储过程；这个存储过程有一个参数就是要产生的数据库的表名，可是表名怎么和sql语句结合起来……
create or replace Procedure generateTable(TableName in varchar2) 
is
begin
create table office  (
   OfficeNum          VARCHAR2(6)                      not null,
   OfficeName         VARCHAR2(80)                     not null,
   constraint PK_OFFICE primary key (OfficeNum)
);
end generateTable

Answer 6

create or replace Procedure generateTable(TableName in varchar2) 
is
begin
  l_str:='create table '||tablename||'(OfficeNum          VARCHAR2(6)                      not null,OfficeName         VARCHAR2(80)                     not null,
   constraint PK_OFFICE primary key (OfficeNum)
);' cursor_i:=dbms_sql.open_cursor;
 DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
 cursor_ret:=dbms_sql.execute(cursor_i);
 dbms_sql.close_cursor(cursor_i);end;
end generateTable不就ok了么？？

Answer 7

好像还不OK：（
警告：已创建的过程出现编译错误。我看上面的代码似乎l_str应该声明（declare）吧，还有cursor_i等，是不是也需要声明？
我装的是Oracle8.05。你上面的代码执行通过了？

Answer 8

create or replace Procedure generateTable(p_TableName in varchar2) 
is
str varchar2(100);
begin
str:='create table '||p_tablename||'(
   OfficeNum          VARCHAR2(6)                      not null,
   OfficeName         VARCHAR2(80)                     not null,
   constraint PK_OFFICE primary key (OfficeNum)
)';  --grant create any table to your_user
execute immediate str;
end generateTable;
/

Answer 9

beckhambobo(beckham) 
 execute immediate 的用法好像是Oracle8i才有，我现在用的Oracle版本是Oracle8.05所以，还是没有编译通过。

Answer 10

错误提示为：SQL> show errors;
PROCEDURE GENERATETABLE出现错误：LINE/COL ERROR
-------- -----------------------------------------------------------------
10/9     PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时：
         :=.(@%;

Answer 11

还望各位多多帮助呀！折腾了半天也没搞定：（我把上面“寂寞撩人”兄的代码改了一下，创建过程成功，但是还是不对：SQL> create or replace Procedure generateTable(TableName in varchar2) 
  2  is
  3  l_str varchar2(1000);
  4  cursor_i INTEGER;
  5  cursor_ret INTEGER;
  6  begin
  7    l_str:='create table '||TableName||' (nameZ VARCHAR2(6) not null,ageZ VARCHAR2(80) not null);
';
  8    cursor_i:=dbms_sql.open_cursor;
  9   DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
 10   cursor_ret:=dbms_sql.execute(cursor_i);
 11   dbms_sql.close_cursor(cursor_i);
 12  end generateTable;
 13  /过程已创建。SQL> execute generateTable('testOK');
begin generateTable('testOK'); end;*
错误位于第1行：
ORA-00911: 无效字符
ORA-06512: 在"SYS.DBMS_SYS_SQL", line 491
ORA-06512: 在"SYS.DBMS_SQL", line 32
ORA-06512: 在"TEST.GENERATETABLE", line 9
ORA-06512: 在line 1

Answer 12

l_str:='create table '||TableName||' (nameZ VARCHAR2(6) not null,ageZ VARCHAR2(80) not null)';   --注意此处少了";"分号

Answer 13

l_str:='create table '||TableName||' (nameZ VARCHAR2(6) not null,ageZ VARCHAR2(80) not null)';   --注意此处少了";"分号

Answer 14

上面回答得很好啊，动态SQL基本上就是这两种办法

Answer 15

动态SQL有两种方法能解决
Execute IMMEDIATE
DBMS_SQL包
能Delphi调用的是存储过程名。不用调DBMS_SQL。

Answer 16

好像还是有些问题，说是权限不足。但是我把登陆用户的所有权限给了呀：DBA、SQL> create or replace Procedure generateTable(TableName in varchar2) 
  2  is
  3  l_str varchar2(1000);
  4  cursor_i INTEGER;
  5  cursor_ret INTEGER;
  6  begin
  7    l_str:='create table ' || TableName || ' (nameZ varchar2(50) not null,ageZ varchar2(80))';
  8    cursor_i:=dbms_sql.open_cursor;
  9   DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
 10   cursor_ret:=dbms_sql.execute(cursor_i);
 11   dbms_sql.close_cursor(cursor_i);
 12  end generateTable;
 13  /过程已创建。SQL> execute generateTable('test10');
begin generateTable('test10'); end;*
错误位于第1行：
ORA-01031: 权限不足
ORA-06512: 在"SYS.DBMS_SYS_SQL", line 491
ORA-06512: 在"SYS.DBMS_SQL", line 32
ORA-06512: 在"TEST.GENERATETABLE", line 9
ORA-06512: 在line 1

Answer 17

grant create table to 登陆用户  必须的

Answer 18

哇哈哈，终于搞定了，兴奋ing……总结一下：在Oracle的sp中不能出现DDL语句.因为所有的DDL语句(create,drop,alter,truncate等)都是显式带有commit命令,Oracle的sp中不能有显式commit的存在.如果你非要在sp中建表或者删除表的话,你可以用动态SQL来完成隐式commit. 
在Oracle中,可以使用DBMS_SQL包和execute immediate ‘……’来执行动态SQL,不过要注意的是execute immediate是Oracle8i才推出的新特性,在Oracle8及以前的版本中是不能用这种方式的. 
因为我用的数据库系统是Oracle8.05,所以只能使用DBMS_SQL包；
同时为了生成表，必须 grant create table to 用户