¶

--
--  tpcc.sql
--
--  Implementation of the TPC C Benchmark transactions as stored procedures.
--
--  These are for use with the tpcc test driver (tpcc) in the Virtuoso sample
--  directory.  See TPCC.DOC in the virtuoso documentation bundle for comments
--  and instructions.
--
--  Copyright (C) 1998-2024 OpenLink Software.
--  All Rights Reserved.
--
--  The copyright above and this notice must be preserved in all
--  copies of this source code.  The copyright above does not
--  evidence any actual or intended publication of this source code.
--
--  This is unpublished proprietary trade secret of OpenLink Software.
--  This source code may not be copied, disclosed, distributed, demonstrated
--  or licensed except as authorized by OpenLink Software.
--

--  slevel - The transaction procedure for the Stock Level transaction.
--
--  This is executed as an autocommitting history read transaction. The number
--  of STOCK rows where quantity is below th threshold.  The rows are taken
--  from the last 20 orders on a warehouse / district combination.

CREATE PROCEDURE SLEVEL (
    IN W_ID		INTEGER,
    IN D_ID		INTEGER,
    IN THRESHOLD	INTEGER)
{
    DECLARE LAST_O, N_ITEMS INTEGER;

    SELECT D_NEXT_O_ID INTO LAST_O
    FROM DISTRICT
    WHERE
	    D_W_ID = W_ID
	AND D_ID = D_ID;

    SELECT COUNT (DISTINCT S_I_ID)
    INTO N_ITEMS
    FROM ORDER_LINE, STOCK
    WHERE
	    OL_W_ID = W_ID
	AND OL_D_ID = D_ID
	AND OL_O_ID < LAST_O
	AND OL_O_ID >= LAST_O - 20
	AND S_W_ID = W_ID
	AND S_I_ID = OL_I_ID AND S_QUANTITY < THRESHOLD;

    RESULT_NAMES (N_ITEMS);

    RESULT (N_ITEMS);
}

--
-- c_by_name, call_c_by_name
-- Examples on retrieving CUSTOMER by last name.
-- Functionality open coded in actual transaction procedures.
--

CREATE PROCEDURE C_BY_NAME (
    IN  W_ID 		INTEGER,
    IN  D_ID 		INTEGER,
    IN  NAME 		VARCHAR,
    OUT ID 		INTEGER)
{
    DECLARE N, C_COUNT INTEGER;

    DECLARE C_CUR CURSOR FOR
    SELECT C_ID
    FROM CUSTOMER
    WHERE
	    C_W_ID = W_ID
	AND C_D_ID = D_ID
	AND C_LAST = NAME
    ORDER BY C_W_ID, C_D_ID, C_LAST, C_FIRST;

    SELECT COUNT (*) INTO C_COUNT
    FROM CUSTOMER
    WHERE
	    C_W_ID = W_ID
	AND C_D_ID = D_ID
	AND C_LAST = NAME;

    N := 0;
    OPEN C_CUR;
    WHENEVER NOT FOUND GOTO NOTFOUND;

    WHILE (N <= C_COUNT / 2) {
	FETCH C_CUR INTO ID;
	N := N + 1;
    }
    RETURN;

NOTFOUND:
    SIGNAL ('CNF', 'CUSTOMER NOT FOUND BY NAME');
    RETURN;
}

CREATE PROCEDURE CALL_C_BY_NAME (
    IN W_ID		INTEGER,
    IN D_ID 		INTEGER,
    IN C_LAST		VARCHAR)
{
    DECLARE C_ID INTEGER;

    C_BY_NAME (W_ID, D_ID, C_LAST, C_ID);
}

--
-- payment
-- This procedure implements the Payment transaction.
--

CREATE PROCEDURE BC_C_DATA (
    INOUT C_NEW		VARCHAR,
    INOUT C_DATA 	VARCHAR)
{
    RETURN (
	CONCATENATE (C_NEW, SUBSEQ (C_DATA, LENGTH (C_NEW), LENGTH (C_DATA))));
}

