--游标cursor
游标的作用:不让程序中的select直接向终端屏幕输出,转向将select结果输出到PL/SQL程序中的内存变量
(a).隐式游标
a).
SELECT
...
INTO
...
FROM
... 【只能返回一行,不能多行】
b).
FOR
cur
IN
(
SELECT
...)
LOOP
...
END
LOOP
;
(b).显式游标
定义游标:
CURSOR
cursorName[(parameter[,parameter]...)]
IS
SELECT
...
打开游标:
OPEN
cursorName;
抓取游标数据到变量:
FETCH
cursorName
INTO
(变量列表 | 记录型);
关闭游标:
CLOSE
cursorName;
例:
Name
Type
Nullable
Default
Comments
------- ------------ -------- ------- --------
EMP_NO
NUMBER
(
10
) Y
NAME
VARCHAR2
(
20
) Y
AGE
NUMBER
(
3
) Y
TEL
VARCHAR2
(
30
) Y
DEPT_NO
NUMBER
(
5
) Y
SAL
NUMBER
(
15
,
2
) Y
BD
DATE
Y
DECLARE
v_id
NUMBER
(
3
);
v_name
VARCHAR2
(
5
);
v_age
NUMBER
(
3
);
v_sal
NUMBER
(
12
);
CURSOR
cur1
IS
SELECT
ROWNUM
,
NAME
,age,sal
FROM
t1;
BEGIN
OPEN
cur1;
FOR
i
IN
1
..10
LOOP
FETCH
cur1
INTO
v_id,v_name,v_age,v_sal;
dbms_output.put_line(v_id||
','
||v_name||
','
||v_age||
','
||v_sal);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
TYPE
rec
IS
RECORD
(
v_id
NUMBER
(
3
),
v_name
VARCHAR2
(
5
),
v_age
NUMBER
(
3
),
v_sal
NUMBER
(
12
)
);
recv rec;
CURSOR
cur1
IS
SELECT
ROWNUM
,
NAME
,age,sal
FROM
t1;
BEGIN
OPEN
cur1;
FOR
i
IN
1
..10
LOOP
FETCH
cur1
INTO
recv;
dbms_output.put_line(recv.v_id||
','
||recv.v_name||
','
||recv.v_age||
','
||recv.v_sal);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
recv t1%
ROWTYPE
;
CURSOR
cur1
IS
SELECT
*
FROM
t1;
BEGIN
OPEN
cur1;
FOR
i
IN
1
..10
LOOP
FETCH
cur1
INTO
recv;
dbms_output.put_line(recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
CLOSE
cur1;
END
;
游标属性 【使用方式 :游标名游标属性 例:cursorName%
FOUND
】
%
FOUND
布尔型属性,当最近一次读记录时成功返回,则值为TRUE;
%
NOTFOUND
布尔型属性,与%FOUND相反;
%
ISOPEN
布尔型属性,当游标已打开时返回
TRUE
;
%
ROWCOUNT
数字型属性,返回已从游标中读取的记录数。
DECLARE
/*表中有多少条数据就输出多少条*/
rowscount
NUMBER
(
2
);
recv t1%
ROWTYPE
;
CURSOR
cur1
IS
SELECT
*
FROM
t1;
BEGIN
SELECT
COUNT
(*)
INTO
rowscount
FROM
t1;
OPEN
cur1;
FOR
i
IN
1
..rowscount
LOOP
FETCH
cur1
INTO
recv;
dbms_output.put_line(recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
/*表中有多少条数据就输出多少条*/
rowscount
NUMBER
(
2
);
recv t1%
ROWTYPE
;
CURSOR
cur1
IS
SELECT
*
FROM
t1;
BEGIN
SELECT
COUNT
(*)
INTO
rowscount
FROM
t1;
OPEN
cur1;
FOR
i
IN
1
..20
LOOP
FETCH
cur1
INTO
recv;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
CLOSE
cur1;
END
;
带参数的游标使用
DECLARE
rowscount
NUMBER
(
2
);
recv t1%
ROWTYPE
;
CURSOR
cur1(v_emp_no
NUMBER
/*地处的变量只能声明类型,不能指定长度*/
)
IS
SELECT
*
FROM
t1
WHERE
emp_no=v_emp_no
ORDER
BY
sal;
BEGIN
SELECT
COUNT
(*)
INTO
rowscount
FROM
t1;
OPEN
cur1(
1
);
FOR
i
IN
1
..20
LOOP
EXIT
WHEN
cur1%
NOTFOUND
;
FETCH
cur1
INTO
recv;
dbms_output.put_line(recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
rowscount
NUMBER
(
2
);
recv t1%
ROWTYPE
;
CURSOR
cur1(v_emp_no
NUMBER
/*地处的变量只能声明类型,不能指定长度*/
)
IS
SELECT
*
FROM
t1
WHERE
emp_no=v_emp_no
ORDER
BY
sal;
BEGIN
FOR
i
IN
1
..10
LOOP
OPEN
cur1(i);
LOOP
FETCH
cur1
INTO
recv;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(cur1%
rowcount
||
','
||recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
dbms_output.put_line(
'---------------------------------------'
);
END
LOOP
;
CLOSE
cur1;
END
LOOP
;
END
;
游标的for循环
DECLARE
/*不带参数*/
CURSOR
cura
IS
SELECT
*
FROM
t1;
BEGIN
FOR
recv
IN
cura
LOOP
dbms_output.put_line(cura%
rowcount
||
','
||recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
END
;
DECLARE
/*带参数*/
CURSOR
cura(v_emp_no
NUMBER
)
IS
SELECT
*
FROM
t1
WHERE
emp_no=v_emp_no;
BEGIN
FOR
recv
IN
cura(
3
)
/*参数在这里使用*/
LOOP
dbms_output.put_line(cura%
rowcount
||
','
||recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
END
;
DECLARE
/*带参数*/
CURSOR
cura(v_emp_no
NUMBER
)
IS
SELECT
*
FROM
t1
WHERE
emp_no=v_emp_no;
BEGIN
FOR
i
IN
1
..10
LOOP
FOR
recv
IN
cura(i)
/*参数在这里使用*/
LOOP
dbms_output.put_line(cura%
rowcount
||
','
||recv.EMP_NO||
','
||recv.name||
','
||recv.age||
','
||recv.sal);
END
LOOP
;
END
LOOP
;
END
;
隐式游标属性
SQL
%
FOUND
布尔型属性,当最近一次读记录时成功返回,则值为true;
SQL
%
NOTFOUND
布尔型属性,与%found相反;
SQL
%
ROWCOUNT
数字型属性, 返回已从游标中读取得记录数;
SQL
%
ISOPEN
布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。
例:
DECLARE
v_deptno emp.deptno%
TYPE
:=
2
;
BEGIN
DELETE
FROM
emp
WHERE
deptno=v_deptno;
IF
SQL
%
FOUND
THEN
DELETE
FROM
dept
WHERE
deptno=v_deptno;
END
IF
;
END
;
DECLARE
TYPE
mycur
IS
REF
CURSOR
;
cur1 mycur;
rotype t1%
ROWTYPE
;
BEGIN
OPEN
cur1
FOR
SELECT
*
FROM
t1;
LOOP
FETCH
cur1
INTO
rotype;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(rotype.name||
' , '
||rotype.age);
END
LOOP
;
CLOSE
cur1;
END
;
CREATE
TABLE
t2
AS
SELECT
NAME
,age
FROM
t1;
DECLARE
t1_r t1%
ROWTYPE
;
TYPE
mycur
IS
REF
CURSOR
RETURN
t1_r%
ROWTYPE
/*添加return语句,进行请类型检查,限制返回的类型,return后面的返回类型必须是记录类型的*/
;
cur1 mycur;
rotype t1%
ROWTYPE
;
BEGIN
OPEN
cur1
FOR
SELECT
*
FROM
t1;
LOOP
FETCH
cur1
INTO
rotype;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(rotype.name||
' , '
||rotype.age);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
t1_r t1%
ROWTYPE
;
TYPE
mycur
IS
REF
CURSOR
;
cur1 mycur;
rotype t1%
ROWTYPE
;
i
NUMBER
(
5
):=
1
;
BEGIN
OPEN
cur1
FOR
'SELECT * FROM t1 where emp_no=:a'
USING
i
/*此处使用变量条件,使用条件变量时只能是弱类型游标*/
;
LOOP
FETCH
cur1
INTO
rotype;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(rotype.name||
' , '
||rotype.age);
END
LOOP
;
CLOSE
cur1;
END
;
DECLARE
t1_r t1%
ROWTYPE
;
TYPE
mycur
IS
REF
CURSOR
;
cur1 mycur;
rotype t1%
ROWTYPE
;
i
NUMBER
(
5
):=
1
;
tableName
VARCHAR2
(
20
):=
't1'
;
/*使用游标变量可以灵活指定表名*/
BEGIN
OPEN
cur1
FOR
'SELECT * FROM '
||tableName||
' where emp_no=:a'
USING
i
/*此处使用变量条件,使用条件变量时只能是弱类型游标*/
;
LOOP
FETCH
cur1
INTO
rotype;
EXIT
WHEN
cur1%
NOTFOUND
;
dbms_output.put_line(rotype.name||
' , '
||rotype.age);
END
LOOP
;
CLOSE
cur1;
END
;