个人笔记基础必看游标、本地动态SQL、程序包实现动态SQL

最新推荐文章于 2019-05-28 08:03:49 发布

原创最新推荐文章于 2019-05-28 08:03:49 发布 · 415 阅读

0 ·

CC 4.0 BY-SA版权

数据库知识专栏收录该内容

30 篇文章

订阅专栏

本文深入探讨了SQL游标的基本概念、显式游标的应用，并通过实例展示了如何使用游标来遍历数据集、执行更新操作以及进行多表查询。文章详细介绍了游标的声明、打开、提取数据、循环和关闭过程，以及如何通过游标进行动态SQL查询和参数化查询。通过丰富的示例，读者可以了解到如何高效地利用游标技术在数据库操作中提升性能和灵活性。

--显式游标

DECLARE

name scott.emp.ename%type;

sal scott.emp.sal%type; --定义两个变量

--1声明游标

CURSOR emp_cursor IS

SELECT ename, sal from scott.emp;

BEGIN

--2打开游标

open emp_cursor;

LOOP

--3使用提取游标

FETCH emp_cursor

INTO name, sal;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line('第' || emp_cursor%ROWCOUNT || '员工的名字是' || name ||

'工资是' || sal);

END LOOP;

--关闭游标

CLOSE emp_cursor;

END;

--打印部门名称，地点

select * from dept;

DECLARE

name scott.dept.dname%type;

loc scott.dept.loc%type; --定义两个变量

--1声明游标

CURSOR dept_cursor IS

SELECT dname, loc from scott.dept;

BEGIN

--2打开游标

open dept_cursor;

LOOP

--3使用提取游标

FETCH dept_cursor

INTO name, loc;

EXIT WHEN dept_cursor%NOTFOUND;

dbms_output.put_line('部门名称' || name || '部门地点' || loc);

END LOOP;

--关闭游标

CLOSE dept_cursor;

END;

--基于游标定义记录

DECLARE

--声明游标

cursor emp_cursor is

select ename, sal from scott.emp where deptno = 10;

--声明变量

emp_record emp_cursor%rowtype;

BEGIN

if not emp_cursor%isopen then

open emp_cursor;

END if;

loop

fetch emp_cursor

into emp_record;

exit when emp_cursor%notfound;

dbms_output.put_line(emp_record.ename || ':' || '工资是' ||

emp_record.sal);

end loop;

close emp_cursor;

END;

--打印部门名称，地点,用定义游标的方法

select * from dept;

DECLARE

--1声明游标

CURSOR dept_cursor IS

SELECT dname, loc from scott.dept;

--声明变量

dept_record dept_cursor%rowtype;

BEGIN

--2打开游标

if not dept_cursor%isopen then

open dept_cursor;

end if;

LOOP

--3使用提取游标

FETCH dept_cursor

INTO dept_record;

EXIT WHEN dept_cursor%NOTFOUND;

dbms_output.put_line('部门名称' || dept_record.dname || '部门地点' ||

dept_record.loc);

END LOOP;

--关闭游标

CLOSE dept_cursor;

END;

--带参显式游标

declare

v_no number := &v_no; --动态赋值

cursor emp_cursor(no number) is

select ename, sal from scott.emp where deptno = no;

emp_record emp_cursor%rowtype;

v_emp scott.emp%rowtype;

v_sal scott.emp.sal%type;

begin

if not emp_cursor%isopen then

open emp_cursor(v_no);

end if;

loop

fetch emp_cursor

into emp_record;

exit when emp_cursor%notfound;

dbms_output.put_line('名字' || emp_record.ename || '工资' ||

emp_record.sal);

end loop;

close emp_cursor;

end;

--使用显式游标更新游标

--单表

select * from scott.emp;

declare

cursor emp_cursor is

select ename, sal, deptno from scott.emp for update;

emp_record emp_cursor%rowtype;

begin

if not emp_cursor%isopen then

open emp_cursor;

end if;

loop

fetch emp_cursor

into emp_record;

exit when emp_cursor%notfound;

if emp_record.deptno = 30 and emp_record.sal > 2500 then

-- delete from scott.emp where current of emp_cursor;

update scott.emp set sal = sal + 500 where current of emp_cursor;

end if;

end loop;

close emp_cursor;

end;

--多表

--for update of 表的字段

select * from emp;

select * from dept;

declare

cursor emp_cursor is

select ename, sal, emp.deptno, dname

from scott.emp, scott.dept

where emp.deptno = dept.deptno

for update of scott.emp.sal, scott.dept.deptno;

