--
声明游标；CURSOR cursor_name IS select_statement
--
For 循环游标
--
（1）定义游标
--
（2）定义游标变量
--
（3）使用for循环来使用这个游标
declare
--
类型定义
cursor
c_job
is
select
empno,ename,job,sal
from
emp
where
job=
'
MANAGER
'
;
--
定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_job%
rowtype;
begin
for
c_row in
c_job loop
dbms_output.put_line(c_row.empno||
'
-
'
||
c_row.ename||
'
-
'
||
c_row.job||
'
-
'
||
c_row.sal);
end
loop;
end
;
--
Fetch游标
--
使用的时候必须要明确的打开和关闭
declare
--
类型定义
cursor
c_job
is
select
empno,ename,job,sal
from
emp
where
job=
'
MANAGER
'
;
--
定义一个游标变量
c_row c_job%
rowtype;
begin
open
c_job;
loop
--
提取一行数据到c_row
fetch
c_job into
c_row;
--
判读是否提取到值，没取到值就退出
--
取到值c_job%notfound 是false
--
取不到值c_job%notfound 是true
exit
when
c_job%
notfound;
dbms_output.put_line(c_row.empno||
'
-
'
||
c_row.ename||
'
-
'
||
c_row.job||
'
-
'
||
c_row.sal);
end
loop;
--
关闭游标
close
c_job;
end
;
--
1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
begin
update
emp set
ENAME=
'
ALEARK
'
WHERE
EMPNO=
7469
;
if
sql%
isopen then
dbms_output.put_line('
Openging
'
);
else
dbms_output.put_line('
closing
'
);
end
if
;
if
sql%
found then
dbms_output.put_line('
游标指向了有效行
'
);--
判断游标是否指向有效行
else
dbms_output.put_line('
Sorry
'
);
end
if
;
if
sql%
notfound then
dbms_output.put_line('
Also Sorry
'
);
else
dbms_output.put_line('
Haha
'
);
end
if
;
dbms_output.put_line(sql%
rowcount
);
exception
when
no_data_found then
dbms_output.put_line('
Sorry No data
'
);
when
too_many_rows then
dbms_output.put_line('
Too Many rows
'
);
end
;
declare
empNumber emp.EMPNO%
TYPE;
empName emp.ENAME%
TYPE;
begin
if
sql%
isopen then
dbms_output.put_line('
Cursor is opinging
'
);
else
dbms_output.put_line('
Cursor is Close
'
);
end
if
;
if
sql%
notfound then
dbms_output.put_line('
No Value
'
);
else
dbms_output.put_line(empNumber);
end
if
;
dbms_output.put_line(sql%
rowcount
);
dbms_output.put_line('
-------------
'
);
select
EMPNO,ENAME into
empNumber,empName from
emp where
EMPNO=
7499
;
dbms_output.put_line(sql%
rowcount
);
if
sql%
isopen then
dbms_output.put_line('
Cursor is opinging
'
);
else
dbms_output.put_line('
Cursor is Closing
'
);
end
if
;
if
sql%
notfound then
dbms_output.put_line('
No Value
'
);
else
dbms_output.put_line(empNumber);
end
if
;
exception
when
no_data_found then
dbms_output.put_line('
No Value
'
);
when
too_many_rows then
dbms_output.put_line('
too many rows
'
);
end
;
--
2,使用游标和loop循环来显示所有部门的名称
--
游标声明
declare
cursor
csr_dept
is
--
select语句
select
DNAME
from
Depth;
--
指定行指针,这句话应该是指定和csr_dept行类型相同的变量
row_dept csr_dept%
rowtype;
begin
--
for循环
for
row_dept in
csr_dept loop
dbms_output.put_line('
部门名称:
'
||
row_dept.DNAME);
end
loop;
end
;
--
3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
declare
--
游标声明
cursor
csr_TestWhile
is
--
select语句
select
LOC
from
Depth;
--
指定行指针
row_loc csr_TestWhile%
rowtype;
begin
--
打开游标
open
csr_TestWhile;
--
给第一行喂数据
fetch
csr_TestWhile into
row_loc;
--
测试是否有数据，并执行循环
while
csr_TestWhile%
found loop
dbms_output.put_line('
部门地点：
'
||
row_loc.LOC);
--
给下一行喂数据
fetch
csr_TestWhile into
row_loc;
end
loop;
close
csr_TestWhile;
end
;
select
*
from
emp
--
4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
--
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--
定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]
declare
CURSOR
c_dept(p_deptNo number
)
is
select
*
from
emp where
emp.depno=
p_deptNo;
r_emp emp%
rowtype;
begin
for
r_emp in
c_dept(20
) loop
dbms_output.put_line('
员工号：
'
||
r_emp.EMPNO||
'
员工名：
'
||
r_emp.ENAME||
'
工资：
'
||
r_emp.SAL);
end
loop;
end
;
select
*
from
emp
--
5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
declare
cursor
c_job(p_job nvarchar2)
is
select
*
from
emp where
JOB=
p_job;
r_job emp%
rowtype;
begin
for
r_job in
c_job('
CLERK
'
) loop
dbms_output.put_line('
员工号
'
||
r_job.EMPNO||
'
'
||
'
员工姓名
'
||
r_job.ENAME);
end
loop;
end
;
SELECT
*
FROM
EMP
--
6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来
--
http://zheng12tian.iteye.com/blog/815770
create
table
emp1 as
select
*
from
emp;
declare
cursor
csr_Update
is
select
*
from
emp1 for
update
OF
SAL;
empInfo csr_Update%
rowtype;
saleInfo emp1.SAL%
TYPE;
begin
FOR
empInfo IN
csr_Update LOOP
IF
empInfo.SAL<
1500
THEN
saleInfo:=
empInfo.SAL*
1.2
;
elsif empInfo.SAL<
2000
THEN
saleInfo:=
empInfo.SAL*
1.5
;
elsif empInfo.SAL<
3000
THEN
saleInfo:=
empInfo.SAL*
2
;
END
IF
;
UPDATE
emp1 SET
SAL=
saleInfo WHERE
CURRENT
OF
csr_Update;
END
LOOP;
END
;
--
7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare
cursor
csr_AddSal
is
select
*
from
emp1 where
ENAME LIKE
'
A%
'
OR
ENAME LIKE
'
S%
'
for
update
OF
SAL;
r_AddSal csr_AddSal%
rowtype;
saleInfo emp1.SAL%
TYPE;
begin
for
r_AddSal in
csr_AddSal loop
dbms_output.put_line(r_AddSal.ENAME||
'
原来的工资:
'
||
r_AddSal.SAL);
saleInfo:=
r_AddSal.SAL*
1.1
;
UPDATE
emp1 SET
SAL=
saleInfo WHERE
CURRENT
OF
csr_AddSal;
end
loop;
end
;
--
8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
declare
cursor
csr_AddComm(p_job nvarchar2)
is
select
*
from
emp1 where
JOB=
p_job FOR
UPDATE
OF
COMM;
r_AddComm emp1%
rowtype;
commInfo emp1.comm%
type;
begin
for
r_AddComm in
csr_AddComm('
SALESMAN
'
) LOOP
commInfo:=
r_AddComm.COMM+
500
;
UPDATE
EMP1 SET
COMM=
commInfo where
CURRENT
OF
csr_AddComm;
END
LOOP;
END
;
--
9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
--
（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）
declare
cursor
crs_testComput
is
select
*
from
emp1 order
by
HIREDATE asc
;
--
计数器
top_two number
:=
2
;
r_testComput crs_testComput%
rowtype;
begin
open
crs_testComput;
FETCH
crs_testComput INTO
r_testComput;
while
top_two>
0
loop
dbms_output.put_line('
员工姓名：
'
||
r_testComput.ENAME||
'
工作时间：
'
||
r_testComput.HIREDATE);
--
计速器减一
top_two:=
top_two-
1
;
FETCH
crs_testComput INTO
r_testComput;
end
loop;
close
crs_testComput;
end
;
--
10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
--
如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)
declare
cursor
crs_UpadateSal
is
select
*
from
emp1 for
update
of
SAL;
r_UpdateSal crs_UpadateSal%
rowtype;
salAdd emp1.sal%
type;
salInfo emp1.sal%
type;
begin
for
r_UpdateSal in
crs_UpadateSal loop
salAdd:=
r_UpdateSal.SAL*
0.2
;
if
salAdd>
300
then
salInfo:=
r_UpdateSal.SAL;
dbms_output.put_line(r_UpdateSal.ENAME||
'
: 加薪失败。
'
||
'
薪水维持在：
'
||
r_UpdateSal.SAL);
else
salInfo:=
r_UpdateSal.SAL+
salAdd;
dbms_output.put_line(r_UpdateSal.ENAME||
'
: 加薪成功.
'
||
'
薪水变为：
'
||
salInfo);
end
if
;
update
emp1 set
SAL=
salInfo where
current
of
crs_UpadateSal;
end
loop;
end
;
--
11:将每位员工工作了多少年零多少月零多少天输出出来
--
近似
--
CEIL(n)函数：取大于等于数值n的最小整数
--
FLOOR(n)函数：取小于等于数值n的最大整数
--
truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
cursor
crs_WorkDay
is
select
ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) /
12
) AS
SPANDYEARS,
trunc(mod(months_between(sysdate, hiredate), 12
)) AS
months,
trunc(mod(mod(sysdate -
hiredate, 365
), 12
)) as
days
from
emp1;
r_WorkDay crs_WorkDay%
rowtype;
begin
for
r_WorkDay in
crs_WorkDay loop
dbms_output.put_line(r_WorkDay.ENAME||
'
已经工作了
'
||
r_WorkDay.SPANDYEARS||
'
年,零
'
||
r_WorkDay.months||
'
月,零
'
||
r_WorkDay.days||
'
天
'
);
end
loop;
end
;
--
12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
--
deptno raise(%)
--
10 5%
--
20 10%
--
30 15%
--
40 20%
--
加薪比例以现有的sal为标准
--
CASE expr WHEN comparison_expr THEN return_expr
--
[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
cursor
crs_caseTest
is
select
*
from
emp1 for
update
of
SAL;
r_caseTest crs_caseTest%
rowtype;
salInfo emp1.sal%
type;
begin
for
r_caseTest in
crs_caseTest loop
case
when
r_caseTest.DEPNO=
10
THEN
salInfo:=
r_caseTest.SAL*
1.05
;
when
r_caseTest.DEPNO=
20
THEN
salInfo:=
r_caseTest.SAL*
1.1
;
when
r_caseTest.DEPNO=
30
THEN
salInfo:=
r_caseTest.SAL*
1.15
;
when
r_caseTest.DEPNO=
40
THEN
salInfo:=
r_caseTest.SAL*
1.2
;
end
case
;
update
emp1 set
SAL=
salInfo where
current
of
crs_caseTest;
end
loop;
end
;
--
13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
--
AVG([distinct|all] expr) over (analytic_clause)
--
-作用：
--
按照analytic_clause中的规则求分组平均值。
--
分析函数语法:
--
FUNCTION_NAME(<argument>,<argument>...)
--
OVER
--
(<Partition-Clause><Order-by-Clause><Windowing Clause>)
--
PARTITION子句
--
按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
select
*
from
emp1
DECLARE
CURSOR
crs_testAvg
IS
select
EMPNO,ENAME,JOB,SAL,DEPNO,AVG
(SAL) OVER
(PARTITION BY
DEPNO ) AS
DEP_AVG
FROM
EMP1 for
update
of
SAL;
r_testAvg crs_testAvg%
rowtype;
salInfo emp1.sal%
type;
begin
for
r_testAvg in
crs_testAvg loop
if
r_testAvg.SAL>
r_testAvg.DEP_AVG then
salInfo:=
r_testAvg.SAL-
50
;
end
if
;
update
emp1 set
SAL=
salInfo where
current
of
crs_testAvg;
end
loop;
end
;
分享到：
相关推荐
通过阅读"oracle定时执行存储过程.pdf"和"Oracle 游标使用全解.wps"这两个文档，你可以更深入地了解这些概念并获得实践指导。 总的来说，Oracle数据库的强大在于其丰富的特性和工具，而JOB、PROCEDURE和CURSOR是...
在这里，你会学习如何声明变量、游标，使用流程控制语句（IF-THEN-ELSIF、CASE、FOR循环）以及异常处理（TRY-CATCH）。此外，还会讲解如何创建存储过程、函数、包、触发器，这些是数据库应用中的核心组件，用于实现...
### ORACLE转DB2对照全解 #### 一、Oracle SQL PL与DB2 inline SQL PL对比 本章节主要介绍Oracle SQL PL与DB2 inline SQL PL之间的对比，包括但不限于存储过程、触发器、用户定义函数（UDF）、条件语句及流程控制...
"高级Oracle学习资料一栏全解"着重于深入理解Oracle的功能和优化技巧，包括但不限于数据库管理、性能调优、故障诊断等方面。 **核心问题与诊断结果** 在Oracle数据库的日常运维中，遇到的问题可能涉及到应用软件、...
Oracle数据库是全球广泛使用的大型关系型数据库管理系统，尤其在企业级应用中占据重要地位。"Oracle入门教程大全"是一份全面介绍Oracle基础知识和工具的资料，适合初学者系统学习。以下将围绕标题和描述中的知识点...