用spt更新ORACLE

Using Oracle SPs to Insert and Update Data from VFP
    Summary
    This demo code will connect to Oracle [using your ID and password] to create a table of Presidents, a trigger, a sequence and
    some stored procedures. The stored procedures can be called to insert and update data in the Presidents table. Three of the
    methods in the VFP class will pass data values to the Oracle procedures which will either be inserted into the table or
    update existing records in the table. The update SPs will also return a value equal to the number of records updated.
    Description
    The following demo code can be pasted into a Visual FoxPro PRG and run without modifications.
    *!* To use this code, you must be able to connect to Oracle and
    *!* have been granted the following Oracle privileges for the
    *!* UserID you connected with:
    *!*
    *!* DROP ANY SEQUENCE
    *!* DROP TABLE
    *!* CREATE TABLE
    *!* CREATE SEQUENCE
    *!* CREATE PROCEDURE
    *!* CREATE TRIGGER
    *!*
    *!* The UpdateMultiple and UpdateByKeyID methods show you how to
    *!* get a Stored Procedure to stuff a value inta a VFP variable
    *!* [i.e., passing a value by reference between an Oracle SP and VFP].
    *!*
    *!* The Stored Procedures created in this demo do not have a COMMIT; line
    *!* after the INSERT or UPDATE SQL. When the connection is terminated,
    *!* Oracle will perform an implicit COMMIT. The Destroy method below
    *!* issues a SQLCOMMIT() if the lCommitOnDestroy property is TRUE.
    *!*
    CLEAR
    LOCAL oDemo
    oDemo = CREATEOBJECT(’OracleDemo’)
    IF TYPE(’oDemo’) <> "O"
    ?
    ? ’No Connection. An Invalid UserID, Password and/or Server parameter was specified.’
    RETURN
    ENDIF
    IF NOT oDemo.Cr8_Presidents()
    oDemo.Release()
    ?
    ? ’Cr8_Presidents method failed.’
    RETURN
    ENDIF
    IF NOT oDemo.Cr8_SProcs()
    oDemo.Release()
    ?
    ? ’Cr8_SProcs method failed.’
    RETURN
    ENDIF
    IF NOT oDemo.InsertData()
    oDemo.Release()
    ?
    ? ’InsertData method failed.’
    RETURN
    ENDIF
    ?
    ? oDemo.UpdateMultiple()
    ?
    ? [The Updated_By column was changed from "InsTest" to "Test1"]
    ? [for Presidents with a LastName LIKE "Adams%"]
    ?
    ? [-----------------------------------------------------------]
    ?
    ? oDemo.UpdateByKeyID()
    ?
    ? [For the record with KeyID = 40:]
    ? [The FirstName column was changed from "George" to "GeorgE"]
    ? [The LastName column was changed from "Bush" to "Bush1"]
    ? [The Update_By column was changed from "InsTest" to "Test2"]
    ?
    oDemo.Release()
    RETURN
    DEFINE CLASS OracleDemo AS Custom
    nHandle = 0
    cUserID = []
    lCommitOnDestroy = .T.
    PROTECTED PROCEDURE Init
    LOCAL lcConString, lnOldValue
    lnOldValue = SQLGETPROP(0, "DispLogin")
    SQLSETPROP(0, "DispLogin", 2)
    lcConString = [Driver=Microsoft ODBC for Oracle;UID=;PWD=;Server=;]
    THIS.nHandle = SQLSTRINGCONNECT(lcConString)
    SQLSETPROP(0, "DispLogin", lnOldValue)
    IF THIS.nHandle > 0
    THIS.GetUserID()
    ENDIF
    RETURN THIS.nHandle > 0
    ENDPROC
    PROCEDURE Release
    RELEASE THIS
    ENDPROC
    PROTECTED PROCEDURE Destroy
    IF THIS.nHandle > 0
    IF THIS.lCommitOnDestroy
    SQLCOMMIT(THIS.nHandle)
    ENDIF
    SQLDISCONNECT(THIS.nHandle)
    ENDIF
    ENDPROC
    PROTECTED PROCEDURE GetUserID
    SQLEXEC(THIS.nHandle, [select USER UserID from dual], [crsUser])
    THIS.cUserID = ALLTRIM(crsUser.UserID)
    USE IN crsUser
    ENDPROC
    PROCEDURE UpdateMultiple
    LOCAL lcSQL, lnRetVal, laError(1)
    PRIVATE pcNameMask pcUpdated_By, pnRowCount
    pcNameMask = [Adams%]
    pcUpdated_By = [TEST1]
    pnRowCount = 0
    TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2
    begin <>.PrezUpdateAll
    (nRowCount=>?@pnRowCount,
    cNameMask=>?pcNameMask,
    cUpdated_By=>?pcUpdated_By); end;
    ENDTEXT
    lcSQL = STRTRAN(lcSQL, CHR(13) + CHR(10), [ ])
    IF THIS.RunSQL(lcSQL)
    RETURN [UpdateMultiple RowCount: ] + TRANSFORM(pnRowCount)
    ELSE
    RETURN []
    ENDIF
    ENDPROC
    PROCEDURE UpdateByKeyID
    LOCAL lcSQL, lnRetVal, laError(1)
    PRIVATE pnKeyID, pcLoginID, pcFirstName, pcLastName, pcUpdated_By, pnRowCount
    pnKeyID = 40
    pcLoginID = [GBUSH]
    pcFirstName = [GeorgE]
    pcLastName = [Bush1]
    pcUpdated_By = [Test2]
    pnRowCount = 0
    TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2
    begin <>.PrezUpdate
    (nRowCount=>?@pnRowCount, nKeyID=>?pnKeyID, cLoginID=>?pcLoginID,
    cFirstName=>?pcFirstName, cLastName=>?pcLastName,
    cUpdated_By=>?pcUpdated_By); end;
    ENDTEXT
    lcSQL = STRTRAN(lcSQL, CHR(13) + CHR(10), [ ])
    IF THIS.RunSQL(lcSQL)
    RETURN [UpdateByKeyID RowCount: ] + TRANSFORM(pnRowCount)
    ELSE
    RETURN []
    ENDIF
    ENDPROC
    PROCEDURE Cr8_Presidents
    LOCAL lcScript, lnRetVal
    TEXT TO lcScript TEXTMERGE NOSHOW PRETEXT 2
    drop sequence S_Presidents
    ENDTEXT
    IF NOT THIS.RunSQL(lcScript)
    RETURN .f.
    ENDIF