项目中用到的两个Oracle函数实例

--根据比对方案ID得到目标表的主键字段
create or replace function F_getpkfield(COMPARE_ID in number) return varchar2 is PK_FIELD varchar2(100);
begin
select t.field_name into PK_FIELD from t_compare_field t where t.is_unique='1' and t.compare_id=COMPARE_ID and rownum=1;
return(PK_FIELD);
end F_getpkfield;
------------------------------------------------------------------
create or replace function F_GetSimpleRelateSql(P_SOURCE_OBJECT_ID in number, --关联对象id
P_TARGET_OBJECT_ID in number) --被关联对象id
--获取关联简要结果sql
return varchar2 is
Result varchar2(2000); --返回SQL语句
field_str varchar2(2000); --查询结果
--restrict_str varchar2(2000); --查询条件
cursor field_cur is --目标对象对应的查询结果游标
select decode((select t4.dictionary_id from t_query_restrict t4 where t4.sources_field_id =
(select t5.dictionary_id from t_sources_field_check t5 where t.sources_field_id = t5.id)),
0,
(select t1.FIELD_NAME from t_sources_field t1 where t.sources_field_id = t1.id),
'',
(select t1.FIELD_NAME from t_sources_field t1 where t1.id = t.sources_field_id),
'(select ' ||
(select d.name_field || ' from ' || d.dictionary_object ||
' o where o.' || d.code_field || '=t$.'
from t_dictionary d
where d.id = (select t4.dictionary_id from t_query_restrict t4 where t4.sources_field_id =
(select t5.dictionary_id from t_sources_field_check t5 where t.sources_field_id = t5.id))
and rownum = 1) ||
(select t1.FIELD_NAME from t_sources_field t1 where t1.id = t.sources_field_id) ||
' and rownum=1) ' ||
(select t1.FIELD_NAME from t_sources_field t1 where t1.id = t.sources_field_id)) field_name
from t_query_field t, t_query_relate r
where r.target_query_id = P_TARGET_OBJECT_ID
and r.source_query_id = P_SOURCE_OBJECT_ID
and r.target_query_id = t.query_object_id
and (t.is_simple_output = '1' or t.is_unique = '1')
order by t.is_unique desc, t.id asc;

begin
/*if (P_SOURCE_OBJECT_ID is null or P_TARGET_OBJECT_ID is null or
P_OBJECT_PK is null) then*/
if (P_SOURCE_OBJECT_ID is null or P_TARGET_OBJECT_ID is null) then
--若传进来参数为空，则返回0
Result := '0';
return(Result);
end if;

-- 20110830 1050 添加rtrim()去掉char(number)的右空格，防止显示出错
for my_cur in field_cur loop
if field_str is null then
--若为第一个参数
field_str := my_cur.field_name;
else
field_str := field_str || ', ' || my_cur.field_name; --拼查询结果sql
end if;
end loop;

if field_str is null then
Result := '0';
return(Result);
end if;

select 'select ' || field_str || ' from ' || (select t6.query_object from t_sources t6 where t6.id = t.source_id) ||
' t$ where t$.' || l.target_field_name || '='
into Result
from t_query_object t, t_query_relate l
where l.target_query_id = P_TARGET_OBJECT_ID
and l.source_query_id = P_SOURCE_OBJECT_ID
and t.id = l.target_query_id; --拼sql语句
return(Result);
exception
when others then
--若出错，则返回
Result := '0';
return(Result);
end F_GetSimpleRelateSql;