CREATE OR REPLACE FUNCTION f_lf_find_organise(
organise_id in
varchar2,
column_name in varchar2,
level_in in number)
/************************************************************************
函数名称:f_lf_find_organise
输入参数:organise_id 组织机构ID
column_name 想要查询返回出的列,如果为空默认返回的是organise_name这一列
level_in
想要查询的组织机构级别,如果为空默认查询id本身的信息.
返回参数:返回查询出的column_name这一列的值。
如果传入的层级level_in多于organise_id这个组织的层级,那么返回-2
如果出现异常,或者organise_id为空,那么返回-1
功能描述:根据组织机构的ID,查询出该组织几个第level_in层级的column_name字段的值
处理原则:根据organise_code去查询相应的数据
---------------------------------------------------------------------
版本: V1.0.0
创建人: 李峰(lifeng):396442668@qq.com
创建日期:
2012-11-02
修改历史:
2012-11-08
如果传入level_in为空时,现在默认返回其本身的信息
************************************************************************/
return varchar2 is
str_sql varchar2(800);
str_column_name
varchar2(50);
num_level number;
num_index number;
str_tmp
varchar2(1000);
begin
if organise_id is null then
return
'-1';
end if;
str_tmp := '-1';
str_column_name :=
column_name;
if column_name is null then
str_column_name :=
'organise_name';
end if;
-- 默认查询ID本身的信息
if level_in is null
then
str_sql := 'select ' || str_column_name || ' from sys_organise_info
oi where oi.organise_id = :p0';
execute immediate str_sql into str_tmp
using organise_id;
return str_tmp;
else
num_level :=
level_in;
end if;
num_index := 4 * num_level;
str_sql :=
'select ' || str_column_name || '
from sys_organise_info
a
where a.organise_code = (select
subStr(organise_code,0,:p1)
from
sys_organise_info b
where
b.organise_id = :p2
and
length(b.organise_code) >= :p3)';
execute immediate str_sql into
str_tmp using num_index,organise_id,num_index;
return
str_tmp;
exception
when No_data_found then
return '-2';
when
others then
dbms_output.put_line(sqlerrm || 'sql = ' || str_sql);
return '-1';
end f_lf_find_organise;