一些复杂的sql处理

十二岁的杨

于 2024-07-25 10:28:23 发布

阅读量1.1k

点赞数 34

文章标签： sql 数据库

本文链接：https://blog.csdn.net/2301_76787626/article/details/140682040

版权

1、使用排序时会有null处理影响结果

使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。

可以这样： rank over(partition by course order by score desc nulls last)

2、删除的几种不同高效处理

删除方法：
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
使用exists较为高效，因为in使用全表扫描，在数据量很少时 in 比exists效率高
SELECT count(*) FROM T_EMP T WHERE EMPNO > 0 AND EXISTS (SELECT 1 FROM T_EMP WHERE JOB = 'MANAGER' AND EMPNO=T.EMPNO);
表连接比exists更为高效
SELECT count(T.EMPNO) FROM EMP T ,EMP B WHERE T.EMPNO > 0 AND B.JOB = 'MANAGER' AND B.EMPNO=T.EMPNO;

3、两表关联update

update customers a
set (city_name,customer_type)=(select b.city_name,b.customer_type
from tmp_cust_city b
where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)

4、查询oracle锁表

select s.username,s.sid,s.serial#,s.logon_time,o.object_name
from v$locked_object l,v$session s,dba_objects o
where l.session_id=s.sid and l.object_id=o.object_id
and lower(o.OBJECT_NAME)='a_cpt_area_ot_d_new'
order by s.logon_time

杀掉进程

alter system kill session '159,54635' ;

5、查看锁

