-----------------------------------------------------------------------------
-- (c) Copyright IBM Corp. 2007 All rights reserved.
-- 
-- The following sample of source code ("Sample") is owned by International 
-- Business Machines Corporation or one of its subsidiaries ("IBM") and is 
-- copyrighted and licensed, not sold. You may use, copy, modify, and 
-- distribute the Sample in any form without payment to IBM, for the purpose of 
-- assisting you in the development of your applications.
-- 
-- The Sample code is provided to you on an "AS IS" basis, without warranty of 
-- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
-- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
-- not allow for the exclusion or limitation of implied warranties, so the above 
-- limitations or exclusions may not apply to you. IBM shall not be liable for 
-- any damages you suffer as a result of using, copying, modifying or 
-- distributing the Sample, even if IBM has been advised of the possibility of 
-- such damages.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: stack_functions.db2 
--
-- DESCRIPTION: This is the set up script for the sample Array_Stack.java
--
-- SQL STATEMENTS USED:
--               SELECT
--               DROP
--               CALL
--               CREATE PROCEDURE
--
-----------------------------------------------------------------------------
--
-- For more information about the command line processor (CLP) scripts,
-- see the README file.
--
-- For information on using SQL statements, see the SQL Reference.
--
-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
-----------------------------------------------------------------------------

-- Connect to the database
CONNECT TO sample@

-- Drop the database objects if already exists.
DROP PROCEDURE push @
DROP PROCEDURE top @
DROP PROCEDURE pop @
DROP PROCEDURE stack_2_resultset @
DROP PROCEDURE use_stack @
DROP TYPE int_stack @

----------------------------------------------------------------------------
--
-- 1. Create an ARRAY type to implement a stack. 
--
-----------------------------------------------------------------------------

CREATE TYPE int_stack AS INTEGER ARRAY[] @

----------------------------------------------------------------------------
--
-- 2. Create a procedure to push a value in the stack. 
--
-----------------------------------------------------------------------------

-- Create a stored procedure to insert value in the stack.
CREATE  PROCEDURE push(INOUT s int_stack, IN element INTEGER)
BEGIN
  IF (s is NULL) THEN
    SET s[1] = element;
  ELSE
    SET s[cardinality(s) + 1] = element;
  END IF;
END @

----------------------------------------------------------------------------
--
-- 3. Create a procedure to pop/retrieve value from the stack.
--
-----------------------------------------------------------------------------

-- Create a procedure to pop/retrieve a value from the stack.
CREATE PROCEDURE pop(INOUT s int_stack, OUT element INTEGER)
BEGIN
  IF NOT(s is NULL) AND cardinality(s) > 0 THEN
    SET element = s[cardinality(s)];
    SET s = trim_array(s, 1);
  END IF;
END @

----------------------------------------------------------------------------
--
-- 4. Create a procedure to select the topmost value from the stack. 
--
-----------------------------------------------------------------------------

-- Create a procedure to select the topmost value in the stack.
CREATE PROCEDURE top(IN s int_stack, OUT element INTEGER)
BEGIN
  IF NOT(s is NULL) AND cardinality(s) > 0 THEN
    SET element = s[cardinality(s)];
  END IF;
END @

----------------------------------------------------------------------------
--
-- 5. Create a procedure to return all the stack values as a result set. 
--
-----------------------------------------------------------------------------

-- Create a procedure to return the stack values as a result set
CREATE PROCEDURE stack_2_resultset(IN s int_stack)
BEGIN
  DECLARE cur CURSOR WITH RETURN  TO CLIENT FOR
    SELECT elem, idx FROM unnest(s) WITH ORDINALITY AS t(elem, idx);

  OPEN cur;

END @

----------------------------------------------------------------------------
--
-- 6. Create a procedure to show case the stack functionalities. 
--
-----------------------------------------------------------------------------

-- Create a procedure to show case the stack functionalities.
CREATE PROCEDURE use_stack(INOUT  s int_stack,
                           OUT val1 INTEGER,
                           OUT val2 INTEGER)
BEGIN
  CALL push(s, 100);
  CALL push(s, 200);
  CALL push(s, 300);
  CALL push(s, 400);

  CALL pop(s, val1);
  CALL top(s, val2);

  CALL stack_2_resultset(s);
END @