现在的项目比较紧,加上自己也比较懒,实在是“没时间”写啊,呵呵,昨天看到一篇挺好的Oracle存储过程的例子,正好最近要用,转过来大家一起分享一下,谢谢(晨光映霞),原作地址:http://blog.csdn.net/xuyabao/archive/2008/03/20/2200205.aspx。
--------------------自定义函数开始--------------------
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;
--------------------包体结束--------------------