home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: 11 Util / 11-Util.zip / BENCHSQL.ZIP / TP1.SQL < prev    next >
Text File  |  1989-05-16  |  4KB  |  160 lines
  1. /* */
  2. /* This script creates the TP/1 environment */
  3. /* */
  4.  
  5. use bench
  6. go
  7.  
  8. /*****************************************************************/
  9. /*                   ACCOUNT TABLE                               */
  10. /*****************************************************************/
  11.  
  12. drop table account
  13. go
  14. create table account
  15. (
  16.  number          int,
  17.  name            char(20),
  18.  branch          int,
  19.  balance         int,
  20.  altnumber       varchar(12),
  21.  joinkey         int,
  22.  field7          char(12),
  23.  field8          char(10),
  24.  field9          char(10),
  25.  field10         char(10),
  26.  field11         char(10)
  27. )
  28. go
  29.  
  30. /*****************************************************************/
  31. /*                   BRANCH TABLE                                */
  32. /*****************************************************************/
  33.  
  34. drop table branch
  35. go
  36. create table branch
  37. (
  38.         number          int,
  39.         name            char(20),
  40.         branch          int,
  41.         balance         int,
  42.         altnumber       varchar(12),
  43.         joinkey         int,
  44.         field7          char(12),
  45.         field8          char(10),
  46.         field9          char(10),
  47.         field10         char(10),
  48.         field11         char(10)
  49. )
  50. go
  51.  
  52. /*****************************************************************/
  53. /*                   TELLER TABLE                                */
  54. /*****************************************************************/
  55.  
  56. drop table teller
  57. go
  58. create table teller
  59. (
  60.         number          int,
  61.         name            char(20),
  62.         branch          int,
  63.         balance         int,
  64.         altnumber       varchar(12),
  65.         joinkey         int,
  66.         field7          char(12),
  67.         field8          char(10),
  68.         field9          char(10),
  69.         field10         char(10),
  70.         field11         char(10)
  71. )
  72. go
  73.  
  74. /*****************************************************************/
  75. /*                   HISTORY TABLE                               */
  76. /*****************************************************************/
  77.  
  78. drop table history
  79. go
  80. create table history
  81. (
  82.         account         int,
  83.         teller          int,
  84.         branch          int,
  85.         balance         int,
  86.         delta           int,
  87.         pid             int,
  88.         transid         int,
  89.         other           char(22)
  90. )
  91. go
  92.  
  93. /*****************************************************************/
  94. /*                  TP1 STORED PROCEDURE                         */
  95. /*****************************************************************/
  96.  
  97. drop procedure tp1
  98. go
  99.  
  100. create procedure tp1 @acct int, @teller int, @branch int, @delta int,
  101. @transid int=0, @pid int=0, @inbuff char(100) as
  102.  
  103. declare @balance int
  104. declare @outbuff char(200)
  105.  
  106. select @outbuff = "12345678901234567890123456789012345678901234567890" 
  107.                 + "12345678901234567890123456789012345678901234567890" 
  108.                 + "12345678901234567890123456789012345678901234567890" 
  109.                 + "12345678901234567890123456789012345678901234567890" 
  110.  
  111. begin tran
  112.  
  113. /*fetch current balance for account*/
  114. select @balance = balance
  115. from account
  116. where number=@acct
  117.  
  118. /*Verify that the account exists */
  119. if @balance is NULL
  120. begin
  121.   declare @err varchar(80)
  122.   select @err =  "Account " + convert(varchar(8), @acct) + " does not exist"
  123.   raiserror 21000 @err
  124.   rollback transaction
  125.   return
  126. end
  127.  
  128. /* Check for sufficient funds */
  129. if @balance + @delta < 0
  130. begin
  131.   raiserror 21001 "Insufficient funds."
  132.   rollback transaction
  133.   return
  134. end
  135.  
  136. /*
  137. **update account and teller and transid
  138. */
  139.  
  140. update account
  141. set balance = balance + @delta
  142. where number = @acct
  143.  
  144. update teller
  145. set balance = balance + @delta
  146. where number = @teller
  147.  
  148. update branch
  149. set balance = balance + @delta
  150. where number = @branch
  151.  
  152. insert history
  153. values(@acct,@teller,@branch,@balance,@delta,@pid,@transid,"Hi Mom")
  154.  
  155. select @outbuff
  156.  
  157. commit transaction
  158. go
  159.  
  160.