<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="19. TPC C Benchmark Kit" />
<meta name="dc.subject" content="19. TPC C Benchmark Kit" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="tpcc.html" title="Chapter Contents" />
<link rel="prev" href="ddlstmt.html" title="DDL Statements" />
<link rel="next" href="contents.html" title="Contents" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>19. TPC C Benchmark Kit</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="storedprocs" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>19. TPC C Benchmark Kit</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="tpcc.html">Chapter Contents</a> | <a class="link" href="ddlstmt.html" title="DDL Statements">Prev</a> | <a class="link" href="xa.html" title="Using Virtuoso with Tuxedo">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="tpcc.html">TPC C Benchmark Kit</a>
</div>
<br />
<div>
<a href="tpcctestdb.html">Building the Test Database</a>
</div>
<div>
<a href="tpccusingtestprg.html">Using the Test Program</a>
</div>
<div>
<a href="tpcctuningparams4users.html">Tuning Parameters and Number of Users</a>
</div>
<div>
<a href="omissionsexcp.html">Omissions, Exceptions from the Definition</a>
</div>
<div>
<a href="sampleconf.html">Sample Configuration</a>
</div>
<div>
<a href="otherfactors.html">Other Factors</a>
</div>
<div>
<a href="tpccprocs.html">TPC C Procedures</a>
</div>
<div>
<a href="ddlstmt.html">DDL Statements</a>
</div>
<div class="selected">
<a href="storedprocs.html">Stored Procedures</a>
</div>
<br />
</div>
<div id="text">
<a name="storedprocs" />
<h2>19.9. Stored Procedures</h2>
<div>
<pre class="programlisting">
--
-- 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.
--
-- This file is part of the OpenLink Software Virtuoso Open-Source (VOS)
-- project.
--
-- Copyright (C) 1998-2010 OpenLink Software
--
-- This project is free software; you can redistribute it and/or modify it
-- under the terms of the GNU General Public License as published by the
-- Free Software Foundation; only version 2 of the License, dated June 1991.
--
-- This program is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along
-- with this program; if not, write to the Free Software Foundation, Inc.,
-- 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
-- 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;
}
</pre>
</div>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="ddlstmt.html" title="DDL Statements">Previous</a>
<br />DDL Statements</td>
<td align="center" width="34%">
<a href="tpcc.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="xa.html" title="Using Virtuoso with Tuxedo">Next</a>
<br />Contents of Using Virtuoso with Tuxedo</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
888
