oracle怎么测试包,oracle练习（二）创建包、包内function

SQL 语句(编辑器不好排版！)：

1、cereate package：

create or replace package pkg_user is

OPER_CODE_NO_ERROR constant integer:=0;

OPER_CODE_USER_EXIST constant integer:=1000;

OPER_CODE_NO_USER constant integer:=1001;

OPER_CODE_INPUTS_HAS_NULL constant integer:=4444;

OPER_CODE_ERROR_OTHER constant integer:=9999;

function adduser(name in varchar2, type in number,mobileno in varchar2,note in varchar2,oper_code out number) return number;

function updateuser(id in number,name in varchar2, type in number,mobileno varchar2,note varchar2,oper_code out number) return number;

function deleteuser(id in number,oper_code out number) return number;

function statUser return sys_refcursor;

end pkg_user;

2、create package body：

create or replace package body pkg_user is

----------------------- function  adduser  开始 -----------------------------

function adduser(name in varchar2, type in number,mobileno in varchar2,note in varchar2,oper_code out number) return number

is

user_id integer;

begin

-- 校验输入参数是否为null

if(

(name is null) or

(type is null) or

(mobileno is null) or

(note is null)

) then

oper_code:=pkg_user.OPER_CODE_INPUTS_HAS_NULL;

goto Exit2;

end if;

select se_t_user_id.nextval into user_id from dual;

-- 插入数据到 t_user 表中

begin

insert into t_user(t_user.id,t_user.name,t_user.type,t_user.mobileno,t_user.note) values(user_id,name,type,mobileno,note);

exception

when DUP_VAL_ON_INDEX then

oper_code:=pkg_user.OPER_CODE_USER_EXIST;

goto Exit2;

when others then

oper_code:=pkg_user.OPER_CODE_ERROR_OTHER;

goto Exit2;

end;

-- 插入数据到 t_log_oper 中

insert into t_log_oper(id,log_type) values(user_id,1);

-- 用 dblink(test_63) 向 T_GROUP_USER中插入值

insert intot_group_user@test_63(id,name) values(user_id,name);

oper_code:=pkg_user.OPER_CODE_NO_ERROR;

return 1; --操作成功

<>

return 2; -- 操作失败

end adduser;

---------------------- function adduser  结束 ------------------------------------------

-----------------------  function updateuser  开始 --------------------------------------

function updateuser(id in number,name in varchar2, type in number,mobileno varchar2,note varchar2,oper_code out number) return number

is

v_id t_user.id%type;

v_name t_user.name%type;

v_mobileno t_user.mobileno%type;

v_note t_user.note%type;

v_type integer;

begin

-- 参数null 值校验

if(

(id is null) or

(name is null) or

(type is null)

)then

oper_code:=pkg_user.OPER_CODE_INPUTS_HAS_NULL;

goto Exit2;

end if;

v_id:=id;

v_mobileno:=mobileno;

v_type:=type;

v_note:=note;

-- 根据 id 修改 t_user

update t_user set t_user.mobileno=v_mobileno,t_user.type=type,t_user.last_modified_time=sysdate,t_user.note=note where t_user.id=v_id;

if (sql%rowcount = 0) then

oper_code:=pkg_user.OPER_CODE_NO_USER;

goto Exit2;

end if;

-- 记日志

insert into t_log_oper(id,log_type) values(id,2);

oper_code:=pkg_user.OPER_CODE_NO_ERROR;

return 1;-- 操作成功

<>

return 2; -- 操作失败

end updateuser;

----------------------   function updateuser  结束 ----------------------------------------

---------------------- function deleteuser  开始 -----------------------------------------

function deleteuser(id in number,oper_code out number) return number

is

v_id t_user.id%type;

begin

-- 参数null 值校验

if(id is null) then

oper_code:=pkg_user.OPER_CODE_INPUTS_HAS_NULL;

goto Exit2;

end if;

v_id:=id;

delete from t_user where t_user.id=v_id;

if (sql%rowcount = 0) then

oper_code:=pkg_user.OPER_CODE_NO_USER;

goto Exit2;

end if;

-- 记日志

insert into t_log_oper(id,log_type) values(id,2);

oper_code:=pkg_user.OPER_CODE_NO_ERROR;

return 1;

<>

return 2; -- 操作失败

end deleteuser;

----------------------- function deleteuser  结束 ------------------------------------------

----------------------- function statUser 开始 --------------------------------------------

function statUser return sys_refcursor

is

v_ref_cursor SYS_REFCURSOR;

begin

open v_ref_cursor for

'select type ,count(*) total from t_user group by type order by type';

return v_ref_cursor;

end statUser;

---------------------  function statUser 结束 -------------------------------------------------

end pkg_user;