pro_select_roleinfo_p3
DELIMITER | drop procedure if exists pro_select_roleinfo_p3; CREATE PROCEDURE pro_select_roleinfo_p3 ( croleid VARCHAR(50), noffset INT, nrows INT ) BEGIN DECLARE linrows BIGINT ; SET linrows = IF(nrows>0,nrows,18446744073709551615); IF noffset>=0 AND nrows>=0 THEN SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) AS pwd ,(CASE WHEN tam.AccountID IS null THEN '' ELSE tam.AccountID END ) AS AccountID, IFNULL(from_base64(inv.AccountPwd),'') AS AccountPwd FROM traceroleid trd LEFT JOIN traceaccountmap tam on (trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END )) LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) LIMIT noffset,linrows ; ELSE SELECT '',1,'','',''; END IF; END; | DELIMITER ; #SELECT croleid ,noffset,nrows ,(croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) ,trd.TraceRoleID,(CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ), (trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END )) ,trd.TraceRoleID FROM traceroleid trd LEFT JOIN traceaccountmap tam on # (trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END )) AND (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID ; #SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) AS pwd ,(CASE WHEN tam.AccountID IS null THEN '' ELSE tam.AccountID END ) AS AccountID, IFNULL(from_base64(inv.AccountPwd),'') AS AccountPwd FROM traceroleid trd LEFT JOIN traceaccountmap tam on # (trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END )) LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) ; #ELSEIF nrows >0 AND noffset>=0 THEN /* SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) ,(CASE WHEN ta.AccountID IS null THEN '' ELSE ta.AccountID END ), IFNULL(from_base64(inv.AccountPwd),'')FROM traceroleid trd WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) LEFT JOIN traceaccountmap ta on (CASE trd.Role WHEN 1 then ta.InvestConsultantID when 2 THEN ta.InvestManagerID WHEN 3 THEN ta.TraderID END ) =trd.TraceRoleID LEFT JOIN investorinfo inv ON ta.AccountID = inv.AccountID LIMIT noffset,nrows ; */ #update traceroleid set Pwd =to_base64(CONCAT(''',crolepwd,''')) where TraceRoleID =CONCAT(''',croleid,'''); #update traceroleid set Pwd =to_base64(crolepwd) where TraceRoleID =croleid; #SELECT croleid ,crolepwd;
CALL pro_select_roleinfo_p3("",0,0);