stored procedure help

[TheNewbie81]

Hi i have a SP below.

which does the following,

it takes a triplet of values (msisdn,imei, imsi) and based on the timestamp with the triplet either inserts into table A or Updates the Triplet Timestamp in table A if exists, while also writing to Table B.

A = ACTIVE_HANDSETS
B = ACTIVE_HANDSETS_NEW.
C = ACTIVE_HANDSETS_ONCE


I need this to change as follows... but im not sure about the best way of doing this with the SP i have below.

new triplet comes in

If New, does not exist write to A
Write to B and write to C

another triplet comes in, if triplet exists
update timestamp on A for triplet
based on MSISDN see if there is already an entry in B and if the IMEI and IMSI are different. delete existing triplet if this is true and insert new triplet then send this triplet to C.

CREATE OR REPLACE PROCEDURE UPDATE_INSERT_LAST_CALL(
val_msisdn IN    VARCHAR2, 
val_imei IN      VARCHAR2, 
val_imsi IN      VARCHAR2, 
val_call_date IN VARCHAR2)
AS
val_count NUMBER;
BEGIN
SELECT count(*) INTO val_count
FROM ACTIVE_HANDSETS
WHERE MSISDN = val_msisdn 
AND IMEI = val_imei 
AND IMSI = val_imsi;
IF(val_count > 0)
THEN
UPDATE ACTIVE_HANDSETS
SET MOST_RECENT_CDR_DATE = TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS')
WHERE MSISDN = val_msisdn 
AND IMEI = val_imei 
AND IMSI = val_imsi;
ELSE
INSERT INTO ACTIVE_HANDSETS
VALUES(val_msisdn, val_imei, val_imsi, TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'), 
TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO ACTIVE_HANDSETS_NEW
VALUES(val_msisdn, val_imei, val_imsi, 0);
END IF;
END;
/

any advice or tips are welcome.

 

[Dagon]

I don't see any great complexity here (other than understanding exactly what you want). It sounds as if you just need another SELECT statement to retrieve the current value of IMEI and IMSI from ACTIVE_HANDSETS_NEW where msisdn = val_msisdn. If they are different, you branch into an insert/delete (or update) routine etc. This is pretty standard stuff, so I'm not clear exactly what you are finding difficult.

Is it just that you don't know PL/SQL at all and you want someone to write it for you ?

 

[Thargy]

Also, you appear to be doing the so-called "upsert" operation, i.e. a combination of insert and/or update. Look up the merge function, which does this for you.

Regards

T

 

[TheNewbie81]

i managed to do it, i never wrote SQL before so got my head around it in the end.

 

[Beilstwh]

Please put your corrected sql in the issue. That will help the next person with a similar question or we might suggest another method of doing the same thing.

Bill
Lead Application Developer
New York State, USA

 

[TheNewbie81]

here you go

CREATE OR REPLACE PROCEDURE UPDATE_INSERT_LAST_CALL(
val_msisdn IN    VARCHAR2, 
val_imei IN      VARCHAR2, 
val_imsi IN      VARCHAR2, 
val_call_date IN VARCHAR2)
AS
val_count NUMBER;
val_count1 NUMBER;
old_imsi NUMBER;
old_imei NUMBER;


BEGIN

dbms_output.put_line('The next line is blank1');

SELECT count(*) INTO val_count
FROM ACTIVE_HANDSETS
WHERE MSISDN = val_msisdn 
AND IMEI = val_imei 
AND IMSI = val_imsi;


SELECT count(*) INTO val_count1
FROM ACTIVE_HANDSETS_ONCE
WHERE MSISDN = val_msisdn; 
dbms_output.put_line(val_count);

IF(val_count > 0)

THEN
dbms_output.put_line('The next line is blank2');
SELECT IMEI,IMSI into old_imei,old_imsi FROM ACTIVE_HANDSETS
WHERE  msisdn = val_msisdn
AND    most_recent_cdr_date = 
       (SELECT max(most_recent_cdr_date)
          FROM ACTIVE_HANDSETS
            WHERE msisdn = val_msisdn);
 
  

UPDATE ACTIVE_HANDSETS
SET MOST_RECENT_CDR_DATE = TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS')
WHERE MSISDN = val_msisdn 
AND IMEI = val_imei 
AND IMSI = val_imsi;
 
 IF(val_count1 >0)
 
 THEN
 dbms_output.put_line('The next line is blank3');
 INSERT INTO ACTIVE_HANDSETS_NEW
 VALUES(val_msisdn, val_imei, val_imsi, 0);
 INSERT INTO ACTIVE_HANDSETS_ONCE
 VALUES(val_msisdn, val_imei, val_imsi,TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'), 0); 
 
 ELSE
  IF (old_imei != val_imei) 
  THEN
  dbms_output.put_line('exists in once');
  UPDATE ACTIVE_HANDSETS_ONCE
  SET CDR_DATE = TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'), IMEI = val_imei, IMSI = val_imsi
  WHERE MSISDN = val_msisdn; 
  INSERT INTO ACTIVE_HANDSETS_NEW
  VALUES(val_msisdn, val_imei, val_imsi, 0);
  END IF;
 END IF;
 

ELSE
dbms_output.put_line('The next line is blank again');

INSERT INTO ACTIVE_HANDSETS
VALUES(val_msisdn, val_imei, val_imsi, TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'), 
TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO ACTIVE_HANDSETS_NEW
VALUES(val_msisdn, val_imei, val_imsi, 0);
INSERT INTO ACTIVE_HANDSETS_ONCE
VALUES(val_msisdn, val_imei, val_imsi,TO_DATE(val_call_date, 'YYYY-MM-DD HH24:MI:SS'), 0);

 
END IF;
END;
/