oracle存储过程，sql语句执行时间

oracle存储过程，sql语句执行时间

create or replace procedure sum_info is 
	i integer;
	temp1 varchar2(50);
	temp2 varchar2(50);
	t1 date;
	t2 date;
	v_sql varchar2(100);
	v_sqm varchar2(100);
	u_name t_temp.u_name%TYPE;
	t_name t_temp.t_name%TYPE;
	c_name t_temp.c_name%TYPE;
	cursor tb_list is 
	select u_name,t_name,c_name from t_temp;
begin
	open tb_list;
LOOP
	t1:=sysdate;
	fetch tb_list into u_name,t_name,c_name;
	v_sql:='select min('||c_name||') from '||u_name||'.'||t_name;
	v_sqm:='select max('||c_name||') from '||u_name||'.'||t_name;
	--dbms_output.put_line(v_sql);
	execute immediate v_sql into temp1;
	execute immediate v_sqm into temp2;
	t2:=sysdate;
	i:=round(to_number(t2-t1)*24*60*60);
	insert into t_ret values(u_name,t_name,c_name,temp1,temp2,i);
	commit;
	exit when tb_list%notfound;
end LOOP;
	close tb_list;
end;

　　

编写shell脚本执行存储过程

vi xx.sh

sqlplus system/oracle <<EOF
exec  sum_info ();
exit
EOF

　　

Oracle 存储过程 延迟执行 DBMS_LOCK.SLEEP(60);

 

posted @ 2018-09-05 14:11 酸奶加绿茶 阅读( ...) 评论( ...) 编辑 收藏