Oracle_SP

#分割字符串
create or replace procedure sp_string(v_string in varchar2)
 as
  /*author:xuguoqi
  time:  2018-12-10
  usage: 输入一个以逗号分割的字符串,输出分割后的结果 如 输入:aa,bb,cc
                                                        输出:aa
                                                             bb
                                                             cc
                                                       
  */
  v_array dbms_utility.lname_array;
  v_tab binary_integer;
  v_list varchar(100):=v_string;
  valid_string exception;
  begin
   if instr(v_list,',')<=0 then 
     raise valid_string;
   else  
     dbms_utility.comma_to_table(list=>v_list,tab=>v_array,tablen=>v_tab);
      for i in 1..v_tab loop
        dbms_output.put_line(v_array(i));
       end loop;
   end if;
   exception 
           when valid_string then 
              dbms_output.put_line('只能以逗号作为分隔符,如 aa,bb,cc,dd');
           when others then 
              dbms_output.put_line(sqlerrm);
  end;
  


#util_file包的使用

create or replace directory dir2 as '/home/oracle/utlfile';
grant read,write on directory dir2 to scott;
grant execute on utl_file to scott;



create or replace procedure utl_file_use(v_str in varchar2,v_filename in varchar2)
as
/*author:xuguoqi
  time:  2018-12-10
  usage: 输入一个sql语句,在服务器上面生成一个文件
  */
 file_type utl_file.file_type;               --定义file_type
 v_dir_name varchar2(100) default 'DIR2';    --dirname
 v_check_dir varchar2(20);                   --检查dir的是否存在
 invalid_directory exception;                --定义异常
 file_name varchar2(100):=to_char( current_timestamp,'yyyymmddhh24miss');         --定义filename为时间.txt
 type v_recoed is table of varchar(4000);    --定义一个table type
 v_rc v_recoed;
 v_cur sys_refcursor;                        --声明一个游标
begin
     select nvl(DIRECTORY_NAME,'0') into v_check_dir from dba_directories where DIRECTORY_NAME=v_dir_name;
     if v_check_dir='0' then
       raise invalid_directory;
     else
         file_name :=file_name||'_ '||v_filename||'.txt';
        
         file_type :=utl_file.fopen(v_dir_name,file_name,'W',1000);         
                open v_cur for v_str;
                loop 
                  exit when v_cur%notfound;
                   fetch v_cur bulk collect into v_rc;
                    for i in v_rc.first..v_rc.last loop                                                    
                             utl_file.put_line(file_type,v_rc(i));
                    end loop;
                end loop; 
                close v_cur;         
     end if;
     utl_file.fflush(file_type);
     utl_file.fclose(file_type);
    exception 
             when invalid_directory then
                  dbms_output.put_line('检查输入的directory是否存在或者是否授予访问 读写权限');
             when others then
                  dbms_output.put_line(sqlerrm);
end;


运行结果:
[oracle@oracle utlfile]$ more 20181130113724_emp.txt
7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK



create or replace type str is table of varchar(50);
/


CREATE OR REPLACE FUNCTION random_strings (v_number IN INTEGER

)
/*author:xuguoqi
  生成长度为10的随机字符串
*/
   RETURN str
   AUTHID CURRENT_USER
IS
    string str := str();
BEGIN
    string.EXTEND (v_number);

   FOR indx IN 1 .. v_number
   LOOP
      string(indx) := DBMS_RANDOM.string ('u', 10);
   END LOOP;

   RETURN string;
END;



#把查询结果转换为列名
create table ass(acptnum varchar(30),qu_name varchar(4000),awname varchar(2000));


insert into ass values('15101232097','问题1','答案1');
insert into ass values('15101232097','问题2','答案2');
insert into ass values('15101232097','问题3','答案3');
insert into ass values('18290890601','x','x');
insert into ass values('18290890601','x1','x2');
insert into ass values('18290890601','x2','x2');
insert into ass values('18290890601','x3','x3');

select * from ass;
  acptnum     qu_name   awname                  
