得到动态sql信息-优快云博客

本文介绍了一个使用PL/SQL编写的函数，该函数用于从数据库中获取特定条件的数据，并通过动态SQL实现数据检索。详细展示了如何打开游标、执行SQL语句及获取结果集的过程。

create or replace function fun_test(psname in varchar2,org_group_id in varchar2,model_id in varchar2,col_id in varchar2) return varchar2
is
v_cursor   number;
Result varchar2(255);
lsWhere varchar2(1000);
lsValue varchar2(1000);
lsSQL varchar2(1000);
iLen number(2);
v_process Integer;
TYPE value_array IS TABLE OF Varchar2(100) INDEX BY BINARY_INTEGER;
lsColValue value_array;
begin
   lsWhere := '';
   select query_where, query_value into lsWhere,lsValue from test where row_id=col_id;
   if then

   end if;
   iLen := length(lsValue) - length(replace(lsValue,',',''));
   lsSQL := 'select '|| lsValue ||' from test1 where a='''||psname||''' and b='||org_group_id||' and rownum < 2';
    for i in 0..iLen Loop
        lsColValue(i) := '';
    end loop;
   v_cursor:=DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_cursor,lsSQL,dbms_sql.v7);
   for i in 0..iLen loop
       DBMS_SQL.define_column_char(v_cursor,i+1,lsColValue(i),100);
   end loop;
    v_process:=DBMS_SQL.execute(v_cursor);
    loop
        if DBMS_SQL.FETCH_ROWS(v_cursor)=0   then
            exit;
        end   if;
        for i in 0..iLen Loop
            --lsColValue(i-1)
            DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor,i+1,lsColValue(i));
            lsWhere := replace(lsWhere,'$'||(i+1)||'$',''''||trim(lsColValue(i))||'''');
        end loop;
    end   loop;
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    return lsWhere;
end fun_getAdvanceConditin;