我在本地创建的存储过程一点问题没有.DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`touch_298`$$CREATE PROCEDURE `touch_298`(IN i_First_Introduer varchar(20),IN j_First_Introduer varchar(20) )
BEGIN
DECLARE TEMP INT ;
DECLARE TEMP_LEFT INT ;
DECLARE TOUCH_298_LEFT INT ;
SET @TEMP_RIGHT = 0 ;
SET @TEMP_LEFT = 0;
CALL create_touch_temp(); -- 创建touch_temp表
DROP TABLE IF EXISTS `t` ;
CREATE TABLE t ( touch_count int );
-- sales_product_management表 查询左区 count积分个数
SET @COUNT_PRO_INTEGRAL_LEFT = concat('select count(pro_integral) into @COUNT_INTEGRAL_LEFT from sales_product_management where igtxtParentCardNoTxt in ( select igtxtParentCardNoTxt from sales_product_management where settlement_touch = 0 and igtxtParentCardNoTxt = "' , j_First_Introduer ,'" GROUP BY igtxtParentCardNoTxt ) and placeCmb = 1 AND settlement_touch = 0 AND pro_integral ="', i_First_Introduer , '"') ;
PREPARE E_1 FROM @COUNT_PRO_INTEGRAL_LEFT;
EXECUTE E_1 ;
DEALLOCATE PREPARE E_1;
SET @TEMP_LEFT = @COUNT_INTEGRAL_LEFT;
-- sales_product_management表 查询右区 count积分个数
SET @COUNT_PRO_INTEGRAL_RIGHT = concat('select count(pro_integral) into @COUNT_INTEGRAL_RIGHT from sales_product_management where igtxtParentCardNoTxt in ( select igtxtParentCardNoTxt from sales_product_management where settlement_touch = 0 and igtxtParentCardNoTxt = "' , j_First_Introduer ,'" GROUP BY igtxtParentCardNoTxt ) and placeCmb = 2 AND settlement_touch = 0 AND pro_integral ="', i_First_Introduer , '"') ;
PREPARE E_2 FROM @COUNT_PRO_INTEGRAL_RIGHT;
EXECUTE E_2 ;
DEALLOCATE PREPARE E_2 ;
SET @TEMP_RIGHT= @COUNT_INTEGRAL_RIGHT ;
-- 查看数据库touch_temp 左区298积分碰对数
-- SET @QUERY_TOUCH_298_LEFT = concat('select touch_298_left into @QUERY_298_A_LEFT from touch_temp where touch_name ="', j_First_Introduer,'"' );
-- PREPARE E_3 FROM @QUERY_TOUCH_298_LEFT;
-- EXECUTE E_3 ;
-- DEALLOCATE PREPARE E_3;
-- SET @TOUCH_298_LEFT = @QUERY_298_A_LEFT;
-- 查看数据库touch_temp 右区298积分碰对数
-- SET @QUERY_TOUCH_298_RIGHT = concat('select touch_298_right into @QUERY_298_A_RIGHT from touch_temp where touch_name ="', j_First_Introduer,'"' );
-- PREPARE E_4 FROM @QUERY_TOUCH_298_RIGHT;
-- EXECUTE E_4 ;
-- DEALLOCATE PREPARE E_4;
-- SET @TOUCH_298_RIGHT = @QUERY_298_A_RIGHT;
-- IF @TOUCH_298_RIGHT is not null THEN
-- SET @TEMP_RIGHT= @COUNT_INTEGRAL_RIGHT + @TOUCH_298_RIGHT ; -- 右区 298积分 个数
-- END IF;
-- IF @TOUCH_298_LEFT is not null THEN
-- SET @TEMP_LEFT = @QUERY_298_A_LEFT + @TOUCH_298_LEFT ; -- 左区 298积分 个数
-- END IF;
-- 查看touch_temp 表某一数据是否存在
/* SET @QUERY_TOUCH_TEMP = concat('select count(*) into @FLAG_TEMP from touch_temp where touch_name ="', j_First_Introduer,'"' );
PREPARE E_5 FROM @QUERY_TOUCH_TEMP;
EXECUTE E_5 ;
DEALLOCATE PREPARE E_5;
SET @FLAG = @FLAG_TEMP;
*/
IF ( @TEMP_LEFT > @TEMP_RIGHT) THEN
SET TEMP = @TEMP_LEFT - @TEMP_RIGHT;
/* IF ( @FLAG>0 ) THEN
-- 添加左区碰对余数
SET @UPDATE_298_LEFT = concat('update touch_temp set touch_298_left = ' ,TEMP ,' where touch_name ="', j_First_Introduer,'"' );
PREPARE E_6 FROM @UPDATE_298_LEFT;
EXECUTE E_6 ;
DEALLOCATE PREPARE E_6;
-- 清空 右區碰对余数
SET @CLEAR_298_RIGHT = concat('update touch_temp set touch_298_right = 0 where touch_name ="', j_First_Introduer,'"' );
PREPARE E_7 FROM @CLEAR_298_RIGHT;
EXECUTE E_7 ;
DEALLOCATE PREPARE E_7;
ELSE
SET @INSERT_298_LEFT = concat('insert INTO touch_temp(touch_name,touch_298_left) VALUES("',j_First_Introduer,'",',TEMP,')');
PREPARE E_8 FROM @INSERT_298_LEFT;
EXECUTE E_8 ;
DEALLOCATE PREPARE E_8;
END IF ;
*/
CALL settlement_298_touch (j_First_Introduer,@TEMP_RIGHT);
ELSEIF ( @TEMP_LEFT < @TEMP_RIGHT ) THEN
SET TEMP = @TEMP_RIGHT- @TEMP_LEFT ;
/* IF ( @FLAG>0 ) THEN
-- 添加右区碰对余数
SET @UPDATE_298_RIGHT = concat('update touch_temp set touch_298_right =' ,TEMP,' where touch_name ="', j_First_Introduer ,'"');
PREPARE E_9 FROM @UPDATE_298_RIGHT;
EXECUTE E_9 ;
DEALLOCATE PREPARE E_9;
-- 清空 左區碰对余数
SET @CLEAR_298_LEFT = concat('update touch_temp set touch_298_left = 0 where touch_name ="', j_First_Introduer,'"' );
PREPARE E_10 FROM @CLEAR_298_LEFT;
EXECUTE E_10 ;
DEALLOCATE PREPARE E_10;
ELSE
SET @INSERT_298_RIGHT = concat('insert INTO touch_temp(touch_name,touch_298_right) VALUES("',j_First_Introduer,'",',TEMP,')');
PREPARE E_11 FROM @INSERT_298_RIGHT;
EXECUTE E_11 ;
DEALLOCATE PREPARE E_11;
END IF ;
*/
CALL settlement_298_touch (j_First_Introduer, @TEMP_LEFT );
ELSE
CALL settlement_298_touch (j_First_Introduer,@TEMP_RIGHT );
END IF;
-- 1490碰对
call touch_1490(1490,j_First_Introduer );
END$$DELIMITER ;-----------------------------------------------------------------------------------------
远程链接到服务器的数据库..创建的时候就报错.错误码: 1044
Access denied for user 'test'@'%' to database 'sqltest'请大家帮我解答一下..谢谢
BEGIN
DECLARE TEMP INT ;
DECLARE TEMP_LEFT INT ;
DECLARE TOUCH_298_LEFT INT ;
SET @TEMP_RIGHT = 0 ;
SET @TEMP_LEFT = 0;
CALL create_touch_temp(); -- 创建touch_temp表
DROP TABLE IF EXISTS `t` ;
CREATE TABLE t ( touch_count int );
-- sales_product_management表 查询左区 count积分个数
SET @COUNT_PRO_INTEGRAL_LEFT = concat('select count(pro_integral) into @COUNT_INTEGRAL_LEFT from sales_product_management where igtxtParentCardNoTxt in ( select igtxtParentCardNoTxt from sales_product_management where settlement_touch = 0 and igtxtParentCardNoTxt = "' , j_First_Introduer ,'" GROUP BY igtxtParentCardNoTxt ) and placeCmb = 1 AND settlement_touch = 0 AND pro_integral ="', i_First_Introduer , '"') ;
PREPARE E_1 FROM @COUNT_PRO_INTEGRAL_LEFT;
EXECUTE E_1 ;
DEALLOCATE PREPARE E_1;
SET @TEMP_LEFT = @COUNT_INTEGRAL_LEFT;
-- sales_product_management表 查询右区 count积分个数
SET @COUNT_PRO_INTEGRAL_RIGHT = concat('select count(pro_integral) into @COUNT_INTEGRAL_RIGHT from sales_product_management where igtxtParentCardNoTxt in ( select igtxtParentCardNoTxt from sales_product_management where settlement_touch = 0 and igtxtParentCardNoTxt = "' , j_First_Introduer ,'" GROUP BY igtxtParentCardNoTxt ) and placeCmb = 2 AND settlement_touch = 0 AND pro_integral ="', i_First_Introduer , '"') ;
PREPARE E_2 FROM @COUNT_PRO_INTEGRAL_RIGHT;
EXECUTE E_2 ;
DEALLOCATE PREPARE E_2 ;
SET @TEMP_RIGHT= @COUNT_INTEGRAL_RIGHT ;
-- 查看数据库touch_temp 左区298积分碰对数
-- SET @QUERY_TOUCH_298_LEFT = concat('select touch_298_left into @QUERY_298_A_LEFT from touch_temp where touch_name ="', j_First_Introduer,'"' );
-- PREPARE E_3 FROM @QUERY_TOUCH_298_LEFT;
-- EXECUTE E_3 ;
-- DEALLOCATE PREPARE E_3;
-- SET @TOUCH_298_LEFT = @QUERY_298_A_LEFT;
-- 查看数据库touch_temp 右区298积分碰对数
-- SET @QUERY_TOUCH_298_RIGHT = concat('select touch_298_right into @QUERY_298_A_RIGHT from touch_temp where touch_name ="', j_First_Introduer,'"' );
-- PREPARE E_4 FROM @QUERY_TOUCH_298_RIGHT;
-- EXECUTE E_4 ;
-- DEALLOCATE PREPARE E_4;
-- SET @TOUCH_298_RIGHT = @QUERY_298_A_RIGHT;
-- IF @TOUCH_298_RIGHT is not null THEN
-- SET @TEMP_RIGHT= @COUNT_INTEGRAL_RIGHT + @TOUCH_298_RIGHT ; -- 右区 298积分 个数
-- END IF;
-- IF @TOUCH_298_LEFT is not null THEN
-- SET @TEMP_LEFT = @QUERY_298_A_LEFT + @TOUCH_298_LEFT ; -- 左区 298积分 个数
-- END IF;
-- 查看touch_temp 表某一数据是否存在
/* SET @QUERY_TOUCH_TEMP = concat('select count(*) into @FLAG_TEMP from touch_temp where touch_name ="', j_First_Introduer,'"' );
PREPARE E_5 FROM @QUERY_TOUCH_TEMP;
EXECUTE E_5 ;
DEALLOCATE PREPARE E_5;
SET @FLAG = @FLAG_TEMP;
*/
IF ( @TEMP_LEFT > @TEMP_RIGHT) THEN
SET TEMP = @TEMP_LEFT - @TEMP_RIGHT;
/* IF ( @FLAG>0 ) THEN
-- 添加左区碰对余数
SET @UPDATE_298_LEFT = concat('update touch_temp set touch_298_left = ' ,TEMP ,' where touch_name ="', j_First_Introduer,'"' );
PREPARE E_6 FROM @UPDATE_298_LEFT;
EXECUTE E_6 ;
DEALLOCATE PREPARE E_6;
-- 清空 右區碰对余数
SET @CLEAR_298_RIGHT = concat('update touch_temp set touch_298_right = 0 where touch_name ="', j_First_Introduer,'"' );
PREPARE E_7 FROM @CLEAR_298_RIGHT;
EXECUTE E_7 ;
DEALLOCATE PREPARE E_7;
ELSE
SET @INSERT_298_LEFT = concat('insert INTO touch_temp(touch_name,touch_298_left) VALUES("',j_First_Introduer,'",',TEMP,')');
PREPARE E_8 FROM @INSERT_298_LEFT;
EXECUTE E_8 ;
DEALLOCATE PREPARE E_8;
END IF ;
*/
CALL settlement_298_touch (j_First_Introduer,@TEMP_RIGHT);
ELSEIF ( @TEMP_LEFT < @TEMP_RIGHT ) THEN
SET TEMP = @TEMP_RIGHT- @TEMP_LEFT ;
/* IF ( @FLAG>0 ) THEN
-- 添加右区碰对余数
SET @UPDATE_298_RIGHT = concat('update touch_temp set touch_298_right =' ,TEMP,' where touch_name ="', j_First_Introduer ,'"');
PREPARE E_9 FROM @UPDATE_298_RIGHT;
EXECUTE E_9 ;
DEALLOCATE PREPARE E_9;
-- 清空 左區碰对余数
SET @CLEAR_298_LEFT = concat('update touch_temp set touch_298_left = 0 where touch_name ="', j_First_Introduer,'"' );
PREPARE E_10 FROM @CLEAR_298_LEFT;
EXECUTE E_10 ;
DEALLOCATE PREPARE E_10;
ELSE
SET @INSERT_298_RIGHT = concat('insert INTO touch_temp(touch_name,touch_298_right) VALUES("',j_First_Introduer,'",',TEMP,')');
PREPARE E_11 FROM @INSERT_298_RIGHT;
EXECUTE E_11 ;
DEALLOCATE PREPARE E_11;
END IF ;
*/
CALL settlement_298_touch (j_First_Introduer, @TEMP_LEFT );
ELSE
CALL settlement_298_touch (j_First_Introduer,@TEMP_RIGHT );
END IF;
-- 1490碰对
call touch_1490(1490,j_First_Introduer );
END$$DELIMITER ;-----------------------------------------------------------------------------------------
远程链接到服务器的数据库..创建的时候就报错.错误码: 1044
Access denied for user 'test'@'%' to database 'sqltest'请大家帮我解答一下..谢谢
试一下以确认是权限的问题。
链接远程
在服务器设置
grant all privileges on *.* to test@"%";
flush privileges;从新链接mysql 即可