|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?注册账号
×
使用方式 insert into XXX (id)values(generatePK('1001'))
nc5 | nc6 | - create or replace function generatePK(corp varchar) return varchar as
- /*
- * 生成方法参考自中间件内部算法
- *输入参数: corp 公司主键
- * 输出参数:new_pk 新生成的pk
- */
- new_oid varchar(14); --the oid string
- old_oid varchar(14); --the oid store in the pub_oid
- temp_oid varchar(14);
- MINI_CODE number(2);
- MAX_CODE number(2);
- CODE_LENGTH number(2);
- tempchar char(1);
- tempascii number(2);
- carryup boolean;
- global_count number(2);
- begin
- --初始化数据
- temp_oid :='';
- old_oid := '';
- new_oid := '10000000000000';
- CODE_LENGTH := 14;
- MINI_CODE := 48;
- MAX_CODE := 90;
- global_count := 14;
- --查询该公司下最大的pk
- select pub_oid.oid
- into old_oid
- from pub_oid
- where pub_oid.pk_corp = corp;
- -- dbms_output.put_line('old_oid=' || old_oid);
- --生成新的oid
- new_oid := '';
- FOR counter IN REVERSE 1..CODE_LENGTH LOOP
- carryup := false;
- global_count := counter;
- tempchar := substr(old_oid,counter,1);
- tempascii := ascii(tempchar)+1;
- if tempascii > MAX_CODE then
- tempascii := MINI_CODE;
- carryup := true;
- end if;
- if tempascii = 58 then
- tempascii := 65;
- end if;
- new_oid := new_oid||chr(tempascii);
- if carryup = false then
- -- 'ABCD' --> 'DCBA'
- for icounter in reverse 1..CODE_LENGTH-global_count+1 loop
- tempchar := substr(new_oid,icounter,1);
- temp_oid := temp_oid || tempchar;
- end loop;
- temp_oid := substr(old_oid,1,global_count-1)|| temp_oid;
- --变更临时oid为 new_oid
- new_oid := temp_oid;
- exit; -- 跳出循环
- end if;
- END LOOP;
- --DBMS_OUTPUT.PUT_LINE('老主键:'||corp || 'AA' || old_oid);
- --DBMS_OUTPUT.PUT_LINE('新主键:'||corp || 'AA' || new_oid);
- --update the new value 更新pk为新生成的pk
- update pub_oid set pub_oid.oid = new_oid where pub_oid.pk_corp = corp;
- return corp || 'AA' || new_oid;
- exception
- WHEN NO_DATA_FOUND THEN
- --INSERT THE NEW VALUE 插入新的pk
- insert into pub_oid
- (dr, oid, pk_corp)
- values
- (0, new_oid, corp);
- return corp || 'AA' || new_oid;
- WHEN OTHERS THEN
- rollback;
- DBMS_OUTPUT.PUT_LINE('公司' || corp || '生成主键发生错误');
- return corp || 'AA' || new_oid;
- end;
复制代码
| - create or replace function generatePK(corp varchar) return varchar as
- /*
- * 生成方法参考自中间件内部算法
- *输入参数: corp 公司主键
- * 输出参数:new_pk 新生成的pk
- */
- new_oid varchar(14); --the oid string
- old_oid varchar(14); --the oid store in the pub_oid
- temp_oid varchar(14);
- MINI_CODE number(2);
- MAX_CODE number(2);
- CODE_LENGTH number(2);
- tempchar char(1);
- tempascii number(2);
- carryup boolean;
- global_count number(2);
- begin
- --初始化数据
- temp_oid :='';
- old_oid := '';
- new_oid := '10000000000000';
- CODE_LENGTH := 14;
- MINI_CODE := 48;
- MAX_CODE := 90;
- global_count := 14;
- --查询该公司下最大的pk
- select pub_oid.idnumber
- into old_oid
- from pub_oid
- where pub_oid.pk_corp = corp;
- -- dbms_output.put_line('old_oid=' || old_oid);
- --生成新的oid
- new_oid := '';
- FOR counter IN REVERSE 1..CODE_LENGTH LOOP
- carryup := false;
- global_count := counter;
- tempchar := substr(old_oid,counter,1);
- tempascii := ascii(tempchar)+1;
- if tempascii > MAX_CODE then
- tempascii := MINI_CODE;
- carryup := true;
- end if;
- if tempascii = 58 then
- tempascii := 65;
- end if;
- new_oid := new_oid||chr(tempascii);
- if carryup = false then
- -- 'ABCD' --> 'DCBA'
- for icounter in reverse 1..CODE_LENGTH-global_count+1 loop
- tempchar := substr(new_oid,icounter,1);
- temp_oid := temp_oid || tempchar;
- end loop;
- temp_oid := substr(old_oid,1,global_count-1)|| temp_oid;
- --变更临时oid为 new_oid
- new_oid := temp_oid;
- exit; -- 跳出循环
- end if;
- END LOOP;
- --DBMS_OUTPUT.PUT_LINE('老主键:'||corp || 'AA' || old_oid);
- --DBMS_OUTPUT.PUT_LINE('新主键:'||corp || 'AA' || new_oid);
- --update the new value 更新pk为新生成的pk
- update pub_oid set pub_oid.idnumber = new_oid where pub_oid.pk_corp = corp;
- return corp || 'AA' || new_oid;
- exception
- WHEN NO_DATA_FOUND THEN
- --INSERT THE NEW VALUE 插入新的pk
- insert into pub_oid
- (dr, idnumber, pk_corp)
- values
- (0, new_oid, corp);
- return corp || 'AA' || new_oid;
- WHEN OTHERS THEN
- rollback;
- DBMS_OUTPUT.PUT_LINE('公司' || corp || '生成主键发生错误');
- return corp || 'AA' || new_oid;
- end;
复制代码
|
|
评分
-
查看全部评分
|