CREATE PROCEDURE PAYMENT (
    IN _W_ID		INTEGER,
    IN _C_W_ID		INTEGER,
    IN H_AMOUNT		FLOAT,
    IN _D_ID		INTEGER,
    IN _C_D_ID		INTEGER,
    IN _C_ID		INTEGER,
    IN _C_LAST		VARCHAR)
{
    DECLARE
	_C_DATA, _C_FIRST, _C_MIDDLE, _C_STREET_1,  _C_STREET_2, _C_CITY,
	_C_STATE, _C_ZIP, _C_PHONE, _C_CREDIT, _C_CREDIT_LIM, _C_DISCOUNT,
	_C_BALANCE, _C_SINCE, _C_DATA_1, _C_DATA_2 VARCHAR;

    DECLARE
	N, _W_YTD, _D_YTD, _C_CNT_PAYMENT INTEGER;

    DECLARE
	_D_STREET_1, _D_STREET_2, _D_CITY, _D_STATE, _D_ZIP, _D_NAME VARCHAR;

    DECLARE
	_W_STREET_1, _W_STREET_2, _W_CITY, _W_STATE, _W_ZIP, _W_NAME,
	SCREEN_DATA VARCHAR;

    IF (_C_ID = 0) {
	DECLARE NAMECNT INTEGER;
	WHENEVER NOT FOUND GOTO NO_CUSTOMER;

	SELECT COUNT(C_ID) INTO NAMECNT
	FROM CUSTOMER
	WHERE
		C_LAST = _C_LAST
	    AND C_D_ID = _D_ID
	    AND C_W_ID = _W_ID;

	DECLARE C_BYNAME CURSOR FOR
	SELECT C_ID
	FROM CUSTOMER
	WHERE
		C_W_ID = _C_W_ID
	    AND C_D_ID = _C_D_ID
	    AND C_LAST = _C_LAST
	ORDER BY
	    C_W_ID, C_D_ID, C_LAST, C_FIRST;

	OPEN C_BYNAME (EXCLUSIVE);

	N := 0;
	WHILE (N <= NAMECNT / 2) {
	    FETCH C_BYNAME   INTO _C_ID;
	    N := N + 1;
	}

	CLOSE C_BYNAME;
    }

    DECLARE C_CR CURSOR FOR
    SELECT
	C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2, C_CITY,
	C_STATE, C_ZIP, C_PHONE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT,
	C_BALANCE, C_SINCE, C_DATA_1, C_DATA_2, C_CNT_PAYMENT
    FROM
	CUSTOMER
    WHERE
	    C_W_ID = _C_W_ID
	AND C_D_ID = _C_D_ID
	AND C_ID = _C_ID;

    OPEN C_CR (EXCLUSIVE);

    FETCH C_CR INTO
	_C_FIRST, _C_MIDDLE, _C_LAST,
	_C_STREET_1, _C_STREET_2, _C_CITY, _C_STATE, _C_ZIP,
	_C_PHONE, _C_CREDIT, _C_CREDIT_LIM,
	_C_DISCOUNT, _C_BALANCE, _C_SINCE, _C_DATA_1, _C_DATA_2,
	_C_CNT_PAYMENT;

    _C_BALANCE := _C_BALANCE + H_AMOUNT;

    IF (_C_CREDIT = 'BC') {
	UPDATE CUSTOMER
	SET C_BALANCE = _C_BALANCE,
	    C_DATA_1 = BC_C_DATA (
		SPRINTF ('%5d%5d%5d%5d%5d%9f',
		    _C_ID, _C_D_ID, _C_W_ID, _D_ID, _W_ID, H_AMOUNT),
		_C_DATA_1),
	    C_CNT_PAYMENT = _C_CNT_PAYMENT + 1
	WHERE CURRENT OF C_CR;
	SCREEN_DATA := SUBSEQ (_C_DATA_1, 1, 200);
    } ELSE {
	UPDATE CUSTOMER
	SET C_BALANCE = _C_BALANCE, C_CNT_PAYMENT = _C_CNT_PAYMENT + 1
	WHERE CURRENT OF C_CR;
	SCREEN_DATA := ' ';
    }

    DECLARE D_CUR CURSOR FOR
    SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME, D_YTD
    FROM DISTRICT
    WHERE
	    D_W_ID = _W_ID
	AND D_ID = _D_ID;

    OPEN D_CUR (EXCLUSIVE);

    FETCH D_CUR INTO
	_D_STREET_1,  _D_STREET_2, _D_CITY, _D_STATE, _D_ZIP,  _D_NAME, _D_YTD;

    UPDATE DISTRICT SET D_YTD = _D_YTD + H_AMOUNT WHERE CURRENT OF D_CUR;

    CLOSE D_CUR;

    DECLARE W_CUR CURSOR FOR
    SELECT  W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME, W_YTD
    FROM WAREHOUSE
    WHERE W_ID = _W_ID;

    OPEN W_CUR (EXCLUSIVE);
    FETCH  W_CUR INTO
	_W_STREET_1, _W_STREET_2, _W_CITY, _W_STATE, _W_ZIP, _W_NAME, _W_YTD;

    UPDATE WAREHOUSE SET W_YTD = _W_YTD + H_AMOUNT;

    DECLARE H_DATA VARCHAR;
    H_DATA := _W_NAME;

    INSERT INTO HISTORY
	(H_C_D_ID, H_C_W_ID, H_C_ID, H_D_ID, H_W_ID, H_DATE, H_AMOUNT, H_DATA)
    VALUES
	(_C_D_ID, _C_W_ID, _C_ID, _D_ID, _W_ID, NOW (), H_AMOUNT, H_DATA);

    RESULT (
	_C_ID, _C_LAST, NOW (), _W_STREET_1, _W_STREET_2, _W_CITY, _W_STATE,
	_W_ZIP, _D_STREET_1, _D_STREET_2, _D_CITY, _D_STATE, _D_ZIP,
	_C_FIRST, _C_MIDDLE, _C_STREET_1, _C_STREET_2, _C_CITY, _C_STATE,
	_C_ZIP, _C_PHONE, _C_SINCE, _C_CREDIT, _C_CREDIT_LIM, _C_DISCOUNT,
	_C_BALANCE, SCREEN_DATA);

    RETURN;

NO_CUSTOMER:
    SIGNAL ('NOCUS', 'NO CUSTOMER IN PAYMENT.');
}

