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 LOOPBEGINIF RS_ROW.supassortmentid=0 THENsupassortmentstr1:='0|';ELSEv_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()
/