mysql与oracle遍历游标

MYSQL部分

CREATE DEFINER=`root`@`localhost` PROCEDURE `ProcedureTest`()
BEGIN
 DECLARE i int;
 -- 设置初始值，用于循环结束的判断依据
 DECLARE s int DEFAULT 0;
 DECLARE  GO_CARGO_ID1,GO_ORDER_ID1 LONG;
 DECLARE cus CURSOR FOR select a.GO_CARGO_ID,a.GO_ORDER_ID from go_cargo a;
  -- 这句话是用在while循环前的，如果游标到了最后就会将之前定义的s设置为1 ，直接拷贝进入就行
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 

 set i = 0;
OPEN cus;
 
FETCH  cus INTO GO_CARGO_ID1,GO_ORDER_ID1; 
  WHILE s<>1 DO
		select GO_CARGO_ID1;
FETCH cus INTO GO_CARGO_ID1,GO_ORDER_ID1;
	
  END WHILE;

CLOSE cus;
END

ORACLE部分
1.案例一

CREATE OR REPLACE FUNCTION "BAFR_IMP_ADJUST_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN VARCHAR2
AS
resultStr VARCHAR2(20):='';
BkFlag VARCHAR2(500):=''; 
BEGIN

FOR c IN ( SELECT SERVICE_CODE from "go_service_item" where GO_ORDER_ID = GO_ORDER_ID_IN ) LOOP
	
	CASE c.SERVICE_CODE
	WHEN 'BK' THEN
	BkFlag := CONCAT(BkFlag, 'BK');
	ELSE
			BkFlag := CONCAT(BkFlag, 'HASEXISTS');
END CASE;

	
END LOOP;

CASE 
	WHEN INSTR(BkFlag, 'BK') > 0 AND INSTR(BkFlag, 'HASEXISTS') <= 0 THEN
	resultStr := CONCAT(resultStr, 'Y');
	ELSE
		resultStr := CONCAT(resultStr, 'N');
END CASE;

	RETURN resultStr;
END;

2.案例二

CREATE OR REPLACE FUNCTION "BAFR_IMP_WHOLE_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN VARCHAR2
AS
resultStr VARCHAR2(1000):='';
rebackStr VARCHAR2(20):='';
BEGIN

FOR c IN (SELECT SERVICE_CODE from "go_service_item" where GO_ORDER_ID = GO_ORDER_ID_IN) LOOP
resultStr := CONCAT('#', c.SERVICE_CODE);
END LOOP;

CASE 
	WHEN INSTR(resultStr, 'BK') > 0 AND  INSTR(resultStr, 'PKP') > 0 AND INSTR(resultStr, 'DLV') > 0 AND  INSTR(resultStr, 'CD') > 0 THEN
		rebackStr := CONCAT(rebackStr, 'Y');
	ELSE
		rebackStr := CONCAT(rebackStr, 'N');
END CASE;

	RETURN rebackStr;
END;

3.案例三

CREATE OR REPLACE FUNCTION "BAFR_MAIN_OPERATION_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN VARCHAR2
AS
resultStr VARCHAR2(1000):='';
BEGIN
	FOR c IN ( select ITEM_CODE from "op_operation_item" where "op_operation_item".GO_ORDER_ID = GO_ORDER_ID_IN) LOOP
	
	CASE 
	WHEN INSTR(c.ITEM_CODE, 'LPC') > 0 THEN
	resultStr := CONCAT(resultStr, '0100#');
	WHEN INSTR(c.ITEM_CODE, 'UDP') > 0 OR INSTR(c.ITEM_CODE, 'AT') > 0  OR INSTR(c.ITEM_CODE, 'FT') > 0 THEN
		resultStr := CONCAT(resultStr, '0500#');
	WHEN INSTR(c.ITEM_CODE, 'AR') > 0 THEN
		resultStr := CONCAT(resultStr, '0200#');
	WHEN INSTR(c.ITEM_CODE, 'DP') > 0 THEN
		resultStr := CONCAT(resultStr, '0300#');
	WHEN INSTR(c.ITEM_CODE, 'CD') > 0 THEN
		resultStr := CONCAT(resultStr, '0400#');
	ELSE
		resultStr := CONCAT(resultStr, '');
END CASE;
	
END LOOP;


	RETURN resultStr;
END;

4.案例四

