oracle常用函數積累

oracle常用函數積累

這些天的工作很多都是圍繞著oracle函數來展開的，這裡來總結一下這些天在工作中使用過的函數。其實也算不上總結，就是把這些日子對於oracle的使用做一個簡單的知識羅列。

以管道化表函數方式返回數據

--創建指定數據類型的對象
create type row_typedepluju as object (rootid NUMBER(19),projectid NUMBER(19));
--創建指定數據類型的對象table
create type table_typedepluju as table of row_typedepluju;
create or replace function getlujudepinfobyaccount(useraccount in VARCHAR2)
return table_typedepluju pipelined as v row_typedepluju;
begin
--遍歷存放數據
for myrowdep in (
-- 查出路局/客專所有的項目id 及根部門id
select t.id as rootid,pinfo.id as projectid from base_security_department t
left join pbs_projectinfo pinfo on pinfo.constructiondep_id = t.id
where t.useflag = 1 and t.grade in (60,130)
start with t.id=(
select a.departmentid from base_security_userinfo a where a.account=useraccount)
connect by prior t.parentid=t.id
) loop
v :=row_typedepluju(myrowdep.rootid,myrowdep.projectid);
pipe row (v);
end loop;
return ;
end;

minus函數返回表的差集

--取得逗號分隔後的數據中，在關聯表中不存在的部分
insert into base_security_deptproject b (departmentid,projectid) select v.DEPARTMENTID,v.PROJECTID from (select * from
--選取切割後不為空的數據
(select c.DEPARTMENTID,c.PROJECTID from (
--根據逗號切割數據，一行轉多行
--temp0為臨時表，20為可接受的逗號數量
with temp0 as (select LEVEL lv from dual CONNECT BY LEVEL <= 20)
--切割
select DEPARTMENTID,substr(
t.vals,instr(t.vals, ',', 1, tv.lv) + 1,
instr(t.vals, ',', 1, tv.lv + 1) -(
instr(t.vals, ',', 1, tv.lv) + 1)
) AS projectid from
--選取待分解的數據，即在用的，指揮部。並取得“,”號數量
(select id as DEPARTMENTID, PROJECTIDS AS vals,
length(PROJECTIDS ) - (nvl(length(REPLACE(PROJECTIDS, ',')), 0)+1) AS cnt
from BASE_SECURITY_DEPARTMENT g where g.GRADE='145' and g.USEFLAG='1' and g.PROJECTIDS is not NULL) t
left join temp0 tv
on tv.lv <= t.cnt) c where c.PROJECTID is not NULL)
--取差集
MINUS
-- 關聯表中的數據
(select t.DEPARTMENTID as DEPARTMENTID,"TO_CHAR"(t.PROJECTID) from BASE_SECURITY_DEPTPROJECT t)) v

start with connect 方式展現整棵樹

select * from base_security_department t connect by prior t.parentid=t.id start with t.id=2400;

substr(字符串,截取開始位置,截取長度) //返回截取的字

`select decode(max(substr(t.bscode,length(t.bscode)-1,length(t.bscode))),null,0,max(substr(t.bscode,length(t.bscode)-1,length(t.bscode)))+1) as codenum from base_security_department t where t.useflag=1 and t.bscode like 'XKHJ01SG07SY__';`

nextval的值是每調用一次就增加一次