我写了以下内容来生成IMEI
DECLARE
hs_dev EMCDU.HS_DEVICE_ASSIGN.HS_DEVICE_ASSIGN_ID%type;
eq_id varchar(15);
hs_dev2 EMCDU.HS_DEVICE_ASSIGN.HS_DEVICE_ASSIGN_ID%type;
eq_id2 varchar(15);
retval number;
FUNCTION imei ( hs_dev2 NUMBER)
RETURN NUMBER
IS
i NUMBER;
v_NBi SMALLINT;
v_retour SMALLINT;
v_somme NUMBER := 0;
v_nbCar NUMBER;
BEGIN
v_nbCar := LENGTH(hs_dev2);
FOR i IN 1..v_nbCar
LOOP
v_NBi := TO_NUMBER(SUBSTR(hs_dev2,v_nbCar+1-i,1));
v_somme := v_somme
+ MOD(i,2) * v_NBi
+ MOD(i+1,2) * SIGN(-SIGN(v_Nbi-4)+1) * (2*v_NBi)
+ MOD(i+1,2) * SIGN( SIGN(v_Nbi-5)+1) * (2*v_NBi-9);
END LOOP;
v_retour := SIGN(MOD(v_somme,10));
RETURN v_retour;
EXCEPTION
WHEN OTHERS
THEN
RETURN 1;
END imei;
BEGIN
for i in 1..100
LOOP
select max(hs_device_assign_id) into hs_dev from emcdu.hs_device_assign;
select max(equipment_id) into eq_id from emcdu.hs_device_assign;
hs_dev2 := hs_dev + 1;
eq_id2 := eq_id + 1;
retval :=imei(hs_dev2);
dbms_output.put_line(hs_dev2||','||eq_id2);
/*insert into emcdu.hs_device_assign (hs_device_assign_id,equipment_id,sm_serialnum,entry_date,bundle_type,status,purpose,status_histno,hs_device_id)
values
(hs_dev2,eq_id2,NULL,TO_DATE(sysdate),'Phoenix','f',2,NULL,313); */
END LOOP;
END;
/
EXIT;
代码出错了
第1行错误ORA-06502:PL / SQL:数字或值错误:字符到数字转换错误ORA-06512:第52行
请有人帮忙 . 如果LUHN的算法是正确的,请有人建议 .
注意:HS_DEVICE_ASSIGN_ID定义为INTEGER
表DDL:
ALTER TABLE EMCDU.HS_DEVICE_ASSIGN DROP PRIMARY KEY CASCADE;
DROP TABLE EMCDU.HS_DEVICE_ASSIGN CASCADE CONSTRAINTS;
CREATE TABLE EMCDU.HS_DEVICE_ASSIGN(HS_DEVICE_ASSIGN_ID INTEGER NOT NULL,EQUIPMENT_ID VARCHAR2(15 BYTE),SM_SERIALNUM VARCHAR2(50 BYTE),ENTRY_DATE DATE,BUNDLE_TYPE VARCHAR2(30 BYTE),STATUS VARCHAR2(1 BYTE),PURPOSE VARCHAR2(1 BYTE),STATUS_HISTNO NUMBER,HS_DEVICE_ID数字非空)表格数据0 PCTFREE 10 INITRANS 1 MAXTRANS 255存储(初始64K文本1最大限度无限PCTINCREASE 0 BUFFER_POOL默认值)记录NOCOMPRESS NOCACHE NOPARALLEL监控;
创建独特的索引EMCDU.NDX_HS_DEVICE_ASSIGN_IMEI关于EMCDU.HS_DEVICE_ASSIGN(EQUIPMENT_ID)记录表空间数据PCTFREE 10 INITRANS 2 MAXTRANS 255存储(初始64K文本1最大限度无限PCTINCREASE 0 BUFFER_POOL默认)NOPARALLEL;
创建索引EMCDU.NDX_HS_DEVICE_ASSIGN_SIM ON EMCDU.HS_DEVICE_ASSIGN(SM_SERIALNUM)记录表空间数据PCTFREE 10 INITRANS 2 MAXTRANS 255存储(初始64K文本1最大限度无限PCTINCREASE 0 BUFFER_POOL默认)NOPARALLEL;
- 索引EMCDU.SYS_C00213907没有声明 . - 创建父对象时创建对象 .
为EMCDU.HS_DEVICE_ASSIGN创建或替换PUBLIC SYNONYM HS_DEVICE_ASSIGN;
ALTER TABLE EMCDU.HS_DEVICE_ASSIGN ADD(PRIMARY KEY(HS_DEVICE_ASSIGN_ID)使用索引表空间数据PCTFREE 10 INITRANS 2 MAXTRANS 255存储(初始64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL默认值)ENABLE VALIDATE);
ALTER TABLE EMCDU.HS_DEVICE_ASSIGN ADD(CONSTRAINT HS_DEVASSIGN_DEVICE FOREIGN KEY(HS_DEVICE_ID)REFERENCES EMCDU.HS_DEVICE(HS_DEVICE_ID)ENABLE VALIDATE);
GRANT INSERT,SELECT,UPDATE on EMCDU.HS_DEVICE_ASSIGN to BSCS_ROLE;
选择EMCDU.HS_DEVICE_ASSIGN到POSDB;
GRANT INSERT,更新EMCDU.HS_DEVICE_ASSIGN到SITREAD;
SAMPLE DATA: hs_device_assign_id equipment_id
13239002 357220074746978 13238995 357220074754063 13238983 357220074753537 13238982 357220074744742 13238981 357220074744692 13238970 357220074748941