- 浏览: 239324 次
最新评论
oracle游标
游标-----内存中的一块区域,存放的是select的结果
游标用来处理从数据库中检索的多行记录(使用SELECT语句)。利用游标,程序可以逐个地处理和遍历一次检索返回的整个记录集。
为了处理SQL语句,Oracle将在内存中分配一个区域,这就是上下文区。这个区包含了已经处理完的行数、指向被分析语句的指针,游标就是指向上下文区句柄或指针。
缺点:游标使用时会对行加锁,可能会影响其他业务的正常进行。
而且,数据量大时其效率也较低。因为游标其实是相当于把磁盘数据整体放入了内存中,如果游标数据量大会造成内存不足,也有可能导致cpu高,所以,在数据量小时才使用游标。
优点:相当于程序中的FOR循环处理。一条一条的处理你读取的记录内容。
数据存放在内存中,数据量少的时候速度比较快。
一、显示游标
显示游标被用于处理返回多行数据的SELECT 语句,游标名通过CURSOR….IS 语句显示地赋给SELECT 语句。
在PL/SQL中处理显示游标所必需的四个步骤:
1)声明游标;CURSOR cursor_name IS select_statement
2)为查询打开游标;OPEN cursor_name
3)取得结果放入PL/SQL变量中;
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4)关闭游标。CLOSE cursor_name
注意:在声明游标时,select_statement不能包含INTO子句。当使用显示游标时,INTO子句是FETCH语句的一部分。
二、隐式游标
所有的隐式游标都被假设为只返回一条记录。
使用隐式游标时,用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理,然后关掉游标。
例如:
SELECT studentNo,studentName INTO curStudentNo,curStudentName
FROM StudentRecord WHERE name=’gg’;
上述游标自动打开,并把相关值赋给对应变量,然后关闭。执行完后,PL/SQL变量curStudentNo,curStudentName中已经有了值。
单条sql语句所产生的结果集合
用关键字SQL表示隐式游标
4个属性 %rowcount 影响的记录的行数 整数
%found 影响到了记录 true
%notfound 没有影响到记录 true
%isopen 是否打开 布尔值 永远是false
多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果
主要使用在update 和 delete语句上
三、常见游标实例
(1)for循环游标
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select id,ins_time,random_string from mytest where random_id=50; ---定义游标
c1cur c1%rowtype; ---定义一个游标变量
begin
for c1cur in c1 loop ---for循环
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(2) fetch游标 --使用的时候 必须要明确的打开和关闭
declare
cursor c1 is select id,ins_time,random_id,random_string from mytest where random_id=50; ---定义显示游标
c1cur c1%rowtype; ---定义一个游标变量
begin
open c1; --打开游标
loop
fetch c1 into c1cur; ---取一行数据到游标变量
exit when c1%notfound; ---判断是否取到了值
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
close c1; ---关闭游标
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(3)参数游标(以下都利用scott自带的几张表测试)
SQL> declare
cursor c1 is select deptno from dept;
cursor c2(pno number,pjob varchar2) is select * from emp where deptno=pno and job=pjob; --只能指定类型,不能指定长度
c1cur c1%rowtype;
c2cur c2%rowtype;
begin
for c1cur in c1 loop
for c2cur in c2(c1cur.deptno,'MANAGER')loop
dbms_output.put_line(c1cur.deptno||':'||c2cur.ename||'---'||c2cur.sal);
end loop;
end loop;
end;
/
10:CLARK---2450
20:JONES---2975
30:BLAKE---2850
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(4)引用游标/动态游标
SQL>declare
type c1 is ref cursor; --定义一个类型(ref cursor)弱类型
type c2 is ref cursor return emp%rowtype; --定义一个强类型(返回的结果集有要求)
c0cur c1; ---定义一个弱类型的游标变量
c1cur emp%rowtype;
c2cur dept%rowtype;
begin
dbms_output.put_line('all employees :');
open c0cur for select * from emp;
loop
fetch c0cur into c1cur;
exit when c0cur%notfound;
dbms_output.put_line(c1cur.ename);
end loop;
dbms_output.put_line('all departments:');
open c0cur for select * from dept;
loop
fetch c0cur into c2cur;
exit when c0cur%notfound;
dbms_output.put_line(c2cur.dname);
end loop;
close c0cur;
end;
/
all employees:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
all departments:
ACCOUNTING
RESEARCH
SALES
OPERATIONS
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(5)while循环
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
fetch c1 into c1cur; ---与for循环不同,while循环需要将下一行的值赋给游标变量,否则会进入死循环,并报错
end loop;
close c1;
end;
/
location: NEW YORK
location: DALLAS
location: CHICAGO
location: BOSTON
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
死循环,报错:
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
end loop;
close c1;
end;
/
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)
执行时,单个进程就导致cpu 100%,因此游标还是要小心使用的!
(6)利用游标来更新(update)
6.1 声明更新显示游标:
Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名];
6.2 使用显示游标当前记录来更新:
Update 表名 SET 更新语句 Where Current Of 游标名;
1.所有人普调
SQL> declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
saladd emp.sal%type;
salnew emp.sal%type;
begin
for c1cur in c1 loop
saladd:=c1cur.sal*0.2; --所有员工加薪20%
if saladd<300 then
salnew:=c1cur.sal+300; --加薪不足300的,按300加
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal+saladd;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end if;
update emp set sal=salnew where current of c1;
end loop;
end ;
/
SMITH:new salary 1300 ---原来工资1000
ALLEN:new salary 2304
WARD:new salary 1800
JONES:new salary 4284
MARTIN:new salary 1800
BLAKE:new salary 4104
CLARK:new salary 3528
SCOTT:new salary 4320
KING:new salary 7200
TURNER:new salary 2160
ADAMS:new salary 1620
JAMES:new salary 1450
FORD:new salary 4320
MILLER:new salary 1872
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
2.按照部门来调整
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
case ---必须包含所有的分类,否则会报错ORA-06592: CASE not found while executing CASE statement
when c1cur.deptno=10
then salnew:=c1cur.sal*1.1;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=20
then salnew:=c1cur.sal*1.15;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=30
then salnew:=c1cur.sal*1.2;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=40
then salnew:=c1cur.sal*1.3;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end case;
update emp set sal=salnew where current of c1;
end loop;
end;
/
SMITH:new salary 1495
ALLEN:new salary 2764.8
WARD:new salary 2160
JONES:new salary 4926.6
MARTIN:new salary 2160
BLAKE:new salary 4924.8
CLARK:new salary 3880.8
SCOTT:new salary 4968
KING:new salary 7920
TURNER:new salary 2592
ADAMS:new salary 1863
JAMES:new salary 1740
FORD:new salary 4968
MILLER:new salary 2059.2
LITING:new salary 7800
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
3.符合if条件的
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
if c1cur.deptno=20 then
salnew:=c1cur.sal*5-8000;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal;
end if;
update emp set sal=salnew where current of c1;
end loop;
end;
/
SMITH:new salary 1525
JONES:new salary 6633
SCOTT:new salary 6840
ADAMS:new salary 1315
FORD:new salary 6840
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
4.以平均值为分界
SQL> select avg(sal) from emp; 平均工资3715
AVG(SAL)
----------
3715
SQL> declare
2 cursor c1 is select empno,ename,sal,deptno,avg(sal) over(partition by deptno) as depavg from emp for update of sal;
3 c1cur c1%rowtype;
4 salnew emp.sal%type;
5 begin
6 for c1cur in c1 loop
7 if c1cur.sal>c1cur.depavg then
8 salnew:=c1cur.sal+50; ---高于平均工资的奖金发50
9 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
10 else
11 salnew:=c1cur.sal+100; ---低于平均工资的奖金发100
12 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
13 end if;
14 update emp set sal=salnew where current of c1;
15 end loop;
16 end;
17 /
CLARK:new salary 3980
MILLER:new salary 2160
KING:new salary 7970
JONES:new salary 4976
SMITH:new salary 1595
SCOTT:new salary 5018
FORD:new salary 5071
ADAMS:new salary 1415
WARD:new salary 2260
TURNER:new salary 2692
ALLEN:new salary 2814
JAMES:new salary 1840
MARTIN:new salary 2260
BLAKE:new salary 4974
LITING:new salary 7900
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
(7)利用游标来删除(delete)
7.1 声明删除显示游标:
Cursor 游标名IS SELECT 语句 For Delete;
7.2 使用显示游标当前记录来更新或删除:
Delete From 表名 Where Current Of 游标名;
SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:03.05
SQL> declare
cursor c1 is select ename,job from emp1 for update;
e_job emp1.job%type;
e_name emp1.ename%type;
begin
open c1;
loop
fetch c1 into e_name,e_job; --顺序要跟上面的select顺序对应
exit when c1%notfound;
if e_job='ANALYST' then
delete from emp1 where current of c1;
dbms_output.put_line(e_name||':'||'deleted');
end if;
end loop;
close c1;
end;
/
SCOTT:deleted
FORD:deleted
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
(8)利用游标来插入
SQL> declare
2 cursor c1 is select * from emp1;
3 c1cur c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c1cur;
8 exit when c1%notfound;
9 insert into emp values c1cur;
10 dbms_output.put_line(c1cur.ename||': inserted');
11 end loop;
12 close c1;
13 end;
14 /
TINA: inserted
BOBO: inserted
LIO: inserted
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
SQL> select * from emp; ---查看数据,插入成功
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1595 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2814 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2260 500 30
7566 JONES MANAGER 7839 02-APR-81 4976 20
7654 MARTIN SALESMAN 7698 28-SEP-81 2260 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4974 30
7782 CLARK MANAGER 7839 09-JUN-81 3980 10
7788 SCOTT ANALYST 7566 19-APR-87 5018 20
7839 KING PRESIDENT 17-NOV-81 7970 10
7844 TURNER SALESMAN 7698 08-SEP-81 2692 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1415 20
7900 JAMES CLERK 7698 03-DEC-81 1840 30
7902 FORD ANALYST 7566 03-DEC-81 5071 20
7934 MILLER CLERK 7782 23-JAN-82 2160 10
7200 LITING CLERK 7788 03-DEC-81 7900 500 40
6000 TINA CLERK 7788 02-APR-86 4503 300 10
6221 BOBO CLERK 7788 02-APR-86 4000 200 10
6307 LIO CLERK 7788 02-APR-86 4409 0 10
游标-----内存中的一块区域,存放的是select的结果
游标用来处理从数据库中检索的多行记录(使用SELECT语句)。利用游标,程序可以逐个地处理和遍历一次检索返回的整个记录集。
为了处理SQL语句,Oracle将在内存中分配一个区域,这就是上下文区。这个区包含了已经处理完的行数、指向被分析语句的指针,游标就是指向上下文区句柄或指针。
缺点:游标使用时会对行加锁,可能会影响其他业务的正常进行。
而且,数据量大时其效率也较低。因为游标其实是相当于把磁盘数据整体放入了内存中,如果游标数据量大会造成内存不足,也有可能导致cpu高,所以,在数据量小时才使用游标。
优点:相当于程序中的FOR循环处理。一条一条的处理你读取的记录内容。
数据存放在内存中,数据量少的时候速度比较快。
一、显示游标
显示游标被用于处理返回多行数据的SELECT 语句,游标名通过CURSOR….IS 语句显示地赋给SELECT 语句。
在PL/SQL中处理显示游标所必需的四个步骤:
1)声明游标;CURSOR cursor_name IS select_statement
2)为查询打开游标;OPEN cursor_name
3)取得结果放入PL/SQL变量中;
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4)关闭游标。CLOSE cursor_name
注意:在声明游标时,select_statement不能包含INTO子句。当使用显示游标时,INTO子句是FETCH语句的一部分。
二、隐式游标
所有的隐式游标都被假设为只返回一条记录。
使用隐式游标时,用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理,然后关掉游标。
例如:
SELECT studentNo,studentName INTO curStudentNo,curStudentName
FROM StudentRecord WHERE name=’gg’;
上述游标自动打开,并把相关值赋给对应变量,然后关闭。执行完后,PL/SQL变量curStudentNo,curStudentName中已经有了值。
单条sql语句所产生的结果集合
用关键字SQL表示隐式游标
4个属性 %rowcount 影响的记录的行数 整数
%found 影响到了记录 true
%notfound 没有影响到记录 true
%isopen 是否打开 布尔值 永远是false
多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果
主要使用在update 和 delete语句上
三、常见游标实例
(1)for循环游标
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select id,ins_time,random_string from mytest where random_id=50; ---定义游标
c1cur c1%rowtype; ---定义一个游标变量
begin
for c1cur in c1 loop ---for循环
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(2) fetch游标 --使用的时候 必须要明确的打开和关闭
declare
cursor c1 is select id,ins_time,random_id,random_string from mytest where random_id=50; ---定义显示游标
c1cur c1%rowtype; ---定义一个游标变量
begin
open c1; --打开游标
loop
fetch c1 into c1cur; ---取一行数据到游标变量
exit when c1%notfound; ---判断是否取到了值
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
close c1; ---关闭游标
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(3)参数游标(以下都利用scott自带的几张表测试)
SQL> declare
cursor c1 is select deptno from dept;
cursor c2(pno number,pjob varchar2) is select * from emp where deptno=pno and job=pjob; --只能指定类型,不能指定长度
c1cur c1%rowtype;
c2cur c2%rowtype;
begin
for c1cur in c1 loop
for c2cur in c2(c1cur.deptno,'MANAGER')loop
dbms_output.put_line(c1cur.deptno||':'||c2cur.ename||'---'||c2cur.sal);
end loop;
end loop;
end;
/
10:CLARK---2450
20:JONES---2975
30:BLAKE---2850
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(4)引用游标/动态游标
SQL>declare
type c1 is ref cursor; --定义一个类型(ref cursor)弱类型
type c2 is ref cursor return emp%rowtype; --定义一个强类型(返回的结果集有要求)
c0cur c1; ---定义一个弱类型的游标变量
c1cur emp%rowtype;
c2cur dept%rowtype;
begin
dbms_output.put_line('all employees :');
open c0cur for select * from emp;
loop
fetch c0cur into c1cur;
exit when c0cur%notfound;
dbms_output.put_line(c1cur.ename);
end loop;
dbms_output.put_line('all departments:');
open c0cur for select * from dept;
loop
fetch c0cur into c2cur;
exit when c0cur%notfound;
dbms_output.put_line(c2cur.dname);
end loop;
close c0cur;
end;
/
all employees:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
all departments:
ACCOUNTING
RESEARCH
SALES
OPERATIONS
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
(5)while循环
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
fetch c1 into c1cur; ---与for循环不同,while循环需要将下一行的值赋给游标变量,否则会进入死循环,并报错
end loop;
close c1;
end;
/
location: NEW YORK
location: DALLAS
location: CHICAGO
location: BOSTON
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
死循环,报错:
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
end loop;
close c1;
end;
/
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)
执行时,单个进程就导致cpu 100%,因此游标还是要小心使用的!
(6)利用游标来更新(update)
6.1 声明更新显示游标:
Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名];
6.2 使用显示游标当前记录来更新:
Update 表名 SET 更新语句 Where Current Of 游标名;
1.所有人普调
SQL> declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
saladd emp.sal%type;
salnew emp.sal%type;
begin
for c1cur in c1 loop
saladd:=c1cur.sal*0.2; --所有员工加薪20%
if saladd<300 then
salnew:=c1cur.sal+300; --加薪不足300的,按300加
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal+saladd;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end if;
update emp set sal=salnew where current of c1;
end loop;
end ;
/
SMITH:new salary 1300 ---原来工资1000
ALLEN:new salary 2304
WARD:new salary 1800
JONES:new salary 4284
MARTIN:new salary 1800
BLAKE:new salary 4104
CLARK:new salary 3528
SCOTT:new salary 4320
KING:new salary 7200
TURNER:new salary 2160
ADAMS:new salary 1620
JAMES:new salary 1450
FORD:new salary 4320
MILLER:new salary 1872
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
2.按照部门来调整
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
case ---必须包含所有的分类,否则会报错ORA-06592: CASE not found while executing CASE statement
when c1cur.deptno=10
then salnew:=c1cur.sal*1.1;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=20
then salnew:=c1cur.sal*1.15;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=30
then salnew:=c1cur.sal*1.2;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=40
then salnew:=c1cur.sal*1.3;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end case;
update emp set sal=salnew where current of c1;
end loop;
end;
/
SMITH:new salary 1495
ALLEN:new salary 2764.8
WARD:new salary 2160
JONES:new salary 4926.6
MARTIN:new salary 2160
BLAKE:new salary 4924.8
CLARK:new salary 3880.8
SCOTT:new salary 4968
KING:new salary 7920
TURNER:new salary 2592
ADAMS:new salary 1863
JAMES:new salary 1740
FORD:new salary 4968
MILLER:new salary 2059.2
LITING:new salary 7800
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
3.符合if条件的
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
if c1cur.deptno=20 then
salnew:=c1cur.sal*5-8000;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal;
end if;
update emp set sal=salnew where current of c1;
end loop;
end;
/
SMITH:new salary 1525
JONES:new salary 6633
SCOTT:new salary 6840
ADAMS:new salary 1315
FORD:new salary 6840
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
4.以平均值为分界
SQL> select avg(sal) from emp; 平均工资3715
AVG(SAL)
----------
3715
SQL> declare
2 cursor c1 is select empno,ename,sal,deptno,avg(sal) over(partition by deptno) as depavg from emp for update of sal;
3 c1cur c1%rowtype;
4 salnew emp.sal%type;
5 begin
6 for c1cur in c1 loop
7 if c1cur.sal>c1cur.depavg then
8 salnew:=c1cur.sal+50; ---高于平均工资的奖金发50
9 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
10 else
11 salnew:=c1cur.sal+100; ---低于平均工资的奖金发100
12 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
13 end if;
14 update emp set sal=salnew where current of c1;
15 end loop;
16 end;
17 /
CLARK:new salary 3980
MILLER:new salary 2160
KING:new salary 7970
JONES:new salary 4976
SMITH:new salary 1595
SCOTT:new salary 5018
FORD:new salary 5071
ADAMS:new salary 1415
WARD:new salary 2260
TURNER:new salary 2692
ALLEN:new salary 2814
JAMES:new salary 1840
MARTIN:new salary 2260
BLAKE:new salary 4974
LITING:new salary 7900
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
(7)利用游标来删除(delete)
7.1 声明删除显示游标:
Cursor 游标名IS SELECT 语句 For Delete;
7.2 使用显示游标当前记录来更新或删除:
Delete From 表名 Where Current Of 游标名;
SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:03.05
SQL> declare
cursor c1 is select ename,job from emp1 for update;
e_job emp1.job%type;
e_name emp1.ename%type;
begin
open c1;
loop
fetch c1 into e_name,e_job; --顺序要跟上面的select顺序对应
exit when c1%notfound;
if e_job='ANALYST' then
delete from emp1 where current of c1;
dbms_output.put_line(e_name||':'||'deleted');
end if;
end loop;
close c1;
end;
/
SCOTT:deleted
FORD:deleted
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
(8)利用游标来插入
SQL> declare
2 cursor c1 is select * from emp1;
3 c1cur c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c1cur;
8 exit when c1%notfound;
9 insert into emp values c1cur;
10 dbms_output.put_line(c1cur.ename||': inserted');
11 end loop;
12 close c1;
13 end;
14 /
TINA: inserted
BOBO: inserted
LIO: inserted
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
SQL> select * from emp; ---查看数据,插入成功
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1595 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2814 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2260 500 30
7566 JONES MANAGER 7839 02-APR-81 4976 20
7654 MARTIN SALESMAN 7698 28-SEP-81 2260 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4974 30
7782 CLARK MANAGER 7839 09-JUN-81 3980 10
7788 SCOTT ANALYST 7566 19-APR-87 5018 20
7839 KING PRESIDENT 17-NOV-81 7970 10
7844 TURNER SALESMAN 7698 08-SEP-81 2692 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1415 20
7900 JAMES CLERK 7698 03-DEC-81 1840 30
7902 FORD ANALYST 7566 03-DEC-81 5071 20
7934 MILLER CLERK 7782 23-JAN-82 2160 10
7200 LITING CLERK 7788 03-DEC-81 7900 500 40
6000 TINA CLERK 7788 02-APR-86 4503 300 10
6221 BOBO CLERK 7788 02-APR-86 4000 200 10
6307 LIO CLERK 7788 02-APR-86 4409 0 10
发表评论
-
ETL工具--kettle简介
2016-02-24 11:21 0oracle ETL工具---数据迁移 常用的有:OWB(o ... -
10053事件分析
2015-12-25 17:37 7901)10053介绍: 10053 事件是oracle 提供的用 ... -
oracle绑定变量学习
2015-12-25 17:01 1590绑定变量(binding variable) ... -
oracle 绑定变量
2015-12-24 17:26 0关键词: 绑定变量(binding variable),共享池 ... -
oracle插入大量数据
2015-12-23 17:35 1417oracle插入大量数据 1.生 ... -
ORA-03113:end-of-file on communication channel
2015-12-23 14:32 1148测试上面的一个库 plsql报错:shared memory ... -
oracle分区表【转】
2015-12-23 14:20 438oracle分区表 1.表空间及 ... -
迁移数据文件到ASM【转】
2015-12-23 11:53 7711.迁移数据文件到ASM 1) ... -
adrci命令
2015-12-23 11:46 3056一、adrci说明 在oracle11g中,dump ... -
用户+角色+权限
2015-12-21 17:58 901角色与用户权限的学习 ... -
oracle database link
2015-12-21 17:08 876目前我的数据库里只有tinadb一个实例,要创建db link ... -
oracle回收站
2015-12-21 14:22 432oracle回收站 1.drop table books; ... -
使用nid修改sid和dbname
2015-12-21 15:29 770如非必要,不建议在生产库上对dbid进行修改 1、修改dbi ... -
rman模拟故障恢复实验
2015-12-18 16:08 1154补充1:restore database和recover da ... -
rman命令学习-tina(下)
2015-12-18 16:07 1162五、rman删除 delete命令 删除相关的 ... -
rman命令学习-tina(上)
2015-12-18 16:06 884RMAN学习-tina rman的功能非常强大,下面我们来一 ... -
oracle profile介绍
2015-12-16 17:34 625profile文件的介绍: Oracle系统中的profi ... -
oracle缩小表空间
2015-12-18 16:59 1762oracle缩小表空间: oracle常用的此类命令Alte ... -
oracle数据库的启动和关闭【转】
2015-12-16 15:02 1141数据库的启动和关闭 Or ... -
oracle的主要进程
2015-12-16 14:21 1189[size=small]oracle的主要进程 Oracle实 ...
相关推荐
Oracle游标实例.txt
详细介绍了 oracle的游标使用 及 实例
oracle游标实例
关于oracle数据库隐式游标、显式游标相关用法的详细实例,还不错
主要给大家介绍了关于Oracle游标使用的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或着工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
oracle游标的用法,比较通俗易懂的讲解游标用法,有实例
oracle 的函数、存储过程、游标、简单实例 oracle常用语句oracle 的函数、存储过程、游标、简单实例 oracle常用语句
oracle job 存储过程 视图 游标 实例
主要介绍了详解Oracle游标的简易用法,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
实现了游标的一些使用方法,两种定义与使用的方法,存储过程的定义一使用调用返回结果集的存储过程
oracle内部培训资料——游标 实例代码,使用注意事项等
存储过程调用 Function 并使用 游标循环得到每一条数据然后更新本条数据。初学代码写的不是很好,希望能给初学存储过程的兄弟们提供一点借鉴哦。
Oracle存储过程返回游标有两种实现方法一种是声明系统游标,一种是声明自定义游标,本文将详细介绍,需要了解的朋友可以参考下
游标是从表中检索出结果集,从中每次指向一条记录进行交互的机制。 作用 指定结果集中特定行的位置。 基于当前的结果集位置检索一行或连续的几行。 在结果集的当前位置修改行中的数据。 对其他用户所做的数据...
用callabledStatement调用oracle存储过程实用例子(IN OUT 传参数包括游标类型)
本文实例讲述了Oracle出现超出打开游标最大数的解决方法。分享给大家供大家参考,具体如下: Java代码在执行conn.createStatement()和conn.prepareStatement()的时候,实际上都是相当与在数据库中打开了一个cursor。...
本文介绍了Oracle PL/SQL语句块、变量、赋值、条件语句、循环语句、游标、集合及例外等,在测试后,结合实例讲解了PL/SQL语法的使用,并对重点内容进行了标注和解析。读者只需按照本文进行学习和操作,即可掌握...
oracle应用经典实例,oracle操作,存储过程,游标,触发器,oracle帮助文档,oracleAPI