mysql动态视图与静态视图 动态sql和静态sql的区别

admin2024-06-06  13

一  为什么要用动态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,'女');

  ③  查看表格

mysql动态视图与静态视图 动态sql和静态sql的区别,mysql动态视图与静态视图 动态sql和静态sql的区别_sql,第1张

 ④  通过游标实现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;

执行了游标后,输出的结果为

mysql动态视图与静态视图 动态sql和静态sql的区别,mysql动态视图与静态视图 动态sql和静态sql的区别_SQL_02,第2张

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;

 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!