探讨动态sql扩展的应用

wabjtam123 贡献于2016-06-16

作者 liangjb  创建于2015-11-28 01:33:00   修改者liangjb  修改于2015-11-28 01:47:00字数18754

文档摘要:Oracle编译PL/SQL程序块分为两种:静态SQL与动态SQL 所谓静态 SQL,即早期绑定(early binding),指在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象,大多数的编译情况属于这种类型。 而动态SQL,即后期绑定(late binding),是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。 本文主要就动态SQL的开发进行讨论,并在最后给出一些实际开发的技巧。
关键词:

探讨动态SQL扩展的应用 IT-BG /  张荣志 摘要 Oracle编译PL/SQL程序块分为两种:静态SQL与动态SQL 所谓静态 SQL,即早期绑定(early binding),指在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象,大多数的编译情况属于这种类型。 而动态SQL,即后期绑定(late binding),是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。 本文主要就动态SQL的开发进行讨论,并在最后给出一些实际开发的技巧。 关键词:动态SQL 绑定变量 编译 分析 1. 动态SQL用途和应用场景 理解了动态SQL编译的原理,也就掌握了其基本的开发思想。动态SQL在执行前是 “不确定”的SQL,那其执行就有其相应的特点。 动态SQL一般用在如下场景: 1) 执行数据定义语句(例如CREATE TABLE,TRUNCATE TABLE等等);执行数据控制语句(例如GRANT语句);执行会话控制语句(例如ALTER SESSION);这些与INSERT,UPDATE,DELETE语句不同,上述语句是不能在PL/SQL程序中直接运行的。 2) 为了开发灵活和通用的程序,多数情况下是利用数据字典来生成相关脚本。 3) 运用在复杂查询和报表环境中,在这两种情况下,查询参数是由用户的输入开关和输入项而定的,甚至查询出的字段也根据查询参数有所不同、 Oracle中动态SQL可以通过本地动态SQL来执行,也可以通过DBMS_SQL包来执行。 2. EXECUTE IMMEDIATE语法 Oracle中提供了EXECUTE IMMEDIATE语句来执行动态SQL,语法如下: 动态SQL语法 EXECUTE IMMEDIATE dynamic_string [INTO {define_variable[,define_variable]…|record}] [BULK COLLECT INTO collection_name|host_array_name] [USING [IN|OUT|IN OUT] bind_argument[,[IN|OUT|IN OUT] bind_argument]…] [{RETURNING|RETURN} INTO bind_argument[, bind_argument]…] 2.1 执行DDL、DCL语句 在PL/SQL程序中执行数据定义语句、数据控制语句、会话控制语句必须使用动态SQL语句。 以下的例子是根据给定的表名称和字段名称动态生成相应的表。 但在表生成之前,首先要判断该表是否存在,如果存在的话则首先删除与其该表相关的的外键关系,然后再删除该表;最后根据给定的字段列表生成相关表。 CREATE OR REPLACE PROCEDURE sp_exec_DDL(iTableName VARCHAR,iColumnList VARCHAR) AS vTableExists INT; --判断该表是否存在 vTableName VARCHAR2(32); --经过处理的表名称 vSQL VARCHAR2(4000); --创建表的SQL vConstraintSQL VARCHAR2(200); --删除外键的SQL CURSOR cur_getForeignKey (curTableName VARCHAR) is --带变量的游标获取外键SQL SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name FROM user_constraints WHERE constraint_type='R' AND r_constraint_name=(SELECT constraint_name FROM user_constraints WHERE TABLE_NAME=curTableName AND CONSTRAINT_TYPE='P'); BEGIN vTableName:=UPPER(LTRIM(RTRIM(SUBSTR(iTableName,1,32)))); vTableExists:=0; SELECT COUNT(*) INTO vTableExists FROM USER_TABLES WHERE table_name=vtableName; IF vTableExists=1 THEN --EXECUTE IMMEDIATE 'DROP TABLE '||TableName||' CASCADE CONSTRAINTS'; --以上语句与下列语句是等效的,即首先删除与该表相关的外键,再删除该表 OPEN cur_getForeignKey(vTableName); LOOP FETCH cur_getForeignKey INTO vConstraintSQL; EXIT WHEN cur_getForeignKey%NOTFOUND; EXECUTE IMMEDIATE vConstraintSQL; END LOOP; CLOSE cur_getForeignKey; --删除相关表 EXECUTE IMMEDIATE 'DROP TABLE '||vTableName; END IF; --根据字段重新生成该表 vSQL:='CREATE TABLE '||vTableName||'('||iColumnList||')'; EXECUTE IMMEDIATE vSQL; END; 2.2 构造通用SQL处理 由于动态SQL特有的灵活性,我们很容易的按照某种共性去构造通用和重用功能的代码,例如基于某个表的动态字段查询; 但凡事有利有弊;首先动态SQL语句无法在编译前期检查SQL是否正确,必须等到运行期才会发现问题;其次静态SQL是一次解析,多次执行,虽然动态SQL也可以使用绑定变量的方式,但是也会带来一些意想不到的性能问题,例如绑定变量在SQL要访问的表存在数据倾斜时会提供错误的执行计划;最后动态SQL语句可读性较差,比较难以维护。 下面我们就以比较经典的分页功能为例: CREATE OR REPLACE Procedure sp_exec_dynamic_page ( i_tablename VARCHAR2, --表名 employees e,departments d i_tablecolumn VARCHAR2, --查询列 a.employee_id,b.department_name i_where VARCHAR2, --查询条件 b.department_name like 'S%' i_ordercolumn VARCHAR2, --排序 b.department_name desc i_pagesize NUMBER, --每页大小 20 i_curpage NUMBER, --当前页 6 o_rowcount OUT NUMBER, --返回总条数 o_pagecount OUT NUMBER, --返回总页数 o_cursor OUT ref_cursor.t_RetDataSet --返回分页结果集 ) IS v_startrecord INT; v_endrecord INT; v_pagesize INT; v_curpage INT; v_tablecolumn VARCHAR2(2000); v_where VARCHAR2(2000); v_ordercolumn VARCHAR2(200); v_count_sql VARCHAR2(2000); v_select_sql VARCHAR2(2000); BEGIN --如果没有表名称,则直接返回异常消息 --如果没有字段,则表示全部字段 IF i_tablecolumn IS NOT NULL THEN v_tablecolumn:=i_tablecolumn; ELSE v_tablecolumn:=' * '; END IF; --可以没有WHERE条件 IF i_where IS NOT NULL THEN v_where:=' WHERE 1=1 AND '||i_where||' '; ELSE v_where:=' WHERE 1=1 '; END IF; --可以没有ORDER BY条件 IF i_ordercolumn IS NULL THEN v_ordercolumn:=' '; ELSE v_ordercolumn:=' ORDER BY '||i_ordercolumn; END IF; --如果未指定查询页,则默认为首页 IF i_curpage IS NULL OR i_curpage<1 THEN v_curpage:=1; ELSE v_curpage:=i_curpage; END IF; --如果未指定每页记录数,则默认为10条记录 IF i_pagesize IS NULL THEN v_pagesize:=10; ELSE v_pagesize:=i_pagesize; END IF; --查询总条数 v_count_sql:='SELECT COUNT(*) FROM '||i_tablename||v_where; --构造最核心的查询语句 v_select_sql:='(SELECT '||v_tablecolumn||' FROM '||i_tablename||v_where||v_ordercolumn||') e'; --执行查询,查询总条数 EXECUTE IMMEDIATE v_count_sql INTO o_rowcount; DBMS_OUTPUT.PUT_LINE('查询总条数SQL=>'||v_count_sql); DBMS_OUTPUT.PUT_LINE('查询总条数Count='||o_rowcount); --得到总页数,并进行处理 IF MOD(o_rowcount,i_pagesize)=0 THEN o_pagecount:=o_rowcount/i_pagesize; ELSE o_pagecount:=FLOOR(o_rowcount/i_pagesize)+1; END IF; --如果当前页大于最大页数,则取最大页数 IF i_curpage>o_pagecount THEN v_curpage:=o_pagecount; END IF; --设置开始结束的记录数 v_startRecord := (v_curpage - 1) * v_pagesize + 1; v_endRecord := v_curpage * v_pagesize; --进行完整的动态SQL语句拼写 v_select_sql:='SELECT * FROM '|| '( '|| ' SELECT e.*,ROWNUM rn '|| ' FROM '|| v_select_sql|| ' WHERE ROWNUM<='||v_endRecord|| ') '|| ' WHERE rn>='||v_startRecord; DBMS_OUTPUT.PUT_LINE('查询SQL=>'||v_select_sql); OPEN o_cursor FOR v_select_sql; END; 2.3 执行复杂业务逻辑查询 动态SQL语句应用的另外一个重要场合是复杂业务逻辑查询和报表开发环境中;在某移动公司呼叫中心业务工作流系统中,有将近20个查询条件,而且根据这些查询条件会关联到7、8个不同的数据表;这些构造出来的SQL能达到几百行代码。 当然能查询出来是一码事,效率又是一码事;这些极端复杂的SQL通常效率不佳,而且由于不同的条件组合,多表的联合,很难去定位和优化相关程序。 给出的建议是首先从业务角度去简化问题;其次是业务逻辑大于性能要求;再次是限定复杂业务逻辑查询的应用场合。 下面的几个例子均采用Oracle10g的HR用户的相关数据表。 根据部门名称、工作名称、曾经参加过的工作这三个条件组合进行员工信息查询。 CREATE OR REPLACE PROCEDURE sp_exec_complex_query ( i_department_name IN VARCHAR2, --部门名称 i_job_title IN VARCHAR2, --工作名称 i_history_job_title IN VARCHAR2, --曾经参加过的工作 r_Query IN OUT ref_cursor.t_RetDataSet ) IS v_select_sql VARCHAR2(500); v_table_sql VARCHAR2(500); v_where_sql VARCHAR2(500); v_sql VARCHAR2(1000); BEGIN --分别构造SELECT、FROM、WHERE三个变量的基本部分 v_select_sql:='SELECT a.employee_id,a.first_name,a.last_name,a.email,a.hire_date,a.salary '; v_table_sql:=' FROM employees a '; v_where_sql:='WHERE 1=1 '; --如果部门名称不为空,则与部门表相关联,并且从部门表查询,并且把部门名称引入字段列表 IF (i_department_name IS NOT NULL) THEN v_select_sql:=v_select_sql||',b.department_name '; v_table_sql:=v_table_sql||',departments b '; v_where_sql:=v_where_sql||'AND a.department_id=b.department_id '; v_where_sql:=v_where_sql||'AND b.department_name LIKE '''||RTRIM(LTRIM(i_department_name))||'%'''; END IF; --如果工作名称不为空,则与工作表相关联,并且从工作表查询,并且把工作名称引入字段列表 IF (i_job_title IS NOT NULL) THEN v_select_sql:=v_select_sql||',c.job_title '; v_table_sql:=v_table_sql||',jobs c '; v_where_sql:=v_where_sql||'AND a.job_id=c.job_id '; v_where_sql:=v_where_sql||'AND c.job_title LIKE '''||RTRIM(LTRIM(i_job_title))||'%'''; END IF; --如果曾经工作名称不为空,则去job_history表中查询曾做过该工作的职员。 IF (i_history_job_title IS NOT NULL) THEN v_where_sql:=v_where_sql||'AND a.employee_id IN '; v_where_sql:=v_where_sql||'(SELECT employee_id FROM job_history d,jobs e '; v_where_sql:=v_where_sql||'WHERE d.job_id=e.job_id '; v_where_sql:=v_where_sql||'AND e.job_title LIKE '''||RTRIM(LTRIM(i_history_job_title))||'%'')'; END IF; v_sql:=v_select_sql||v_table_sql||v_where_sql; OPEN r_Query FOR v_sql; END; 以上存储过程完全采用动态SQL的语法,达到灵活的查询目的;事实上还有另外一种广 泛应用的Ad-hoc查询方式,即在WHERE条件中进行逻辑的判断。 这种SQL虽然是静态SQL,但与上面的动态SQL语句相比可读性却更差。 CREATE OR REPLACE PROCEDURE sp_exec_complex_query2 ( i_department_name IN VARCHAR2, --部门名称 i_job_title IN VARCHAR2, --工作名称 i_history_job_title IN VARCHAR2, --曾经参加过的工作 r_Query IN OUT ref_cursor.t_RetDataSet ) IS BEGIN OPEN r_Query FOR SELECT a.employee_id,a.first_name,a.last_name,a.email,a.hire_date, a.salary,b.department_name,c.job_title FROM employees a,departments b,jobs c WHERE a.department_id=b.department_id AND (i_department_name='-1' OR (i_department_name<>'-1' AND b.department_name LIKE i_department_name||'%')) AND a.job_id=c.job_id AND (i_job_title='-1' OR (i_job_title<>'-1' AND c.job_title LIKE i_job_title||'%')) AND (i_history_job_title='-1' OR (i_history_job_title<>'-1' AND a.employee_id IN (SELECT employee_id FROM job_history d,jobs e WHERE d.job_id=e.job_id AND e.job_title like i_history_job_title||'%')) ); END; 再介绍一种构造复杂SQL语句的方式,也就是利用最传统的IF ELSE以穷举法分别返回不同的结果集,但是每个结果集都是可控的单个静态SQL。 这种切分成若干简单的静态SQL的方式,更适合于条件相对简单的环境。 CREATE OR REPLACE PROCEDURE sp_exec_complex_query3 ( i_department_name IN VARCHAR2, --部门名称 i_job_title IN VARCHAR2, --工作名称 i_history_job_title IN VARCHAR2, --曾经参加过的工作 r_Query IN OUT ref_cursor.t_RetDataSet ) IS BEGIN IF i_department_name='' THEN IF i_job_title='' THEN IF i_history_job_title='' THEN OPEN r_Query FOR SELECT …; ELSE OPEN r_Query FOR SELECT …; END IF; ELSE IF i_history_job_title='' THEN OPEN r_Query FOR SELECT …; ELSE OPEN r_Query FOR SELECT …; END IF; END IF; ELSE IF i_job_title='' THEN IF i_history_job_title='' THEN OPEN r_Query FOR SELECT …; ELSE OPEN r_Query FOR SELECT …; END IF; ELSE IF i_history_job_title='' THEN OPEN r_Query FOR SELECT …; ELSE OPEN r_Query FOR SELECT …; END IF; END IF; END IF; END; 最后给出一种绑定变量的方式,有些类似与第一、二个例子的综合。 CREATE OR REPLACE PROCEDURE sp_exec_complex_query4 ( i_department_name IN VARCHAR2, --部门名称 i_job_title IN VARCHAR2, --工作名称 i_history_job_title IN VARCHAR2, --曾经参加过的工作 r_Query IN OUT ref_cursor.t_RetDataSet ) IS v_select_sql VARCHAR2(500); v_table_sql VARCHAR2(500); v_where_sql VARCHAR2(500); v_sql VARCHAR2(1000); BEGIN --分别构造SELECT、FROM、WHERE三个变量的基本部分 v_select_sql:='SELECT a.employee_id,a.first_name,a.last_name,a.email,a.hire_date,a.salary '; v_table_sql:=' FROM employees a '; v_where_sql:='WHERE 1=1 '; --如果部门名称不为空,则与部门表相关联,并且从部门表查询,并且把部门名称引入字段列表 IF (i_department_name IS NOT NULL) THEN v_select_sql:=v_select_sql||',b.department_name '; v_table_sql:=v_table_sql||',departments b '; v_where_sql:=v_where_sql||' AND a.department_id=b.department_id '; v_where_sql:=v_where_sql||' AND b.department_name LIKE RTRIM(LTRIM(:i_department_name))||''%'''; ELSE v_select_sql:=v_select_sql||',b.department_name '; v_table_sql:=v_table_sql||',departments b '; v_where_sql:=v_where_sql||' AND a.department_id=b.department_id '; v_where_sql:=v_where_sql||' AND (1=1 OR :i_department_name IS NULL) '; END IF; --如果工作名称不为空,则与工作表相关联,并且从工作表查询,并且把工作名称引入字段列表 IF (i_job_title IS NOT NULL) THEN v_select_sql:=v_select_sql||',c.job_title '; v_table_sql:=v_table_sql||',jobs c '; v_where_sql:=v_where_sql||' AND a.job_id=c.job_id '; v_where_sql:=v_where_sql||' AND c.job_title LIKE RTRIM(LTRIM(:i_job_title))||''%'''; ELSE v_select_sql:=v_select_sql||',c.job_title '; v_table_sql:=v_table_sql||',jobs c '; v_where_sql:=v_where_sql||' AND a.job_id=c.job_id '; v_where_sql:=v_where_sql||' AND (1=1 OR :i_job_title IS NULL) '; END IF; --如果曾经工作名称不为空,则去job_history表中查询曾做过该工作的职员。 IF (i_history_job_title IS NOT NULL) THEN v_where_sql:=v_where_sql||'AND a.employee_id IN '; v_where_sql:=v_where_sql||'(SELECT employee_id FROM job_history d,jobs e '; v_where_sql:=v_where_sql||' WHERE d.job_id=e.job_id '; v_where_sql:=v_where_sql||' AND e.job_title LIKE RTRIM(LTRIM(:i_history_job_title))||''%'')'; ELSE v_where_sql:=v_where_sql||' AND (1=1 OR :i_department_name IS NULL)'; END IF; v_sql:=v_select_sql||v_table_sql||v_where_sql; DBMS_OUTPUT.put_line(V_SQL); OPEN r_Query FOR v_sql USING i_department_name,i_job_title,i_history_job_title; END; 通过这四个例子,我们可以得到以下结论 第一个例子:完全采用动态SQL拼写。优点是非常灵活、任意组合,并且最容易构造;缺点是每次执行需要进行硬解析,效率最差,此外还有SQL注入的安全隐患。 第二个例子:通过静态SQL方法。优点是只需要写一个SQL;但缺点是会导致只有一种执行计划,而且第一次传入的参数决定了CBO生成什么计划,其它参数执行这个SQL可能很低效,其次可阅读性比较差、编程比较困难,在极端复杂的环境下,难以构造。 第三个例子:通过静态SQL方法。优点是高效、结构清晰、容易理解;缺点是由于使用穷举法,只能胜任一些比较简单的环境中。 第四个例子:通过动态SQL绑定变量。优点是即灵活,容易构造,又通过绑定变量,达到了高效的目的;兼具第二、三实例的优点。 事实上,采用何种写法,首先要考虑效率,其次也要考虑个人的编程习惯。 2.4 绑定变量 绑定变量的意思就是在动态SQL中的一个占位符,它告诉Oracle现在需要生成一个执行计划,我随后会为这个占位符提供一个值。 绑定变量在动态SQL中应用,主要是为了实现一次解析,多次执行,从而提高效率的目的。绑定变量的优势是可以在库缓存中共享游标,这样可以避免硬解析以及与之相关的额外开销,因此绑定变量是一种减少应用程序在分析查询时使用栓锁数目的可靠方法;其次使用绑定变量可以避免注入攻击。 但是绑定变量在SQL要访问的表如果存在数据倾斜(如某个列的数据分布不均匀,那么被称为数据倾斜),就会提供错误的执行计划,而导致产生性能问题。 因此绑定变量的使用并不是一把攻无不克的银弹,而是一把双刃剑。 绑定变量之语法: EXECUTE IMMEDIATE dynamic_string [USING [IN|OUT|IN OUT] bind_argument[,[IN|OUT|IN OUT] bind_argument]…] [{RETURNING|RETURN} INTO bind_argument[, bind_argument]…] 绑定变量的占位符是在dynamic_string中指定的,可以是:1,:2,:3,之类的,也可以是:a,:b,:c这样的定义。 USING后的是绑定变量的实际值,按照dynamic_string中绑定变量的顺序赋值,传递值遵循规则为:动态SQL,按顺序赋值,不管名字是否重复,全部都要赋值。 RETURING之后也可以使用绑定变量。 绑定变量之限制: 1. 绑定变量不能直接传入NULL值 2. USING后的绑定变量必须是SQL类型例如字符型、整型、浮点型等,而PL/SQL的BOOLEAN等类型是不行的。 3. 绑定变量不能用SCHEMA的名字来替换,比如用表名或列名来替换是错误的。 4. 绑定变量只能代替SQL或PL/SQL中的字面量、变量、简单的表达式;比较大的语句,如整个WHERE条件,则是不可以使用绑定变量的。 5. 动态DDL语句中不允许出现绑定变量,否则返回ORA-01027: 在数据定义操作中不允许有绑定变量错误,这个原因是对于动态语句,运行期引擎首先要解析这个SQL,保证SQL语法正确,绑定变量为自动考虑为变量,如果SCHEMA对象或DDL使用绑定变量,这样表或列等不存在,则SQL错误,这时候可以使用拼字符串实现. n 绑定变量之USING 在USING中的绑定变量通常使用在DML需要传入的变量中,例如WHERE条件,再例如INSERT、UPDATE中的字段值。通过绑定变量能够在库缓存中共享游标,这样可以避免硬解析以及与之相关的额外开销;能够用于各种DML操作环境中。 CREATE OR REPLACE PROCEDURE sp_dynamic_sql_onlyforDML ( iemployee_id IN INTEGER, iemail IN VARCHAR2, ihire_date IN DATE, ioperate_type IN VARCHAR2, orow_count OUT INTEGER ) IS v_dynamicSQL VARCHAR2(500); BEGIN --使用了绑定变量,在update语句被解析之后,pl/sql引擎会用using后面的替换占位符 IF ioperate_type='UPDATE' THEN v_dynamicSQL:='UPDATE employees SET email =:1,hire_date=:2 WHERE employee_id =:3'; EXECUTE IMMEDIATE v_dynamicSQL USING iemail, ihire_date, iemployee_id; --返回更新的数目 orow_count:= SQL%ROWCOUNT; ELSIF ioperate_type='DELETE' THEN v_dynamicSQL:='DELETE FROM employees WHERE employee_id = :id'; EXECUTE IMMEDIATE v_dynamicSQL USING iemployee_id; orow_count:= SQL%ROWCOUNT; END IF; END; n 绑定变量之RETURNING INTO RETURNING INTO主要用于DML语句中所返回被影响的列值,例如生成的自动增长值、其他计算字段等等;可以用于UPDATE、INSERT等环境下。 CREATE OR REPLACE PROCEDURE sp_dynamic_sql_withreturning ( iemployee_id IN INTEGER, iemail IN VARCHAR2, ihire_date IN DATE, ioperate_type IN VARCHAR2, oemployee_name OUT VARCHAR2, orow_count OUT INTEGER ) IS v_dynamicSQL VARCHAR2(500); v_phone_number VARCHAR2(50); BEGIN --使用了绑定变量,在update语句被解析之后,pl/sql引擎会用using后面的替换占位符 IF ioperate_type='UPDATE' THEN v_dynamicSQL:='UPDATE employees SET email =:1,hire_date=:2 WHERE employee_id =:3 RETURNING first_name,phone_number INTO :4,:5'; EXECUTE IMMEDIATE v_dynamicSQL USING iemail, ihire_date, iemployee_id,OUT oemployee_name,OUT v_phone_number; --返回更新的数目 DBMS_OUTPUT.put_line(v_phone_number); orow_count:= SQL%ROWCOUNT; ELSIF ioperate_type='DELETE' THEN v_dynamicSQL:='DELETE FROM employees WHERE employee_id = :1 RETURNING first_name INTO :2'; EXECUTE IMMEDIATE v_dynamicSQL USING iemployee_id,OUT oemployee_name; orow_count:= SQL%ROWCOUNT; END IF; END; n 动态SQL之INTO INTO子句用于将查询返回的结果值传递给已定义的标量类型变量或记录变量,这里的结果只能为单行查询返回结果;INTO 语句主要用于SELECT语句。 INTO子句与绑定变量无直接关系。 CREATE OR REPLACE PROCEDURE sp_dynamic_sql_withinto ( iemployee_id IN INTEGER ) IS vfirst_name VARCHAR2(20); vlast_name VARCHAR2(20); vemail VARCHAR2(25); vhire_date DATE; v_dynamicSQL VARCHAR2(500); BEGIN v_dynamicSQL:='SELECT first_name,last_name,email,hire_date FROM employees WHERE employee_id =:1'; EXECUTE IMMEDIATE v_dynamicSQL INTO vfirst_name,vlast_name,vemail,vhire_date USING iemployee_id; DBMS_OUTPUT.PUT_LINE('************************************'); DBMS_OUTPUT.PUT_LINE('*****current employee_id is '||TO_CHAR(iemployee_id)||'*****'); DBMS_OUTPUT.PUT_LINE('**employee name is '||vfirst_name||','||vlast_name); DBMS_OUTPUT.PUT_LINE('************************************'); END; 2.5 BULK COLLECT动态SQL BULK COLLECT可以看做是一种批获取数据的方式,通过bulk collect可以把我们的查询结果一次性地加载到一个集合中,集合的类型包括index-by table, nested table, or varray。集合的成员必须是SQL简单数据类型,例如字符串型、数字型、日期型。 EXECUTE IMMEDIATE, FETCH, and FORALL三种语句支持动态BULK COLLECT绑定。 BULK COLLECT依然可以使用USING、INTO、RETURNING INTO语法使用 n BULK COLLECT之EXECUTE IMMEDIATE用法 CREATE OR REPLACE PROCEDURE sp_dynamic_bulk_collect_excute ( iminsalary IN NUMERIC, imaxsalary IN NUMERIC ) IS TYPE numlist IS TABLE OF NUMBER; vempids numList; vsalarys numList; i int; BEGIN --直接使用EXECUTE IMMEDIATE将数据 EXECUTE IMMEDIATE 'SELECT employee_id,salary FROM employees WHERE salary>=:1 AND salary<=:2' BULK COLLECT INTO vempids,vsalarys USING iminsalary,imaxsalary; --集合的另外一种遍历方式 i := vsalarys.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(vsalarys(i)); i := vsalarys.NEXT(i); END LOOP; END; n BULK COLLECT之FETCH用法 CREATE OR REPLACE PROCEDURE sp_dynamic_bulk_collect_fetch ( iminsalary IN NUMERIC, imaxsalary IN NUMERIC ) IS TYPE empcurtype IS REF CURSOR; TYPE numlist IS TABLE OF NUMBER; TYPE nameList IS TABLE OF VARCHAR2(25); curemp empcurtype; vempids numList; vfnames nameList; vlnames nameList; i int; vdynamicsql VARCHAR2(500); --通过FETCH将游标记录集加载到指定的集合中。 BEGIN vdynamicsql:='SELECT employee_id, first_name,last_name FROM employees WHERE salary>=:1 AND salary<=:2'; OPEN curemp FOR vdynamicsql USING iminsalary,imaxsalary; FETCH curemp BULK COLLECT INTO vempids, vfnames,vlnames; CLOSE curemp; --遍历集合,实现打印 FOR i IN vempids.FIRST .. vempids.LAST LOOP DBMS_OUTPUT.PUT_LINE(vempids(i)); DBMS_OUTPUT.PUT_LINE(vfnames(i)); DBMS_OUTPUT.PUT_LINE(vlnames(i)); END LOOP; END; n BULK COLLECT之FORALL用法 DECLARE TYPE NumList IS TABLE OF NUMBER; TYPE NameList IS TABLE OF VARCHAR2(15); empids NumList; enames NameList; BEGIN empids := NumList(101,102,103,104,105); FORALL i IN 1..5 EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1 RETURNING last_name INTO :2' USING empids(i) RETURNING BULK COLLECT INTO enames; END; 3. 使用DBMS_SQL包 在Oracle8.1之前只提供使用DBMS_SQL包来执行一些动态语句,Oracle8.1才提供了 对Native Dynamic SQL的支持,这是PL/SQL语言的Native组成部分。 Native Dynamic SQL比使用DBMS_SQL简单,而且大部分程序要比DBMS_SQL更高效,其次两者的使用场合基本差不多,所以多数情况下推荐使用Native Dynamic SQL。 下面将对DBMS_SQL做一些简单的案例介绍。 3.1 DBMS_SQL函数和过程说明 FUNCTION OPEN_CURSOR():打开一个动态游标,并返回一个整型; PROCEDURE CLOSE_CURSOR(c IN OUT INTEGER):关闭一个动态游标,参数为OPEN_CURSOR所打开的游标; PROCEDURE PARSE(c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER):对动态游标所提供的SQL语句进行解析,参数C表示游标,statement为SQL语句,language-flag为解析SQL语句所用Oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native); PROCEDURE DEFINE_COLUMN(c IN INTEGER, position IN INTEGER, column ANY DATATYPE, [column_size IN INTEGER]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态SQL中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述); FUNCTION EXECUTE(c IN INTEGER):执行游标,并返回处理一个整型,代表处理结果(对INSERT、DELETE、UPDATE才有意义,而对SELECT语句而言可以忽略); FUNCTION FETCH_ROWS(c IN INTEGER):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端; PROCEDURE COLUMN_VALUE(c IN INTEGER, position IN INTEGER, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量; PROCEDURE BIND_VARIABLE(c IN INTEGER, name IN VARCHAR2, value):定义动态SQL语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值。 3.2 DBMS_SQL执行步骤 使用DBMS_SQL包实现动态SQL的步骤如下: A. 先将要执行的SQL语句或一个语句块放到一个字符串变量中。 B. 使用DBMS_SQL包的 PARSE过程来分析该字符串。 C. 使用DBMS_SQL包的BIND_VARIABLE过程来绑定变量。 D. 使用DBMS_SQL包的EXECUTE函数来执行语句。 说明: 1. 对于一般的SELECT操作,如果使用动态的SQL语句则需要进行以下几个步骤: OPEN CURSOR--->PARSE--->DEFINE COLUMN--->EXCUTE--->FETCH ROWS--->CLOSE CURSOR; 2. 而对于DML操作(INSERT、UPDATE)则需要进行以下几个步骤: OPEN CURSOR--->PARSE--->BIND VARIABLE--->EXECUTE--->CLOSE CURSOR; 3. 对于DELETE操作只需要进行以下几个步骤: OPEN CURSOR--->PARSE--->EXECUTE--->CLOSE CURSOR; 4. 对于DDL操作与DELELE操作步骤基本一致: OPEN CURSOR--->PARSE--->EXECUTE--->CLOSE CURSOR; 3.3 DBMS_SQL应用场景 n DBMS_SQL的基本语法(可与EXECUTE IMMEDIATE相互替换) CREATE OR REPLACE PROCEDURE SP_GET_SERIALNO ( /* 中文名称:产生编号 功 能:存储过程根据传入的表名,取表中最大的编号,加1, 将新生成的流水号赋值给输出参数 */ i_TableName IN VARCHAR2, --表名称 i_FieldName IN VARCHAR2, --字段名 o_Serialno OUT NUMBER --返回流水号 ) IS v_SerialNo NUMBER(10); v_CursorHandle NUMBER; v_Result NUMBER; BEGIN /**************** Oracle 8.1 以上版本使用如下代码 *******************/ EXECUTE IMMEDIATE 'SELECT NVL(MAX(TO_NUMBER('||i_FieldName||')),0)+1 FROM '||i_TableName INTO v_SerialNo; /******************************************************************/ /**************** Oracle 8.0 或以下版本使用如下代码 *******************/ v_CursorHandle:= DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (v_CursorHandle, 'SELECT NVL(MAX(TO_NUMBER('||i_FieldName||')),0)+1 FROM '||i_TableName, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN (v_CursorHandle,1,v_SerialNo); v_Result:= DBMS_SQL.EXECUTE (v_CursorHandle); v_Result:= DBMS_SQL.FETCH_ROWS (v_CursorHandle); DBMS_SQL.COLUMN_VALUE (v_CursorHandle,1,v_SerialNo); DBMS_SQL.CLOSE_CURSOR (v_CursorHandle); /******************************************************************/ o_Serialno:=v_SerialNo; END; 以上例子仅供DBMS_SQL与EXECUTE IMMEDIATE可以互换的语法参考,在实际环境中,出于并发性能考虑,基本不这么做。 3.4 DBMS_SQL与EXECUTE IMMEDIATE比较 EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包。它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能,较之以前它相当轻易编码;尽管 DBMS_SQL仍然可用, 一般情况下,EXECUTE IMMEDIATE方式比DBMS_SQL包方式要编码简单,运行速度也要快一些。 但是,DBMS_SQL包提供了更为强大的功能,在下列情况下只能使用DBMS_SQL包: 1. 当SQL语句长度超过32k时,必须用DBMS_SQL包来执行。 2. 输出输入的列均未知个数和类型的动态SQL语句,必须用DBMS_SQL来执行。 总结 动态SQL的应用场景非常的广泛,尤其是在需求较为不确定和较为复杂的时候。本文中的诸多案例都有很强的实用性和通用性,请读者多多体会,争取能应用到项目中去。 参考文献: [1] Oracle Database Concepts, 11g Release 2 [2] Oracle® Database Performance Tuning Guide 11g Release 2 [3]Thomas Kyte的《Expert One on One: Oracle》

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档