首先,创建一个队列Sequence
-- Create sequence
create sequence PRIMARYKEYSEQUENCE
minvalue 0
maxvalue 999999999999999999999999999
start with 83
increment by 1
nocache;
然后,创建触发器Triggers
-- Create triggers
create or replace trigger XG_ATTENDANCETRIGGER
before insert on xg_attendance
for each row
declare
nextid number;
begin
if:new.AID is null or:new.AID=0
then
select primarykeysequence.nextval into nextid from sys.dual;
:new.AID:= nextid;
end if;
end XG_ATTENDANCETRIGGER;
最后,在包Packages中创建存储过程Procedure
-- Create procedure
create or replace package body xg.AttendancePackage is
procedure proc_insertAttendance(wid in varchar2,inTime in varchar2,aid out varchar2)
is
begin
insert into xg.xg_attendance(wid,aintime) values(wid,inTime);
select xg.primarykeysequence.currval into aid from sys.dual;
end;
end AttendancePackage;
在ASP.NET中用企业库调用Oracle的存储过程
using System;
using System.Data;
using EntityLibrary;
using System.Data.Common;
using System.Diagnostics;
public class AttendanceBiz:Biz
{
/// <summary>
/// 记录职工上班时间
/// </summary>
/// <param name="wid">职工号</param>
/// <param name="inTime">上班时间</param>
/// <returns>出勤记录编号</returns>
public int SetInTime(string wid,string inTime)
{
DbCommand cmd = _database.GetStoredProcCommand("AttendancePackage.proc_insertAttendance");
_database.AddInParameter(cmd, "wid", DbType.String,wid);
_database.AddInParameter(cmd, "inTime", DbType.String,inTime);
_database.AddOutParameter(cmd, "aid", DbType.Int32, 22);
cmd.Connection = _database.CreateConnection();
int aid = 0;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
aid = Convert.ToInt32(cmd.Parameters["aid"].Value);
}
catch(DbException e)
{
if (!EventLog.Exists("csit"))
{
EventLog.CreateEventSource("csit", "csit");
}
EventLog.WriteEntry("csit", e.Message);
}
finally
{
cmd.Connection.Close();
}
return aid;
}
}
using System.Data;
using EntityLibrary;
using System.Data.Common;
using System.Diagnostics;
public class AttendanceBiz:Biz
{
/// <summary>
/// 记录职工上班时间
/// </summary>
/// <param name="wid">职工号</param>
/// <param name="inTime">上班时间</param>
/// <returns>出勤记录编号</returns>
public int SetInTime(string wid,string inTime)
{
DbCommand cmd = _database.GetStoredProcCommand("AttendancePackage.proc_insertAttendance");
_database.AddInParameter(cmd, "wid", DbType.String,wid);
_database.AddInParameter(cmd, "inTime", DbType.String,inTime);
_database.AddOutParameter(cmd, "aid", DbType.Int32, 22);
cmd.Connection = _database.CreateConnection();
int aid = 0;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
aid = Convert.ToInt32(cmd.Parameters["aid"].Value);
}
catch(DbException e)
{
if (!EventLog.Exists("csit"))
{
EventLog.CreateEventSource("csit", "csit");
}
EventLog.WriteEntry("csit", e.Message);
}
finally
{
cmd.Connection.Close();
}
return aid;
}
}
本文转自钢钢博客园博客，原文链接：http://www.cnblogs.com/xugang/archive/2008/02/20/1074535.html，如需转载请自行联系原作者