以下是书上的一段代码,用于说明游标的四个属性的用法:
SET SERVEROUTPUT ON ESCAPE OFFDECLARE v_first_name AUTHORS.FIRST_NAME%TYPE;
v_last_name AUTHORS.LAST_NAME%TYPE;
v_row_count PLS_INTEGER := 0;
v_book_count PLS_INTEGER := 0; CURSOR auth_cur IS
SELECT a.first_name, a.last_name, count(b.title)
FROM authors a, books b
WHERE a.id = b.author1
OR a.id = b.author2
OR a.id = b.author3
GROUP BY a.first_name, a.last_name
HAVING count(b.title) > 0
ORDER BY a.last_name;BEGIN DBMS_OUTPUT.ENABLE(1000000); OPEN auth_cur;
LOOP
FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
EXIT WHEN auth_cur%NOTFOUND;
-- Alternatively use EXIT WHEN NOT auth_cur%FOUND; v_row_count := auth_cur%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(v_row_count||' rows processed so far'); DBMS_OUTPUT.PUT_LINE(v_last_name
||', '
||v_first_name
||' wrote '
||v_book_count
||' book(s).');
END LOOP;
CLOSE auth_cur; IF auth_cur%ISOPEN = FALSE
THEN
DBMS_OUTPUT.PUT_LINE('Cursor closed');
ELSE
DBMS_OUTPUT.PUT_LINE('The cursor is still open');
END IF;EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
以上就是一个完整的匿名块,切可以通过编译。但是如果将它改成一个过程,就不行了:
SQL> create or replace procedure TEST_CURSOR is
2 begin
3 DECLARE
4
5 v_first_name AUTHORS.FIRST_NAME%TYPE;
6 v_last_name AUTHORS.LAST_NAME%TYPE;
7 v_row_count PLS_INTEGER := 0;
8 v_book_count PLS_INTEGER := 0;
9
10 CURSOR auth_cur IS
11 SELECT a.first_name, a.last_name, count(b.title)
12 FROM authors a, books b
13 WHERE a.id = b.author1
14 OR a.id = b.author2
15 OR a.id = b.author3
16 GROUP BY a.first_name, a.last_name
17 HAVING count(b.title) > 0
18 ORDER BY a.last_name;
19
20 OPEN auth_cur;
21 LOOP
22 FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
23 EXIT WHEN auth_cur%NOTFOUND;
24 -- Alternatively use EXIT WHEN NOT auth_cur%FOUND;
25
26 v_row_count := auth_cur%ROWCOUNT;
27 DBMS_OUTPUT.PUT_LINE(v_row_count||' rows processed so far');
28
29 DBMS_OUTPUT.PUT_LINE(v_last_name
30 ||', '
31 ||v_first_name
32 ||' wrote '
33 ||v_book_count
34 ||' book(s).');
35 END LOOP;
36
37 CLOSE auth_cur;
38
39 IF auth_cur%ISOPEN = FALSE
40 THEN
41 DBMS_OUTPUT.PUT_LINE('Cursor closed');
42 ELSE
43 DBMS_OUTPUT.PUT_LINE('The cursor is still open');
44 END IF;
45
46 EXCEPTION
47 WHEN OTHERS
48 THEN
49 DBMS_OUTPUT.PUT_LINE(SQLERRM);
50
51 end TEST_CURSOR;
52 /Warning: Procedure created with compilation errorsSQL> show error;
Errors for PROCEDURE PLSQL.TEST_CURSOR:LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
22/7 PLS-00103: 出现符号 "FETCH"在需要下列之一时: constant exception <一个标识符> <一个双引号分隔的标识符> table LONG_ double ref char time timestamp interval date binary national character nchar
37/4 PLS-00103: 出现符号 "CLOSE"在需要下列之一时: end not pragma final instantiable order overriding static member constructor map
怎么都编译不过,百思不得其解--说是FETCH有问题,请大家帮忙看看,到底那里出错了,谢谢!
SET SERVEROUTPUT ON ESCAPE OFFDECLARE v_first_name AUTHORS.FIRST_NAME%TYPE;
v_last_name AUTHORS.LAST_NAME%TYPE;
v_row_count PLS_INTEGER := 0;
v_book_count PLS_INTEGER := 0; CURSOR auth_cur IS
SELECT a.first_name, a.last_name, count(b.title)
FROM authors a, books b
WHERE a.id = b.author1
OR a.id = b.author2
OR a.id = b.author3
GROUP BY a.first_name, a.last_name
HAVING count(b.title) > 0
ORDER BY a.last_name;BEGIN DBMS_OUTPUT.ENABLE(1000000); OPEN auth_cur;
LOOP
FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
EXIT WHEN auth_cur%NOTFOUND;
-- Alternatively use EXIT WHEN NOT auth_cur%FOUND; v_row_count := auth_cur%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(v_row_count||' rows processed so far'); DBMS_OUTPUT.PUT_LINE(v_last_name
||', '
||v_first_name
||' wrote '
||v_book_count
||' book(s).');
END LOOP;
CLOSE auth_cur; IF auth_cur%ISOPEN = FALSE
THEN
DBMS_OUTPUT.PUT_LINE('Cursor closed');
ELSE
DBMS_OUTPUT.PUT_LINE('The cursor is still open');
END IF;EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
以上就是一个完整的匿名块,切可以通过编译。但是如果将它改成一个过程,就不行了:
SQL> create or replace procedure TEST_CURSOR is
2 begin
3 DECLARE
4
5 v_first_name AUTHORS.FIRST_NAME%TYPE;
6 v_last_name AUTHORS.LAST_NAME%TYPE;
7 v_row_count PLS_INTEGER := 0;
8 v_book_count PLS_INTEGER := 0;
9
10 CURSOR auth_cur IS
11 SELECT a.first_name, a.last_name, count(b.title)
12 FROM authors a, books b
13 WHERE a.id = b.author1
14 OR a.id = b.author2
15 OR a.id = b.author3
16 GROUP BY a.first_name, a.last_name
17 HAVING count(b.title) > 0
18 ORDER BY a.last_name;
19
20 OPEN auth_cur;
21 LOOP
22 FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
23 EXIT WHEN auth_cur%NOTFOUND;
24 -- Alternatively use EXIT WHEN NOT auth_cur%FOUND;
25
26 v_row_count := auth_cur%ROWCOUNT;
27 DBMS_OUTPUT.PUT_LINE(v_row_count||' rows processed so far');
28
29 DBMS_OUTPUT.PUT_LINE(v_last_name
30 ||', '
31 ||v_first_name
32 ||' wrote '
33 ||v_book_count
34 ||' book(s).');
35 END LOOP;
36
37 CLOSE auth_cur;
38
39 IF auth_cur%ISOPEN = FALSE
40 THEN
41 DBMS_OUTPUT.PUT_LINE('Cursor closed');
42 ELSE
43 DBMS_OUTPUT.PUT_LINE('The cursor is still open');
44 END IF;
45
46 EXCEPTION
47 WHEN OTHERS
48 THEN
49 DBMS_OUTPUT.PUT_LINE(SQLERRM);
50
51 end TEST_CURSOR;
52 /Warning: Procedure created with compilation errorsSQL> show error;
Errors for PROCEDURE PLSQL.TEST_CURSOR:LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
22/7 PLS-00103: 出现符号 "FETCH"在需要下列之一时: constant exception <一个标识符> <一个双引号分隔的标识符> table LONG_ double ref char time timestamp interval date binary national character nchar
37/4 PLS-00103: 出现符号 "CLOSE"在需要下列之一时: end not pragma final instantiable order overriding static member constructor map
怎么都编译不过,百思不得其解--说是FETCH有问题,请大家帮忙看看,到底那里出错了,谢谢!
解决方案 »
- 请问:你觉的这种方案可以实现么?
- 在Domino环境下如何将图片插进oracle的Blob类型的字段?
- 初/中级PHP和Oracle程序员进,加入交易程序开发团队,在家工作!
- OTL模板库中可以使用自定义数据类型吗?
- 求行列转化sql语句?
- 如何将列数据变成一个字符串??
- (菜鳥)改歸檔模式時shutdown後oracle not available,怎麼辦?在線等
- oracle9.0我想重新配置一个资料档案库,然后把原来的sid对应的资料档案库删除,怎么做?
- 求ORACLE 的学习资料和安装软件?
- 一个n难的sql,大虾来挑战
- 请问如何在oracle的函数或者存储过程中返回一张表
- 触发器多条记录插入问题
v_first_name AUTHORS.FIRST_NAME%TYPE;
v_last_name AUTHORS.LAST_NAME%TYPE;
v_row_count PLS_INTEGER := 0;
v_book_count PLS_INTEGER := 0;
CURSOR auth_cur IS
SELECT a.first_name, a.last_name, count(b.title)
FROM authors a, books b
WHERE a.id = b.author1
OR a.id = b.author2
OR a.id = b.author3
GROUP BY a.first_name, a.last_name
HAVING count(b.title) > 0
ORDER BY a.last_name;
begin
OPEN auth_cur;
LOOP
FETCH auth_cur INTO v_first_name, v_last_name, v_book_count;
EXIT WHEN auth_cur%NOTFOUND;
-- Alternatively use EXIT WHEN NOT auth_cur%FOUND;
v_row_count := auth_cur%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(v_row_count||' rows processed so far');
DBMS_OUTPUT.PUT_LINE(v_last_name
||', '
||v_first_name
||' wrote '
||v_book_count
||' book(s).');
END LOOP;
CLOSE auth_cur;
IF auth_cur%ISOPEN = FALSE
THEN
DBMS_OUTPUT.PUT_LINE('Cursor closed');
ELSE
DBMS_OUTPUT.PUT_LINE('The cursor is still open');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end TEST_CURSOR;
/
只有test script需要 (你称它 匿名块? )
DECLEAR
.
.
.
BEGIN
.
.
.
END