关于MySQL存储过程的个人总结

1。存储过程有返回参数的

DELIMITER $$ //申明将mysql中 ;结束标签改为 以$$结束

USE `gaia`$$

DROP PROCEDURE IF EXISTS `returnProcedure`$$
IN 为输入类型  OUT 为输出类型  INOUT 既可以为输入类型也可以为输出类型
CREATE DEFINER=`root`@`localhost` PROCEDURE `returnProcedure`(IN pram1 DATETIME,OUT pram2 VARCHAR(255))
BEGIN
    DECLARE v_table VARCHAR(255)  DEFAULT 'item_visit_stats';//申明为局部变量，在改存储过程有效    
    SET @v_out=NULL;//申明为全局变量
    SET @v_sql=CONCAT('select iid into @v_out from ',v_table,' where business_day ="',pram1,'"');
    SELECT @v_sql;
    PREPARE statement FROM @v_sql;
    EXECUTE statement;
    SELECT @v_out INTO pram2;
    END$$

DELIMITER ;

2。存储过程不带返回参数
DELIMITER $$

USE `gaia`$$

DROP PROCEDURE IF EXISTS `pvtouv`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pvtouv`(IN `v_business_day` DATETIME)
BEGIN
	DECLARE v_table_name VARCHAR(20) DEFAULT 'user_visit';
	DECLARE v_referer TEXT;
	DECLARE v_shop_id BIGINT;
	DECLARE v_sid, v_uid, v_identify_id  VARCHAR(255);
	DECLARE v_view_time DATETIME;
	DECLARE v_page_type INT;
	DECLARE v_origin_type INT;
	DECLARE v_exit, v_count INT DEFAULT 0;
               //下面这句是申明游标
	DECLARE cur_page_visit CURSOR FOR SELECT referer, shop_id, uid, view_time, page_type, origin_type, identify_id  FROM page_visit;
	//为游标设置标志位，当游标遍历完成后没数据 则设置v_exit 为1
               DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = 1;
	#SELECT NOW() AS _begin;
	OPEN cur_page_visit;//打开游标
	loop_lable: LOOP
		FETCH cur_page_visit INTO v_referer, v_shop_id, v_uid, v_view_time, v_page_type, v_origin_type, v_identify_id;
		#if v_count > 10 then
		#	SET v_exit = 1;
		#end if;
		IF v_exit = 1 THEN
			LEAVE loop_lable;
		END IF;
		#SET v_count = v_count +1;
		BEGIN
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = 0;
			SET @v_id = NULL, @v_second_type = NULL, @v_pv_count = NULL;
			SET @v_sql = CONCAT('SELECT id ,second_page_type,page_visit_count into @v_id,  @v_second_type, @v_pv_count FROM ',v_table_name,' WHERE business_day = "',v_business_day,'" AND shop_id = ',v_shop_id,' AND uid = "',v_uid,'"');
			#select @v_sql;
			PREPARE statment FROM @v_sql;
			EXECUTE statment;
			#DEALLOCATE PREPARE statment;
			SET @v_sql = NULL;
			IF v_identify_id IS NULL THEN
				SET v_identify_id = '';
			END IF;
			#select @v_id;
			IF @v_id IS NULL THEN
				SET @v_pv_count = 1;
				#select v_business_day, v_shop_id, v_view_time, v_view_time, @v_pv_count, v_uid, v_origin_type, v_page_type, v_identify_id, v_page_type, v_identify_id;
				SET @v_sql = CONCAT('INSERT INTO  ',v_table_name,' (business_day,shop_id,begin,end,page_visit_count,uid,origin_type,first_page_type,first_page_id,second_page_type,second_page_id,last_page_type,last_page_id) 
								VALUES("',v_business_day,'",',v_shop_id,',"',v_view_time,'","',v_view_time,'",',@v_pv_count,',"',v_uid,'",',v_origin_type,',',v_page_type,',"',v_identify_id,'",-1,NULL,',v_page_type,',"',v_identify_id,'")');
			ELSEIF @v_second_type = -1 THEN
				SET @v_pv_count = @v_pv_count + 1;
				#select v_page_type, v_identify_id, @v_pv_count, v_view_time, @v_id;
				SET @v_sql = CONCAT('UPDATE  ',v_table_name,' SET second_page_type ="',v_page_type,'", second_page_id = "',v_identify_id,'", last_page_type = "',v_page_type,'", last_page_id = "',v_identify_id,'",
								 page_visit_count = ',@v_pv_count,', end = "',v_view_time,'" WHERE id = ',@v_id);
			ELSE
				SET @v_pv_count = @v_pv_count + 1;
				SET @v_sql = CONCAT('UPDATE  ',v_table_name,' SET  last_page_type = "',v_page_type,'", last_page_id = "',v_identify_id,'",page_visit_count = ',@v_pv_count,', end = "',v_view_time,'" WHERE id = ',@v_id);
			END IF;
			#SELECT @v_sql;
			PREPARE statment FROM @v_sql;
			EXECUTE statment;
			#DEALLOCATE PREPARE statment;
			SET @v_sql = NULL;
			CALL addToItemVisitStats(v_business_day, v_shop_id, v_page_type, v_identify_id);
		END;
	END LOOP;
	CLOSE cur_page_visit;
	CALL stats(v_business_day);
	SELECT NOW() AS _end;
END$$

DELIMITER ;
 

 

对于有返回值的存储过程测试和获取返回值

@Test
	public void returnProcedure(){
		
		CallableStatement cs;
		try {
			cs = (CallableStatement) hibernateTemplate.getSessionFactory().getCurrentSession().connection().prepareCall("Call returnProcedure(?,?)");
			cs.setString(1,"2010-6-26");
			cs.registerOutParameter(2,Types.VARCHAR);
			cs.execute();
			System.out.print(cs.getString(2));
		
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

 

 

总结:

1。关于存储过程的调试，可以用select 变量名;进行输出到控制台进行显示

2。mysql中对于返回结果是列表类型的好像暂时不支持，但是可以将返回值存储在临时的一张表中，程序可以对该表进行处理