存储过程简单测试

来源：互联网发布：数控车床gsktdb编程编辑：程序博客网时间：2024/06/05 07:38

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');

0 0