使用oracle系统游标处理数据
实例代码：
--set serveroutput on;
create or replace procedure split_page_procedure(page_index in number,
page_size in number,
key_word in varchar2) is
my_curser SYS_REFCURSOR;
--分页参数
page_count number(7);
min_index number(7); --起始位置
max_index number(7); --结束位置
--打印参数
name_param userlogin.user_name%type;
uid_param userlogin.user_id%type;
begin
if (page_index < 1) or (page_size < 1) then
RAISE NO_DATA_FOUND;
end if;
--参数校验
--设置分页参数 分页参数值最少为1，
min_index := (page_index - 1) * page_size - 1;
max_index := page_index * page_size;
--利用分页查询数据;
open my_curser for
select i.user_name, i.user_id name
from (select u.*, rownum rn from userlogin u where rownum < max_index) i
where i.rn > min_index;
loop
FETCH my_curser
INTO name_param, uid_param;
--游标取不到数据则退出
exit when my_curser%NOTFOUND;
dbms_output.put_line('参数：name:' || name_param || ' uid:' || uid_param);
end loop;
close my_curser;
--査取总记录
select count(1) into page_count from userlogin;
dbms_output.put_line('返回的总记录条数：' || page_count);
--异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('分页参数不合理 page_index：' || page_index ||
' page_size:' || page_size);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM);
end split_page_procedure;
方法一, 直接抛
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| SQL> DECLARE
2 -- 测试异常.
3 e_test_exception EXCEPTION;
4 BEGIN
5
6 -- 直接抛出异常，测试下面的捕获
7 RAISE e_test_exception;
8
9 EXCEPTION
10 WHEN e_test_exception THEN
11 dbms_output.put_line('Test Error !');
12 WHEN OTHERS THEN
13 dbms_output.put_line('OTHERS Error!');
14 END;
15 /
Test Error !
PL/SQL procedure successfully completed.
|
方法二, 定义个错误代码与消息后， 再抛。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| SQL> BEGIN
2 -- 错误代码允许的范围是 -20,000～20,999
3 RAISE_APPLICATION_ERROR(-20000, 'My Error Happen!');
4
5 EXCEPTION
6 WHEN OTHERS THEN
7 dbms_output.put_line('Error Code = ' || TO_CHAR(SQLCODE) );
8 dbms_output.put_line('Error Message = ' || SQLERRM );
9 END;
10 /
Error Code = -20000
Error Message = ORA-20000: My Error Happen!
PL/SQL procedure successfully completed.
|
原标题：oracle异常
关键词：oracle
我知道了 