存储过程
2020年6月19日
15:24
create or replace package wl_system is
TYPE tp_ref_cursor is ref cursor;
TYPE type_varchar2_table is table of varchar2(200);
type type_list_bizholder is table of type_object_bizholder;
function GetUserName(puserid app_user.id%type) return varchar2;
function split_text(
ptext in varchar2,
pdelimiterchars varchar2 :=',;:/,;、',
pignoreEmpty number := 1
) return type_varchar2_table pipelined;
function GetSequenceNumber(pid sequencenumber.id%type) return number;
end;
体
create or replace package body wl_system is
function GetUserName(puserid app_user.id%type) return varchar2
is
cursor c1 is select name from app_user where id=puserid;
vname app_user.name%type;
begin
open c1;
fetch c1 into vname;
close c1;
return vname;
end;
function split_text(
ptext in varchar2,
pdelimiterchars varchar2 :=',;:/,;、',
pignoreEmpty number := 1
) return type_varchar2_table pipelined
is
vchar varchar2(8);
vlen number := length(ptext);
vpos number := 1;
vdelimpos number(5) := 0;
vPreDelimiterPos number(5) := 0;
vseg varchar2(200);
begin
while(vpos <= vlen)
loop
vchar := substr(ptext, vpos, 1);
vdelimpos := instr(pdelimiterchars, vchar);
if vdelimpos > 0 then
vseg := substr(ptext, vPreDelimiterPos + 1, vpos - vPreDelimiterPos - 1);
if pignoreEmpty = 0 then
pipe row(vseg);
else
if vseg is not null then
pipe row(vseg);
end if;
end if;
vPreDelimiterPos := vpos;
end if;
vpos := vpos + 1;
end loop;
if vPreDelimiterPos = 0 or vPreDelimiterPos <> vlen then
pipe row(substr(ptext, vPreDelimiterPos + 1));
end if;
return;
end;
function GetSequenceNumber(pid sequencenumber.id%type) return number
is
cursor c1 is select seqnumber from sequencenumber where id=pid for update of seqnumber;
vseqnumber sequencenumber.seqnumber%type :=1;
begin
open c1;
fetch c1 into vseqnumber;
if c1%found then
if substr(vseqnumber,1,8) = to_char(sysdate,'yyyymmdd') then
vseqnumber := vseqnumber + 1;
update sequencenumber set seqnumber = vseqnumber where current of c1;
else
delete from sequencenumber where current of c1;
vseqnumber := to_number(to_char(sysdate,'yyyymmdd')) * 10000 + 1;
insert into sequencenumber(id,seqnumber)values(pid, vseqnumber);
end if;
end if;
if c1%notfound then
vseqnumber := to_number(to_char(sysdate,'yyyymmdd')) * 10000 + 1;
insert into sequencenumber(id,seqnumber)values(pid, vseqnumber);
end if;
close c1;
return vseqnumber;
end;
end;
用法
create table SEQUENCENUMBER
(
id VARCHAR2(36) not null,
seqnumber NUMBER(30) default 1 not null
)
用法
declare
begin
dbms_output.put_line(wl_system.GetSequenceNumber('trans-package-name'));
for q in(
select * from table(wl_system.split_text('1234,eferg,457'))
)loop
dbms_output.put_line(q.column_value);
insert into temp_ (str) values(q.column_value);
end loop;