如何用oracle存储过程动态建表..

Question

如何写一oracle存储过程来动态create表...其中的列名是通过参数传过来的..而且都是不确定个数...

Answer 1

-- 创建一个内嵌表的类型
CREATE OR REPLACE PACKAGE PKG_TYPES AS
  TYPE col_definitions IS TABLE OF VARCHAR2(30);
END PKG_TYPES;-- 只处理了表名和字段，没有约束，主键等，楼主有兴趣可以自己试着加进去
CREATE OR REPLACE PROCEDURE prc_create_table(
  p_table_name VARCHAR2,
  p_columns    PKG_TYPES.col_definitions
) AS
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'CREATE TABLE ' || p_table_name || ' (';
  
  FOR i IN p_columns.FIRST .. p_columns.LAST LOOP
    v_sql := v_sql || p_columns(i);
    
    IF i != p_columns.LAST THEN
      v_sql := v_sql || ',';
    END IF;
  END LOOP;
  
  v_sql := v_sql || ')';
  
  EXECUTE IMMEDIATE v_sql;
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;-- 测试创建一个表
BEGIN
  prc_create_table('MYTEST_TABLE', PKG_TYPES.col_definitions('id number','name varchar2(20)','birth_date date'));
END;-- 显示结果
desc mytest_table;
TABLE mytest_table
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER                      
 NAME                                               VARCHAR2(20)                
 BIRTH_DATE                                         DATE 

Answer 2

请问3楼...那如何用java来调用..怎么调用？？
 CallableStatement cstmt =conn.prepareCall("{call prc_create_tablety}") ;
    cstmt.executeUpdate();
这里参数怎么传进去？

Answer 3

-- 创建一个内嵌表的类型
CREATE TYPE col_definitions IS TABLE OF VARCHAR2(30);-- 创建一个存储过程来创建表
CREATE OR REPLACE PROCEDURE prc_create_table(
  p_table_name VARCHAR2,
  p_columns    col_definitions
) AS
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'CREATE TABLE ' || p_table_name || ' (';
  
  FOR i IN p_columns.FIRST .. p_columns.LAST LOOP
    v_sql := v_sql || p_columns(i);
    
    IF i != p_columns.LAST THEN
      v_sql := v_sql || ',';
    END IF;
  END LOOP;
  
  v_sql := v_sql || ')';
  
  EXECUTE IMMEDIATE v_sql;
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
// 准备一个数组
String[] cols = {"id number","name varchar2(20)","birth_date date"};// 获取数据库连接
Connection conn = OracleHelper.getConnection();// 创建一个数组描述实例
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("col_definitions".toUpperCase(), conn);// 创建一个oracle.sql.ARRAY数组
ARRAY array= new ARRAY(arraydesc, conn, cols);// 调用存储过程
CallableStatement cstmt = conn.prepareCall("{call prc_create_table(?,?)}");// 设置参数
cstmt.setString(1, "MY_TEST_TABLE");
cstmt.setArray(2, array);// 执行
cstmt.execute();
conn.close();

Answer 4

按照你那样做下来..在oracle里可以测试成功...可是用java调用的时候...execute执行成功..可是在数据库就是没反应...?

Answer 5

把存储过程的
EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
去除，可能出错了，没显示错误代码来

Answer 6

java调用报了这个错...在pl\sql直接执行可以成功
java.sql.SQLException: ORA-00904: : 无效的标识符
ORA-06512: 在"DAYANDEV.PRC_CREATE_TABLE", line 19
ORA-06512: 在line 1