动态分配值的参考

foxe5 · 发表于 2019-5-15 17:33:24

CREATE OR REPLACE PROCEDURE SFIS1.MANTIS_ASSIGNKEY (dsn IN VARCHAR2,
res OUT VARCHAR2)
IS
hdcptemp VARCHAR2 (100);
hdcpupdate VARCHAR2 (100);
nerokeytemp VARCHAR2 (100);
nerokeyupdate VARCHAR2 (100);
counttemp NUMBER;
modeltype VARCHAR2 (20);
myrow ROWID;
CURSOR getdata
IS
SELECT ROWID
FROM sfism4.r_mantis_hdcp1_t
WHERE serial_number IS NULL and rownum<100 ORDER BY DBMS_RANDOM.VALUE ;
CURSOR getdata2
IS
SELECT ROWID
FROM sfism4.R_mantis_WV_T
WHERE serial_number IS NULL and rownum<100 ORDER BY DBMS_RANDOM.VALUE ;
BEGIN
BEGIN
SELECT model_type
INTO modeltype
FROM sfis1.c_model_desc_t
WHERE model_name = (SELECT model_name
FROM sfism4.r107
WHERE serial_number = dsn AND ROWNUM = 1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
res := 'NO SN OR NO MODEL_TYPE.';
GOTO endproc;
END;
--mantis--
IF INSTR (modeltype, '221') > 0
THEN
SELECT COUNT (1)
INTO counttemp
FROM sfism4.r_mantis_hdcp1_t
WHERE serial_number = dsn;
IF counttemp < 1
THEN
<<mantis_hdcp1>>
/*
BEGIN
SELECT ROWID
INTO myrow
FROM (SELECT ROWID
FROM sfism4.r_mantis_hdcp1_t
WHERE serial_number IS NULL
)--AND status IS NULL -- ORDER BY hdcp
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
res := 'NO HDCP1 FOR ASSIGN.';
GOTO endproc;
END;*/
OPEN getdata;
FETCH getdata INTO myrow;
IF getdata%NOTFOUND THEN
res := 'NO HDCP1 FOR ASSIGN.';
GOTO endproc;
ELSE
NULL;
END IF;
CLOSE getdata;
BEGIN
SELECT ROWID
INTO myrow
FROM sfism4.r_mantis_hdcp1_t
WHERE ROWID = myrow
AND serial_number IS NULL
FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
GOTO mantis_hdcp1;
END;
-- dbms_output.put_line('22');
UPDATE sfism4.r_mantis_hdcp1_t
SET serial_number = dsn, status = 'PREPARE'
WHERE ROWID = myrow AND serial_number IS NULL;
COMMIT;
SELECT COUNT (1)
INTO counttemp
FROM sfism4.r_mantis_hdcp1_t
WHERE serial_number = dsn;
if(counttemp=0)then
GOTO mantis_hdcp1;
end if;
END IF;
SELECT COUNT (1)
INTO counttemp
FROM sfism4.R_mantis_WV_T
WHERE serial_number = dsn;
IF counttemp < 1
THEN
<<MANTIS_WV>>
/* BEGIN
SELECT ROWID
INTO myrow
FROM (SELECT ROWID
FROM sfism4.R_mantis_WV_T
WHERE serial_number IS NULL
)--AND status IS NULL -- ORDER BY hdcp
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
res := 'NO WV KEY for assign.';
GOTO endproc;
END;*/
OPEN getdata2;
FETCH getdata2 INTO myrow;
IF getdata2%NOTFOUND THEN
res := 'NO WV KEY for assign.';
GOTO endproc;
ELSE
NULL;
END IF;
CLOSE getdata2;
BEGIN
SELECT ROWID
INTO myrow
FROM sfism4.R_mantis_WV_T
WHERE ROWID = myrow
AND serial_number IS NULL
FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
GOTO MANTIS_WV;
END;
UPDATE sfism4.R_mantis_WV_T
SET serial_number = dsn, status = 'PREPARE'
WHERE ROWID = myrow AND serial_number IS NULL;
COMMIT;
SELECT COUNT (1)
INTO counttemp
FROM sfism4.R_mantis_WV_T
WHERE serial_number = dsn;
if(counttemp=0)then
GOTO MANTIS_WV;
end if;
END IF;
res := 'OK';
GOTO endproc;
END IF;
<<endproc>>
counttemp := 0;
END;
/

复制代码

		自动登录	找回密码
密码			立即注册

动态分配值的参考

浏览过的版块