PL/SQL 动态 SQL

来源：互联网发布：氨基酸数据库编辑：程序博客网时间：2024/05/18 15:05

-- Start

什么是动态 SQL 呢？它是运行时才动态拼接和执行的 SQL。相对于静态 SQL，它最大的优势就是灵活，可以执行 DDL 语句。缺点是它没有经过预编译，运行时可能由于拼接错误、权限等问题失败。我们可以通过下面两种方式执行动态 SQL。

EXECUTE IMMEDIATE

-- 创建测试表  CREATE TABLE student  (    id number(10),    name varchar2(20)  );    -- 创建测试存储过程  CREATE OR REPLACE PROCEDURE insert_student  (      id IN OUT student.id%TYPE,      name student.name%TYPE  ) IS      max_id student.id%TYPE;  BEGIN      select max(id) into max_id from student;        if id <= max_id then        id := max_id + 1;      end if;        insert into student values (id, name);  END;    -- 测试动态 SQL  DECLARE    id number(10);    name varchar2(20);        TYPE name_list IS TABLE OF student.name%TYPE;    names name_list := name_list();    studentCur  SYS_REFCURSOR;BEGIN    -- 无绑定变量    EXECUTE IMMEDIATE 'delete from student';        -- 输入绑定变量    id := 1;    name := 'Shang Bo';    EXECUTE IMMEDIATE 'insert into student values (:a, :b)' USING id, name;    EXECUTE IMMEDIATE 'insert into student values (:x, :y)' USING 2, 'Zhang San';        -- RETURNING INTO    EXECUTE IMMEDIATE 'update student set name = :a where id = :b returning name into :name' USING 'Wang wu', 1 RETURNING INTO name;    DBMS_OUTPUT.PUT_LINE('name = ' || name);        -- RETURNING BULK COLLECT INTO    EXECUTE IMMEDIATE 'update student set name = :a returning name into :names' USING 'Wang wu' RETURNING BULK COLLECT INTO names;    FOR i IN names.FIRST()..names.LAST() LOOP          DBMS_OUTPUT.PUT_LINE(i || '-' || names(i));        END LOOP;        -- select 返回一行    id := 1;    name := '';    EXECUTE IMMEDIATE 'select name from student where id = :b' INTO name USING id;    DBMS_OUTPUT.PUT_LINE('name = ' || name);        -- select 返回多行  EXECUTE IMMEDIATE 'select name from student' BULK COLLECT INTO names;    FOR i IN names.FIRST()..names.LAST() LOOP          DBMS_OUTPUT.PUT_LINE(i || '-' || names(i));        END LOOP;    -- select 返回多行  OPEN studentCur FOR 'select name from student';  LOOP    FETCH studentCur INTO name;    EXIT WHEN studentCur%NOTFOUND;    DBMS_OUTPUT.PUT_LINE('name = ' || name);  END LOOP;  CLOSE studentCur;      -- 调用存储过程，输入输出绑定变量    id := 1;    EXECUTE IMMEDIATE 'BEGIN insert_student(:a, :b); END;' USING IN OUT id, 'Li Si';    DBMS_OUTPUT.PUT_LINE('id = ' || id);  END;

DBMS_SQL

下面是一个简单的例子，有关更多如果使用它的例子请参考 “Oracle Database PL/SQL Packages and Types Reference”

DECLARE  cursor_name INTEGER;  rows_processed INTEGER;BEGIN  -- 第一步：打开游标  cursor_name := dbms_sql.open_cursor;    -- 第二步：解析游标  DBMS_SQL.PARSE(cursor_name, 'DELETE FROM employees WHERE salary > :x',DBMS_SQL.NATIVE);    -- 第三步：绑定变量  DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', 9999);    -- 第四步：执行  rows_processed := DBMS_SQL.EXECUTE(cursor_name);    -- 第五步：关闭游标  DBMS_SQL.CLOSE_CURSOR(cursor_name);    -- 异常处理部分  EXCEPTION    WHEN OTHERS THEN      DBMS_SQL.CLOSE_CURSOR(cursor_name);END;

--更多参见：Oracle PL/SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-06-02

-- Created by ShangBo on 2015-03-08

-- End

0 0