#分割字符串
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;
/
继续阅读与本文标签相同的文章
上一篇 :
springmvc之pojo
-
中国接入互联网25年，这些发展成就值得点赞！
2026-05-15栏目： 教程
-
微信群聊现已上线接龙表格功能
2026-05-15栏目： 教程
-
住客扫码住酒店疑被收集信息？华住回应：不排除个别员工引导
2026-05-15栏目： 教程
-
5G网络即将来临，家里的宽带还有存在的必要吗？听听专家怎么说
2026-05-15栏目： 教程
-
禅城奇槎最快本月底将推出41亩商住地
2026-05-15栏目： 教程