知识点的梳理:
- 使用动态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:权限不足"错误: 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 |