----查看锁
SELECT a.INST_ID,a.username,decode(b.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
c.owner,c.object_name,c.object_type,
a.sid,a.serial#,a.terminal,a.machine,a.program,a.osuser
FROM gv$session a,gv$lock b,dba_objects c
WHERE b.sid = a.sid
AND b.id1 = c.object_id(+)
AND c.OBJECT_NAME like '%S_B_CDR_201609%';

6、查看正在执行的存储过程

正在执行的存过
select b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# || ''';' kill_command
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
and (a.OBJECT like upper('%存储过程名%') or
a.OBJECT like lower('%存储过程名%'))
and a.sid = b.sid
and b.status = 'ACTIVE';

7、查看和某个表有关的存储过程

SELECT * FROM ALL_SOURCE WHERE TYPE='PROCEDURE' AND UPPER(TEXT) LIKE '%TABLE_NAME%';

8、一键删除主键重复语句

删除重复主键语句
delete from edw_share.s_u_user_credit_m t
where t.rowid<(select max(rowid)
from edw_share.s_u_user_credit_m t1
where t.user_id=t1.user_id and op_time=201708
) and op_time=201708

9、一些简单语句

加字段语句-- ALTER TABLE employee ADD post varchar(20)
删除字段 alter table tablename drop (column);
update 表名 set 字段=null where 字段=某值 --即将表中字段为某值的替换为null
加分区alter table integ.i_u_user_360_view_201802 add partition P_20180203 values(20180203) tablespace tbs_data26 compress;
修改表空间（分区表） alter table integ.i_u_user_360_l_view_m move partition P_201805 tablespace TBS_DATA4 compress nologging parallel 8;
一般表 alter table integ.i_u_user_360_l_view_m move tablespace TBS_DATA4 compress nologging parallel 8;
alter table integ.i_u_user_market_label drop/truncate partition P_20180203 ;
alter tablespace tbs_data6 coalesce;
alter table table_name truncate partition partition_name drop storage;
update：
update table1 a set a.c = (select b.b from table2 b where a.a=b.a)
update table1 a set a.c = (select max(b.b) from table2 b where a.a=b.a);

10、查看表空间分区

看表空间 SELECT * FROM dba_segments t where t.segment_name like upper('%i_u_user_360_view_201802%')
查看某个分区记录数： select count(*) as 记录数 from ods_share.s_itf_mss_cw_owe_m partition(P_201802) ;
select * from dba_segments T where t.segment_name like upper('%I_U_USER_MARKET_LABEL_M%')

查看分区记录数拼接出语句
select 'select '''||partition_name||''' partition_name,min(主键列) flag from '||table_owner||'.'||table_name||' partition ('||partition_name||') union all ' from dba_tab_partitions where table_name='XXX';

alter table 表名 modify 字段名字段类型; 修改字段长度

11、查看数据库存储空间

看表存储空间

select TABLESPACE_NAME,
ROUND((t.TABLESPACE_SIZE * p.value) / 1024 / 1024 / 1024/1024,4) TABLESPACE_SIZE_GB,
ROUND((t.USED_SPACE * p.value) / 1024 / 1024 / 1024/1024,4) USED_SPACE_GB,
ROUND((t.TABLESPACE_SIZE * p.value) / 1024 / 1024 / 1024/1024,4) -
ROUND((t.USED_SPACE * p.value) / 1024 / 1024 / 1024/1024,4) free_SPACE_GB,
USED_PERCENT
from DBA_TABLESPACE_USAGE_METRICS t
INNER JOIN v$parameter p
ON p.name
= 'db_block_size'
order by 4 desc;
SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='TBS_DATA22' ORDER BY BYTES DESC ；

12、某些varchar转换数值有乱码处理

替换^E ^M
replace(a,CHR(13)||CHR(10),'')
检查数字不是数字为null
SELECT DECODE(
(REPLACE(TRANSLATE(TRIM('XX'),'0123456789','00000000000'),'0' ,NULL)),
NULL, to_number(trim('XX') ) )from dual

说明：当XX为全是数字时,如587，则返回587。
当XX不全是数字事,如58ab,则返回null。

select translate('XX','*0123456789','*') from dual
说明：当XX为全是数字时,如587，则返回null。
当XX不全是数字事,如58ab,则返回58ab。


select * from table where regexp_like(column,'^[0-9]+[0-9]$')

说明：当XX为全是数字时,如587，则返回1。
当XX不全是数字事,如58ab,则返回0。

13、查看表更新时间

select LAST_DDL_TIME from user_objects where object_type='TABLE' and object_name='表名'

14 --sqlldr命令执行语法

sqlldr ods/sdOOds_2016@odsdb_new data=OCS_MOBILE_DATA_EVENT_20180714.000000.dat control=ocs_1009.ctl log=OCS_MOBILE_DATA_EVENT_20180710.000000.dat.log bad=OCS_MOBILE_DATA_EVENT_20180710.000000.dat.bad direct=y

15恢复删除过表：

select owner,object_name,
original_name,partition_name,type,ts_name,createtime,
droptime from dba_recyclebin order by droptime desc;

flashback table table_name to before drop
flashback table table_name to before drop rename to table_name

16、Oracle将小于1的数字to_char后丢掉小数点前0

解决方法如下：关于Oracle将小于1的数字to_char后丢掉小数点前0的解决办法

select rtrim(to_char(0.11, 'fm9990.99'), '.') from dual;
1
其中
0.11为需要to_char的数字

fm去掉字符串前面的空格

如果需要永久显示两位小数，则用9990.00进行格式化

rtrim(str, '.') 去掉右侧的小数点符号[用于没有小数的整数]

17、利用spool导出数据

利用Oracle的spool工具，可以自定义导出数据的格式

/***************************
* sql脚本部分 demo.sql begin
**************************/
/**************************
* @author 12咩咩
* @date 20090519
**************************/

set pagesize 0

set arraysize 1000

set linesize 80

set numwidth 12

set echo off

set feedback off

set heading off

set termout off;

set timing off;

set trimout on;

set trimspool on;

set colsep ','

spool d:\ctltest\data_new.txt;

select user_||','||ip||','||to_char(time_,'yyyy-mm-dd hh24:mm:ss')||','||channel||','||count_||','||vers||','||part_val from basechannelvisittab;

spool off;

eof

exit
/***********************
* demo.sql end
***********************/

然后执行：

sqlplus -S "ganwu/ganwu@ora_98" @demo.sql

18、利用ctl导入文件到数据表

ctl

cat zxd_202_0.ctl
LOAD DATA
INFILE "/data/etl/ftpdata/202/AAAA"
INTO TABLE gat.g_bil_c_data_202_t_1
append
FIELDS TERMINATED BY ','
trailing nullcols
( ticket_id CHAR(1000)
, file_id CHAR(1000)
, batch_id CHAR(1000)
, switch_id CHAR(1000)
, region_id2 CHAR(1000)
, pricing_plan_id2 CHAR(1000)
, card_flag2 CHAR(1000)
, acct_item_type_id3 CHAR(1000)
, rum_type3 CHAR(1000)
, rum_value3 CHAR(1000)
, rate3 CHAR(1000)
, charge3 CHAR(1000)
, disct_charge3 CHAR(1000)
, acct_id3 CHAR(1000)
, payment_method3 CHAR(1000)
, region_id3 CHAR(1000)
, pricing_plan_id3 CHAR(1000)
, card_flag3 CHAR(1000)
, indb_file_name "BBBB"
, partition_id "to_char(sysdate,'YYYYMMDD')"

)

shell

cat zxd_202_0.sh
op_time1=$1
op_time=${op_time1:0:10}
pre_file='C1010.202'
date=${op_time1:0:8}
date_p5=`date -d "$date 1days-ago" +%Y%m%d`
echo $date
echo 's/${date}/'"${date}"'/g'
sqlplus -s ods/sdOOds_2016@odsdb_new <<EOF
alter table gat.g_bil_c_data_202_t_1 drop partition p_${date_p5} ;
alter table gat.g_bil_c_data_202_t_1 add partition p_${date} values (${date}) ;
exit;
EOF
echo "-------------$op_time finish---------------"
echo "-------------##########$pre_file finish---------------"
cd /data/etl/ftpdata/202/
#mkdir -p ${op_time}
cd /data/etl/ftpdata/202/
echo "###############-deleting ctl---------------"
rm -rf ${pre_file}*.0.rat.ctl
cd /data/etl/ftpdata/202
ls *${pre_file}*.rat |while read fn
do
cd /data/etl/ftpdata/202/
sed '1,$s/AAAA/'$fn'/g' zxd_202_0.ctl |sed '1,$s/BBBB/'$op_time'/g' >/data/etl/ftpdata/202/$fn.ctl
echo "making ctl........"$fn.ctl
cd /data/etl/ftpdata/202/
chmod 777 $fn.ctl
cd /data/etl/ftpdata/202/
echo "sql= sqlldr ods/sdOOdS_2016@odsdb_new control=$fn.ctl log=/data/etl/ftpdata/202/$fn.log bad=/data/etl/ftpdata/202/$fn.bad direct=true"
sqlldr ods/sdOOdS_2016@odsdb_new control=$fn.ctl log=/data/etl/ftpdata/202/$fn.log bad=/data/etl/ftpdata/202/$fn.bad direct=true
log_file=/data/etl/ftpdata/202/$fn.log
#cd /biapp/aiinsight/log/loadlog/
res=`grep "ORA" ${log_file}|wc -l`
echo "%%%%%%%%%%%%%res=${res}"
if [ $res -gt 0 ]
then
cd /data/etl/ftpdata/202/
echo "----------deleteing ctl file................................ " $fn.ctl
rm -rf $fn.ctl
exit 1
else
cd /data/etl/ftpdata/202/
echo "----------deleteing ctl file................................ " $fn.ctl
rm -rf $fn.ctl
echo "----------deleteing data file................................ " $fn
cd /data/etl/ftpdata/202/
rm -rf $fn
cd /data/etl/ftpdata/202/
echo "----------deleteing log file................................ " $fn.log
rm -rf $fn.log
#exit 0
fi
echo "##############$pre_file finish---------------"
done