# 19. TPC C Benchmark Kit

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
TPC C Benchmark Kit
Building the Test Database
Using the Test Program
Tuning Parameters and Number of Users
Omissions, Exceptions from the Definition
Sample Configuration
Other Factors
TPC C Procedures
DDL Statements
Stored Procedures

## 19.9. Stored Procedures

    --
    --  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.
    --
    --  (C)Copyright 1998 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 - Pasrt 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;
    }

Previous DDL Statements | Chapter Contents | Next Contents of Using Virtuoso with Tuxedo