run.sql

本人在工程上收集的比较常用的oracle sql语句 有问题请发email:[e-mail]

	SET ECHO      OFF;
	SET FEEDBACK  OFF;
	SET VERIFY    OFF;
	SET PAGESIZE  0;
	SET TERMOUT   ON;
	SET HEADING   OFF;
	echo SET LINESIZE  355;

	spool result.txt;
	--echo 手机号 时间 归属地 漫游地
	select history.condition,history.msisdn,to_char(history.stime,'yyyy-mm-dd hh24:mi:ss'),tc.name||'->'||city.name,condition.msg
	 from history,city,city tc,vlr,no,condition 
	where history.condition in(105,104)--设置要查询的消息ID
	and history.stime>sysdate-0.3 --设置时间范围
	and history.stime<sysdate-0.25
	and city.id=vlr.city_id and history.vlr=vlr.id
	and tc.id=no.city_id and trunc(mod(history.msisdn,1000000000)/10000)=no.id
	and history.errorcode=0
	and condition.id=history.condition
	order by history.condition,history.stime;

	spool off;
quit