- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
procedure xxx_procedure
is
-- lngIDVidPP Number;
sSQL varchar2(4000);
begin
sSQL := '';
sSQL := sSQL || ' Declare ';
sSQL := sSQL || ' lngIDVidPP Number; ';
sSQL := sSQL || ' lngColRec Number; ';
sSQL := sSQL || ' strTypeProc Varchar2(255); ';
sSQL := sSQL || ' BEGIN ';
sSQL := sSQL || ' for rec in (select f.param2 , A.idpp, pp.crastr, pp.cvector, a.param1, a.et, a.tet ';
sSQL := sSQL || ' from table1 e, <a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2d797264697d7d6d">[email protected]</a>' || 'app' || '_' ||'xxx' || ' a, table2 f, ';
sSQL := sSQL || ' <a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="96e2c9e6e6c9f5f7e4f2d6">[email protected]</a>' || 'app' || '_' || 'xxx' || ' pp, refitem r';
sSQL := sSQL || ' where ';
sSQL := sSQL || ' e.param1 = a.param1 ';
sSQL := sSQL || ' and e.id_building = F.ID_BUILDING ';
sSQL := sSQL || ' and F.FLOOR_NUMBER = a.et ';
sSQL := sSQL || ' and R.REFERENCEID = 2019 and R.ITEMID = F.ID_FLOOR_TYPE';
sSQL := sSQL || ' and R.CODE = a.tet';
sSQL := sSQL || ' and a.idpp = pp.cidpp)';
sSQL := sSQL || ' loop ';
sSQL := sSQL || ' lngColRec := 0; ';
sSQL := sSQL || ' lngIDVidPP := 0; ';
sSQL := sSQL || ' SELECT COUNT(*) INTO lngColRec FROM <a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6b3f343b3924282e3838343b3b2b">[email protected]</a>' || 'app' || '_' || 'xxx || ' Z ';
sSQL := sSQL || ' WHERE Z.PPP_UNOM = rec.param1 AND Z.PPP_ET = rec.et AND Z.PPP_TET = rec.tet; ';
sSQL := sSQL || ' if lngColRec = 0 then ';
sSQL := sSQL || ' if BITAND(rec.crastr, 8) <> 0 and BITAND(rec.cvector, 8) <> 0 then ';
sSQL := sSQL || ' lngIDVidPP := 50868; ';
sSQL := sSQL || ' else ';
sSQL := sSQL || ' if BITAND(rec.crastr, 8) <> 0 then ';
sSQL := sSQL || ' lngIDVidPP := 50866; ';
sSQL := sSQL || ' else ';
sSQL := sSQL || ' lngIDVidPP := 50867; ';
sSQL := sSQL || ' end if; ';
sSQL := sSQL || ' end if; ';
sSQL := sSQL || ' update table2 f ';
sSQL := sSQL || ' set f.xx = rec.idpp, ';
sSQL := sSQL || ' f.xxx = lngIDVidPP, ';
sSQL := sSQL || ' f.xxxx = 54554 ';
sSQL := sSQL || ' where f.xxxxx = rec.id_floor; ';
sSQL := sSQL || ' else '; --if lngColRec > 0 then
sSQL := sSQL || ' strTypeProc := Null; ';
sSQL := sSQL || ' SELECT Y.PH_ACTION_KEY INTO strTypeProc ';
sSQL := sSQL || ' FROM <a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d3878c83819c909680808c9b96929793">[email protected]</a>' || 'app' || '_' || 'SGP' || ' Y ';
sSQL := sSQL || ' WHERE ';
sSQL := sSQL || ' Y.PHID IN (SELECT X.PPP_PHID ';
...... 10 строк вырезано из-за ограничения 4000символов
sSQL := sSQL || ' if BITAND(rec.crastr, 8) <> 0 and BITAND(rec.cvector, 8) <> 0 then ';
sSQL := sSQL || ' lngIDVidPP := 50868; ';
sSQL := sSQL || ' else ';
sSQL := sSQL || ' if BITAND(rec.crastr, 8) <> 0 then ';
sSQL := sSQL || ' lngIDVidPP := 50866; ';
sSQL := sSQL || ' else ';
sSQL := sSQL || ' lngIDVidPP := 50867; ';
sSQL := sSQL || ' end if; ';
sSQL := sSQL || ' end if; ';
sSQL := sSQL || ' update table3 f ';
sSQL := sSQL || ' set f.ID_PLANE_G = rec.idpp, ';
sSQL := sSQL || ' f.id_plane_scan = lngIDVidPP, ';
sSQL := sSQL || ' f.id_status_plane = 54552 ';
sSQL := sSQL || ' where f.param1 = rec.param1; ';
sSQL := sSQL || ' ELSE ';
sSQL := sSQL || ' update table3 f ';
sSQL := sSQL || ' set f.id_status_plane = 54552 ';
sSQL := sSQL || ' where f.param1 = rec.param1; ';
sSQL := sSQL || ' END IF; ';
sSQL := sSQL || ' end if; '; --if lngColRec > 0 then
sSQL := sSQL || ' end loop;';
sSQL := sSQL || ' null;';
sSQL := sSQL || ' end;';
EXECUTE IMMEDIATE sSQL;
dbms_output.put_line ('Процедура xxx_procedure успешно создана');
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line('Ошибка создания xxx_procedure. ' || SQLERRM);
end;
Комментарии (98) RSS