DROP TABLE TEST/

CREATE TABLE TEST (
	ID INTEGER NOT NULL PRIMARY KEY, 
	NAME VARCHAR(80), 
	KIND CHAR(1), 
	PRICE DECIMAL(10,2), 
	UPDATE_TIME TIMESTAMP
)/

INSERT INTO TEST VALUES(1001, 'NAME 1001', '1', 1001.01, '2009-01-01')/
INSERT INTO TEST VALUES(1002, 'NAME 1002', '2', 1002.02, '2009-02-02')/
INSERT INTO TEST VALUES(1003, 'NAME 1003', '3', 1003.03, '2009-03-03')/
INSERT INTO TEST VALUES(1004, 'NAME 1004', '4', 1004.04, '2009-04-04')/
INSERT INTO TEST VALUES(1005, 'NAME 1005', '5', 1005.05, '2009-05-05')/

DROP PROCEDURE SELECT_TEST/
CREATE PROCEDURE SELECT_TEST(IN vid INTEGER)
	RESULT SET 1
	LANGUAGE SQL
BEGIN
	DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM TEST WHERE ID=vid;
	OPEN c1;
END
/

DROP PROCEDURE GET_TEST_PRICE/
CREATE PROCEDURE GET_TEST_PRICE(IN vid INTEGER, OUT vprice DECIMAL(10,2))
	LANGUAGE SQL
BEGIN
	SELECT PRICE INTO vprice FROM TEST WHERE ID=vid;
END
/