CREATE OR REPLACE FUNCTION "BRFR_BMS_SERV_SEGMENT_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN VARCHAR2
AS
resultStr VARCHAR2(1000):='';
BEGIN
FOR c IN ( select ITEM_CODE from "op_operation_item" where "op_operation_item".GO_ORDER_ID = GO_ORDER_ID_IN) LOOP
	
	CASE 
	WHEN INSTR(c.ITEM_CODE, 'BK') > 0 THEN
	resultStr := CONCAT(resultStr, '0100#');
	WHEN INSTR(c.ITEM_CODE, 'NT') > 0 OR INSTR(c.ITEM_CODE, 'AT') > 0  THEN
		resultStr := CONCAT(resultStr, '0500#');
	WHEN INSTR(c.ITEM_CODE, 'CO') > 0 THEN
		resultStr := CONCAT(resultStr, '0300#');
	WHEN INSTR(c.ITEM_CODE, 'PS') > 0 THEN
		resultStr := CONCAT(resultStr, '0600#');
	WHEN INSTR(c.ITEM_CODE, 'CD') > 0 THEN
		resultStr := CONCAT(resultStr, '0400#');
	ELSE
		resultStr := CONCAT(resultStr, '');
END CASE;
END LOOP;
	RETURN resultStr;
END;

5.案例五

CREATE OR REPLACE FUNCTION "BRFR_BULK_VOLUME_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN NUMBER
AS
 countNum NUMBER:=0 ;
BEGIN
 FOR c IN (select "op_container".WEIGHT_UNIT,"op_container".GROSS_WEIGHT from "go_order" LEFT JOIN "op_container"   on "go_order".GO_ORDER_ID = "op_container".GO_ORDER_ID where "go_order".GO_ORDER_ID = GO_ORDER_ID_IN )
LOOP

IF INSTR(c.WEIGHT_UNIT, 'KGS') > 0 THEN
 countNum := countNum+NVL(c.GROSS_WEIGHT, 0)*0.001;
ELSE
	countNum := countNum+NVL(c.GROSS_WEIGHT, 0);
END IF;

-- 	 dbms_output.put_line('11111111111111111');
END LOOP;

	RETURN countNum;
END;

6.案例六

CREATE OR REPLACE FUNCTION "BWFR_TEU_FUNC" (GO_ORDER_ID_IN IN NUMBER DEFAULT '-1')
RETURN NUMBER
AS
 countNum NUMBER:=0 ;
BEGIN

	FOR c IN (select CTN_TYPE_SIZE,QUANTITY from "op_container" where "op_container".GO_ORDER_ID = GO_ORDER_ID_IN) LOOP
-- 	    


  IF TO_NUMBER(SUBSTR( c.CTN_TYPE_SIZE, 1, 2 ) ) <= 20

THEN
	countNum := countNum+NVL(c.QUANTITY, 0);
	
ELSE
  countNum := countNum+NVL(c.QUANTITY, 0)*2;
	
END IF;
--  dbms_output.put_line(countNum);
END LOOP;

	RETURN countNum;
END;

7.案例七

CREATE OR REPLACE FUNCTION "BWFR_BMS_SUBCLASS_FUNC" (GO_ORDER_ID_IN IN NUMBER)
RETURN VARCHAR2
AS
resultStr VARCHAR2(1000):='';
HblFlag VARCHAR2(20):=''; 
BkFlag VARCHAR2(500):=''; 
BEGIN

FOR c IN ( SELECT SERVICE_CODE from "go_service_item" where GO_ORDER_ID = GO_ORDER_ID_IN ) LOOP
	
	CASE c.SERVICE_CODE
	WHEN 'HBL' THEN 
	HblFlag := CONCAT(HblFlag, 'HBL');
	BkFlag := CONCAT(BkFlag, 'HASEXISTS');
	EXIT;
		WHEN 'BK' THEN
		BkFlag := CONCAT(BkFlag, 'BK');
	ELSE
			BkFlag := CONCAT(BkFlag, 'HASEXISTS');
		
END CASE;

	
END LOOP;

CASE 
	WHEN INSTR(HblFlag, 'HBL') > 0 THEN
	resultStr := CONCAT(resultStr, '0103');
	WHEN INSTR(BkFlag, 'BK') > 0 AND INSTR(BkFlag, 'HASEXISTS') <= 0 THEN
	resultStr := CONCAT(resultStr, '0102');
	ELSE
		resultStr := CONCAT(resultStr, '0101');
END CASE;

	RETURN resultStr;
END;