Oracle12C--动态SQL(60)

知识点的梳理：

• 使用动态SQL可以在依赖对象不存在时创建子程序；
• 动态SQL主要利用EXECUTE IMMEDIATE 语句执行DML,DDL，DCL等语句操作；
• 如果使用了绑定变量，则必须在EXECUTE IMMEDIATE中使用USING子句设置所需要的绑定变量；
• 使用RETURNING或RETURN语句可以接收查询或更新后的返回结果；
• 使用批处理可以一次性将数据库中取回的多个数据保存在集合中，或者使用FORALL 将多个绑定参数设置到动态SQL 中；

    

• 动态SQL简介
  • PL/SQL程序有一个特点：所操作的数据库对象必须存在，否则创建的子程序会出现问题，这种操作被称为静态SQL操作；
  • 动态SQL可让用户在定义程序时不指定具体的操作对象，在执行时动态传入需要的数据库对象，让程序更加灵活；
    • 相对与静态SQL，动态SQL在程序编译时无法检测数据对象是否存在，是否有指定的操作权限，只能等运行时才能发现这些错误；
  • Oracle构建动态SQL，可以使用NDS或DBMS_SQL包两种方式完成。在Oracle11g中，动态SQL的构建主要依靠NDS方式来完成，因为NDS方式完成的动态SQL运行速度比DBMS_SQL包快，同时在NDS中也使用了比DBMS_SQL包中更简单的语法：
    • NDS主要使用的是EXECUTE IMMEDIATE语句；
    • 处理多行数据，可用游标，以及批量SQL；
• 举个栗子
  • 示例1：利用动态SQL在执行时创建一张数据表

CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_statement VARCHAR2(200) ; -- 定义操作的SQL语句 v_count NUMBER ; -- 保存表中记录 BEGIN SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ; IF v_count = 0 THEN -- 数据表不存在 v_sql_statement := 'CREATE TABLE ' || p_table_name || ' ( id NUMBER , name VARCHAR2(30) NOT NULL , CONSTRAINT pk_id_' || p_table_name || ' PRIMARY KEY(id)) ' ; -- 创建数据表 EXECUTE IMMEDIATE v_sql_statement ; -- 执行动态SQL END IF ; v_sql_statement := 'SELECT COUNT(*) FROM ' || p_table_name ; -- 查询数据表记录 EXECUTE IMMEDIATE v_sql_statement INTO v_count ; -- 执行动态SQL并保存数据记录 RETURN v_count ; END ; /

流程图：

• 接上例：编写PL/SQL块调用函数

BEGIN DBMS_OUTPUT.put_line('数据表记录：' || get_table_count_fun('mldnjava')) ; END ; /

本程序会直接输出get_table_count_fun()函数的返回结果，在函数操作中，如果要操作的数据表不存在则会自动创建，本程序中输入的mldnjava数据表不存在，执行后可以发现此表自动创建；

问题：在执行get_table_count_fun()函数时，可能会出现"ORA-01031：权限不足"错误： 该程序是在c##scott用户下执行的，但是默认情况下在c##scott.get_table_count_fun()函数中无法创建数据表，此时需要将"CREATE ANY TABLE"的权限授予c##scott用户，可执行如下sql语句： CONN sys/change_on_install AS SYSDBA ; GRANT CREATE ANY TABLE TO c##scott ; CONN c##scott/tiger ; 授权之后，重新使用scott登录，就可以正常使用get_table_count_fun()了

• 一些问题
  • 提示1：如果不使用EXECUTE IMMEDIATE，程序会出现错误
    • 下面的程序由于要操作的数据库对象可能不存在，如果用户直接使用DDL或DML操作就会出现编译错误；
    • 示例：直接在程序中编写DDL或DML

CREATE OR REPLACE FUNCTION get_table_count_fun(p_table_name VARCHAR2) RETURN NUMBER AS v_sql_statement                VARCHAR2(200) ;                        -- 定义操作的SQL语句 v_count                        NUMBER ;                        -- 保存表中记录 BEGIN SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name=UPPER(p_table_name) ; IF v_count = 0 THEN                                        -- 数据表不存在 -- 错误：无法直接使用DDL操作 CREATE TABLE p_table_name ( id        NUMBER        , name        VARCHAR2(30)        NOT NULL , CONSTRAINT id_pk PRIMARY KEY(id)) ; END IF ; -- 错误：查询数据表不存在 SELECT COUNT(*) INTO v_count FROM p_table_name ; RETURN v_count ; END ; /

此时发现，在创建数据表的执行语句上出现了错误，这是因为PL/SQL早期绑定特性，所以导致无法执行DDL