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

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

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