Oracle数据库学习第五章

本章学习要点：

游标

动态游标

静态SQL/动态SQL

事务

子程序

游标

什么是游标？ 游标是系统为用户开设的一个数据缓冲区，存放SQL 语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录

游标的分类 静态游标 :在编译时知道其SELECT 语句的游标

– 1)隐式游标 :在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标 隐式游标的属性有： %FOUND – SQL 语句影响了一行或多行时为 TRUE %NOTFOUND – SQL 语句没有影响任何行时为TRUE %ROWCOUNT – SQL 语句影响的行数 %ISOPEN - 游标是否打开，始终为FALSE

–复制新表 create table myemp as select * from emp; select * from myemp where deptno=10;

begin update myemp set comm=50 where deptno=10; dbms_output.put_line(‘执行后影响的有’||sql%rowcount||’行!’); end;

–2)显示游标: 用于处理返回多行的查询 说明光标语法：CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]…)] IS SELECT 语句; (1)声明游标: (2)打开光标： open c1; (打开光标执行查询) (3)取一行光标的值：fetch c1 into pjob; (取一行到变量中) (4)关闭光标： close c1;(关闭游标释放资源)*/

a)查询某一个部门的员工信息(所有字段) select * from emp where deptno=10;

declare v_deptno emp.deptno%type:=&no; –(1)声明游标 cursor c1 is select * from emp where deptno=v_deptno; –声明变量(保存一行数据) v_emp emp%rowtype; begin –(2)打开游标 open c1; –循环 loop –(3)取一行光标的值 fetch c1 into pjob; (取一行到变量中) fetch c1 into v_emp; –退出循环 exit when c1%notfound; dbms_output.put_line(v_emp.empno||’–’||v_emp.ename||’–’||v_emp.job); end loop; –(4)关闭游标 close c1; end;

b)查询某一个部门的员工的姓名以及工资信息(部分字段) select ename,sal from emp where deptno=10;

declare v_ename emp.ename%type; v_sal emp.sal%type; v_deptno emp.deptno%type:=&no; –1)声明游标 cursor c1 is select ename,sal from emp where deptno=v_deptno; begin –2)打开游标 open c1; –3)循环提前数据 loop fetch c1 into v_ename,v_sal; exit when c1%notfound; dbms_output.put_line(v_ename||’–’||v_sal); end loop;

–4)关闭游标 close c1; end;

显式游标的技巧有哪些？ 简单显式游标 基于游标定义记录变量

使用显式游标更新行

–For循环游标 (不需要打开,关闭) declare –1)声明游标 cursor c1 is select *from emp; begin for v in c1 loop dbms_output.put_line(v.empno||’–’||v.ename||’–’||v.job||’–’||v.sal); end loop; end;

3).使用显式游标更新行 –游标for循环（给所有的部门经理减薪1000） select * from emp where job=’MANAGER’

declare cursor c1 is select * from emp where job=’MANAGER’ for update; –更新 begin for v_emp in c1 loop update emp set sal = sal+1000 where current of c1; –where 条件后面 指定当前游标 end loop; end;

–游标for循环（删除所有的部门经理） select * from myemp where job=’MANAGER’

declare cursor c1 is select * from myemp where job=’MANAGER’ for update; –更新 begin for v_myemp in c1 loop delete from myemp where current of c1; –where 条件后面 指定当前游标 end loop; end;

–带参数显式游标 说明光标语法：CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]…)] IS SELECT 语句; declare -游标for循环（查询20部门并且是CLERK的员工信息） select *from emp where deptno=20 and job=’CLERK’

declare –参数不能写数据类型大小 cursor c1(v_no number,v_job varchar2) is select *from emp where deptno=v_no and job=v_job; begin for v_emp in c1(20,’CLERK’) loop dbms_output.put_line(v_emp.empno||’–>’||v_emp.ename||’–>’||v_emp.job||’–>’||v_emp.mgr||’–>’||v_emp.sal); end loop; end;

.基于游标定义记录变量*/ –查询前10名的员工信息 declare cursor c1(v_number number) is select *from emp where rownum<=v_number; –声明游标 – v_emp emp%rowtype; –记录类型变量 v_emp c1%rowtype; –游标定义记录变量 (比声明记录类型变量要方便，不容易出错) begin –打开游标 open c1(10);

–提起游标 loop fetch c1 into v_emp; –提取数据 exit when c1%notfound; –退出循环 dbms_output.put_line(v_emp.empno||’–>’||v_emp.ename||’–>’||v_emp.job||’–>’||v_emp.mgr||’–>’||v_emp.sal); end loop;