-- ol_stock - Part of the New Order transaction - Set the stock level for
-- an order line.  Compute the price and return it in amount.
--
-- Note - Open the cursor on STOCK as exclusive to avoid deadlocks.
-- Use positioned update on STOCK for speed.
--
-- Fetch the s_dist_01 - 10 columns from STOCK even though they are not used.
-- The test specification requires this. The operation is measurably faster if these
-- are omitted.-- The ORDER LINE is inserted later for better lock concurrency.

CREATE PROCEDURE OL_STOCK (
    IN    _W_ID			INTEGER,
    IN    D_ID			INTEGER,
    INOUT _OL_I_ID		INTEGER,
    IN    _OL_SUPPLY_W_ID	INTEGER,
    IN    QTY			INTEGER,
    OUT   AMOUNT		FLOAT,
    INOUT S_DIST_01		VARCHAR,
    INOUT S_DIST_02		VARCHAR,
    INOUT S_DIST_03		VARCHAR,
    INOUT S_DIST_04		VARCHAR,
    INOUT S_DIST_05		VARCHAR,
    INOUT S_DIST_06		VARCHAR,
    INOUT S_DIST_07		VARCHAR,
    INOUT S_DIST_08		VARCHAR,
    INOUT S_DIST_09		VARCHAR,
    INOUT S_DIST_10		VARCHAR,
    INOUT DIST_INFO		VARCHAR)
{
    IF (_OL_I_ID = 0) RETURN;

    DECLARE _S_DATA VARCHAR;
    DECLARE _S_QUANTITY, _S_CNT_ORDER, _S_CNT_REMOTE INTEGER;

    WHENEVER NOT FOUND GOTO NO_ITEM;

    DECLARE _I_NAME VARCHAR;

    SELECT
	I_PRICE, I_NAME
    INTO
	AMOUNT, _I_NAME
    FROM
	ITEM
    WHERE I_ID = _OL_I_ID;

    DECLARE S_CUR CURSOR FOR
    SELECT
	S_QUANTITY, S_DATA, S_CNT_ORDER, S_CNT_REMOTE,
	S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
	S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10
    FROM STOCK
    WHERE
	    S_I_ID = _OL_I_ID
	AND S_W_ID = _OL_SUPPLY_W_ID;

    WHENEVER NOT FOUND GOTO NO_STOCK;

    OPEN S_CUR (EXCLUSIVE);

    FETCH S_CUR INTO
	_S_QUANTITY, _S_DATA, _S_CNT_ORDER, _S_CNT_REMOTE,
	S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
	S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10;

    IF (_S_QUANTITY < QTY)
	_S_QUANTITY := _S_QUANTITY - QTY + 91;
    ELSE
	_S_QUANTITY := _S_QUANTITY - QTY;

    IF (_W_ID <> _OL_SUPPLY_W_ID)
	_S_CNT_REMOTE := _S_CNT_REMOTE + 1;

    UPDATE STOCK
    SET
	S_QUANTITY = _S_QUANTITY,
	S_CNT_ORDER = _S_CNT_ORDER + 1,
	S_CNT_REMOTE = _S_CNT_REMOTE
    WHERE CURRENT OF S_CUR;

    IF (D_ID = 1)       DIST_INFO := S_DIST_01;
    ELSE IF (D_ID = 2)  DIST_INFO := S_DIST_02;
    ELSE IF (D_ID = 3)  DIST_INFO := S_DIST_03;
    ELSE IF (D_ID = 4)  DIST_INFO := S_DIST_04;
    ELSE IF (D_ID = 5)  DIST_INFO := S_DIST_05;
    ELSE IF (D_ID = 6)  DIST_INFO := S_DIST_06;
    ELSE IF (D_ID = 7)  DIST_INFO := S_DIST_07;
    ELSE IF (D_ID = 8)  DIST_INFO := S_DIST_08;
    ELSE IF (D_ID = 9)  DIST_INFO := S_DIST_09;
    ELSE IF (D_ID = 10) DIST_INFO := S_DIST_10;

    RESULT (_I_NAME, _S_QUANTITY, 'G', AMOUNT, AMOUNT * QTY);

    AMOUNT := QTY * AMOUNT;

    RETURN;

NO_STOCK:
    SIGNAL ('NOSTK', 'NO STOCK ROW FOUND.');
NO_ITEM:
    SIGNAL ('NOITM', 'NO ITEM ROW FOUND.');
}

