mysql 存储过程 游标动态赋值 plsql存储过程游标调用

 1.游标概念

在 PL/SQL 块中执行 SELECT、INSERT、DELETE 和 UPDATE 语句时，ORACLE 会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或
是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式
SQL 语句的应用程序的常用编程方式。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的
OPEN_CURSORS 参数定义。
对于不同的 SQL 语句，游标的使用情况不同：
非查询语句--》 隐式的
结果是单行的查询语句 --》隐式的或显示的
结果是多行的查询语句--》 显示的

2.处理显示游标

2.1显示游标处理的4个步骤

1.定义/ / 声明 游标：就是定义一个游标名，以及与其相对应的 SELECT 语句。

格式：

CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;

注意：1.游标参数只能为输入参数

2.在指定数据类型时，不能使用长度约束

3.[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与 select_statement 中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。

2.打开游标

就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指
针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选
项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式：OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

3.提取游标

就是检索结果集合中的数据行，放入指定的输出变量中。

格式：FETCH cursor_name INTO {variable_list | record_variable };

执行 FETCH 语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当
检索到最后一行数据时，如果再次执行 FETCH 语句，将操作失败，并将游标属性
%NOTFOUND 置为 TRUE。所以每次执行完 FETCH 语句后，检查游标属性%NOTFOUND
就可以判断 FETCH 语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了
值。

4.关闭游标

当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占
用的系统资源，并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。
关闭后的游标可以使用 OPEN 语句重新打开。

格式：CLOSE cursor_name;

注意：定义的游标不能有 INTO 子句。

 

游标属性
游标属性 属性作用
Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作 FETCH成功则为 TRUE,否则为 FALSE；
Cursor_name%NOTFOUND 布尔型属性，与%FOUND 相反；
Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

 

2.2使用案例

--普通游标
declare
  cursor emp_cursor1 is
    select ename,sal from emp where sal < 1000;
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  open emp_cursor1;
  loop
    fetch emp_cursor1
      into v_name,v_sal;
    exit when emp_cursor1%notfound;
    dbms_output.put_line(v_name || '---' || v_sal);
     --dbms_output.put_line('isOpen:'||emp_cursor1%isopen);  --查看游标状态
  end loop;
  close emp_cursor1;
end;--游标传参
declare
  cursor emp_cursor2(cursor_sal number default 2000) is  --(此处的参数是默认参数，后面可以重新设置，此默认值失效)
    select ename,sal from emp where sal >= cursor_sal;
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  open emp_cursor2(cursor_sal=>3000);  --（重新设置参数值）
  loop
    fetch emp_cursor2
      into v_name,v_sal;
    exit when emp_cursor2%notfound;
    dbms_output.put_line(v_name || '---' || v_sal);
  end loop;
  dbms_output.put_line('rowcount:'||emp_cursor2%rowcount);  --(游标记录数)
  close emp_cursor2;
end;--if...then（给工资低于2000的员工加薪1元）
declare
  cursor emp_cursor3 is
    select ename, sal, empno from emp;
  v_no   emp.empno%type;
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  open emp_cursor3;
  loop
    fetch emp_cursor3
      into v_name, v_sal, v_no;
    exit when emp_cursor3%notfound;
    if v_sal <= 2000 then
      update emp set sal = sal + 1 where empno = v_no;
      dbms_output.put_line(v_name || '---' || v_sal);
    end if;
  end loop;
  dbms_output.put_line('rowcount:' || emp_cursor3%rowcount);
  close emp_cursor3;
end;--有参数有返回值的游标（记录类型）
declare
  type emp_record_type is record(
    v_no   emp.empno%type,
    v_name emp.ename%type,
    v_sal  emp.sal%type);
  v_emp_record emp_record_type;
  cursor emp_cursor4(dept_no number) return emp_record_type is
    select empno, ename, sal  from emp where deptno = dept_no;
begin
  open emp_cursor4(dept_no=>20);
  loop
    fetch emp_cursor4
      into v_emp_record;
    if emp_cursor4%found then
      dbms_output.put_line(v_emp_record.v_name || '---' ||
                           v_emp_record.v_sal);
    else
      dbms_output.put_line('已经处理完结果集');
      exit;
    end if;
  end loop;
  close emp_cursor4;
end;



--for循环格式游标
declare
  cursor emp_cursor5 is
    select ename, sal from emp;
begin
  for v_sal in emp_cursor5 loop
    dbms_output.put_line(v_sal.ename || '--***--' || v_sal.sal);
  end loop;
end;--for循环格式游标带参数
declare
  cursor emp_cursor5(dept_no number) is
    select ename, sal from emp where deptno=dept_no;
begin
  for v_sal in emp_cursor5(30) loop
    dbms_output.put_line(v_sal.ename || '--***--' || v_sal.sal);
  end loop;
end;--for循环子查询方式游标
begin
  for v_sal in (select ename, sal from emp where deptno=20) loop
    dbms_output.put_line(v_sal.ename || '--***--' || v_sal.sal);
  end loop;
end;

3.处理隐式游标

 

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对
于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建
其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由
ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE
系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操
作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的
一条 SQL 语句所包含的数据。
格式调用为： SQL%

隐式游标属性
属性 值 SELECT INSERT UPDATE DELETE
SQL%ISOPEN FALSE FALSE FALSE FALSE
SQL%FOUND TRUE 有结果 成功 成功
SQL%FOUND FALSE 没结果 失败 失败
SQL%NOTFUOND TRUE 没结果 失败 失败
SQL%NOTFOUND FALSE 有结果 成功 失败
SQL%ROWCOUNT 返回行数，只为 1插入的行数 修改的行数 删除的行数
使用案例：
DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE emp SET sal = 30000
WHERE deptno = 20;
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--回退更新，以便使数据库的数据保持原样
ROLLBACK;
END;