Package definition:
Create Or Replace Package PKG_LoginiStype 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_Loginis
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, 'username does not exist!'); Endiff;
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, 'Password is incorrect!'); Endiff;
Select C_ID, C_Danwei INTO V_USERID, V_Aracode 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 = 'functional modules' And c_fumok is null; else v_parentId: = ParentID; end if; 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 - returning module SELECT y.c_zu INTO v_groupid FROM t_zuyongh Y WHERE c_renyuan = userid The authority's group; 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
- Returns the module 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 - temporarily determining permissions - 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;
