每次建表的时候传递参数作为表名.在过程中用动态SQL. create or replace procedure pro_name(p_name in varchar2) as strsql varchar2(200); begin strsql:='create table '||p_name||' ( id number,name varchar2(200))'; execute immediate strsql; ..... end;
存储过程里面不可以用create table语句吗?我怎样才能动态建表?例一create or replace function test return varchar2 asv_cursor number;v_string varchar2(200);v_row number;beginv_cursor:=dbms_sql.open_cursor;--v_string:='update fnd_user a set a.description=''fred'' where a.user_id=1055';v_string:='create table testdb (text varchar2(200))';dbms_sql.parse(v_cursor,v_string,dbms_sql.native);v_row:=dbms_sql.execute(v_cursor);dbms_sql.close_cursor(v_cursor);return ('成功执行');exceptionwhen others thendbms_sql.close_cursor(v_cursor);return ('执行失败!'||sqlcode||sqlerrm);raise;end;例二CREATE OR REPLACE PROCEDURE Test1 ISv_SqlVARCHAR2(200); Cursor_create VARCHAR2(100);ResultNUMBER;BEGINv_Sql := 'CREATE TABLE T1(A VARCHAR2(200))';cursor_create := dbms_sql.open_cursor;DBMS_SQL.PARSE(cursor_create, v_SQL,dbms_sql.native);Result := dbms_sql.EXECUTE(cursor_create);DBMS_SQL.close_cursor(cursor_create);END Test1;
create or replace procedure pro_name(p_name in varchar2) as
strsql varchar2(200);
begin
strsql:='create table '||p_name||' ( id number,name varchar2(200))';
execute immediate strsql;
.....
end;