新萄京计算机网络

 新萄京计算机网络     |      2020-04-16

使用oracle系统游标处理数据

/*
语法:
create or replace procedure 存储过程名
[参数列表]
is|as
变量名
begin
PL/SQL语句块
end [存储过程名];
*/
--定义
create or replace procedure my_procedure
as
begin
    dbms_output.put_line('这是第一个存储过程');
end my_procedure;

 

--调用
begin
    my_procedure();
end;

图片 1

--带输入参数的存储过程(插入数据的存储过程)
create or replace procedure my_procedure2
(
v_empno in number,
v_ename in varchar2,
v_job in varchar2,
v_deptno in number
)
as
begin
    --执行一个插入语句
    insert into emp (empno,ename,job,deptno) values (v_empno,v_ename,v_job,v_deptno);
end my_procedure2;

实例代码:

--调用
begin
    my_procedure2(7944,'老梅','teacher',10);
end;

--set serveroutput on;
create or replace procedure split_page_procedure(page_index in number,
page_size in number,
key_word in varchar2) is

select * from emp;

  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;

--带输入输出参数的存储过程,根据输出工资查询出大于该工资的员工人数
create or replace procedure my_procedure3
(
    v_sal in number,
    v_count out number
)
as
begin
    select count(1) into v_count from emp where sal > v_sal;
end my_procedure3;

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);

--调用
declare
    v_count number;
    v_sal number := 7000;
begin
    my_procedure3(v_sal, v_count);
    dbms_output.put_line('工资高于'||v_sal||'的人数为'||v_count);
end;

--异常处理
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;

--cursor test is select * from emp
--分页的存储过程,输入参数必须要: 当前页(number)以及一页显示条数(number)  输出参数:总页数(number)和分页数据
create or replace procedure page_handler
(
    page_number in number,
    page_size in number
)
as
    --声明变量
    total_rows number;
    total_page number;
    copy_page_number number := page_number;
    --声明一个弱类型游标类型
    type page_cursor_type is ref cursor;
    --声明游标变量
    page_cursor page_cursor_type;
    --定义一个记录类型
    type v_emp_type is record(
       rn number,
       empno emp.empno%type,
       ename emp.ename%type,
       job emp.job%type,
       mgr emp.mgr%type,
       hiredate emp.hiredate%type,
       sal emp.sal%type,
       comm emp.comm%type,
       deptno emp.deptno%type
    );
    v_emp v_emp_type;
begin
    --查询数据总条数
    select count(1) into total_rows from emp;
    dbms_output.put_line('数据总条数:'||total_rows);
    --通过数据总数和一页显示行数计算总页数
    --使用向上取整的函数 ceil
    total_page := ceil(total_rows/page_size);
    --对page_number进行合法校验
    --输入参数的值时不能改变的
    if copy_page_number < 1 then
        copy_page_number := 1;
    elsif copy_page_number > total_page then
        copy_page_number := total_page;
    end if;
    dbms_output.put_line('当前页码:'||copy_page_number);

 

    --进行分页查询,打开一个动态游标
    open page_cursor for 'select * from (select rownum rn, emp.* from emp where rownum <= '|| (copy_page_number*page_size) ||') e where e.rn > '|| (copy_page_number-1)*page_size ||'';
    loop
        fetch page_cursor into v_emp;
        exit when page_cursor%notfound;
        dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
    end loop;
    close page_cursor;
end page_handler;

方法一, 直接抛

--子查询
select * from (select rownum rn, emp.* from emp where rownum < 10) e where e.rn > 0;

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.

--执行
begin
    page_handler(-1, 5);
end;

 

select * from emp;

方法二, 定义个错误代码与消息后, 再抛。

select ceil(5/3) from dual;

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.

--包的使用,在包中定义一个存储过程,输入参数为薪资,输出输出为游标变量
create or replace package my_package
as
--定义游标、存储过程、函数等
   
    --定义一个游标类型
    type emp_cursor_type is ref cursor;
    procedure getEmp(v_sal in number, emp_cursor out emp_cursor_type);
end my_package;

--定义包体部分
create or replace package body my_package
as
    --实现存储过程
    procedure getEmp(v_sal in number, emp_cursor out emp_cursor_type)
    as
    begin
        open emp_cursor for select * from emp where sal > v_sal;
    end getEmp;
end my_package;

--调用程序包
declare
    --声明游标变量
    emp_cursor my_package.emp_cursor_type;
    v_emp emp%rowtype;
begin
    my_package.getEmp(9999, emp_cursor);
    loop
        fetch emp_cursor into v_emp;
        exit when emp_cursor%notfound;
        dbms_output.put_line(v_emp.empno||' '||v_emp.ename);
    end loop;
end;

select * from emp;

create or replace procedure page_handler2
(
    page_number in number,
    page_size in number,
    total_page out number,
    data_list out my_package.emp_cursor_type
)
as
    total_rows number;
    copy_page_number number := page_number;
begin
    --查询总条数
    select count(1) into total_rows from emp;
    --计算总页数
    total_page := ceil(total_rows/page_size);
    --校验当前页码
    if copy_page_number < 1 then
        copy_page_number := 1;
    elsif copy_page_number > total_page then
        copy_page_number := total_page;
    end if;
    --分页查询
    open data_list for 'select * from (select rownum rn, emp.* from emp where rownum<='||(copy_page_number*page_size)||') e where e.rn>'||(copy_page_number-1)*page_size||'';
end page_handler2;

--调用
declare
    total_page number;
    data_list my_package.emp_cursor_type;
        --定义一个记录类型
    type v_emp_type is record(
       rn number,
       empno emp.empno%type,
       ename emp.ename%type,
       job emp.job%type,
       mgr emp.mgr%type,
       hiredate emp.hiredate%type,
       sal emp.sal%type,
       comm emp.comm%type,
       deptno emp.deptno%type
    );
    v_emp v_emp_type;
begin
    page_handler2(4,5,total_page,data_list);
    dbms_output.put_line('总页数:'||total_page);
    loop
        fetch data_list into v_emp;
        exit when data_list%notfound;
        dbms_output.put_line('员工姓名:'||v_emp.ename||',员工职位:'||v_emp.job);
    end loop;
end;

call my_procedure();

select * from emp;

drop table emp_history;