函数

oracle

CREATE OR REPLACE FUNCTION GET_JFLYSTR
(p_kpid IN NUMBER --卡片ID
)
   RETURN VARCHAR2
IS
   RESULT   VARCHAR2 (1000);
BEGIN
DECLARE
    V_JFBBH varchar2(40);
    V_JE FLOAT;
    CURSOR CURSOR_JFLY IS SELECT JFBBH,JE FROM ZC_JFLY WHERE KPID= p_kpid;
    BEGIN
       OPEN CURSOR_JFLY;
       LOOP
          FETCH CURSOR_JFLY INTO V_JFBBH,V_JE;
          EXIT WHEN CURSOR_JFLY%NOTFOUND;
          RESULT := RESULT  ||  V_JFBBH || ':' || V_JE || ';';
       END LOOP;
       CLOSE CURSOR_JFLY;
    END;
   RETURN (RESULT);
END GET_JFLYSTR;

mysql：

FUNCTION GET_JFLYSTR (p_kpid  decimal(65))
 RETURNS varchar(1000)
BEGIN 
    DECLARE RESULT VARCHAR(1000);
   DECLARE   V_JFBBH varchar(40);
   DECLARE done INT DEFAULT 0;
   DECLARE  V_JE FLOAT;
 DECLARE CURSOR_JFLY CURSOR  FOR SELECT JFBBH,JE FROM ZC_JFLY WHERE KPID= p_kpid;   
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
     OPEN CURSOR_JFLY;
       emp_loop: LOOP 
          FETCH CURSOR_JFLY INTO V_JFBBH,V_JE;
          -- EXIT WHEN CURSOR_JFLY%NOTFOUND;
            IF done=1 THEN  
             LEAVE emp_loop;  
         END IF;  
       SET   RESULT = concat_ws('',RESULT  ,  V_JFBBH , ':' , V_JE , ';');
       END LOOP emp_loop;
       CLOSE CURSOR_JFLY;
 RETURN RESULT;
    END;