–关闭游标 close c1;

end;

动态游标

什么是动态游标？ 动态游标也称REF游标 经常用于处理运行时动态执行的 SQL 查询 –动态游标的操作步骤: 1.声明游标 – 声明 REF 游标类型: TYPE REF_CURSOR_TYPE IS REF CURSOR; –声明 REF 游标类型的变量 cv_ref REF_CURSOR_TYPE; 2.打开游标 – open 游标名 for sql_statement; 3.提取游标 –fetch 游标名 into cv_ref; 4.关闭游标 – close 游标名

例1. 查询前10名员工的信息。 select *from emp where rownum<=10;

declare –REF 游标类型 type c1 is ref cursor;

–REF 游标类型的变量 v_ref c1;

–记录类型变量 v_emp emp%rowtype; begin –打开游标 open v_ref for select *from emp where rownum<=4;

– 提取游标 loop fetch v_ref into v_emp; exit when v_ref%notfound; dbms_output.put_line(v_emp.empno||’–>’||v_emp.ename||’–>’||v_emp.job||’–>’||v_emp.mgr||’–>’||v_emp.sal); end loop;

–关闭游标 close v_ref; end;

1：定义REF动态游标类型

TYPE <类型名> IS REF CURSOR RETURN <返回类型>; 声明REF动态游标 –<游标名> <类型名> 打开REF动态游标 –OPEN <游标名> FOR <查询语句>

REF动态游标的分类 主要分为两类，强类型和弱类型的， –(1)强类型的为带有RETURN语句的REF动态游标， /例1. 查询前10名员工的信息。/ select *from emp where rownum<=10;

declare –REF 游标类型 type c1 is ref cursor return emp%rowtype;

–REF 游标类型的变量 v_ref c1;

–记录类型变量 (不能使用游标记录类型变量) v_emp emp%rowtype; begin –打开游标 open v_ref for select *from emp where rownum<=4;

– 提取游标 loop fetch v_ref into v_emp; exit when v_ref%notfound; dbms_output.put_line(v_emp.empno||’–>’||v_emp.ename||’–>’||v_emp.job||’–>’||v_emp.mgr||’–>’||v_emp.sal); end loop;

–关闭游标 close v_ref; end;

–(2)弱类型为不带有RETURN语句的REF动态游标 查询员工的姓名与工资 select ename,sal from emp;

declare –ref游标类型 type v_ref is ref cursor; –游标变量 c1 v_ref; –变量 v_ename emp.ename%type; v_sal emp.sal%type; begin –打开游标 open c1 for select ename,sal from emp; loop fetch c1 into v_ename,v_sal; exit when c1%notfound; dbms_output.put_line(v_ename||’:’||v_sal); end loop;

close c1;

end;

–将同一个游标变量对应到另一个SELECT语句 declare type v_ref is ref cursor; c1 v_ref; –记录类型变量 v_emp emp%rowtype; v_dept dept%rowtype; begin open c1 for select * from emp where rownum<=5; loop fetch c1 into v_emp; exit when c1%notfound; dbms_output.put_line(v_emp.empno||’–>’||v_emp.ename||’–>’||v_emp.job||’–>’||v_emp.mgr||’–>’||v_emp.sal);

end loop;

—将同一个游标变量对应到另一个SELECT语句 open c1 for select * from dept where rownum<=2; loop fetch c1 into v_dept; exit when c1%notfound; dbms_output.put_line(v_dept.deptno||’–>’||v_dept.dname||’–>’||v_dept.loc);

end loop;

– close c1; end;

静态SQL/动态SQL

什么是静态SQL？ 需要在编写PL/SQL程序时就确定的SQL语句 什么是动态SQL？ 动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

(1)使用EXECUTE IMMEDIATE 语句执行： DDL 语句、DCL 语句、非查询的DML 语句、单行查询的SELECT 语句

–语法: EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list];

