oracle------详解动态SQL

最新推荐文章于 2022-07-11 09:55:51 发布

置顶 IT界一股清流

最新推荐文章于 2022-07-11 09:55:51 发布

阅读量801

点赞数 1

文章标签： oracle 动态SQL

本文链接：https://blog.csdn.net/jerrytomcat/article/details/82835736

版权

一为什么要用动态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

https://www.cnblogs.com/liunanjava/p/4214209.html

IT界一股清流

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
oracle------详解动态SQL

一为什么要用动态SQL对于静态SQL大家可能都知道，每一个字段或表都是固定的，也就是说表提供给你了，让你执行DML操作，这就是静态SQL；而如果表或条件都提供给你了，但是有很多，你无法确定哪个是你要的表或条件，这时就要用到动态SQL。二静态SQL和动态SQL区别静态SQL：性能较高，不灵活动态SQL：性能较差，比较灵活三创建动态SQL的几种方式1 EXEC...
复制链接

扫一扫