1.无绑定变量的动态语句:静态字符串的动态语句形式,因为Oracle不支持条件下'存在则删除'语法,使用静态字符串作为NDS语句最主要的用途在于,当对象存在时,在重新创建之前先删除已存在的对象。
BEGIN
FOR i IN (SELECT NULL FROM user_objects WHERE object_name = 'TRANSATION')
LOOP
EXECUTE IMMEDIATE 'DROP TABLE TRANSATION CASCADE CONSTRAINTS';
END LOOP;
END;
改进:
BEGIN
FOR i IN (SELECT object_name, object_type
FROM user_objects
WHERE regexp_like(object_name, 'TRANSACTION.'))
LOOP
IF i.object_type = 'SEQUENCE'
THEN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
ELSIF i.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name ||
' cascade constraints';
END IF;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE insert_lookup(table_name VARCHAR2,
lookup_table VARCHAR2,
lookup_column VARCHAR2,
lookup_type VARCHAR2,
lookup_code VARCHAR2 := ' ',
lookup_meaning VARCHAR2) IS
stmt VARCHAR2(2000);
BEGIN
stmt := 'insert into ' || dbms_assert.simple_sql_name(table_name) ||
' values ' || '(common_lookup_s1.nextval ' || ',' ||
dbms_assert.enquote_literal(lookup_table) || ',' ||
dbms_assert.enquote_literal(lookup_column) || ',' ||
dbms_assert.enquote_literal(lookup_type) || ',' ||
dbms_assert.enquote_literal(lookup_code) || ',' ||
dbms_assert.enquote_literal(lookup_meaning) ||
',3,sysdate,3,sysdate)';
--dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt;
END insert_lookup;
2.带绑定变量的动态语句:
CREATE OR REPLACE PROCEDURE insert_lookup_1(table_name VARCHAR2,
lookup_table VARCHAR2,
lookup_column VARCHAR2,
lookup_type VARCHAR2,
lookup_code VARCHAR2,
lookup_meaning VARCHAR2) IS
stmt VARCHAR2(2000);
BEGIN
stmt := 'insert into ' || dbms_assert.simple_sql_name(table_name) ||
' values ' || '(common_lookup_s1.nextval ', ||
' :lookup_table ', || ' :lookup_column ', || ' :lookup_type ', ||
' :lookup_code ', || ' :looup_meaning', ||
' 3,sysdate,3 sysdate)';
dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt;
USING lookup_table, lookup_column, lookup_type, lookup_code, lookup_meaning;
END;
DECLARE
TYPE lookup_record IS RECORD --record structure
(
lookup_type VARCHAR2(30),
lookup_code VARCHAR2(5),
lookup_meaning VARCHAR2(255));
lookup_cursor SYS_REFCURSOR;
lookup_row lookup_record;
stmt VARCHAR2(2000);
BEGIN
stmt := 'SELECT common_lookup_type,' || ' common_lookup_code,' ||
'common_lookup_meaning' || ' from common_lookup ' ||
'where regexp_like(common_lookup_type,:input)';
--dbms_output.put_line(stmt);
OPEN lookup_cursor FOR stmt
USING '(CR|D)E(D|B)IT';
LOOP
FETCH lookup_cursor
INTO lookup_row;
EXIT WHEN lookup_cursor%NOTFOUND;
dbms_output.put_line('[' || lookup_row.lookup_type || '][' ||
lookup_row.lookup_code || ']');
END LOOP;
CLOSE lookup_cursor;
END;