1	15101232097	问题1	    答案1
2	15101232097	问题2	    答案2
3	15101232097	问题3	    答案3
4	18290890601	x	        x
5	18290890601	x1	      x2
6	18290890601	x2	      x2
7	18290890601	x3	      x3


         
create or replace procedure exeute_select(v_out out sys_refcursor)
as
/*把列值转换为列名*/
c1    sys_refcursor;
v_sql VARCHAR(1000):='';
c2 ass.qu_name%type;
BEGIN
       v_sql:='select acptnum';
       open c1 for select distinct qu_name from ass;
       loop
         fetch c1 into c2;
         
         exit when c1%notfound;
         
         v_sql :=v_sql||',max(case qu_name when  '''||c2||'''   then  awname else null end ) as ' ||c2;
         
       end loop;
         close c1;
         
           v_sql :=v_sql|| ' from ass group by acptnum';
           
           open v_out for v_sql;           
  END;  


#实现效果

address    name   color

北京       苹果   绿色

北京       香蕉   黄色

北京       葡萄   紫色


address     苹果   香蕉  葡萄

北京        绿色   黄色  紫色



#parallel

oracle 批量更新大表:

select task_name,chunk_type,status from dba_parallel_execute_tasks;

grant create job to SCOTT; 
grant execute on dbms_scheduler to SCOTT;
grant execute on dbms_parallel_execute to SCOTT;


CREATE OR REPLACE PROCEDURE PARALLEL_SQL
authid current_user
AS
  l_sql_stmt VARCHAR2(1000);
  l_try NUMBER;
  l_status NUMBER;
  v_start number;
  v_end  number;
  v_task_name varchar2(100):='xugq';
  v_table_owner varchar2(100) :='SCOTT';
  v_table_name varchar2(100) :='LFN_TASK_TEST';
BEGIN
	/*如果 CREATE_CHUNKS_BY_ROWID 必须写 where rowid between :start_id and :end_id*/
  l_sql_stmt := 'UPDATE /*+ rowid (dda) */ LFN_TASK_TEST SET SAL=18000 where rowid between :start_id and :end_id';
  v_start :=dbms_utility.get_time;
  DBMS_PARALLEL_EXECUTE.CREATE_TASK (v_task_name);
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(v_task_name,v_table_owner,v_table_name, true, 100);
  
  DBMS_PARALLEL_EXECUTE.RUN_TASK(v_task_name,l_sql_stmt,DBMS_SQL.NATIVE,parallel_level => 1);
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(v_task_name);
  WHILE(l_try < 2 and (l_status != DBMS_PARALLEL_EXECUTE.FINISHED)) 
  LOOP
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK(v_task_name);
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(v_task_name);
  END LOOP;
  DBMS_PARALLEL_EXECUTE.DROP_TASK(v_task_name);
  v_end :=dbms_utility.get_time;
  DBMS_OUTPUT.PUT_LINE('执行时间:'||to_char(v_end-v_start));  
  commit;
        exception
                when others then
                     DBMS_PARALLEL_EXECUTE.DROP_TASK(v_task_name);
                     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;



#建立job 执行job 删除job 停止job.


create or replace package action_job
as
 procedure create_job;
 procedure run_job(v_job_name in number);
 procedure drop_job(v_job in number);
 procedure stop_job(v_stop_num_job number);
 procedure select_info_job(v_out out sys_refcursor);
end;

create or replace package body action_job
as
  /*新建一个Job*/
  procedure create_job
    is
       v_job_num number;               --定义job号
       v_what varchar2(150):='utl_file_use';              --要执行的sp或者函数
       v_next_date varchar2(100):='2018-12-12 18:00:00';  --第一次执行的时间
       v_interval  varchar2(100):='sysdate+1/24';  --执行时间间隔
       /*example interval=sysdate+1/24         每隔一小时
                 interval=sysdate+1/(24*60*60) 每秒钟运行一次
                 interval=sysdate+1/(24*60)    每分钟运行一次
                 interval=last_day(sysdate)+1+1/24 没月凌晨一点执行
                 interval=trunc(next_day(sysdate,'Mon'))+1/24
       
       */
       begin
           dbms_job.submit(job=>v_job_num,
                           what=>v_what||';',
                           next_date => to_date(v_next_date,'yyyy-mm-dd hh24:mi:ss'),
                           interval=>v_interval
                           );
       dbms_output.put_line('the job number is:'||v_job_num);
       end;
   /*删除Job*/
   procedure drop_job(v_job in number)
      is
      drop_num number:=v_job;     
      begin
         dbms_job.remove(drop_num);
         dbms_output.put_line('job'||drop_num||'has been deleted');
         exception 
                  when others then
                    dbms_output.put_line('please input a validate job number');
      end;
    /*运行Job*/  
    procedure run_job(v_job_name in number)
      is
      begin
           dbms_job.run(v_job_name);
           exception 
                   when others then
                     dbms_output.put_line(sqlerrm);
      end;  
    /*停止Job*/
    procedure stop_job(v_stop_num_job number)
      is
      v_broken boolean:=True;
      begin
           dbms_job.broken(v_stop_num_job,broken => v_broken);
             exception
                      when others then
                        dbms_output.put_line(sqlerrm);
      end;
      
      /*查询job相关信息*/
      procedure select_info_job(v_out out sys_refcursor)
        is 
          begin
               open v_out for select * from user_jobs;
          end;
          
                       
end action_job;



Oracle with字句:
with e as (select * from emp),
     d as (select * from dept)
 select e.ename,d.dname from e,d 
  where e.empno=7369 and d.deptno=20;


ENAME      DNAME
---------- --------------
SMITH      RESEARCH


with t(a,b) as (select 1,2 from dual)
select t.a from t;


#



declare
  type c1 is table of indeic_test.name%type index by pls_integer;
  c2 c1;
 begin
 	   c2(1):='lfn';
 	   c2(10):='xugq';
 	   forall i_index in indices of c2
 	       update indeic_test set num=12
 	       where name=c2(i_index);
 	   dbms_output.put_line(sql%rowcount);
 end;
 /

SCOTT@PROD>select * from indeic_test;

NAME              NUM
---------- ----------
lfn                12
xugq               12
bei               500
shen              900


declare
  type c1 is table of indeic_test.name%type index by pls_integer;
  c2 c1;
  type c3 is table of boolean index by pls_integer;
  c4 c3;
 begin
 	   c2(1):='lfn';
 	   c2(10):='xugq';
 	   c2(12):='null';
 	   c4(1) :=FALSE;
 	   c4(10):=TRUE;
 	   c4(12):=null;
 	   forall i_index in indices of c4 between 1 and 30
 	       update indeic_test set num=18
 	       where name=c2(i_index);
 	   dbms_output.put_line(sql%rowcount);
 end;
 /
 
 
#insert all 
insert all 
  into indeic_test(name,num)
  into indeic_test(name,num)
  select 'ab',1 from dual;




declare
   type my_table_type is table of integer;
   t1   my_table_type := my_table_type(1,2,3,4);
   t2   my_table_type := my_table_type(2,4,4,5);
   s    integer;
begin
   for i in 1..t1.count loop
      s := t1(i) + t2(i);
      if not mod(s,2)=1 then
         continue;
      end if;
      dbms_output.put_line(t1(i)||''||t2(i));
      dbms_output.put_line(s);
   end loop;
end;
/