--
-- ol_insert - Part of New Order transaction. Insert an ORDER LINE.
--
-- Note use of inout parameters even though they are not modified here.
-- This saves copying the values.

CREATE PROCEDURE OL_INSERT (
    INOUT W_ID			INTEGER,
    INOUT D_ID			INTEGER,
    INOUT O_ID			INTEGER,
    IN    OL_NUMBER		INTEGER,
    INOUT OL_I_ID		INTEGER,
    INOUT OL_QTY		INTEGER,
    INOUT OL_AMOUNT		FLOAT,
    INOUT OL_SUPPLY_W_ID	INTEGER,
    INOUT OL_DIST_INFO		VARCHAR,
    INOUT TAX_AND_DISCOUNT	FLOAT)
{
    IF (OL_I_ID = -1) RETURN;

    OL_AMOUNT := OL_AMOUNT * TAX_AND_DISCOUNT;

    INSERT INTO ORDER_LINE (
	OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID,
	OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO)
    VALUES (
	O_ID, D_ID, W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID,
	OL_QTY, OL_AMOUNT, OL_DIST_INFO);
}

--
-- cust_info - part of New Order transaction. Return customer info.
--
--

CREATE PROCEDURE CUST_INFO (
    IN    W_ID INTEGER,
    IN    D_ID INTEGER,
    INOUT _C_ID INTEGER,
    INOUT _C_LAST VARCHAR,
    OUT    _C_DISCOUNT FLOAT,
    OUT    _C_CREDIT VARCHAR)
{
    WHENEVER NOT FOUND GOTO ERR;

    SELECT
	C_LAST, C_DISCOUNT, C_CREDIT INTO _C_LAST, _C_DISCOUNT, _C_CREDIT
    FROM
	CUSTOMER
    WHERE
	    C_W_ID = W_ID
	AND C_D_ID = D_ID
	AND C_ID = _C_ID;
    RETURN;
ERR:
    SIGNAL ('NOCUS', 'NO CUSTOMER');
}

-- new_order - Top level procedure of New Order transaction.
-- Take a fixed 10 order lines as individually named parameters
-- to stay easily portable.

