哪位大哥帮忙写一个15位身份证号码转化为18位的Oracle函数? 请到这里领分http://expert.csdn.net/Expert/topic/2406/2406043.xml?temp=.5104639 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 update 身份证表 set sf_id = substr(sf_id,1,6)||'19'||substr(sf_id,7,9)||'随机数' where length(sf_id) = 15 我有个算法是c#,自己转换成oracle吧,有问题问我. protected string converto18(string str15) { int[] arrSeed={7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2,1}; string[] Gunxi={"1","0","X","9","8","7","6","5","4","3","2"}; string str18; str18=str15.Insert(6,"19"); int intResult=0; for(int i=0;i<17;i++) { intResult+=(int)(str18[i]-'0')*arrSeed[i]; } int l=intResult%11; str18=str18.Insert(17,Gunxi[l]); return str18; } 我在Oracle中写了个函数,编译通过了,但是在sql plus中调用时,出现错误ORA-06502: PL/SQL: 数字或值错误 : 字符传缓冲区太小函数如下:CREATE OR REPLACE FUNCTION "CLMIS"."IDCODE15TO18" (codein in char) return charas i number(2); num number(2); idcode char(50); code char(20) ; type a_type is table of number; --定义数组类型 a a_type := a_type(); -- 定义并初始化一个数组变量 begin idcode := substr(codein,1,6) || '19' || substr(codein,7,9) ; a.extend(17); a(1) := 7; a(2) := 9; a(3) := 10; a(4) := 5; a(5) := 8; a(6) := 4; a(7) := 2; a(8) := 1; a(9) := 6; a(10) := 3; a(11) := 7; a(12) := 9; a(13) := 10; a(14) := 5; a(15) := 8; a(16) := 4; a(17) :=2; num := 0; for i in 1..17 loop num := num + a(i) * (to_number(substr(idcode,i,1))); end loop; num := num mod 11 ; if num = 0 then code := '1'; end if; if num = 1 then code := '0'; end if; if num = 2 then code := 'x'; end if; if num = 3 then code := '9'; end if; if num = 4 then code := '8'; end if; if num = 5 then code := '7'; end if; if num = 6 then code := '6'; end if; if num = 7 then code := '5'; end if; if num = 8 then code := '4'; end if; if num = 9 then code := '3'; end if; if num = 10 then code := '2'; end if; idcode := idcode || code; return idcode; end; sql server里面有个 staff(还是stuff?) 函数,不知道ora里面有没有? for update使用問題 oracle数据库的名字问题 rman恢复问题!急!!!!! Oracle 条件写在视图内与视图外效率差很多怎么办 一个过程中多个游标问题 想创建一个综合查询的视图,遇到一个棘手的问题 请问遍历表中所有记录,计算下次检修时间这个功能怎么实现? 上亿条记录,数据文件居然达到了40个G这样正常吗?如何优化呢? Oracle对比SQLServer,临时表的疑问 查詢結果比較 版主:说,为什么要删除我的贴子?说...... 定时自动导出dmp,我想用系统日期时间拼成文件名,在EXP批处理中怎样写?
protected string converto18(string str15)
{
int[] arrSeed={7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2,1};
string[] Gunxi={"1","0","X","9","8","7","6","5","4","3","2"};
string str18;
str18=str15.Insert(6,"19");
int intResult=0;
for(int i=0;i<17;i++)
{
intResult+=(int)(str18[i]-'0')*arrSeed[i];
}
int l=intResult%11;
str18=str18.Insert(17,Gunxi[l]);
return str18;
}
函数如下:
CREATE OR REPLACE FUNCTION "CLMIS"."IDCODE15TO18"
(codein in char)
return char
as
i number(2);
num number(2);
idcode char(50);
code char(20) ;
type a_type is table of number; --定义数组类型
a a_type := a_type(); -- 定义并初始化一个数组变量
begin
idcode := substr(codein,1,6) || '19' || substr(codein,7,9) ;
a.extend(17);
a(1) := 7;
a(2) := 9;
a(3) := 10;
a(4) := 5;
a(5) := 8;
a(6) := 4;
a(7) := 2;
a(8) := 1;
a(9) := 6;
a(10) := 3;
a(11) := 7;
a(12) := 9;
a(13) := 10;
a(14) := 5;
a(15) := 8;
a(16) := 4;
a(17) :=2;
num := 0;
for i in 1..17 loop
num := num + a(i) * (to_number(substr(idcode,i,1)));
end loop;
num := num mod 11 ;
if num = 0 then
code := '1';
end if;
if num = 1 then
code := '0';
end if;
if num = 2 then
code := 'x';
end if;
if num = 3 then
code := '9';
end if;
if num = 4 then
code := '8';
end if;
if num = 5 then
code := '7';
end if;
if num = 6 then
code := '6';
end if;
if num = 7 then
code := '5';
end if;
if num = 8 then
code := '4';
end if;
if num = 9 then
code := '3';
end if;
if num = 10 then
code := '2';
end if;
idcode := idcode || code;
return idcode;
end;