一 为什么要用动态SQL
对于静态SQL大家可能都知道,每一个字段或表都是固定的,也就是说表提供给你了,让你执行DML操作,这就是静态SQL;
而如果表或条件都提供给你了,但是有很多,你无法确定哪个是你要的表或条件,这时就要用到动态SQL。
二 静态SQL和动态SQL区别
静态SQL:性能较高,不灵活
动态SQL:性能较差,比较灵活
三 创建动态SQL的几种方式
1 EXECUTE IMMEDIATE语句实现
适用条件:DDL语句、DCL语句、非查询的DML语句、单行查询的SELECT语句
语法:
EXECUTE IMMEDIATE dynamic_sql_string
[into define_variable_list]
[using bind_argument_list]
① 创建表t1,用的是DDL语句(create)
DECLARE
tablename VARCHAR2(20) := 't1'; --表名
field1 VARCHAR2(20) :='id'; --字段id
datatype1 VARCHAR2(20) :='number'; --字段id类型
field2 VARCHAR2(20) :='name'; --字段name
datatype2 VARCHAR2(20) :='varchar(20)'; --字段name类型
str_sql VARCHAR2(500); --拼接SQL语句的字符串
BEGIN
str_sql := 'create table '||' '||tablename||'('||field1 ||' '||datatype1||','||field2 ||' '||datatype2||')';
EXECUTE IMMEDIATE str_sql;
END;
② 向t1表中插入数据,用的是DML语句(insert)
DECLARE
v_id NUMBER; --输入序号;
v_name VARCHAR(20); --输入姓名;
str_sql VARCHAR2(500); --保存拼接的SQL语句
BEGIN
v_id := '&vid';
v_name := '&name';
str_sql := 'insert into t1 values(:1,:2)'; --使用占位符代表变量
EXECUTE IMMEDIATE str_sql
USING v_id,v_name; --使用变量替换SQL中的占位符,v_id替换:1,v_name替换:2
COMMIT; --执行完毕后直接提交
END;
③ 查询表中的数据有多少行,用的是单行查询的select语句
DECLARE
v_count NUMBER;
str_sql VARCHAR2(500);
BEGIN
str_sql := 'select count(*) from t1' ;
EXECUTE IMMEDIATE str_sql INTO v_count; --将查询的结果存放到变量v_count中。
DBMS_OUTPUT.put_line(v_count);
END;
2 游标实现
适用条件:多行查询的SELECT语句
案例:要通过游标实现select的多行查询,进行如下步骤
① 创建表格
create table emp
(
STUDENT_NO VARCHAR2(10) not null,
STUDENT_NAME VARCHAR2(20),
STUDENT_AGE NUMBER(2),
STUDENT_GENDER VARCHAR2(5)
);
② 插入数据到表格中
insert into HAND_STUDENT values ('s001','张三',23,'男');
insert into HAND_STUDENT values ('s002','李四',23,'男');
insert into HAND_STUDENT values ('s003','吴鹏',25,'男');
insert into HAND_STUDENT values ('s004','琴沁',20,'女');
insert into HAND_STUDENT values ('s005','王丽',20,'女');
insert into HAND_STUDENT values ('s006','李波',21,'男');
insert into HAND_STUDENT values ('s007','刘玉',21,'男');
insert into HAND_STUDENT values ('s008','萧蓉',21,'女');
③ 查看表格
④ 通过游标实现select多行查询
注意:看到这里的人会问了,表格创建好直接执行select就好了,这里是静态SQL,何必用游标?这里是为了演示游标的多行操作,如果有多个不确定的表格就需要用到游标了。
DECLARE
TYPE ref_cur IS REF CURSOR;
rc ref_cur; --定义游标
emprow emp%ROWTYPE; --定义行类型
v_sql VARCHAR2(100):= 'select * from emp where student_age = :x'; --动态执行的SQL语句
BEGIN
OPEN rc FOR v_sql USING 21; --打开游标,绑定执行的SQL语句,并传递参数
LOOP
FETCH rc INTO emprow;
EXIT WHEN rc%NOTFOUND;
dbms_output.put_line('name:'||emprow.student_name||' sal:'||emprow.student_gender);
END LOOP;
CLOSE rc;
END;
执行了游标后,输出的结果为
3 DBMS_SQL程序包实现
DBMS_SQL程序包是系统提供给我们的另一种使用动态SQL的方法。程序包中封装了一些列存储过程,帮助我们动态执行SQL。
使用DBMS_SQL包实现动态SQL的步骤如下:
① 将要执行的SQL语句或一个语句块放到一个字符串变量中。
② 使用DBMS_SQL包的parse过程来分析该字符串。
③ 使用DBMS_SQL包的bind_variable过程来绑定变量。
④ 使用DBMS_SQL包的execute函数来执行语句。
DBMS_SQL中用到的一些函数:
open_cursor:打开一个动态游标,并返回一个整型;
close_cursor(v_cursor):关闭一个动态游标,参数为open_cursor所打开的游标;
parse(c in integer,statement in varchar2,language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);
bind_variable(c in integer, name in varchar2, value):定义动态sql语句中所对应字段的值,c为游标,name为字段名称,value为字段的值;
execute(c in integer):执行游标,并返回处理一个整型,1表示成功,0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;
column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
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等(该过程有很多种情况,此处只对一般使用到的类型进行表述)。
各种常规操作步骤:
对于DDL操作(create)需要进行一下几个步骤
open cursor--->parse--->excute---->close cursor对于DML操作(insert , update )则需要进行以下几个步骤
open cursor --->parse--->bind variable--->execute--->close cursor对于一般的select 操作,如果使用动态的sql语句则需要进行以下几个步骤
open cursor --->parse--->define column--->excute--->fetch rows--->close cursor对于delete 操作只需要进行以下几个步骤
open cursor --->parse--->execute--->close cursor
① 创建表格(create操作)
DECLARE
TABLE_NAME VARCHAR2(20) := 't1' ; --表名
FIELD1 VARCHAR2(20) := 'id'; --字段名
DATATYPE1 VARCHAR2(20) := 'NUMBER'; --字段类型
FIELD2 VARCHAR2(20) := 'name'; --字段名
DATATYPE2 VARCHAR2(20) := 'VARCHAR2(20)'; --字段类型
V_CURSOR NUMBER; --定义光标
V_STRING VARCHAR2(200); --定义字符串变量
V_ROW NUMBER; --行数
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR; --为处理打开光标
V_STRING := 'create table ' || TABLE_NAME || '(' || FIELD1 || ' ' ||
DATATYPE1 || ',' || FIELD2 || ' ' || DATATYPE2 || ')';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析语句
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --执行语句
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
DBMS_OUTPUT.PUT_LINE(V_ROW);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
END;
② 表格中插入数据(insert操作)
DECLARE
ID NUMBER := '&ID';
NAME VARCHAR2(20) := '&name';
v_cursor NUMBER; --定义光标
v_string VARCHAR2(200); --定义字符串变量
v_row NUMBER; --行数变量
BEGIN
v_cursor := dbms_sql.open_cursor; --打开光标
v_string := 'insert into t1 values(:id,:name)';
dbms_sql.parse(v_cursor, v_string, dbms_sql.native); --分析语句
dbms_sql.bind_variable(v_cursor, 'id', ID); --绑定变量
dbms_sql.bind_variable(v_cursor, 'name', NAME); --绑定变量
v_row := dbms_sql.execute(v_cursor); --执行动态SQL
COMMIT;
dbms_sql.close_cursor(v_cursor); --关闭光标
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(v_cursor); --关闭光标
END;
③ 更新表(update操作)
DECLARE
ID NUMBER := &ID;
NAME VARCHAR2(20) := '&NAME';
V_CURSOR NUMBER; --定义光标
V_STRING VARCHAR2(200); --定义字符串变量
V_ROW NUMBER; --行数变量
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR; --打开光标
V_STRING := 'update t2 set name=:name where id=:id';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析语句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'name', NAME); --绑定变量
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'id', ID); --绑定变量
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --执行动态SQL
COMMIT;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
RAISE;
END;
④ 删除表中数据(delete操作)
DECLARE
ID NUMBER := &ID; --定义id
V_CURSOR NUMBER; --定义光标
V_ROW NUMBER; --定义行数
V_STRING VARCHAR2(200); --定义字符串变量
BEGIN
V_CURSOR:=DBMS_SQL.OPEN_CURSOR; --打开光标
V_STRING := 'delete from t1 where id=:id';
DBMS_SQL.PARSE(V_CURSOR, V_STRING, DBMS_SQL.NATIVE); --分析语句
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'id', ID); --绑定字段ID
V_ROW := DBMS_SQL.EXECUTE(V_CURSOR); --执行动态SQL
COMMIT;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR); --关闭光标
RAISE;
END;
参考:https://www.cnblogs.com/zhengcheng/p/4207376.html
https://www.cnblogs.com/zeromyth/archive/2009/09/29/1576627.html