关于DMSQL开发中的游标和动态SQL语句

目录

一：游标概述

二：样例

三：动态的SQL

---

一：游标概述

    在实际工作中，经常会因为某些需求而写SQL代码块，而游标作为很方便的需求功能实现，应该掌握并使用好它！
    DM中的游标分为静态游标和动态游标；
A）、静态游标：分为两种：隐式游标（无需用户进行定义）和显式游标（需要定义）；
B）、动态游标：它是在声明部分只是先声明一个游标类型的变量，并不指定其关联的查询语句，在执行部分打开游标时才指定查询语句。动态游标的使用主要在定义和打开时与显式游标不同。动态游标在 OPEN 时通过 FOR 子句指定与其关联的查询语句。可以用参数以“?”指定，同时在打开游标语句中使用 USING 子句指定参数，且参数的个数和类型与语句中的“?”必须一一匹配

二：样例

--例1：显示游标
ECLARE
  v_name  hr.DEPARTMENTS.department_name%TYPE;
  v_phone hr.EMPLOYEES.first_name%type;
  
  c1      CURSOR FOR
    select de.department_name, em.first_name
      from hr.DEPARTMENTS de, hr.EMPLOYEES em
     where de.department_id = em.department_id;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1
      INTO v_name, v_phone;
    EXIT WHEN c1%NOTFOUND;--显示游标的属性，用exit来退出。
    PRINT v_name || v_phone;
  END LOOP;
  CLOSE c1;
END;
/


--例2：动态游标
DECLARE
  my_ename HR.EMPLOYEES.FIRST_name%TYPE;
  my_empno HR.EMPLOYEES.employee_id%TYPE;
  my_sal   HR.EMPLOYEES.SALARY%TYPE;
  CURSOR c1;
BEGIN
  OPEN C1 FOR
    SELECT FIRST_name, employee_id, SALARY FROM HR.EMPLOYEES;
  LOOP
    FETCH c1
      INTO my_ename, my_empno, my_sal;  -- FETCH 语句默认每次只获取一条记录，所以配LOOP用。
    EXIT WHEN c1%NOTFOUND;
    PRINT '姓名' || my_ename || '工号' || my_empno || ' 薪水' || my_sal;
  END LOOP;
  CLOSE c1;
END;
/


--例3：动态游标--使用绑定变量参数
DECLARE
  str VARCHAR;
  CURSOR csr;
BEGIN
  OPEN csr FOR 'SELECT first_name FROM  HR.EMPLOYEES WHERE employee_id =? OR 
first_name =?' USING '191', 'Randall';
  LOOP
    FETCH csr INTO str;
      EXIT WHEN csr%NOTFOUND;
    PRINT str;
  END LOOP;
  CLOSE csr;
END;
/


--例4：游标变量
DECLARE
  FIRST_NAME HR.EMPLOYEES.FIRST_NAME%TYPE;
  CURSOR c1 IS
    SELECT FIRST_NAME FROM HR.EMPLOYEES WHERE MANAGER_ID = 100; --静态的显示游标
  c2 CURSOR; --声明一个游标变量
BEGIN
  c2 = c1; --将静态的显示游标c1赋值给c2
  open c2;
  LOOP
    FETCH c2
      INTO FIRST_NAME;
    EXIT WHEN c1%NOTFOUND;
    PRINT FIRST_NAME;
  END LOOP;
  close c2;
END;
/


--例5：引用游标
--引用游标实现了在程序间传递结果集的功能!
DECLARE
  TYPE Emptype IS REF CURSOR RETURN HR.EMPLOYEES%ROWTYPE;
  emp Emptype; --声明一个游标变量
  
  PROCEDURE process_emp(emp_v IN Emptype) IS
    person HR.EMPLOYEES%ROWTYPE;--注意：它是一个行的类型%ROWTYPE
  BEGIN
    LOOP
      FETCH emp_v INTO person;
      EXIT WHEN emp_v%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('姓：' || person.FIRST_NAME || ' 电话：' || person.PHONE_NUMBER);
    END LOOP;
  END;
