在原有表中,A列若有数据,则随机返回B列,若无,随机修改A列数据为搜索值,并返回相对相对应的随机值
CREATE OR REPLACE PROCEDURE P_GETSERIAL_NOBY_DEVICE ( MYDEVICE IN VARCHAR2 , mySERIAL_NO OUT VARCHAR2 )
IS
point_old INT;
point_updated INT;
yhjerror EXCEPTION;
tempint INT;
tempa INT;
myresult INT;
MYSTATUS VARCHAR(5);
BEGIN
myresult:=0;
MYSTATUS:='';
SELECT COUNT(*) INTO myresult FROM YHJ_CONPON_NOT_VIP YCNV WHERE YCNV.DEVICEID =MYDEVICE;
IF (myresult=1) THEN
BEGIN
select SERIAL_NO INTO mySERIAL_NO FROM YHJ_CONPON_NOT_VIP YCNV WHERE YCNV.DEVICEID =MYDEVICE;
end;
ELSE
BEGIN
select SERIAL_NO INTO mySERIAL_NO from (select * from YHJ_CONPON_NOT_VIP WHERE DEVICEID IS NULL order by dbms_random.value) where rownum=1;
UPDATE YHJ_CONPON_NOT_VIP SET DEVICEID=MYDEVICE WHERE SERIAL_NO =mySERIAL_NO;
END;
END IF;
END P_GETSERIAL_NOBY_DEVICE;