Oracle存储过程实例

自定义函数 

-- 自定义函数
CREATE   OR   REPLACE   FUNCTION  fn_WFTemplateIDGet
(
    TemplateCategoryID  NUMBER ,
    OrganID  NUMBER ,
    TemplateMode  NUMBER
) 
RETURN   NUMBER
IS
    TemplateID  NUMBER ;
    ItemCount  NUMBER ;
BEGIN
     -- 取模板中指定机构,指定分类的工作流模板记录
     SELECT   COUNT ( * )  INTO  ItemCount
     FROM  t_WFTemplate
     WHERE  f_OrganID  =  OrganID  AND  f_TemplateCategoryID  =  TemplateCategoryID;

     IF  ItemCount  =   1   THEN
         SELECT  f_TemplateID  INTO  TemplateID
         FROM  t_WFTemplate
         WHERE  f_OrganID  =  OrganID  AND  f_TemplateCategoryID  =  TemplateCategoryID;
     ELSE
        TemplateID : =   0 ;
     END   IF ;

     RETURN (TemplateID);
END  fn_WFTemplateIDGet;

包头

-- 包头
CREATE   OR   REPLACE  PACKAGE pkg_TEMP  is

    TYPE curRecordset  IS  REF  CURSOR ;
    
     -- 功能：由人员获取数据
     PROCEDURE  up_ModuleShowByEmployeeID
    (
        EmployeeID  NUMBER ,
        objRs OUT curRecordset
    );

END  pkg_TEMP;

包体

