请教一个oracle存储过程的算法问题

Question

需要实现一个通用的函数，即以字符串的形式返回一个表一整行的数据内容，其中字段之间以‘+’分隔，遇到空值的话就是‘++’，请问怎么做呢？

Answer 1

||连起来，然后用decode函数，not null就是原值，null就是+

Answer 2

比较复杂哦
比如date字段怎么处理,number字段用什么格式等等等

Answer 3

都当做字符串连起来，就好 id||'+'||name||'+'||sex||'+'||age一样，只是不知道字段的数量和连接方法呃

Answer 4

1楼的就是思路.
不过问题就出在你这个通用上了.是否考虑特殊类型字段的处理,还是都是varchar2字段?
关键是你自己的处理逻辑要清楚.

Answer 5

我只会select * ，但是这样木有用，无法按照‘+’分隔写进字符串哈

Answer 6

>.<木分数提问了，支援下贫农呗具体写下来就是 to_char（decode(列明,null,‘+'，列名)）

Answer 7

其实列名，类型，列的个数取得都不是问题
可以在[SYS.ALL_COLL_TYPES]里面查，
建一个course，查询就可以了。关键在结果显示的顺序换个思路吧，从建表的script文着手

Answer 8

顺序可以按user_tab_cols.column_id.
但是你要怎么展现的规则才是重点.因为不是每种类型都是字符串的.
那么问题来了.
你是不现实非字符串类型还是对非字符串类型做转换,如果转换,你转换的规则是什么?

Answer 9

-- 要考虑的问题很多：
-- *(1) 日期类型的字段的输出格式化问题(以什么样的格式显示输出？)
-- *(2) CLOB、BLOB字段应该不能包含在内
......

Answer 10

-- 先写成存储过程，楼主去测试一下：
create or replace procedure table_record_proc(v_tb_name varchar2,o_record out sys_refcursor)
is
  v_cnt number(18,0);
  v_sql VARCHAR2(4000);
  v_column_name user_tab_columns.column_name%type;
  v_egnore VARCHAR2(4000); -- 忽略的字段
  v_data_type user_tab_columns.data_type%type;
  v_record sys_refcursor;
  cursor c_tb(v_tableName IN user_tab_columns.table_name%type)
      is select column_name, data_type 
           from user_tab_columns 
          where table_name = v_tableName 
            and data_type not in ('LONG','XMLTYPE','LONG RAW','RAW','CLOB','BLOB','NCLOB','NBLOB','ROWID') -- 排除这些字段类型
          order by column_id asc;
begin
  SELECT COUNT(1) INTO v_cnt FROM user_tables WHERE table_name=upper(v_tb_name);
  v_sql := '';
  v_egnore := '';
  IF v_cnt=0 THEN
    OPEN o_record FOR SELECT '对不起：您搜索的表在当前用户 '||user||' 中不存在，请核对!' FROM DUAL;  -- 给游标变量赋值
  ELSE
    FOR r_tb IN c_tb(v_tb_name) LOOP
      v_column_name := r_tb.column_name;
      v_data_type := r_tb.data_type;
      IF v_data_type like 'DATE%' or v_data_type like 'TIMESTAMP%' THEN -- 如果是日期类型的字段，格式化输出
        v_sql := v_sql||'to_char('||v_column_name||',''YYYY-MM-DD HH24:MI:SS'')||''+''||';
      ELSIF v_data_type like 'CHAR%' OR v_data_type like 'NCHAR%' OR v_data_type like 'VARCHAR%' THEN
        v_sql :=v_sql||v_column_name||'||''+''||';
      ELSIF v_data_type like 'NUMBER%' THEN
        v_sql :=v_sql||'to_char('||v_column_name||')||''+''||';
      ELSE
        v_egnore :=v_egnore||' '||'Column Name: '||v_column_name||' DataType: '||v_data_type||'; ';
      END IF;
    END LOOP;
    v_sql := substr(v_sql,1,length(v_sql)-7);
    v_sql := 'SELECT '||v_sql||' FROM '||v_tb_name;
    OPEN o_record FOR v_sql;
  END IF;
  dbms_output.put_line(v_sql);
  dbms_output.put_line(v_egnore);
end;
/set serveroutput on;
var c_cur refcursor;
exec table_record_proc('EMP',:c_cur);
print c_cur;

Answer 11

create or replace procedure emptest(tab   in varchar2,
                                    o_cur OUT SYS_REFCURSOR) is
begin
  declare
  
    Result varchar2(500);
    strSql varchar2(500);
  
    cursor mycur is
      select column_name,
             data_type,
             data_length,
             data_precision,
             data_scale
        from user_tab_columns
       where table_name = upper(tab);
  
  begin
  
    Result := '';
  
    for cur in mycur loop
      if Result <> ' ' then
        Result := Result || ' || ''+'' || ' || cur.column_name;
      else
        Result := Result || cur.column_name;
      end if;
    end loop;
  
    Result := ' select ' || Result || ' as a from ' || tab;
  
    if length(Result) <> 0 or Result <> ' ' or Result is not null then
      open o_cur for strSql;
    end if;
  
  end;end emptest;做成方法是不行的，可以做成存储过程

Answer 12

SQL> create or replace type type_line_rec is table of varchar2(4000);
  2  /
 
Type created
 
SQL> 
SQL> create or replace function fun_connect(v_tb varchar2) return type_line_rec
  2  as
  3  res_arr type_line_rec:=type_line_rec();
  4  str_dt varchar2(1000):='';
  5  str_zf varchar2(1000):='';
  6  str_number varchar2(1000);
  7  str varchar2(4000);
  8  begin
  9    for i in
 10      (select column_name,data_type from all_tab_cols
 11      where table_name=upper(v_tb) and data_type not in ('LONG','XMLTYPE','LONG RAW','RAW','CLOB','BLOB','NCLOB','NBLOB','ROWID'))
 12      loop
 13      if i.data_type in('DATE','TIMESTAMP') then
 14      str_dt:=str_dt||'nvl(to_char('||i.column_name||',''yyyy-mm-dd hh24:mi:ss''),''++'')'||'||''+''||';
 15      elsif i.data_type in('VARCHAR','VARCHAR2','CHAR','NVARCHAR2','NVARCHAR','NCHAR') THEN
 16        str_zf:=str_zf||'nvl('||i.column_name||',''++'')'||'||''+''||';
 17        else
 18          str_number:=str_number||'nvl(to_char('||i.column_name||',''fm999,999,999,999.90''),''++'')'||'||''+''||';
 19      end if;
 20     end loop;
 21        str:='select '||str_dt||str_zf||substr(str_number,1,length(str_number)-7)||' from '||v_tb;
 22      execute immediate str bulk collect into res_arr;
 23        return res_arr;
 24        end;
 25  /
 
Function created
 
SQL> select * from table(fun_connect('emp'))
  2  /
 
COLUMN_VALUE
--------------------------------------------------------------------------------
1980-12-17 00:00:00+CLERK+SMITH+20.00++++800.00+7,902.00+7,369.00
1981-02-20 00:00:00+SALESMAN+ALLEN+30.00+300.00+1,600.00+7,698.00+7,499.00
1981-02-22 00:00:00+SALESMAN+WARD+30.00+500.00+1,250.00+7,698.00+7,521.00
1981-04-02 00:00:00+MANAGER+JONES+20.00++++2,975.00+7,839.00+7,566.00
1981-09-28 00:00:00+SALESMAN+MARTIN+30.00+1,400.00+1,250.00+7,698.00+7,654.00
1981-05-01 00:00:00+MANAGER+BLAKE+30.00++++2,850.00+7,839.00+7,698.00
1981-06-09 00:00:00+MANAGER+CLARK+10.00++++2,450.00+7,839.00+7,782.00
1987-04-19 00:00:00+ANALYST+SCOTT+20.00++++3,000.00+7,566.00+7,788.00
1981-11-17 00:00:00+PRESIDENT+KING+10.00++++5,000.00++++7,839.00
1981-09-08 00:00:00+SALESMAN+TURNER+30.00+.00+1,500.00+7,698.00+7,844.00
1987-05-23 00:00:00+CLERK+ADAMS+20.00++++1,100.00+7,788.00+7,876.00
1981-12-03 00:00:00+CLERK+JAMES+30.00++++950.00+7,698.00+7,900.00
1981-12-03 00:00:00+ANALYST+FORD+20.00++++3,000.00+7,566.00+7,902.00
1982-01-23 00:00:00+CLERK+MILLER+10.00++++1,300.00+7,782.00+7,934.00
 
14 rows selected
 

Answer 13

 create or replace type type_line_rec is table of varchar2(4000);
create or replace function fun_connect(v_tb varchar2) return type_line_rec
as
res_arr type_line_rec:=type_line_rec();
str_dt varchar2(1000):='';
str_zf varchar2(1000):='';
str_number varchar2(1000);
str varchar2(4000);
begin
  for i in
    (select column_name,data_type from all_tab_cols
    where table_name=upper(v_tb) and data_type not in ('LONG','XMLTYPE','LONG RAW','RAW','CLOB','BLOB','NCLOB','NBLOB','ROWID'))
    loop
    if i.data_type in('DATE','TIMESTAMP') then
    str_dt:=str_dt||'nvl(to_char('||i.column_name||',''yyyy-mm-dd hh24:mi:ss''),''++'')'||'||''+''||';
    elsif i.data_type in('VARCHAR','VARCHAR2','CHAR','NVARCHAR2','NVARCHAR','NCHAR') THEN
      str_zf:=str_zf||'nvl('||i.column_name||',''++'')'||'||''+''||';
      else
        str_number:=str_number||'nvl(to_char('||i.column_name||',''fm999,999,999,999.90''),''++'')'||'||''+''||';
    end if;
   end loop;
      str:='select '||str_dt||str_zf||substr(str_number,1,length(str_number)-7)||' from '||v_tb;
    execute immediate str bulk collect into res_arr;
      return res_arr;
      end;

Answer 14

完善下 加个OWNERcreate or replace function fun_connect(v_tb varchar2) return type_line_rec
as
res_arr type_line_rec:=type_line_rec();
str_dt varchar2(1000):='';
str_zf varchar2(1000):='';
str_number varchar2(1000);
str varchar2(4000);
begin
  for i in
    (select column_name,data_type,owner from all_tab_cols
    where table_name=upper(v_tb) and data_type not in ('LONG','XMLTYPE','LONG RAW','RAW','CLOB','BLOB','NCLOB','NBLOB','ROWID'))
    loop
    if i.data_type in('DATE','TIMESTAMP') then
    str_dt:=str_dt||'nvl(to_char('||i.column_name||',''yyyy-mm-dd hh24:mi:ss''),''++'')'||'||''+''||';
    elsif i.data_type in('VARCHAR','VARCHAR2','CHAR','NVARCHAR2','NVARCHAR','NCHAR') THEN
      str_zf:=str_zf||'nvl('||i.column_name||',''++'')'||'||''+''||';
      else
        str_number:=str_number||'nvl(to_char('||i.column_name||',''fm999,999,999,999.90''),''++'')'||'||''+''||';
    end if;
   end loop;select 'select '||str_dt||str_zf||substr(str_number,1,length(str_number)-7)||' from '||owner||'.'||v_tb into str
 from all_tables where table_name=upper(v_tb)
      execute immediate str bulk collect into res_arr;
      return res_arr;
      end;

Answer 15

你可以使用Oracle xml的特性来完成，例如:--设置时间格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';SELECT rtrim(EXTRACT(COLUMN_VALUE,'//text()'),'+') 
FROM TABLE (XMLSEQUENCE(extract(APPENDCHILDXML(
     XMLTYPE(CURSOR(SELECT * FROM user_tables WHERE  ROWNUM<5))
     ,'//ROW/*',XMLTYPE('<a>+</a>')),'//ROW')));SQL> 
 
Session altered
 
RTRIM(EXTRACT(COLUMN_VALUE,'//
--------------------------------------------------------------------------------
SUPPLIERS+TS_CORPDM_APDP_DATA+VALID+20+70+1+255+262144+262144+1+2147483645+0+1+1
BUYER_COMPANY_INFO+TS_CORPDM_APDP_DATA+VALID+20+80+1+255+65536+262144+1+21474836
CSR_QUESTIONS+TS_CORPDM_APDP_DATA+VALID+30+70+1+255+262144+262144+1+2147483645+0
CSR_QUESTION_ANSWERS+TS_CORPDM_APDP_DATA+VALID+20+80+1+255+65536+262144+1+214748