CREATE PROCEDURE NEW_ORDER (
    IN _W_ID 		INTEGER,
    IN _D_ID 		INTEGER,
    IN _C_ID 		INTEGER,
    IN O_OL_CNT 	INTEGER,
    IN O_ALL_LOCAL 	INTEGER,
    IN I_ID_1 		INTEGER,
    IN S_W_ID_1 	INTEGER,
    IN QTY_1 		INTEGER,
    IN I_ID_2 		INTEGER,
    IN S_W_ID_2 	INTEGER,
    IN QTY_2 		INTEGER,
    IN I_ID_3 		INTEGER,
    IN S_W_ID_3 	INTEGER,
    IN QTY_3 		INTEGER,
    IN I_ID_4 		INTEGER,
    IN S_W_ID_4 	INTEGER,
    IN QTY_4 		INTEGER,
    IN I_ID_5 		INTEGER,
    IN S_W_ID_5 	INTEGER,
    IN QTY_5 		INTEGER,
    IN I_ID_6 		INTEGER,
    IN S_W_ID_6 	INTEGER,
    IN QTY_6 		INTEGER,
    IN I_ID_7 		INTEGER,
    IN S_W_ID_7 	INTEGER,
    IN QTY_7 		INTEGER,
    IN I_ID_8 		INTEGER,
    IN S_W_ID_8 	INTEGER,
    IN QTY_8 		INTEGER,
    IN I_ID_9 		INTEGER,
    IN S_W_ID_9 	INTEGER,
    IN QTY_9 		INTEGER,
    IN I_ID_10 		INTEGER,
    IN S_W_ID_10 	INTEGER,
    IN QTY_10 		INTEGER)
{
    DECLARE
	OL_A_1, OL_A_2, OL_A_3, OL_A_4, OL_A_5,
	OL_A_6, OL_A_7, OL_A_8, OL_A_9, OL_A_10 INTEGER;
    DECLARE _C_DISCOUNT, _D_TAX, _W_TAX, TAX_AND_DISCOUNT FLOAT;
    DECLARE DATETIME DATE;
    DECLARE  _C_LAST, _C_CREDIT VARCHAR;
    DECLARE _O_ID INTEGER;

    DECLARE
	I_NAME, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05,
	S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 VARCHAR;
    DECLARE
	DISTI_1, DISTI_2, DISTI_3, DISTI_4, DISTI_5,
	DISTI_6, DISTI_7, DISTI_8, DISTI_9, DISTI_10 VARCHAR;

    DATETIME := NOW ();

    -- DECLARE RESULT ROW FROM OL_STOCK. OPTIONAL.;
    RESULT_NAMES (I_NAME, QTY_1, DISTI_1, OL_A_1, OL_A_2);

    OL_STOCK (
	_W_ID, _D_ID, I_ID_1, S_W_ID_1, QTY_1, OL_A_1, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_1);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_2, S_W_ID_2, QTY_2, OL_A_2, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_2);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_3, S_W_ID_3, QTY_3, OL_A_3, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_3);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_4, S_W_ID_4, QTY_4, OL_A_4, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_4);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_5, S_W_ID_5, QTY_5, OL_A_5, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_5);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_6, S_W_ID_6, QTY_6, OL_A_6, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_6);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_7, S_W_ID_7, QTY_7, OL_A_7, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_7);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_8, S_W_ID_8, QTY_8, OL_A_8, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_8);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_9, S_W_ID_9, QTY_8, OL_A_9, S_DIST_01, S_DIST_02,
	S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08,
	S_DIST_09, S_DIST_10, DISTI_9);
    OL_STOCK (
	_W_ID, _D_ID, I_ID_10, S_W_ID_10, QTY_10, OL_A_10, S_DIST_01,
	S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07,
	S_DIST_08, S_DIST_09, S_DIST_10, DISTI_10);

    CUST_INFO (_W_ID, _D_ID, _C_ID, _C_LAST, _C_DISCOUNT, _C_CREDIT);

    DECLARE  D_CUR CURSOR FOR
    SELECT
	D_TAX, D_NEXT_O_ID
    FROM
	DISTRICT
    WHERE
	    D_W_ID = _W_ID
	AND D_ID = _D_ID;

    WHENEVER NOT FOUND GOTO NOWARE;

    OPEN D_CUR (EXCLUSIVE);
    FETCH D_CUR INTO _D_TAX, _O_ID;
    UPDATE DISTRICT SET D_NEXT_O_ID = _O_ID + 1 WHERE CURRENT OF D_CUR;
    CLOSE D_CUR;

    INSERT INTO ORDERS
	(O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL)
    VALUES (_O_ID, _D_ID, _W_ID, _C_ID, DATETIME, O_OL_CNT, O_ALL_LOCAL);

    INSERT INTO NEW_ORDER
	(NO_O_ID, NO_D_ID, NO_W_ID)
    VALUES (_O_ID, _D_ID, _W_ID);

    SELECT W_TAX INTO _W_TAX FROM WAREHOUSE WHERE W_ID = _W_ID;

    TAX_AND_DISCOUNT := (1 + _D_TAX + _W_TAX) * (1 - _C_DISCOUNT);

    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 1, I_ID_1, QTY_1, OL_A_1,  S_W_ID_1, DISTI_1,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 2, I_ID_2, QTY_2, OL_A_2,  S_W_ID_2, DISTI_2,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 3, I_ID_3, QTY_3, OL_A_3,  S_W_ID_3, DISTI_3,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 4, I_ID_4, QTY_4, OL_A_4,  S_W_ID_4, DISTI_4,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 5, I_ID_5, QTY_5, OL_A_5,  S_W_ID_5, DISTI_5,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 6, I_ID_6, QTY_6, OL_A_6,  S_W_ID_6, DISTI_6,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 7, I_ID_7, QTY_7, OL_A_7,  S_W_ID_7, DISTI_7,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 8, I_ID_6, QTY_8, OL_A_8,  S_W_ID_8, DISTI_8,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 9, I_ID_9, QTY_9, OL_A_9,  S_W_ID_9, DISTI_9,
	TAX_AND_DISCOUNT);
    OL_INSERT (
	_W_ID, _D_ID, _O_ID, 10, I_ID_10, QTY_10, OL_A_10,  S_W_ID_10, DISTI_10,
	TAX_AND_DISCOUNT);

    END_RESULT ();
    RESULT (_W_TAX, _D_TAX, _O_ID, _C_LAST, _C_DISCOUNT, _C_CREDIT);
    RETURN;

