Question

oracle存储过程动态建表

PROCEDURE up_CreateTable
(
tableName_in IN VARCHAR2
)
AS
SQL_in VARCHAR2(5000);
BEGIN
SQL_in := 'CREATE TABLE "'||tableName_in||'"
(
DM VARCHAR2(20) NOT NULL,
MC VARCHAR2(60) NOT NULL,
BZSM VARCHAR2(100)
)tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
);
alter table "'||tableName_in||'"
add constraint PK_'||tableName_in||' Primary key(DM)';
EXECUTE IMMEDIATE SQL_in ;
END;

Answer 1

需要用到动态sql。

1、首先要给用户赋予权限：

grant create any table to 用户名;

2、创建存储过程：

create procedure p_create_table
(v_sql varchar2)--输入建表语句
as 
begin
  execute immediate v_sql;
end;

3、执行：

Answer 2

PROCEDURE up_CreateTable
(
tableName_in IN VARCHAR2
)
AS
Declare SQL_in VARCHAR2(5000);
BEGIN
SQL_in := 'CREATE TABLE '||tableName_in||'
(
DM VARCHAR2(20) NOT NULL,
MC VARCHAR2(60) NOT NULL,
BZSM VARCHAR2(100)
)tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
)';
EXECUTE IMMEDIATE SQL_in ;
SQL_in :='alter table '||tableName_in||' add constraint PK_'||tableName_in||' Primary key(DM)';
EXECUTE IMMEDIATE SQL_in ;
END;