dynamic_sql_string 是动态SQL 语句字符串 INTO 子句用于接受SELECT语句选择的记录值 USING 子句用于绑定输入参数变量 创建表student(id number primary key,sname varchar2(20);

declare v_sql varchar2(500); –存放sql的变量 begin v_sql:=’create table student(id number primary key,sname varchar2(20))’; –立即动态执行DDL语句 execute immediate v_sql; –处理异常 exception when others then null; end;

添加学生一条数据 declare v_sql varchar2(500); –存放sql的变量 begin v_sql:=’insert into student values(:1,:2)’; –立即动态执行DDL语句 – execute immediate v_sql using 1,’张三’; execute immediate v_sql using 2,’司马光’; –处理异常 exception when others then null; end;

查询学生表的个数 declare v_sql varchar2(500); –存放sql的变量 v_number number; –存放个数 begin v_sql:=’select count(1) from student where id=:1’; –立即动态执行DDL语句 execute immediate v_sql into v_number using 2; dbms_output.put_line(v_number); –处理异常 exception when others then null; end;

select * from student;

–(2)通过游标实现： 多行查询的SELECT语句

OPEN cursor_name FOR dynamic_sql_string [USING bind_argument_list];

dynamic_sql_string 是动态SQL 语句字符串 USING 子句用于绑定输入参数变量

–利用动态游标实现动态SQL循环遍历 select * from emp where deptno=20;

declare type v_ref is ref cursor; c1 v_ref; v_emp emp%rowtype; –sql语句存放的变量 v_sql varchar2(500); begin v_sql:=’select * from emp where deptno=:1’; –:1代表是参数第一个,依次类推…2,3..

--打开游标 open c1 for v_sql using 20; --using 后面是具体的参数值 --提取数据 loop fetch c1 into v_emp; exit when c1%notfound; dbms_output.put_line(v_emp.empno||'-->'||v_emp.ename||'-->'||v_emp.job||'-->'||v_emp.mgr||'-->'||v_emp.sal); end loop; --关闭游标 close c1;

end;

事务

是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位 –事务通常是以BEGIN搜索 TRANSACTION开始，以COMMIT或ROLLBACK结束。 –事务的特性(ACID特性) A:原子性(Atomicity) 事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。 B:一致性(Consistency) 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 C:隔离性(Isolation) 一个事务的执行不能被其他事务干扰。 D:持续性/永久性(Durability) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

select * from student;

insert into student values(3,’boy’); commit; –手动提交事务

–命令窗口测试 –4.撤销的处理必须是在没有发出commit命令的前提下才能有效。 update student set sname=’战俘 ‘where id=3; savepoint cc; –保存点 delete from student where id=2; rollback to savepoint cc; –回滚到保存点 commit; –手动提交事务

子程序

1.PL/SQL命名程序块的定义在PL/SQL程序块的声明部分，用户称之为子程序。

2.Oracle提供了4中类型的子程序：过程，函数，包，触发器。

3.子程序的优点： （1）模块性：子程序可以将程序划分成易于管理，定义明确的逻辑模块，它支持自顶向下的设计方法。 （2）可维护性：简化维护操作。 （3）可重性：子程序一旦被定义可以用于许多个应用程序。 （4）安全性：通过设置权限，使数据更安全。

存储过程的语法: CREATE [OR REPLACE] PROCEDURE –关键字固定写法 [()] –存储过程名称,以及参数 IS|AS –声明变量 BEGIN –执行语句 [EXCEPTION –异常处理 ] END;

–java 两数求和方法 1.无参无返回 /* public void add() { int a=10; int b=20; sysout(a+b) }*/

/* add()*/

create or replace procedure proc_add1 is a number:=10; b number:=20; begin dbms_output.put_line(a+b); end;

–调用存储过程 pl/sql begin proc_add1; end;

–命令窗口调用 必须设置使用set serveroutput on 命令设置环境变量 execute proc_add1;

2.有参无返回 /* public void add(int a,int b) { sysout(a+b) }*/

/* add()*/

–(1)输入参数 默认是in(一般不写) create or replace procedure proc_add2(a in number,b in number) as begin dbms_output.put_line(a+b); end; –调用存储过程 pl/sql begin proc_add2(1000,20); end;

其中model有三种形式，分别是in,out和in out.

–(3)输出参数 必须写out 3.无参有返回 /* public int add() { int a=10; int b=20;

return(a+b) }*/

/* int sum=add() sysout(sum)*/

create or replace procedure proc_add3(sum out number) is a number:=10; b number:=20; begin sum:=a+b; end;

–调用存储过程 declare sums number; begin proc_add3(sums); dbms_output.put_line(sums); end;

–in out. 输入输出 (在过程中可以读取和写入该类型的参数) (一般很少用!!) 2*2*2 –select power(2,3) from dual; create or replace procedure proc_power(nums in out number) is begin nums:=nums*nums*nums; end;

–调用存储过程 declare nums number:=6; begin proc_power(nums); dbms_output.put_line(nums); end;

4.有参有返回 /* public int add(int a,int b) { return(a+b) }*/

create or replace procedure proc_add4(a number,b number, sums out number) is begin sums:=a+b; end;

–调用存储 declare a number:=&a; b number:=&b; nums number; begin proc_add4(a,b,nums); dbms_output.put_line(nums); end;