IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ts_tblAgencyInfo1_Add')
BEGIN
PRINT 'Dropping Ts_tblAgencyInfo1_Add'
DROP Procedure Ts_tblAgencyInfo1_Add
END
GOPRINT 'Creating Ts_tblAgencyInfo1_Add'
GO CREATE Procedure dbo.Ts_tblAgencyInfo1_Add
@decAGENTID int,
@strORDERVALUE nvarchar(),
@strZIPCODE nvarchar(),
@strPROVINCE nvarchar(),
@strADDRESS nvarchar(),
@strAGENTCODE nvarchar(),
@strFAX nvarchar(),
@strEMAIL nvarchar(),
@strREMARK nvarchar(),
@strOPERATION nvarchar(),
@strTELEPHONE nvarchar(),
@datTIMECREATED datetime ,
@strAGENTNAME nvarchar(),
@strCITY nvarchar(),
@strLINKTELEPHONE nvarchar(),
@strLINKMAN nvarchar(),
@datTIMELASTMODIFIED datetime /*
Parameters :
full field list of the rows in the table
Descriptions :
This procedure will add a new data row
Error Result :
-1 Record already exist
Author/Date : 2008-04-03, Jason*/WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @_intTableID AS INTEGER
DECLARE @_strSprocName AS NVARCHAR(100)
SET @_strSprocName = N'Ts_tblAgencyInfo1_Add'
SET @_intTableID =
DECLARE @_intVersion AS BIGINT
DECLARE @_intError AS INTEGER
DECLARE @_datNow AS DATETIME
DECLARE @_intNeedLog AS INTEGER
SET @_intError = 0
SET @_datNow = GETDATE()
SET @_intVersion = 1
SET @_intNeedLog = -1
/* check to see if the record exist */
IF EXISTS(SELECT AGENTID FROM dbo.tblAgencyInfo1 WHERE AGENTID = @decAGENTID)
RETURN -1
/* insert the record */
INSERT INTO dbo.tblAgencyInfo1
(AGENTID, ORDERVALUE, ZIPCODE, PROVINCE, ADDRESS, AGENTCODE, FAX, EMAIL, REMARK, OPERATION, TELEPHONE, TIMECREATED, AGENTNAME, CITY, LINKTELEPHONE, LINKMAN, TIMELASTMODIFIED)
VALUES
(@decAGENTID, @strORDERVALUE, @strZIPCODE, @strPROVINCE, @strADDRESS, @strAGENTCODE, @strFAX, @strEMAIL, @strREMARK, @strOPERATION, @strTELEPHONE, @datTIMECREATED, @strAGENTNAME, @strCITY, @strLINKTELEPHONE, @strLINKMAN, @datTIMELASTMODIFIED)
SET @_intError = @@ERROR
IF @_intError != 0
BEGIN
/* Log down the error*/
EXEC dbo.Ts_Sys_ErrorLog @strSproc=@_strSprocName, @intErrorCode=@_intError
RETURN @_intError
END
RETURN 0
GO
BEGIN
PRINT 'Dropping Ts_tblAgencyInfo1_Add'
DROP Procedure Ts_tblAgencyInfo1_Add
END
GOPRINT 'Creating Ts_tblAgencyInfo1_Add'
GO CREATE Procedure dbo.Ts_tblAgencyInfo1_Add
@decAGENTID int,
@strORDERVALUE nvarchar(),
@strZIPCODE nvarchar(),
@strPROVINCE nvarchar(),
@strADDRESS nvarchar(),
@strAGENTCODE nvarchar(),
@strFAX nvarchar(),
@strEMAIL nvarchar(),
@strREMARK nvarchar(),
@strOPERATION nvarchar(),
@strTELEPHONE nvarchar(),
@datTIMECREATED datetime ,
@strAGENTNAME nvarchar(),
@strCITY nvarchar(),
@strLINKTELEPHONE nvarchar(),
@strLINKMAN nvarchar(),
@datTIMELASTMODIFIED datetime /*
Parameters :
full field list of the rows in the table
Descriptions :
This procedure will add a new data row
Error Result :
-1 Record already exist
Author/Date : 2008-04-03, Jason*/WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @_intTableID AS INTEGER
DECLARE @_strSprocName AS NVARCHAR(100)
SET @_strSprocName = N'Ts_tblAgencyInfo1_Add'
SET @_intTableID =
DECLARE @_intVersion AS BIGINT
DECLARE @_intError AS INTEGER
DECLARE @_datNow AS DATETIME
DECLARE @_intNeedLog AS INTEGER
SET @_intError = 0
SET @_datNow = GETDATE()
SET @_intVersion = 1
SET @_intNeedLog = -1
/* check to see if the record exist */
IF EXISTS(SELECT AGENTID FROM dbo.tblAgencyInfo1 WHERE AGENTID = @decAGENTID)
RETURN -1
/* insert the record */
INSERT INTO dbo.tblAgencyInfo1
(AGENTID, ORDERVALUE, ZIPCODE, PROVINCE, ADDRESS, AGENTCODE, FAX, EMAIL, REMARK, OPERATION, TELEPHONE, TIMECREATED, AGENTNAME, CITY, LINKTELEPHONE, LINKMAN, TIMELASTMODIFIED)
VALUES
(@decAGENTID, @strORDERVALUE, @strZIPCODE, @strPROVINCE, @strADDRESS, @strAGENTCODE, @strFAX, @strEMAIL, @strREMARK, @strOPERATION, @strTELEPHONE, @datTIMECREATED, @strAGENTNAME, @strCITY, @strLINKTELEPHONE, @strLINKMAN, @datTIMELASTMODIFIED)
SET @_intError = @@ERROR
IF @_intError != 0
BEGIN
/* Log down the error*/
EXEC dbo.Ts_Sys_ErrorLog @strSproc=@_strSprocName, @intErrorCode=@_intError
RETURN @_intError
END
RETURN 0
GO
decAGENTID IN NUMBER,
strORDERVALUE IN varchar2,
strZIPCODE IN varchar2,
strPROVINCE IN varchar2,
strADDRESS IN varchar2,
strAGENTCODE IN varchar2,
strFAX IN varchar2,
strEMAIL IN varchar2,
strREMARK IN varchar2,
strOPERATION IN varchar2,
strTELEPHONE IN varchar2,
datTIMECREATED IN date ,
strAGENTNAME IN varchar2,
strCITY IN varchar2,
strLINKTELEPHONE IN varchar2,
strLINKMAN IN varchar2,
datTIMELASTMODIFIED date) RETURN NUMBER
IS
_intTableID NUMBER;
_strSprocName VARCHAR2(100);
_intVersion NUMBER;
_intError NUMBER;
_datNow DATE;
_intNeedLog NUMBER;
_strSprocName VARCHAR2(100);
VNUM NUMBER;
BEGIN
_strSprocName := 'Ts_tblAgencyInfo1_Add';
_intTableID :=0; _intError := 0;
_datNow := DATE;
_intVersion:= 1;
_intNeedLog:= -1;/* check to see if the record exist */
SELECT COUNT(*) INTO VNUM FROM tblAgencyInfo1 WHERE AGENTID=decAGENTID;
IF VNUM>0 THEN
RETURN -1;
END IF;/* insert the record */
INSERT INTO tblAgencyInfo1
(AGENTID, ORDERVALUE, ZIPCODE, PROVINCE, ADDRESS, AGENTCODE, FAX, EMAIL, REMARK, OPERATION, TELEPHONE, TIMECREATED, AGENTNAME, CITY, LINKTELEPHONE, LINKMAN, TIMELASTMODIFIED)
VALUES
(decAGENTID, strORDERVALUE, strZIPCODE, strPROVINCE, strADDRESS, strAGENTCODE, strFAX, strEMAIL, strREMARK, strOPERATION, strTELEPHONE, datTIMECREATED, strAGENTNAME, strCITY, strLINKTELEPHONE, strLINKMAN, datTIMELASTMODIFIED);
COMMIT;
RETURN 0;
exception
--_intError//自己记录 或者可以存 exception
-- dbms_output.put_line(exception);
--when others then
/* Log down the error*/
EXEC Ts_Sys_ErrorLog(_strSprocName,_intError);
RETURN NULL;
END;
, @intError INT
, @strErrMsg NVARCHAR(100)
, @intReturnValue INT
, @strCurSproc NVARCHAR(50)SET NOCOUNT ON
SET @intError = 0
SET @strErrMsg = ''
SET @strCurSproc = ''SET @strCurSproc='Ts_tblAgencyInfo1_Add'EXEC @intReturnValue = Ts_tblAgencyInfo1_Add @decAGENTID=1.00000, @strORDERVALUE=N'', @strZIPCODE=N'f', @strPROVINCE=N'adsf', @strADDRESS=N'adsf', @strAGENTCODE=N'1', @strFAX=N'asdf', @strEMAIL=N'as', @strREMARK=N'asdf', @strOPERATION=N'', @strTELEPHONE=N'adf', @datTIMECREATED='20080403 17:22:41.265', @strAGENTNAME=N'df', @strCITY=N'asdf', @strLINKTELEPHONE=N'adf', @strLINKMAN=N'asdf', @datTIMELASTMODIFIED='20080403 17:22:41.265'SET @intError = @@ERROR
IF @intError <> 0 OR @intReturnValue <> 0 BEGIN
GOTO ExitHandler
ENDExitHandler:
IF @intError <> 0 BEGIN
SET @strErrMsg='#TSQL' + '#' + CAST(@intError AS NVARCHAR(100)) + '#' + @strCurSproc + '#'
RAISERROR(@strErrMsg, 16,1)
END
ELSE IF @intReturnValue <> 0 BEGIN
SET @strErrMsg='#SPROC' + '#' + CAST(@intReturnValue AS NVARCHAR(100)) + '#' + @strCurSproc + '#'
RAISERROR(@strErrMsg, 16,1)
END
DECLARE
strSource varchar2(100);
intError number;
strErrMsg varchar2(100);
intReturnValue number;
strCurSproc varchar2(50);begin
intError := 0 ;
strErrMsg := '' ;
strCurSproc := '' ; strCurSproc:='Ts_tblAgencyInfo1_Add'; --如果你定义是procedure可用
execute immediate Ts_tblAgencyInfo1_Add(...);
--如果定义成function
select Ts_tblAgencyInfo1_Add(decAGENTID->1.00000,strORDERVALUE->'', strZIPCODE->'f', strPROVINCE->'adsf', strADDRESS->'adsf',strAGENTCODE->'1',strFAX->'asdf',strEMAIL->'as',strREMARK->'asdf',strOPERATION->'', strTELEPHONE->'adf',datTIMECREATED->to_date('2008-040-3 17:22:41','yyyy-mm-dd hh24:mi:ss'),strAGENTNAME->'df', strCITY->'asdf', strLINKTELEPHONE->'adf',strLINKMAN->'asdf',datTIMELASTMODIFIED->to_date('2008-04-03 17:22:41','yyyy-mm-dd hh24:mi:ss') into intError from dual;
--或者不用符号-〉 直接传变量 一开始定义一个 aTab tblAgencyInfo1%rowtype 然后取值 aTab.AGENTID:=1;
--在直接传变量 aTab.AGENTID给 Ts_tblAgencyInfo1_Add即可end;
datTIMECREATED->to_date('2008-04-03 17:22:41','yyyy-mm-dd hh24:mi:ss')