oracle基础教程之32包(子程序重载)
直接上代码吧
--根据员工号或员工姓名获取员工的信息 --根据员工号或员工姓名删除员工的信息 --创建包规范 create or replace package overload_pkg is function get_info(eno number) return emp%rowtype; function get_info(name varchar2) return emp%rowtype; procedure del_emp(eno number); procedure del_emp(name varchar2); end;
--创建包体 create or replace package body overload_pkg is function get_info(eno number) return emp%rowtype is emp_record emp%rowtype; begin select * into emp_record from emp where empno = eno; return emp_record; exception when no_data_found then raise_application_error(-20020,'不存在此员工!'); end; function get_info(name varchar2) return emp%rowtype is emp_record emp%rowtype; begin select * into emp_record from emp where ename = name; return emp_record; exception when no_data_found then raise_application_error(-20020,'不存在此员工!'); end; procedure del_emp(eno number) is begin delete from emp where empno = eno; if sql%notfound then raise_application_error(-20020,'不存在此员工'); end if; end; procedure del_emp(name varchar2) is begin delete from emp where ename = name; if sql%notfound then raise_application_error(-20020,'不存在此员工'); end if; end; end;
调用:
--调用 --根据员工号查询员工信息 declare emp_record emp%rowtype; e_no_emp exception; pragma exception_init(e_no_emp,-20020); begin emp_record:= overload_pkg.get_info(&no); dbms_output.put_line('员工号:'||emp_record.empno||'姓名:'||emp_record.ename||'工资:'||emp_record.sal); exception when e_no_emp then dbms_output.put_line(sqlerrm); end; --根据员工姓名查询员工信息 declare emp_record emp%rowtype; e_no_emp exception; pragma exception_init(e_no_emp,-20020); begin emp_record:= overload_pkg.get_info('&name'); dbms_output.put_line('员工号:'||emp_record.empno||'姓名:'||emp_record.ename||'工资:'||emp_record.sal); exception when e_no_emp then dbms_output.put_line(sqlerrm); end; --根据员工号删除员工信息 declare e_no_emp exception; pragma exception_init(e_no_emp, -20020); begin overload_pkg.del_emp(&no); commit; exception when e_no_emp then dbms_output.put_line(sqlerrm); rollback; end; --根据员工的姓名删除员工的信息 declare e_no_emp exception; pragma exception_init(e_no_emp, -20020); begin overload_pkg.del_emp('&name'); commit; exception when e_no_emp then dbms_output.put_line(sqlerrm); rollback; end;
如对本文有疑问, 点击进行留言回复!!
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句(时间格式、去重、去特殊符号、查询表空间等)
Navicate 如何导出数据库中的存储过程、事件、视图等?
每日一记:Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
网友评论