创建存储过程,如果存在就进行替换: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()
/