OracleSP_PRODUCTS
create or replace FUNCTION Products_Add
(
v_ProductId OUT CHAR,
v_ProductCode IN NVARCHAR2 DEFAULT NULL ,
v_ProductName IN NVARCHAR2 DEFAULT NULL ,
v_ProductLine IN NVARCHAR2 DEFAULT NULL
)
RETURN NUMBER
AS
v_sys_error NUMBER := 0;
v_ErrorCode NUMBER(10,0);
v_TranStarted NUMBER(1,0);
BEGIN
v_ErrorCode := 0 ;
v_TranStarted := 0 ;
IF ( sqlserver_utilities.trancount = 0 ) THEN
BEGIN
--SQL Server BEGIN TRANSACTION;
sqlserver_utilities.incrementTrancount;
v_TranStarted := 1 ;
END;
ELSE
v_TranStarted := 0 ;
END IF;
BEGIN
v_ProductId := SYS_GUID() ;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
BEGIN
INSERT INTO Products
( ProductId, ProductCode, ProductName, ProductLine )
VALUES ( v_ProductId, v_ProductCode, v_ProductName, v_ProductLine );
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF ( v_sys_error <> 0 ) THEN
BEGIN
v_ErrorCode := -1 ;
GOTO Cleanup;
END;
END IF;
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
sqlserver_utilities.commit_transaction;
END;
END IF;
RETURN 0;
<<Cleanup>>
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
ROLLBACK;
sqlserver_utilities.resetTrancount;
END;
END IF;
RETURN v_ErrorCode;
END;
++++++++++++++++++++++
create or replace FUNCTION PRODUCTS_ADD1
( PRO_CODE IN VARCHAR2
, PRO_NAME IN VARCHAR2
, PRO_LINE IN VARCHAR2
) RETURN number AS
BEGIN
INSERT INTO Products
( ProductId, ProductCode, ProductName, ProductLine )
VALUES ( null, PRO_CODE, PRO_NAME, PRO_LINE );
RETURN 1;
END PRODUCTS_ADD1;
++++++++++++++++++++++
create or replace FUNCTION Products_Modify
(
v_ProductId IN CHAR DEFAULT NULL ,
v_ProductCode IN NVARCHAR2 DEFAULT NULL ,
v_ProductName IN NVARCHAR2 DEFAULT NULL ,
v_ProductLine IN NVARCHAR2 DEFAULT NULL
)
RETURN NUMBER
AS
v_sys_error NUMBER := 0;
v_ErrorCode NUMBER(10,0);
v_TranStarted NUMBER(1,0);
BEGIN
v_ErrorCode := 0 ;
v_TranStarted := 0 ;
IF ( sqlserver_utilities.trancount = 0 ) THEN
BEGIN
--SQL Server BEGIN TRANSACTION;
sqlserver_utilities.incrementTrancount;
v_TranStarted := 1 ;
END;
ELSE
v_TranStarted := 0 ;
END IF;
BEGIN
UPDATE Products
SET ProductCode = v_ProductCode,
ProductName = v_ProductName,
ProductLine = v_ProductLine
WHERE ProductId = v_ProductId;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF ( v_sys_error <> 0 ) THEN
BEGIN
v_ErrorCode := -1 ;
GOTO Cleanup;
END;
END IF;
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
sqlserver_utilities.commit_transaction;
END;
END IF;
RETURN 0;
<<Cleanup>>
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
ROLLBACK;
sqlserver_utilities.resetTrancount;
END;
END IF;
RETURN v_ErrorCode;
END;
+++++++++++++++++++++++++++
create or replace FUNCTION Products_Remove
(
v_ProductId IN CHAR DEFAULT NULL
)
RETURN NUMBER
AS
v_sys_error NUMBER := 0;
v_ErrorCode NUMBER(10,0);
v_TranStarted NUMBER(1,0);
BEGIN
v_ErrorCode := 0 ;
v_TranStarted := 0 ;
IF ( sqlserver_utilities.trancount = 0 ) THEN
BEGIN
--SQL Server BEGIN TRANSACTION;
sqlserver_utilities.incrementTrancount;
v_TranStarted := 1 ;
END;
ELSE
v_TranStarted := 0 ;
END IF;
BEGIN
DELETE CustOrderDetails
WHERE ProductId = v_ProductId;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
BEGIN
DELETE Products
WHERE ProductId = v_ProductId;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF ( v_sys_error <> 0 ) THEN
BEGIN
v_ErrorCode := -1 ;
GOTO Cleanup;
END;
END IF;
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
sqlserver_utilities.commit_transaction;
END;
END IF;
RETURN 0;
<<Cleanup>>
IF ( v_TranStarted = 1 ) THEN
BEGIN
v_TranStarted := 0 ;
ROLLBACK;
sqlserver_utilities.resetTrancount;
END;
END IF;
RETURN v_ErrorCode;
END;
Bạn đang đọc truyện trên: Truyen247.Pro