BEGIN
  OPEN emp FOR
    SELECT *  --注意：这里要为*，因为前边定义了%ROWTYPE的行类型，否则会报：游标类型不匹配！
      FROM HR.EMPLOYEES
     WHERE MANAGER_ID = 100;
  process_emp(emp);
  CLOSE emp;
END;
/


--例6：
--功能：使用静态的显示游标更新表中的数据。
--要点：1、WHERE CURRENT OF csr;一定要有：以限定删除/更新游标当前所指的行。
--     2、在游标关联的查询语句中一定要使用“FOR UPDATE 选项”
--说明：MANAGER_ID = 100的一共有14行记录，是一个结果集，所以用静态的显示游标。
DECLARE
  CURSOR csr is
    SELECT SALARY FROM HR.EMPLOYEES WHERE MANAGER_ID = 100 FOR UPDATE; --一共14行结果集。
BEGIN
  OPEN csr;
  IF csr%ISOPEN THEN
    loop
      FETCH csr; --注意：fetch只fetch当前一行记录，应与loop配合使用！
      UPDATE HR.EMPLOYEES SET SALARY = SALARY + 1 WHERE CURRENT OF csr;
      commit; --这个应放在END LOOP;之后，可以提高效率！
    END LOOP;
    --commit;
  ELSE
    PRINT 'CURSOR IS NOT OPENED';
  END IF;
  CLOSE csr;
EXCEPTION
  WHEN OTHERS THEN
    PRINT 'ex_others';
END;
/
 

--例7：隐式游标 FOR 循环
BEGIN
  FOR v_emp IN (SELECT * FROM HR.EMPLOYEES) LOOP
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPLOYEE_ID || '的工资' || V_EMP.SALARY); --V_EMP它是一个SQL%ROWTYPE 属性隐式声明出来的，不需要显式声明
  END LOOP;
END;
/


--例8：显式游标 FOR 循环
DECLARE
  CURSOR cur_emp IS
    SELECT * FROM HR.EMPLOYEES;--静态的显示游标。
BEGIN
  FOR V_EMP IN cur_emp LOOP
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPLOYEE_ID || '的工资' || V_EMP.SALARY);
  END LOOP;
END;
/

三：动态的SQL

1、静态 SQL 在 DMSQL 程序进行编译时是明确的 SQL 语句，处理的数据库对象也是明确的，这些语句在编译时就可进行语法和语义分析处理。
2、支持动态 SQL，在 DMSQL 程序运行时才动态地生成并执行这些 SQL。
3、DDL 语句只能通过动态 SQL 执行。
4、动态的SQL使用绑定变量来使用。

--例1：SQL动态语句文本为文本普通
--SQL 动态语句文本使用为文本普通方式时，其中的两个:x 被转化为?进行处理，程序执行成功。
DECLARE
  sql_str VARCHAR := 'SELECT * FROM HR.EMPLOYEES where EMPLOYEE_ID=:x  and FIRST_NAME=:x;';
BEGIN
  EXECUTE IMMEDIATE SQL_STR
    USING '100', 'Steven';
END;
/


--例2：SQL动态语句文本为脚本方式
-- SQL动态语句文本为脚本方式时，则将两个:x 视为同名参数，因此在动态执行时指定两个实际参数会报错！
DECLARE
  sql_str VARCHAR := 'begin SELECT * FROM HR.EMPLOYEES where EMPLOYEE_ID=:x  and FIRST_NAME=:x; end;';
BEGIN
  EXECUTE IMMEDIATE SQL_STR
    USING '100', 'Steven';
END;
/


--所以应改为：
DECLARE
  sql_str VARCHAR := 'begin SELECT * FROM HR.EMPLOYEES where EMPLOYEE_ID=:x  and FIRST_NAME=:y; end;';
BEGIN
  EXECUTE IMMEDIATE SQL_STR
    USING '100', 'Steven';
END;
/

 更多学习内容参考：达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心