NOWARE:
    SIGNAL ('NOWRE', 'WAREHOUSE OR DISTRICTNOT FOUND.');
}

-- delivery_1 - Top level procedure for the Delivery transaction
--
-- This is called 10 times by the client in each delivery transaction.
-- The rules allow Delivery to be implemented as up to 10 separately committed
-- transactions. This is done to minimize lock duration.

CREATE PROCEDURE DELIVERY_1 (
    IN W_ID		INTEGER,
    IN CARRIER_ID 	INTEGER,
    IN D_ID 		INTEGER)
{
    DECLARE NO_CUR CURSOR FOR
    SELECT
	NO_O_ID
    FROM
	NEW_ORDER
    WHERE
	    NO_W_ID = W_ID
	AND NO_D_ID = D_ID;

    DECLARE DATETIME DATE;
    DECLARE _O_ID, _C_ID INTEGER;
    DECLARE OL_TOTAL FLOAT;
    DATETIME := NOW ();

    OPEN NO_CUR (EXCLUSIVE, PREFETCH 1);
    FETCH NO_CUR INTO _O_ID;
    DELETE FROM NEW_ORDER WHERE CURRENT OF NO_CUR;
    CLOSE NO_CUR;

    DECLARE O_CUR CURSOR FOR
    SELECT
	O_C_ID
    FROM
	ORDERS
    WHERE
	    O_W_ID = W_ID
	AND O_D_ID = D_ID
    AND O_ID = _O_ID;

    OPEN O_CUR (EXCLUSIVE);
    FETCH O_CUR INTO _C_ID;
    UPDATE ORDERS SET O_CARRIER_ID = CARRIER_ID WHERE CURRENT OF O_CUR;
    CLOSE O_CUR;

    DECLARE OL_CUR CURSOR FOR
    SELECT
	OL_AMOUNT
    FROM
	ORDER_LINE
    WHERE
	    OL_W_ID = W_ID
	AND OL_D_ID = D_ID
	AND OL_O_ID = _O_ID;

    WHENEVER NOT FOUND GOTO LINES_DONE;
    OL_TOTAL := 0.0;
    OPEN OL_CUR (EXCLUSIVE);
    WHILE (1) {
	DECLARE TMP INTEGER;
	FETCH OL_CUR INTO TMP;
	OL_TOTAL := OL_TOTAL + TMP;
	UPDATE ORDER_LINE SET OL_DELIVERY_D = DATETIME WHERE CURRENT OF OL_CUR;
    }
LINES_DONE:
    UPDATE CUSTOMER
    SET
	C_BALANCE = C_BALANCE + OL_TOTAL,
	C_CNT_DELIVERY = C_CNT_DELIVERY + 1
    WHERE
	    C_W_ID = W_ID
	AND C_D_ID = D_ID
	AND C_ID = _C_ID;

    RETURN _O_ID;
}

