空间管理您的位置: 51Testing软件测试网 » karen的个人空间 » 日志

oracle练习（二）创建包、包内function

上一篇 / 下一篇 2009-12-25 19:24:40 / 个人分类：Oracle相关

查看( 578 ) / 评论( 0 ) / 评分( 0 / 0 )

题目：

第三步：创建存储过程

创建存储过程包及其包体：PKG_USER

实现函数1：

函数名adduser ,:

输入参数： (name varchar2, type number,,mobileno varchar2,note varchar2),

输出参数： oper_code number --失败原因如下（

0-无错误，

1000-用户已存在，

4444- 输入的参数中有空值

9999-其他原因失败）

函数返回值： ret number (1-成功，2失败)

实现内容：

1）判断输入参数的4个值是否有null的，如果有，返回错误，per_code=4444

2）插入用户资料到T_USER表，其中id字段要取SE_T_USER_ID的.nextvalue

3）插入操作日志到T_LOG_OPER 。根据操作的不同，其LOG_TYPE = 1

4) 利用dblink(test_63) 插入id,name2个值到 192.168.10.63的test用户下的T_GROUP_USER表内。

实现函数2：

函数名updateuser

输入参数： (id number,name varchar2, type number,,mobileno varchar2,note varchar2),

输出参数： oper_code number --失败原因（0-无错误，1001-无此用户，9999-其他原因失败）

函数返回值： ret number (1-成功，2失败)

实现内容：

1) 根据参数id来修改T_USER表的相应行的MOBILENO，TYPE,LAST_MODIFIED_TIME和note四个字段

2）插入操作日志到T_LOG_OPER 。根据操作的不同，其LOG_TYPE=2

实现函数3：

函数名deleteuser ,

输入参数： (id number,)

输出参数： oper_code number --失败原因（0-无错误，1001-无此用户，9999-其他原因失败）

函数返回值：tet number (1-成功，2失败)

实现内容：

1) 根据传递的参数id,判断是否在V_T_USER视图内。如果在则不删除，返回,。如果不在，则从T_USER表中删除。

2）外部调用该函数后，插入操作日志到T_LOG_OPER 。根据操作的不同，其LOG_TYPE字段值也不同（参见T_LOG_OPER的LOG_TYPE定义）

实现函数4：

函数名：statUser .。无输入参数，返回值为游标

按照T_USER表的TYPE字段，分组统计(group by )用户数。统计的结果为(样例)：

Type total

=== ====

1 100

2 20

3 130

..... .....

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; --操作成功

    <<Exit2>>
        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;-- 操作成功
<<Exit2>>
    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;
<<Exit2>>
    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;

收藏举报

TAG:

查看全部评论

oracle练习（二）创建包、包内function

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