since mysql doesn’t support array data type ,i wrote procedure as follow
which stores output of select query into @variable,later i have use this @variable in if condition and update stmt ,and i also want all the variables
as output of procedure. but this procedure takes time,more than the normal way.
IS THERE ANY WAY TO OPTIMIZING STORED PROCEDURE?
drop procedure test
//
create procedure test
(
IN mode int ,
IN a int
)
BEGIN
SET @x=a;
CASE mode
WHEN 0 THEN
PREPARE STMT FROM "
SELECT SQL_NO_CACHE e.empno,e.ename,e.salary,d.deptname,d.location
FROM EMP e LEFT JOIN DEPT d ON e.deptno=d.deptno WHERE d.deptno>=3 AND d.location=‘USA’ OR d.location=‘UK’
ORDER BY deptno ASC
LIMIT ?,1 into @empno,@ename,@salary,@deptname,@siteid,@location";
EXECUTE STMT USING @x;
WHEN 1 THEN
select stmt
WHEN 2 THEN
select stmt
WHEN 3 THEN
select stmt
END CASE;
SET @deptname_2 = @deptname;
SET @location_2 = @location;
SET @ename_2=@ename;
IF @deptname_2 IN (‘ANALYST’,‘TESTING’) AND @location_2 =‘USA’
THEN
PREPARE STMT FROM "
UPDATE EMP e
SET e.deptname = 'DBA ’
WHERE e.ename=?" ;
EXECUTE STMT USING @ename_2;
END IF ;
PREPARE STMT FROM "
UPDATE …
DEALLOCATE PREPARE STMT;
END;
//