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

[weidegong]

对Oracle不太熟悉；希望能给出一个简单的例子参考一下；另外我在一个网页上看到：“Oracle的sp中不能create table;…… ”不知道是不是这样。
请各位高手指点，谢谢

[weidegong]

execute immediate的用法好像是Oracle8i才开始有的，我现在用的是Oralce8数据库所以只能用第二种写法？

实际上，我是想在Delphi里调用存储过程来动态建立数据表，这样可能效果好一点；那个dbms_sql包能在Delphi等应用程序中调用吗？

[zjhclf]

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

[beckhambobo]

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

[zjhclf]

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

[weidegong]

：：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

[zjhclf]

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了么？？

[weidegong]

好像还不OK：（
警告：已创建的过程出现编译错误。

我看上面的代码似乎l_str应该声明（declare）吧，还有cursor_i等，是不是也需要声明？
我装的是Oracle8.05。你上面的代码执行通过了？

[klbt]

学习中

[beckhambobo]

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;
/

[weidegong]

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

[weidegong]

错误提示为：

SQL> show errors;
PROCEDURE GENERATETABLE出现错误：

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/9 PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时：
:=.(@%;

[weidegong]

还望各位多多帮助呀！折腾了半天也没搞定：（

我把上面“寂寞撩人”兄的代码改了一下，创建过程成功，但是还是不对：

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

[beckhambobo]

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

[beckhambobo]

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

[black_snail]

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

[weidegong]

哇哈哈，终于搞定了，兴奋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 用户

[yangqingdelphi]

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

[weidegong]

好像还是有些问题，说是权限不足。但是我把登陆用户的所有权限给了呀：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

[l2g32003]

grant create table to 登陆用户 必须的

[yxzhjm]

oracle 9i有存储过程吗？