CREATE TABLE `tbl_student` (
`ID` int(11) DEFAULT NULL,
`NAME` varchar(50) DEFAULT NULL,
`CLASSNO` varchar(100) DEFAULT NULL,
`BIRTH` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN ID INT, OUT NAME VARCHAR (50))
BEGIN
IF(ID = 1)
THEN SET NAME = '一班' ;
END IF ;
IF(ID = 2)
THEN SET NAME = '二班' ;
END IF ;
END$$
DELIMITER ;
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `USE_TEST`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USE_TEST`(
IN ID INT,
IN NAME VARCHAR (10),
IN CLASSNO INT,
IN BIRTH DATETIME
)
BEGIN
SET @ID = ID ;
SET @NAME = NAME ;
SET @CLASSNO = CLASSNO ;
SET @BIRTH = BIRTH ;
SET @CLASSNAME = NULL ;
CALL test(CLASSNO, @CLASSNAME) ;
SET @insertSql = CONCAT(
'INSERT INTO TBL_STUDENT VALUES(?,?,?,?)'
) ;
PREPARE stmtinsert FROM @insertSql ;
EXECUTE stmtinsert USING @ID,
@NAME,
@CLASSNAME,
@BIRTH ;
DEALLOCATE PREPARE stmtinsert ;
END$$
DELIMITER ;
CALL USE_TEST(1,'xy',1,'2012-10-01 10:20:01');