max_price.sp.unix

来自「jdbc 的实例源码」· UNIX 代码 · 共 47 行

DROP TABLE food
GO

CREATE TABLE food
    (product CHAR(25),
     price   MONEY)
GO
INSERT INTO food
    VALUES("Carrot",        $35.00)
INSERT INTO food
    VALUES("Cheeseburger",  $50.00)
INSERT INTO food
    VALUES("Hamburger",     $75.00)
INSERT INTO food
    VALUES("Pork Chop",     $175.00)
INSERT INTO food
    VALUES("String Beans",  $7.50)
INSERT INTO food
    VALUES("Summer Squash", $11.50)
INSERT INTO food
    VALUES("Sweet Potato",  $13.50)
GO

DROP PROCEDURE max_price
GO

CREATE PROCEDURE max_price
  @pattern VARCHAR(50),
  @retval  VARCHAR(10) OUTPUT
AS
BEGIN

  DECLARE @max_price money 

  DECLARE @like_pattern VARCHAR(51)
  SELECT @like_pattern = RTRIM(@pattern) + '%'

  SELECT @max_price=MAX(price)
     FROM food
     WHERE product LIKE @like_pattern

  SELECT @retval = CONVERT(VARCHAR(10), @max_price)

END
GO