PACKAGE 定义:
CREATE OR REPLACE PACKAGE pkg_login
IS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE p_login (usercode VARCHAR2,passwd VARCHAR2, Re_CURSOR OUT T_CURSOR);
PROCEDURE p_user_popedom (userid VARCHAR2,parentid VARCHAR2, Re_CURSOR OUT T_CURSOR);
PROCEDURE p_get_relation_modules (a_userid VARCHAR2,a_moduleid VARCHAR2, Re_CURSOR OUT T_CURSOR);
END;
PACKAGE BODIES:
CREATE OR REPLACE PACKAGE BODY pkg_login
IS
PROCEDURE p_login(usercode IN VARCHAR2,
passwd IN VARCHAR2,
Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
v_RowCount NUMBER(3,0);
v_UserID VARCHAR2(36);
v_AreaCode Varchar2(36);
--v_AreaName VARCHAR2(20);
BEGIN
SELECT COUNT(*) INTO v_RowCount FROM t_neibugzry
WHERE c_gongzuozh=usercode;
IF v_RowCount=0 THEN
raise_application_error (-20001,'用户名不存在!');
END IF ;
IF passwd IS NULL THEN
SELECT COUNT(*) INTO v_RowCount FROM t_neibugzry
WHERE c_gongzuozh=usercode
AND c_mima IS NULL;
ELSE
SELECT COUNT(*) INTO v_RowCount FROM t_neibugzry
WHERE c_gongzuozh=usercode
AND c_mima=ltrim(passwd);
END IF;
IF v_RowCount=0 THEN
raise_application_error (-20001,'密码不正确!');
END IF ;
SELECT c_id,
c_danwei
INTO v_UserID,
v_AreaCode
FROM t_neibugzry
WHERE c_gongzuozh=usercode
AND (c_mima=ltrim(passwd)
or (c_mima is null and passwd is null));
-- v_AreaName := '**************';
OPEN V_CURSOR FOR
SELECT v_UserID AS u_id,
P.c_Xingming AS u_name,
y.c_zu AS u_group,
z.c_leixing AS group_type,
1 AS is_supervisor,
c.c_daima AS area,
c.c_mingcheng AS areaname
from t_neibugzry P,
t_zuyongh Y,
t_zu Z,
t_daima c
WHERE p.c_id=y.c_renyuan
AND y.c_zu=z.c_id
AND p.c_id=v_UserID
AND c.c_id = p.c_danwei;
Re_CURSOR := V_CURSOR;
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20001,SQLCODE || substr(SQLERRM,1,200));
END p_login;
PROCEDURE p_user_popedom(userid IN VARCHAR2,
parentid IN VARCHAR2,
Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
v_parentid VARCHAR2(36);
v_groupid VARCHAR2(36);
BEGIN
if parentid IS NULL THEN
SELECT c_id INTO v_parentid from t_mokuai
where c_mingcheng ='功能模块'
and c_fumok IS NULL;
ELSE
v_parentid := parentid;
END IF;
--判断是否是超级管理员
IF dev.f_is_supervisor(userid) THEN
OPEN V_CURSOR FOR
select m.c_id,m.c_mingcheng,m.c_kuming,m.c_leixingmc,m.c_tubiao
from t_mokuai m
where m.c_fumok = v_parentid
order by c_shunxu, c_mingcheng;
Re_CURSOR := V_CURSOR;
RETURN;
ELSE
--根据所在组的权限返回模块
SELECT y.c_zu INTO v_groupid FROM t_zuyongh Y WHERE c_renyuan=userid;
OPEN V_CURSOR FOR
select m.c_id,m.c_mingcheng,m.c_kuming,c_leixingmc,m.c_tubiao
from t_mokuai m
where m.c_fumok = v_parentid
AND (m.c_leixingmc='N'
OR m.c_leixingmc IS NULL
OR (m.c_id IN (SELECT c_mokuai FROM t_zumok WHERE c_zu= v_groupid)))
order by c_shunxu, c_mingcheng;
Re_CURSOR := V_CURSOR;
END IF;
END p_user_popedom;
PROCEDURE p_get_relation_modules (a_userid VARCHAR2,a_moduleid VARCHAR2, Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
v_groupid VARCHAR2(36);
BEGIN
--根据所在组的权限返回模块
SELECT y.c_zu INTO v_groupid FROM t_zuyongh Y WHERE c_renyuan=a_userid;
OPEN V_CURSOR FOR
SELECT m.c_id,m.c_mingcheng,m.c_kuming,c_leixingmc,m.c_tubiao
from t_mokuai m, t_xiangguangn b
where m.c_id = b.c_xiangguangn
AND b.c_mokuai = a_moduleid
--暂不判断权限
-- AND m.c_id IN (SELECT c_mokuai FROM t_zumok WHERE c_zu= v_groupid)
order by m.c_shunxu, m.c_mingcheng;
Re_CURSOR := V_CURSOR;
END p_get_relation_modules;
END pkg_login;