java动态更新oracle表_java调用oracle存储过程动态生成表

1.给用户授予权限

即便是用户有了connect,resource权限了你还是要给他授予权限的，否则在java调用时候会提示你权限不够。

grant create any table to userxxx;

2.创建存储过程

CREATE OR REPLACE PROCEDURE createAnyTable(tableName_in IN VARCHAR2)

AS

SQL_in VARCHAR2(5000);

BEGIN

SQL_in := 'CREATE TABLE ' || tableName_in || '

(

id    number NOT NULL,

name  VARCHAR2(60) NOT NULL,

age  number

)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(id)';

EXECUTE IMMEDIATE SQL_in;

END;

3.用java进行测试

package com.hdx.test;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

public class TestProcedure {

public static void main(String[] args) throws Exception {

Connection conn = getOracleConn();

CallableStatement cs = conn.prepareCall("{call up_CreateTable(?)}");

cs.setString(1, "tableTest");

boolean r = cs.execute();

System.out.println(r);

}

public static Connection getOracleConn() throws Exception {

Connection conn = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test1", "hdx");

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

}