Oracle存储过程、游标、循环

创建存储过程，如果存在就进行替换：create or replace procedure CRM_Sort_LgcAsset is

定义变量：supassortmentstr1 VARCHAR(1000);

定义游标：CURSOR RS IS select id,supassortmentid,supassortmentstr from LgcAssetAssortment where supassortmentid is not null ORDER BY id asc;

赋值： RS_ROW RS%ROWTYPE;

FOR 循环：FOR RS_ROW IN RS LOOP

IF判断：IF **** THEN IF **** THEN IF **** THEN

************ ************ ************

ELSE END IF; ELSE IF **** THEN

************ ************

END IF; END IF;

字符串拼接及变量赋值：supassortmentstr1:=supassortmentstr2||RS_ROW.supassortmentid||'|';

将变量用在sql上，和Java一样，直接拼上去。

执行存储过程：call CRM_Sort_LgcAsset()

例子

create or replace procedure CRM_Sort_LgcAsset is 
	 supassortmentstr1 VARCHAR(1000); 
   supassortmentstr2 VARCHAR(1000); 
   v_sqlfalg VARCHAR(1000); 
  CURSOR RS IS select id,supassortmentid,supassortmentstr from LgcAssetAssortment where supassortmentid is not null ORDER BY id asc;
  RS_ROW RS%ROWTYPE;
BEGIN
FOR RS_ROW IN RS LOOP
	BEGIN
 		IF RS_ROW.supassortmentid=0 THEN
		supassortmentstr1:='0|';
	ELSE
			v_sqlfalg:= 'select supassortmentstr from LgcAssetAssortment where id='''||RS_ROW.supassortmentid|| '''';
			execute immediate v_sqlfalg into supassortmentstr2;
			supassortmentstr1:=supassortmentstr2||RS_ROW.supassortmentid||'|';
	END IF;
  UPDATE LgcAssetAssortment SET supassortmentstr=supassortmentstr1 WHERE id=RS_ROW.id;END;END LOOP;END;
/
call CRM_Sort_LgcAsset()
/