ORACLE function方法学习

6035ix1x93

获取组织机构代码，以'a','b'...形式返回
比如：若为211241003  返回： '211241003','211241','211200','210000'
            211241   返回：'211241','211200','210000'
            211200   返回：'211200','210000'
            210000   返回：'210000'

CREATE OR REPLACE FUNCTION GET_PARENT_ORGCODE(in_code IN VARCHAR2)
RETURN VARCHAR2
deterministic
is
-- 返回值
orgcodelist VARCHAR2(128);
orgCode     varchar2(20);
counter     integer;
CURSOR cur_parentorg IS
select org_registry
from (select a.code, b.org_registry
from up_org_unit_ext b, up_org_unit a
where b.id = a.id
and length(a.code) != 6
union
select b.code, substr(b.code, 0, 4) || '00'
from up_org_unit b
where length(b.code) = 6
and substr(b.code, 5, 2) != 00
/*
select b.org_registry, substr(b.org_registry, 0, 4) || '00'
from up_org_unit_ext b
where length(b.org_registry) = 6
and substr(b.org_registry, 5, 2) != 00
*/
union
select b.org_registry, '210000'
from up_org_unit_ext b
where length(b.org_registry) = 6
and substr(b.org_registry, 5, 2) = '00'
) c
start with c.code = in_code
connect by nocycle prior c.org_registry = c.code;
BEGIN
counter     := 0;
orgcodelist := '';
-- 打开游标
OPEN cur_parentorg;
LOOP
FETCH cur_parentorg
INTO orgcode;
EXIT WHEN cur_parentorg%NOTFOUND;
orgcodelist := orgcodelist || '''' || orgcode || ''',';
counter     := counter + 1;
END LOOP;
CLOSE cur_parentorg;
if (counter = 2 and length(in_code) = 9) then
orgcodelist := '''' || in_code || ''',' || '''' ||
substr(in_code, 0, 6) || ''',' || orgcodelist;
else
orgcodelist := '''' || in_code || ''',' || orgcodelist;
end if;
orgcodelist := substr(orgcodelist, 0, length(orgcodelist) - 1);
return orgcodelist;
EXCEPTION
WHEN OTHERS THEN
return 'error';
END GET_PARENT_ORGCODE;

SQL调用

select * from t_reg_mcjbxxyw a where a.namestatu='01'
and  instr(get_parent_orgcode('211241004'),a.accunit) > 0
and a.saveperto > sysdate;

Java 后台调用

public List<BusNamBasic> querXXXName(final BusNamBasic busNamBasic, final String flag, final String accUnit, final int pageNum, final int pageSize) {
return this.getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)throws HibernateException, SQLException {
List<Object> params = new ArrayList<Object>();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT * FROM T_REG_MCJBXXYW WHERE NAMESTATU = '01' ");
if (StringUtils.isNotBlank(accUnit)) {
sql.append("       AND INSTR(GET_PARENT_ORGCODE(?),ACCUNIT) > 0 ");
params.add(accUnit);
}
if ("0".equals(flag) && StringUtils.isNotBlank(busNamBasic.getEntname())) {
sql.append("       AND ENTNAME = ? ");
params.add(busNamBasic.getEntname());
}
if ("1".equals(flag) && StringUtils.isNotBlank(busNamBasic.getEntname())) {
sql.append("       AND ENTNAME like ? ");
params.add("%" + busNamBasic.getEntname() + "%");
}
if (StringUtils.isNotBlank(busNamBasic.getNotno())) {
sql.append("       AND NOTNO = ? ");
params.add(busNamBasic.getNotno());
}
sql.append("  AND SAVEPERTO > SYSDATE ");
sql.append("  ORDER BY SAVEPERTO");
Query query = (SQLQuery) session.createSQLQuery(sql.toString())
.addScalar("mcjbxxywseq", Hibernate.LONG)
.addScalar("zcdjywseq", Hibernate.STRING)
.addScalar("pripid", Hibernate.STRING)
.addScalar("entname", Hibernate.STRING)
.addScalar("namedistrict", Hibernate.STRING)
.addScalar("apprdate", Hibernate.DATE)
.addScalar("alttime", Hibernate.INTEGER)
.setResultTransformer(Transformers.aliasToBean(BusNamBasic.class));
for (int i = 0; i < params.size(); i++) {
query.setParameter(i, params.get(i));
}
if(pageSize>0){
query.setMaxResults(pageSize);
query.setFirstResult((pageNum-1)*pageSize);
}
return query.list();
}
});
}