使用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;
oracle块操作
declare
v_param number(7,2);
begin
v_param :=12;
if v_param>10 then
dbms_output.put_line(‘aaa‘);
end if;
end;
方法一, 直接抛
|
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 = -20000Error Message = ORA-20000: My Error Happen!PL/SQL procedure successfully completed. |
原文：http://www.cnblogs.com/leo3689/p/4558183.html