对字符串中的字母或数据去重

--自定义类型,管道函数可以返回多行数据,所以不能使用基础数据类型
create or replace type typ_split as table of varchar2 (4000);

--创建管道函数
create or replace function cvg_split(p_string varchar2,
                                     p_flag   varchar2 default ',')
    return typ_split --返回自定义类型
    pipelined is     --管道函数关键字
    idx      pls_integer;
    v_string varchar2(4000) := p_string;
begin
    loop
        idx := instr(v_string, p_flag);
        if idx > 0 then
            pipe row(substr(v_string, 1, idx - 1)); --管道函数返回一行使用
            v_string := substr(v_string, idx + length(p_flag));
        else
            pipe row(v_string); --管道函数返回一行使用
            exit;
        end if;
    end loop;
end;

--使用管道函数
select * from table(cvg_split('A,A,BN,I,I,99'));

--使用管道函数,去重合并为一行
select to_char(wm_concat(distinct column_value)) from table(cvg_split('A,A,BN,I,I,99'));

--------------------------------------------------------------------------------------

--合并为一行
select to_char(xmlagg(xmlparse(content t.fld_nm || ',' wellformed) order by t.fld_nm).getclobval())
      ,to_char(rtrim(xmlagg(xmlelement(e,t.fld_nm,',').extract('//text()')).getclobval(),','))
      ,listagg(t.fld_nm,',')within group(order by t.fld_nm) as pol_list --超过4000报错
  from tfield_values t
 group by t.fld_nm;

--------------------------------------------------------------------------------------

--并行处理大量数据，开多个JOB跑数
declare
    parameters_id varchar2(100):='213131,435345';
    cursor_wsfmdj sys_refcursor;
    sqlstr        varchar2(8000);
begin
    sqlstr := ' select a.* from test_risks a where a.newid in( ';
    for parameters_rec in (select to_char(column_value) code
                             from table(orcl_split_str(parameters_id, ','))) loop
        sqlstr := sqlstr || '''' || parameters_rec.code || '''' || ',';
    end loop;
    sqlstr := substr(sqlstr, 0, (length(sqlstr) - 1)) || ')';
    --open cursor_wsfmdj for sqlstr; --打开游标
    
    dbms_output.put_line(sqlstr);
    
    
end get_cursor_wsfmdj;
/

create or replace procedure proc_test(p_plancd varchar2) is
begin
    update test_risks a set a.vers_num = '00' where a.plan_code = 'ETB17';
    dbms_lock.sleep(3);
    insert into taml_wrk_logs values (sys_guid(), '999', sysdate);

end proc_test;
/
declare
    str_job varchar2(4000);
begin
    delete taml_wrk_logs;
    --产生5个线程调用处理数据
    for x in 1 .. 5 loop
        str_job := '
          declare
              prm_appcode number(3);
              prm_errmesg varchar2(200);
          begin
              proc_test(''' || x || ''');
          end;
        ';
    
        dbms_scheduler.create_job(job_name   => 'TEST_YZ' || x
                                 ,job_type   => 'plsql_block'
                                 ,job_action => str_job
                                 ,start_date => sysdate
                                 ,enabled    => false
                                 ,auto_drop  => true
                                 ,comments   => 'test_yz_数据同步' || x);
        dbms_scheduler.enable(name => 'TEST_YZ' || x);
        --dbms_scheduler.run_job('TEST_YZ'||x);
        dbms_output.put_line('X:' || x);
    end loop;
end;
/

select * from user_scheduler_jobs  a where upper(a.job_name) like '%TEST_YZ%' ;
select * from user_scheduler_running_jobs a where upper(a.job_name) like '%TEST_YZ%' ;


--------------------------------------------------------------------------------------

--产生序号
select rownum from dual connect by rownum<=10;

--------------------------------------------------------------------------------------

create table tb_company
(
  company_id integer primary key,
  company varchar2(256),
  up_companyid integer
);
 
insert into tb_company values (0, '总公司', null);
insert into tb_company values (1, '北京分公司', 0);
insert into tb_company values (2, '上海分公司', 0);
insert into tb_company values (3, '海淀区分部', 1);
insert into tb_company values (4, '东城区分部', 1);
insert into tb_company values (5, '黄埔区分部', 2);
insert into tb_company values (6, '静安区分部', 2);
commit;

select * from tb_company;
--递归查询SQL：
select rpad(' ', 2 * (level - 1), '-') || company company_name,
       company,
       connect_by_root company,           --返回当前节点的最顶端节点 
       connect_by_isleaf,                 --判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点  
       level,                             --伪列 表示节点深度 
       sys_connect_by_path(company, '/'), --函数显示详细路径，并用"/"分隔
       regexp_replace(sys_connect_by_path(company, '/'), '/', '', 1, 1) --函数显示详细路径，并用"/"分隔, 去掉第1个"/"
  from tb_company
 start with up_companyid is null
connect by prior company_id = up_companyid;

--递归查询SQL：
select *
  from tb_company
 start with company_id = 1
connect by prior company_id = up_companyid;


--------------------------------------------------------------------------------------

instr('源字符串','目标字符串',开始位置,第几次出现) --对某个字符串进行判断，判断其是否含有指定的字符
substr('目标字符串',开始位置,长度)                 --取得字符串中指定起始位置和长度的字符串
regexp_replace('源字符串','目标字符串','替换目标字符串',开始位置,第几次出现) --

--------------------------------------------------------------------------------------

ntile(10) over(order by 排序字段)  --平均拆分成10份
--例：
select nt,count(1)
  from (select ntile(10) over(order by id) nt,id from tcomm_status)
 group by nt;

--------------------------------------------------------------------------------------
--导出CSV简化模板
set echo off        --不展示脚本命令
set feedback off    --屏蔽展示行数
set heading off     --标题(linesize设置的值才有效果)
set linesize 30000  --每行允许的最大字符数
set pagesize 0      --不准分页
set term off        --不在屏幕上输出执行结果
set termout off     --屏蔽脚本中的命令的执行结果

spool D:\Test.csv

select '客户号,身份证号,名称' from dual
 union all
select cli_num || chr(9) || ',' || id_num || chr(9) || ',' || cli_nm 
  from tclient_details
 where rownum <= 10;

spool off
exit