StoredProcedures_Script.txt

// This script file has the commands for creating MySQL stored procedures used in
// the example JDX program. For a different kind of database, you may change the 
// commands to create the stored procedures appropriately.

// Most of the stored procedures assume the existence of the table SPTest_Address.
CREATE TABLE SPTest_Address(addrId VARCHAR(20) NOT NULL, addr1 VARCHAR(255) NOT NULL, 
    addr2 VARCHAR(255) NULL, city VARCHAR(255) NOT NULL, state VARCHAR(20) NOT NULL, 
    zip VARCHAR(10) NOT NULL, country VARCHAR(255) NULL, 
    CONSTRAINT JDX_PK_SPTest_Address PRIMARY KEY (addrId))

DELIMITER //
CREATE PROCEDURE SP_AllAddresses()
BEGIN 
SELECT * FROM SPTest_Address;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE SP_AddressesByState(IN p_state varchar(20))
BEGIN 
SELECT * FROM SPTest_Address WHERE state = p_state;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE SP_AddressCountByState(IN p_state varchar(20))
BEGIN 
SELECT COUNT(addrId)FROM SPTest_Address WHERE state = p_state;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE SP_ZipUpdate(IN p_id varchar(20), IN p_zip varchar(10))
BEGIN 
UPDATE SPTest_Address SET zip = p_zip WHERE addrId = p_id;
SELECT ROW_COUNT();
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE SP_Square (IN p_number int)
SELECT p_number * p_number
END //
DELIMITER ;