-- ostat - Top level procedure for the Order Status transaction.
--
--

CREATE PROCEDURE OSTAT (
    IN _W_ID		INTEGER,
    IN _D_ID		INTEGER,
    IN _C_ID		INTEGER,
    IN _C_LAST		VARCHAR)
{
    DECLARE _C_FIRST, _C_MIDDLE, _C_BALANCE VARCHAR;
    DECLARE
	_O_ID, _OL_I_ID, _OL_SUPPLY_W_ID, _OL_QUANTITY, _O_CARRIER_ID,
	N INTEGER;
    DECLARE _OL_AMOUNT FLOAT;
    DECLARE _OL_DELIVERY_D, _O_ENTRY_D VARCHAR;

    IF (_C_ID = 0) {
	DECLARE NAMECNT INTEGER;
	WHENEVER NOT FOUND GOTO NO_CUSTOMER;

	SELECT COUNT (*)
	INTO NAMECNT
	FROM CUSTOMER
	WHERE
	    	C_LAST = _C_LAST
	    AND C_D_ID = _D_ID
	    AND C_W_ID = _W_ID;

	DECLARE C_BYNAME CURSOR FOR
	SELECT
	    C_BALANCE, C_LAST, C_MIDDLE, C_ID
	FROM CUSTOMER
	WHERE
		C_W_ID = _W_ID
	    AND C_D_ID = _D_ID
	    AND C_LAST = _C_LAST
	ORDER BY
	    C_W_ID, C_D_ID, C_LAST, C_FIRST;

	OPEN C_BYNAME;

	N := 0;
	WHILE (N <= NAMECNT / 2) {
	    FETCH C_BYNAME INTO _C_BALANCE, _C_FIRST, _C_MIDDLE, _C_ID;
	    N := N + 1;
	}

	CLOSE C_BYNAME;
    }  ELSE {
	SELECT
	    C_BALANCE, C_FIRST, C_MIDDLE, C_LAST
	INTO
	    _C_BALANCE, _C_FIRST, _C_MIDDLE, _C_LAST
	FROM
	    CUSTOMER
	WHERE
		C_W_ID = _W_ID
	    AND C_D_ID = _D_ID
	    AND C_ID = _C_ID;
    }

    WHENEVER NOT FOUND GOTO NO_ORDER;
    SELECT
	O_ID, O_CARRIER_ID, O_ENTRY_D
    INTO
	_O_ID, _O_CARRIER_ID, _O_ENTRY_D
    FROM
	ORDERS
    WHERE
	    O_W_ID = _W_ID
	AND O_D_ID = _D_ID
	AND O_C_ID = _C_ID
    ORDER BY
	O_W_ID DESC, O_D_ID DESC, O_C_ID DESC, O_ID DESC;

    DECLARE O_LINE CURSOR FOR
    SELECT
	OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DELIVERY_D
    FROM
	ORDER_LINE
    WHERE
	    OL_W_ID = _W_ID
	AND OL_D_ID = _D_ID
	AND OL_O_ID = _O_ID;

    WHENEVER NOT FOUND GOTO LINES_DONE;

    OPEN O_LINE;
    RESULT_NAMES (
	_OL_SUPPLY_W_ID, _OL_I_ID, _OL_QUANTITY, _OL_AMOUNT, _OL_DELIVERY_D);
    WHILE (1 = 1) {
	FETCH O_LINE INTO
	    _OL_I_ID, _OL_SUPPLY_W_ID, _OL_QUANTITY, _OL_AMOUNT,
	    _OL_DELIVERY_D;

	RESULT (
	    _OL_SUPPLY_W_ID, _OL_I_ID, _OL_QUANTITY, _OL_AMOUNT,
	    _OL_DELIVERY_D);
    }

LINES_DONE:
    END_RESULT ();

    RESULT_NAMES  (
	_C_ID, _C_LAST, _C_FIRST, _C_MIDDLE, _O_ENTRY_D, _O_CARRIER_ID,
	_C_BALANCE, _O_ID);

    RESULT (
	_C_ID, _C_LAST, _C_FIRST, _C_MIDDLE, _O_ENTRY_D, _O_CARRIER_ID,
	_C_BALANCE, _O_ID);

    RETURN;

NO_CUSTOMER:
    SIGNAL ('NOCUS', 'NO CUSTOMER IN ORDER STATUS');

NO_ORDER:
    RETURN 0;
}