我这儿有个oracle的,可以参考下,希望能帮到你 CREATE OR REPLACE FUNCTION F_SYS_DATA_PRI_VALUE(V_USER_ID IN VARCHAR2, V_DATA_OBJ_ID IN VARCHAR2) RETURN DATA_PRI_INFO_TABLE PIPELINED AS v_data_pri_info data_pri_info := data_pri_info(NULL, NULL, NULL); TYPE v_cursor IS REF CURSOR; v_list v_cursor; v_str VARCHAR2(10000) := ' '; v_count NUMBER := 1;
CURSOR v_sql1 IS SELECT DISTINCT t2.data_obj_code, t3.data_group_col, t1.data_group_value, t2.data_obj_pri_col FROM t_sys_user_data_pri t1, t_sys_data_obj t2, t_sys_data_group t3 WHERE t1.data_obj_id = t2.data_obj_id AND t1.data_group_id = t3.data_group_id AND t1.user_id = v_user_id AND t1.data_obj_id = v_data_obj_id ;
CURSOR v_sql2 IS SELECT DISTINCT t2.data_obj_code, t3.data_group_col, t1.data_group_value, t2.data_obj_pri_col FROM t_sys_user_data_pri t1, t_sys_data_obj t2, t_sys_data_group t3 WHERE t1.data_obj_id = t2.data_obj_id AND t1.data_group_id = t3.data_group_id AND t1.user_id = v_user_id ; BEGIN
IF v_data_obj_id <> '0' THEN FOR v_list IN v_sql1 LOOP IF v_count = 1 THEN v_str := 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',' || v_list.data_obj_pri_col || ' FROM ' || v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')'; v_count := v_count + 1; ELSE v_str := v_str || ' UNION ALL ' || 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',' || v_list.data_obj_pri_col || ' FROM ' || v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
END IF; END LOOP;
ELSE
FOR v_list IN v_sql2 LOOP IF v_count = 1 THEN v_str := 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',TO_CHAR(' || v_list.data_obj_pri_col || ') FROM ' || v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')'; v_count := v_count + 1; ELSE v_str := v_str || ' UNION ALL ' || 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',TO_CHAR(' || v_list.data_obj_pri_col || ') FROM ' || v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
END IF; END LOOP;
END IF;
OPEN v_list FOR v_str; LOOP
FETCH v_list INTO v_data_pri_info.user_id, v_data_pri_info.data_obj_code, v_data_pri_info.data_value; EXIT WHEN v_list%NOTFOUND;
CREATE OR REPLACE FUNCTION F_SYS_DATA_PRI_VALUE(V_USER_ID IN VARCHAR2,
V_DATA_OBJ_ID IN VARCHAR2)
RETURN DATA_PRI_INFO_TABLE
PIPELINED AS v_data_pri_info data_pri_info := data_pri_info(NULL, NULL, NULL);
TYPE v_cursor IS REF CURSOR;
v_list v_cursor;
v_str VARCHAR2(10000) := ' ';
v_count NUMBER := 1;
CURSOR v_sql1 IS
SELECT DISTINCT t2.data_obj_code,
t3.data_group_col,
t1.data_group_value,
t2.data_obj_pri_col
FROM t_sys_user_data_pri t1, t_sys_data_obj t2, t_sys_data_group t3
WHERE t1.data_obj_id = t2.data_obj_id
AND t1.data_group_id = t3.data_group_id
AND t1.user_id = v_user_id
AND t1.data_obj_id = v_data_obj_id ;
CURSOR v_sql2 IS
SELECT DISTINCT t2.data_obj_code,
t3.data_group_col,
t1.data_group_value,
t2.data_obj_pri_col
FROM t_sys_user_data_pri t1, t_sys_data_obj t2, t_sys_data_group t3
WHERE t1.data_obj_id = t2.data_obj_id
AND t1.data_group_id = t3.data_group_id
AND t1.user_id = v_user_id ;
BEGIN
IF v_data_obj_id <> '0' THEN FOR v_list IN v_sql1 LOOP
IF v_count = 1 THEN
v_str := 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',' || v_list.data_obj_pri_col || ' FROM ' ||
v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
v_count := v_count + 1;
ELSE
v_str := v_str || ' UNION ALL ' || 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',' || v_list.data_obj_pri_col || ' FROM ' ||
v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
END IF;
END LOOP;
ELSE
FOR v_list IN v_sql2 LOOP
IF v_count = 1 THEN
v_str := 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',TO_CHAR(' || v_list.data_obj_pri_col || ') FROM ' ||
v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
v_count := v_count + 1;
ELSE
v_str := v_str || ' UNION ALL ' || 'SELECT '''||V_USER_ID||''','''||v_list.data_obj_code||''',TO_CHAR(' || v_list.data_obj_pri_col || ') FROM ' ||
v_list.data_obj_code || ' WHERE ' || v_list.data_group_col ||' = (''' || v_list.data_group_value || ''')';
END IF;
END LOOP;
END IF;
OPEN v_list FOR v_str;
LOOP
FETCH v_list
INTO v_data_pri_info.user_id,
v_data_pri_info.data_obj_code,
v_data_pri_info.data_value;
EXIT WHEN v_list%NOTFOUND;
PIPE ROW(v_data_pri_info);
END LOOP;
CLOSE v_list; RETURN;END;
/