菜单

储存进程写法小例子

2020年2月10日 - 数据网络
储存进程写法小例子

使用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;

1、存储过程的基本语法:

 

–调用
begin
    my_procedure();
end;

  CREATE OR REPLACE PROCEDURE 存储过程名(param1
in type,param2 out type)

图片 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;

  IS

实例代码:

–调用
begin
    my_procedure2(7944,’老梅’,’teacher’,10);
end;

  变量1 类型(值范围);

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

  变量2 类型(值范围);  

  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

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;

    select
count(*) into 变量1 from 表名 where 列名=param1;

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

    if (判断条件)
then

 

    –进行分页查询,打开一个动态游标
    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 列名
into 变量2 from 表名 where 列名=param1;

方法一, 直接抛

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

      DBMS_OUTPUT.put_line(‘打印信息’);

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;

    Elsif
(判断条件) then

 

select * from emp;

      dbms_output.put_line(‘打印信息’);

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

select ceil(5/3) from dual;

    Else

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;

      Raise
异常名 (NO_DATA_FOUND);

–定义包体部分
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;

    End if;

–调用程序包
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;

  Exception

select * from emp;

      When
others then

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;

        Rollback;
  

–调用
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;

  END;

call my_procedure();

2、已命名的异常:

select * from emp;

  命名的系统异常              
产生原因

drop table emp_history;

  ACCESS_INTO_NULL           未定义对象

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图