一些实用SQL语句

(1)统计一个表的列数

SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('表名')

例如 ：SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('lbinsured')

(2)Oracle 取随机记录数

取随机数（效率最好）
select * from table_name sample(5);
select * from (select * from table_name sample(7)) where rownum <51

取随机数（效率稍差）
select * from (
select dbms_utility.get_hash_value(to_char(dbms_utility.get_time)||a.riskcode,1,1048576) rand_num,a.* from lmrisk a order by rand_num
) where rownum  <=5;

取4条随机记录（效率最差）
select * from (select * from lmrisk order by trunc(dbms_random.value(0,1000)))
where rownum<5;

(3)删除大批量数据

truncate table ldcode