我们有一个框架,在需要的时候创建表格。但是Oracle不支持TABLE IF NOT EXISTS。有没有关于如何在使用jdbi时实现它的建议?
CREATE OR REPLACE FUNCTION fn_exists ( p_table IN VARCHAR2 ) RETURN BOOLEAN IS
l_dummy NUMBER;
l_found BOOLEAN := FALSE:
BEGIN
SELECT 1 INTO l_dummy
FROM user_tables
WHERE table_name = p_table;
l_found := SQL%FOUND;
RETURN ( l_found );
EXCEPTION
WHEN no_data_found THEN
RETURN ( FALSE );
WHEN OTHERS THEN
RAISE:
END fn_exists;
BEGIN
IF NOT fn_exists ( 'THE_TABLE' ) THEN
EXECUTE IMMEDIATE ' CREATE TABLE THE_TABLE ....';
END IF;
END;
CREATE OR REPLACE PROCEDURE pr_crt_if_not_exists
(
p_table IN VARCHAR2,
p_crt_sql IN VARCHAR2
) IS
BEGIN
IF NOT fn_exists ( p_table ) THEN
EXECUTE IMMEDIATE p_crt_sql;
END IF;
END pr_crt_if_not_exists;
BEGIN
pr_crt_if_not_exists ( 'THE_TABLE', 'CREATE TABLE THE_TABLE ...' );
pr_crt_if_not_exists ( 'THE_TABLE2', 'CREATE TABLE THE_TABLE2 ...' );
.
.
END;