Koray Kırdinli

Yazılım ve İş Yaşamı Hakkında Paylaşımlar

PL SQL Örnekleri 2

4- ) UNION ve LIKE örneği

 

CREATE OR REPLACE PROCEDURE TRNSTELSIM.TV_GR_ANLIK_DURUM(CRS OUT SYS_REFCURSOR , kaydeden NVARCHAR2 ) IS

pkaydeden varchar2(100);

BEGIN

pkaydeden := kaydeden;

OPEN CRS FOR

SELECT Bildirim_Tipi,Kayit_Sayisi   FROM(

SELECT ‘Yeni Sistem Toplam’ As Bildirim_Tipi ,COUNT(V_NO)  AS Kayit_Sayisi FROM TV_GR_YENI_SISTEM WHERE IPTAL = 0 AND KAYDEDEN LIKE pkaydeden||’%’

UNION

SELECT ‘Yeni Sistem Bölge OK’ As Bildirim_Tipi ,COUNT(V_NO)  AS Kayit_Sayisi FROM TV_GR_YENI_SISTEM WHERE DURUM = ‘1’ AND IPTAL = 0 AND KAYDEDEN LIKE pkaydeden||’%’

)

ORDER BY Bildirim_Tipi  DESC;

END TV_GR_ANLIK_DURUM;

 

———————————————————————————————————

5- ) IF ELSE örneği

 

CREATE OR REPLACE PROCEDURE TRNSTELSIM.TV_UPDATE_DURUM(durum1 varchar,durum2 varchar,tip varchar)

IS

BEGIN

IF tip = ‘RL’ THEN

UPDATE TRNSTELSIM.TV_RL_ALICI_FREKANS_DEGISIM SET DURUM = durum1 WHERE  DURUM = durum2 ;

END IF;

IF tip = ‘BTS’ THEN

UPDATE TRNSTELSIM.TV_BTS_ILKTRX_DEGISIM SET DURUM = durum1 WHERE  DURUM = durum2 ;

END IF;

IF tip = ‘GR’ THEN

UPDATE TRNSTELSIM.TV_GR_YENI_SISTEM SET DURUM = durum1 WHERE  DURUM = durum2 ;

END IF;

COMMIT;

February 18, 2009 - Posted by | Oracle | , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s