emp_record emp_cursor%rowtype;

begin

if not emp_cursor%isopen then

open emp_cursor;

end if;

loop

fetch emp_cursor

into emp_record;

exit when emp_cursor%notfound;

if emp_record.deptno = 30 then

update scott.emp set sal = sal + 100 where current of emp_cursor;

update scott.dept set dname = 'name' where current of emp_cursor;

end if;

dbms_output.put_line(emp_record.sal);

end loop;

close emp_cursor;

end;

--循环游标

declare

cursor emp_cursor is

select ename, sal from emp;

begin

for emp_record in emp_cursor loop

dbms_output.put_line('第' || emp_cursor%rowcount || '个员工' ||

emp_record.ename || '的工资' || emp_record.sal);

end loop;

end;

--动态游标

select * from emp;

commit;

select * from dept;

--显式雇员表中20号部门雇员信息和部门表中部分部门信息（部门编号是10）

declare

--定义一个游标数据类型

type emp_cursor_type is ref cursor;

--声明游标变量

c1 emp_sursor_type;

--声明两个记录变量

v_emp_record emp%rowtype;

v_dept_record dept%rowtype;

begin

open c1 for

select * from emp where deptno = 20;

loop

fetch c1

into v_emp_record;

exit when c1%notfound;

dbms_output.put_line('名字是' || v_emp_record.ename || '员工号是' ||

v_emp_record || '工作是' || v_emp_record.job ||

'工资是' || v_emp_record.sal);

end loop;

open c1 for

select * from dept where deptno in (10, 20);

loop

fetch c1

into v_dept_record;

exit when c1%notfound;

dbms_output.put_line(v_emp_record.ename || v_emp_record.hiredate);

end loop;

close c1;

end;

--本地动态

DECLARE

v_id number := &v_idddd;

v_count number;

str_sql varchar2(500);

BEGIN

str_sql := 'select count(*) from scott.emp where deptno=:dept1no';

execute immediate str_sql

into v_count

using v_id;

dbms_output.put_line(v_count);

end;

--查询部门30号员工信息

DECLARE

type emp_cur is ref cursor;

my_emp_cur emp_cur;

my_emp_rec emp%rowtype;

BEGIN

open my_emp_cur for 'select * from scott.emp where

deptno=:XXX'

using 30;

loop

fetch my_emp_cur

into my_emp_rec;

exit when my_emp_cur%notfound;

dbms_output.put_line(my_emp_rec.ename || my_emp_rec.sal);

end loop;

close my_emp_cur;

end;

--通过程序包实现动态SQL

DECLARE

id number;

name varchar2(20);

v_cursor number; --定义光标

v_string varchar2(200); --字符串变量

v_row number; --行数

BEGIN

id := 10;

name := 'Marry';

--为处理打开光标

v_cursor := dbms_sql.open_cursor;

v_string := 'update scott.emp

set name=:p_name where

deptno=:p_id';

--分析语句

dbms_sql.parse(v_cursor, v_string, dbms_sql.native);

--绑定变量

dbms_sql.bind_variable(v_cursor, ':p_name', name);

--绑定变量

dbms_sql.bind_variable(v_cursor, ':p_id', id);

v_row ： = dbms_sql.execute(v_cursor); --执行动态SQL

dbms_output.put_line(v_row);

dbms_sql.close_cursor(v_cursor); --关闭光标

exception

when others then

dbms_sql.close_cursor(v_cursor); --关闭光标

raise; --提示异常

END;

--修改雇员编号为7788的福利补助（COMM）列为1000

DECLARE

v_empno number;

v_sql varchar2(200);

BEGIN

v_empno := 7788;

v_sql := 'update emp set comm=1000 where empno=:b_emp';

execute immediate v_sql

using v_empno;

END;

select * from emp where empno = 7788;

--查询工种为‘CLERK’的所有雇员信息

DECLARE

type V_cursor_type is ref cursor; --声明一个游标类型

cur_emp v_cursor_type;

v_sql varchar2(200);

v_job varchar2(20);

v_emp_record emp%rowtype;

BEGIN

v_job := 'CLERK';

v_sql := 'select * from emp where JOB=:b_job';

open cur_emp for v_sql

using v_job;

loop

fetch cur_emp

into v_emp_record;

exit when cur_emp%notfound;

dbms_output.put_line(v_emp_record.ename || v_emp_record.job);

end loop;

close cur_emp;

/*for emp_record in cur_emp loop

dbms_output.put_line(emp_record.ename||emp_record.job);

end loop;

close cur_emp;*/

END;