-- 包体
CREATE OR REPLACE PACKAGE BODY pkg_TEMP  is

     -- 功能：由人员获取数据
    PROCEDURE up_ModuleShowByEmployeeID
    (
        EmployeeID NUMBER,
        objRs OUT curRecordset
    )
    IS
    BEGIN
        OPEN objRs FOR
            SELECT
                a.f_ApplicationID,
                a.f_ApplicationName
            FROM t_Application a
            JOIN t_ApplicationEmployee d ON d.f_RoleID  =  a.f_RoleID
            AND d.f_EmployeeID  =  EmployeeID
            UNION
            SELECT
                b.f_ApplicationID,
                b.f_ApplicationName
            FROM t_Application b
            WHERE BitAnd(f_RowFlag, 3 )  >   0  OR f_AppCode  =   ' 20 '  OR f_AppCode  =   ' 2040 ' ;
            ORDER BY f_ApplicationID ASC ;
    END up_ModuleShowByEmployeeID;
    
     -- 用户登录验证
    PROCEDURE up_LoginValidate
    (
        EmployeeID OUT NUMBER,
        LoginName VARCHAR2,
        Password VARCHAR2
    )
    IS
        RecordCount NUMBER;
    BEGIN
        SELECT COUNT( * ) INTO RecordCount FROM vw_Employee
        WHERE f_LoginName  =  LoginName;

        IF RecordCount  =   0  THEN
             -- 登录用户不存在
            EmployeeID : =   - 1 ;
            RAISE_APPLICATION_ERROR( - 20000 ,  ' 登录用户不存在! ' );
            RETURN;
        END IF;

        SELECT COUNT( * ) INTO RecordCount FROM vw_Employee
        WHERE f_LoginName  =  LoginName
        AND f_RowFlag  =   0 ;

        IF RecordCount  =   0  THEN
             -- 用户没有访问权限
            EmployeeID : =   - 2 ;
            RAISE_APPLICATION_ERROR( - 20000 ,  ' 用户没有访问权限! ' );
            RETURN;
        END IF;

        SELECT COUNT( * ) INTO RecordCount FROM vw_Employee
        WHERE f_LoginName  =  LoginName
        AND f_Password  =  Password
        AND f_RowFlag  =   0 ;

        IF RecordCount  =   0  THEN
             -- 登录名称或密码错误
            EmployeeID : =   - 3 ;
            RAISE_APPLICATION_ERROR( - 20000 ,  ' 登录名称或密码错误! ' );
            RETURN;
        END IF;

        SELECT f_EmployeeID INTO EmployeeID
        FROM vw_Employee
        WHERE f_LoginName  =  LoginName
        AND f_Password  =  Password
        AND f_RowFlag  =   0 ;
    END up_LoginValidate;

     -- 功能：获取记录集
    PROCEDURE up_VisitLogShow
    (
        VisitYear NUMBER,        -- 年
        VisitMonth NUMBER,       -- 月
        objRs OUT curRecordset
    )
    IS
    BEGIN
        OPEN objRs FOR
        SELECT
            c.f_CustomerName AS f_OrganName,
            a.f_VisitLogID,
            a.f_EmployeeID,
            a.f_EmployeeName,
            a.f_IPAddress,
            to_Char(a.f_VisitTime, ' yyyy-MM-dd HH24:mi:ss ' ) AS f_VisitTime,
            a.f_VisitPage
        FROM vw_VisitLog a
        LEFT JOIN vw_Employee b ON b.f_EmployeeID  =  a.f_EmployeeID
        LEFT JOIN vw_customer c ON c.f_CustomerID  =  b.f_OrganID
        WHERE EXTRACT(YEAR FROM a.f_VisitTime)  =  VisitYear
        AND EXTRACT(MONTH FROM a.f_VisitTime)  =  VisitMonth
        ORDER BY f_VisitLogID DESC;
    END up_VisitLogShow;
    
     -- 获取查询结果
    PROCEDURE up_QueryShow
    (
        OrganID NUMBER,
        LineKey VARCHAR2,
        objRs OUT curRecordset
    )
    IS
    BEGIN
        OPEN objRs FOR
        SELECT
                a. * ,
                CASE nvl(b.f_TypeID , 0 )
                    WHEN  0  THEN  ' 无类型 '
                    WHEN  1  THEN  ' 类型1 '
                    ELSE  ' 类型2 '
                END AS f_TypeName,
                b.f_DoubleLineCode
        FROM vw_LineOrganDNShow  a
        LEFT JOIN vw_LineDistributionGroup b ON a.f_LineGroupID  =  b.f_LineGroupID
        WHERE (f_LineKey LIKE LineKey || ' % '  OR f_LineName LIKE LineKey  || ' % ' )
        AND a.f_OrganID  =  OrganID
        AND ROWNUM  <   20 ;
    END up_LineQueryShow;
    
     -- 功能：游标循环
    PROCEDURE up_WFConfigCopy
    (
        TemplateID NUMBER,           -- 源模版ID
        NewTemplateName VARCHAR2,    -- 新模版名称
        NewRemark VARCHAR2,          -- 新模版说明
        NewOrganID NUMBER            -- 新模版机构ID
    )
    IS
        TemplateCategoryID NUMBER;   -- 源模版分类ID
        NewTemplateID NUMBER;        -- 新模版ID

         -- 流程步骤
        StepName VARCHAR2( 50 );
        StepOrder NUMBER;
        MaxHour NUMBER;
        StepFlag NUMBER;
         -- 源模板对应的流程步骤集
        CURSOR WFSteps           
        IS 
            SELECT 
                a.f_StepName,
                a.f_StepOrder,
                a.f_StepFlag
            FROM vw_WFStep a
            WHERE a.f_TemplateID  =  TemplateID; 
         
    BEGIN
         -- StepID : =  fn_WFNextStepIDGet(StepID);
         -- 获取被复制模版分类ID
        SELECT f_TemplateCategoryID INTO TemplateCategoryID
        FROM vw_WFTemplate
        WHERE f_TemplateID  =  TemplateID;
         -- 新增模板
        up_WFTemplateAdd
        (
            NewTemplateID,
            NewTemplateName,
            TemplateCategoryID,
            NewRemark,
            NewOrganID
        );
        
         -- 复制源模板的流程步骤
        OPEN WFSteps;
        LOOP 
        FETCH WFSteps INTO StepName,StepOrder,StepFlag;
            EXIT WHEN WFSteps % NOTFOUND;
             -- 添加步骤
            INSERT INTO t_WFStep
            (
                f_TemplateID,
                f_StepID,
                f_StepName,
                f_StepOrder,
                f_MaxDate,
                f_StepFlag
            )
            VALUES
            (
                NewTemplateID,     
                Seq_WFStep.NEXTVAL,  -- 步骤ID
                StepName,   
                StepOrder,   
                SYSDATE,          -- 当前日期
                StepFlag
            );
        END LOOP;            
        CLOSE WFSteps;
    END up_WFConfigCopy;
    
     -- 功能:新增
    PROCEDURE up_WFRoleAdd
    (
        TemplateCategoryID NUMBER,   -- 模板分类ID
        WFRoleName VARCHAR2        -- 角色名称
    )
    IS
        WFRoleID NUMBER;
    BEGIN
        SELECT Seq_WFRole.NEXTVAL INTO WFRoleID FROM dual;
         -- 流程角色表
        INSERT INTO t_WFRole
        (
            f_RoleID,
            f_RoleName
        )
        VALUES
        (
            WFRoleID,
            WFRoleName
        );
    END up_WFRoleAdd;
    
     -- 功能:修改
    PROCEDURE up_WFRoleEdit
    (
        WFRoleID NUMBER,           -- 角色ID
        WFRoleName VARCHAR2        -- 角色名称
    )
    IS
    BEGIN
         -- 流程角色表
        UPDATE t_WFRole
        SET f_RoleName  =  WFRoleName
        WHERE f_RoleID  =  WFRoleID;        
    END up_WFRoleEdit;
    
    
     -- 功能:删除
    PROCEDURE up_WFRoleDelete
    (
        WFRoleID NUMBER  
    )
    IS
        CountRole NUMBER;
    BEGIN
        SELECT COUNT( * ) INTO CountRole
        FROM vw_WFRoleEmployee
        WHERE f_RoleID  =  WFRoleID;
        IF CountRole1  =   0  THEN
             -- 流程模板分类角色对应表
            DELETE FROM t_WFTemplateCategoryRole
            WHERE f_RoleID  =  WFRoleID;
             -- 流程角色表
            DELETE FROM t_WFRole
            WHERE f_RoleID  =  WFRoleID;
        ELSE
            RAISE_APPLICATION_ERROR( - 20000 ,  ' 该角色已设置到流程中,不能删除! ' );
        END IF;
    END up_WFRoleDelete;

END pkg_TEMP;