这两天一直在弄存储过程,稍微有点头绪,记录一下以备后用。
判断表是否存在,存在则将其删除。
CREATE OR REPLACE PROCEDURE PROC_DROPTABLE(reportName in varchar2) AS
v_tablename varchar(200);
BEGIN
SELECT table_name INTO v_tablename FROM all_tables WHERE table_name = reportName;
IF v_tablename IS NOT NULL THEN
EXECUTE IMMEDIATE 'drop table ' || reportName;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN;
END;
在JAVA中用JDBCTemplate调用:
String sql = "{call PROC_DROPTABLE(?)}";
getJdbcTemplate().execute(sql, new CallableStatementCallback() {
@Override
public Object doInCallableStatement(java.sql.CallableStatement cs) throws SQLException,
DataAccessException {
cs.setString(1, tableName);
cs.execute();
return null;
}
});