Oracle 某存储过程

Posted on 2006-03-28 08:21 剑廿三阅读(273) 评论(0) 收藏举报

工具：PLSQL Developer 6.05

包头定义：

create or replace package PACKAGES_OPERLOG is

-- Author : STEPHEN WANG JIA JIAN

-- Created : 2006-3-27 14:32:40

-- Purpose :

-- Public type declarations

TYPE logcur IS REF CURSOR ;

-- Public function and procedure declarations

PROCEDURE GetAllOperLog(l_cursor OUT logcur);

PROCEDURE CreateLog(p_UserID char ,p_OperateTime date,p_IP varchar2 ,p_OperateType varchar2 ,p_OperateDesc varchar2 ,p_LogID char );

PROCEDURE UpdateLogByLogID(p_UserID char ,p_OperateTime date,p_IP varchar2 ,p_OperateType varchar2 ,p_OperateDesc varchar2 ,p_LogID char );

end PACKAGES_OPERLOG;

包体定义：

create or replace package body PACKAGES_OPERLOG is

-- Function and procedure implementations

PROCEDURE GetAllOperLog(l_cursor OUT logcur) IS

BEGIN

OPEN l_cursor FOR

SELECT U.ZHNAME AS UserName,L.OPERATETIME AS OPERATETIME,L.IP AS IP,L.OPERATETYPE AS OPERATENAME,L.OPERATEDESC AS OPERATEDESC,L.LOGID AS LOGID FROM OPER_LOG L,GDTEL_USER U WHERE L.USERID = U.USERID;

END GetAllOperLog;

-- Create Log

PROCEDURE CreateLog(p_UserID char ,p_OperateTime date,p_IP varchar2 ,p_OperateType varchar2 ,p_OperateDesc varchar2 ,p_LogID char ) IS

v_UserID char ( 32 ): = p_UserID;

v_OperateTime date: = p_OperateTime;

v_IP varchar2 ( 50 ): = p_IP;

v_OperateType varchar2 ( 255 ): = p_OperateType;

v_OperateDesc varchar2 ( 255 ): = p_OperateDesc;

v_LogID char ( 32 ): = p_LogID;

BEGIN

insert into OPER_LOG values (v_UserID,v_OperateTime,v_IP,v_OperateType,v_OperateDesc,v_LogID);

END CreateLog;

-- Update Log by LogID

PROCEDURE UpdateLogByLogID(p_UserID char ,p_OperateTime date,p_IP varchar2 ,p_OperateType varchar2 ,p_OperateDesc varchar2 ,p_LogID char ) IS

v_UserID char ( 32 ): = p_UserID;

v_OperateTime date: = p_OperateTime;

v_IP varchar2 ( 50 ): = p_IP;

v_OperateType varchar2 ( 255 ): = p_OperateType;

v_OperateDesc varchar2 ( 255 ): = p_OperateDesc;

v_LogID char ( 32 ): = p_LogID;

BEGIN

update OPER_LOG set OPER_LOG.USERID = v_UserID, OPER_LOG.OPERATETIME = v_OperateTime, OPER_LOG.IP = v_IP, OPER_LOG.OPERATETYPE = v_OperateType, OPER_LOG.OPERATEDESC = v_OperateDesc where OPER_LOG.LOGID = v_LogID;

END UpdateLogByLogID;

end PACKAGES_OPERLOG;

C# 调用代码：（使用 ADOHelper）