c#存储过程3.txt

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;