Oracle 开发中

1.动态游标中，varchar2参数拼装sql语句时，出现的问题

问题：正常varchar2参数拼装sql语句时，其他游标都能正常执行，唯独动态游标，他执行的是实际语句，所以在拼装语句时，varchar2参数必须两边加单引号

query_table := query_table||' and dim_buss_type='||proname;

解决：加上双引号，在oracle中’单引号可以作为转义，就是说‘’=‘，即在所以想加’单引号的地方加上‘’双引号，拼装出来的语句就可以转义了

query_table := query_table||' and dim_buss_type='''||proname||'''';

2.今天在加一个唯一约束的时候，发现重复值

问题：如何查找到重复的值

解决：select code from bi_kpi_define group by code having count(code)>=2

3.bulk collect 执行select 插入自定义object类型集合

问题：查询的类型与自定义类型不同，即使object定义字段跟查询字段一模一样，也无法into

解决：

a.增加自定义object ：create or replace type type_o_test is object (  id int,name varchar2(100))

b.添加object类型：type type_arr_o is table of type_o_test;

r type_arr_o;

c.增加子查询并用object类型（type_o_test）实例化接收

select type_o_test(id, name) bulk collect
    into r
    from (select 1 id, 'lzf' name
            from dual
          union all
          select 2, 'jame' from dual);

注：这样查询和接收的类型统一了，也就能正常跑了

4.forall执行

问题：forall执行insert，没有数据插入

forall y in qe_meta_wap_res.first .. qe_meta_wap_res.last
      insert into qe_meta_wap_emp values qe_meta_wap_res (y);

解决：forall又不是DDL，甚至不是SQL，本身不能commit，显示commit

5.修改集合数据

问题：把集合一条记录赋值给相同类型，修改该类型，并不会显示到集合中

--------------定义-------------

type kpi_res_tab is table of bi_kpi_result%rowtype index by pls_integer;
kpi_res kpi_res_tab;

bi_kpi  bi_kpi_result%rowtype;

-------------修改-------------

bi_kpi :=kpi_res(3);

bi_kpi.value := 19; --此处修改的值不能体现到kpi_res中

解决：

-------------修改-------------

bi_kpi :=kpi_res(3);

kpi_res(3).value ：=  bi_kpi.value + 19; --此处修改的值能体现到kpi_res中

6.控制分组后，每个需要聚合列的值

问题：如下，针对v_test表，需要对TEST_NO列进行聚合计算，并且TEST_NO<0的列值不参与计算

select trunc(avg(TEST_NO),4) TEST_NO from v_test group by task_his_id,wap_test_id

解决：聚合函数内部使用case表达式做判断，虽然TEST_NO不是分组列

select trunc(avg(case when TEST_NO >= 0 then TEST_NO end),4) TEST_NO from v_test group by task_his_id,wap_test_id

7.经纬度，根据基站四向偏移坐标

问题：四向偏移，需要经度、纬度各自随机加减一个数，数范围(0.0001,0.0009)或者(-0.0009,0.0001)，需要做出++，--，+-，-+四种计算，不要繁杂的if判断

解决：

--经度随机正负偏移量，(0.0001,0.0009)或者(-0.0009,0.0001)范围

select DBMS_RANDOM.VALUE(-9, 9)/10000 into val_a from dual;

--纬度随机正负偏移量，(0.0001,0.0009)或者(-0.0009,0.0001)范围

select DBMS_RANDOM.VALUE(-9, 9)/10000 into val_b from dual;

lng_out := lng_in + val_a; --基站经度+经度随机正负偏移量
lat_out := lat_in + val_b; --基站纬度+纬度随机正负偏移量

8.小数的使用

问题：使用pls_integer类型去接受带小数的数值，导致被自动四舍五入

解决：改用number接受，使用pls_integer是因为该类型参与计算更快

9.Oracle定时任务，不能执行

问题：JOB任务不执行

解决：

a.user_jobs   中看看你的job   状态,   是否broken了,是否   next_date是   合适的?
b.检查你的   job_queue_process   参数,是否大于0

（显示：show parameter job_queue_process; 设置：ALTER SYSTEM SET job_queue_processes = 10;）
c.检查你的数据库,是否是处于受限模式
d.alert   log   中查一些,是否有job   的什么报错? 

10.PL/SQL Developer调试时，一直停着不动

问题：for update锁表

解决：调试的时候，把所有事物全部回滚或者提交

select * from bi_job_log for update 锁表

11.SQL实现timestamp模糊搜索

问题：不使用to_char转换日期格式，进行模糊搜索

解决：select * from qe_meta_wap t where t.test_start_time like '%'||to_date('2011-11-18','yyyy-MM-dd')|| '%'

12.date或者timestamp类型转换中文日期类型

问题：date或者timestamp类型转换成‘yyyy年mm月dd日’ 汉字类型

解决：

date->yyyy年mm月dd日:

select to_char(to_date('2010-05-23','yyyy-mm-dd'),'yyyy"年"MM"月"dd"日"')from dual; --2010年05月23日

timestamp->yyyy年mm月dd日:

2010年05月23日

注意：timestamp类型转换成‘yyyy年mm月dd日’，必须先转date，否则日期显示为2010年05月23，例如下面，

select to_char(to_timestamp('2010-05-23','yyyy-mm-dd hh24:mi:ss'),'yyyy"年"MM"月"dd"日"') from dual

13.生成连续日期，连续数字

问题：生成连续日期，连续数字

解决：

select (first + rownum - 1) r_num
  from (select 1 first, 25 last from dual)
connect by rownum <= last - first

14.对应每天每小时，展示汇总数据，不存在的输出0

问题：全连的话有可能出现空值的情况，连接不上

解决：单独生成一天每个日期，作为一列，与数据集进行union all计算，最后对整个语句进行group by求和

select m.h_date,
       sum(users) users,
       sum(trafficUpPackets) trafficUpPackets,
       sum(trafficDownPackets) trafficDownPackets,
       sum(trafficUpBytes) trafficUpBytes,
       sum(trafficDownBytes) trafficDownBytes,
       sum(attachTotal) attachTotal,
       sum(attachSuccess) attachSuccess,
       sum(activePdpTotal) activePdpTotal,
       sum(activePdpSuccess) activePdpSuccess,
       sum(raUpdateTotal) raUpdateTotal,
       sum(raUpdateSuccess) raUpdateSuccess
  from (select to_date(20120111 || ' ' || (first + rownum - 2),
                       'yyyy-mm-dd hh24') h_date,
               0 users,
               0 trafficUpPackets,
               0 trafficDownPackets,
               0 trafficUpBytes,
               0 trafficDownBytes,
               0 attachTotal,
               0 attachSuccess,
               0 activePdpTotal,
               0 activePdpSuccess,
               0 raUpdateTotal,
               0 raUpdateSuccess
          from (select 1 first, 25 last from dual)
        connect by rownum <= last - first
        union all
        select to_date(t.time_seg, 'yyyy-mm-dd hh24') h_date,
               sum(t.users) as users,
               sum(t.traffic_up_packets) trafficUpPackets,
               sum(t.traffic_down_packets) trafficDownPackets,
               sum(t.traffic_up_bytes) trafficUpBytes,
               sum(t.traffic_down_bytes) trafficDownBytes,
               count(t.attach_total) attachTotal,
               count(t.attach_success) attachSuccess,
               count(t.active_pdp_total) activePdpTotal,
               count(t.active_pdp_success) activePdpSuccess,
               count(t.ra_update_total) raUpdateTotal,
               count(t.ra_update_success) raUpdateSuccess
          from qe_gprs_kpi t
         where t.ci = 25076
           and t.time_seg = 20120111
         group by to_date(t.time_seg, 'yyyy-mm-dd hh24')) m
 group by m.h_date

14.更新数据时，使用SEQ注意

问题：各组test_case_seq更新为序列值时，同样一组test_case_seq应该相同，但是结果却是序列的顺序值，各个都不同了。

例如：

for i in 1 .. test_case_seq_res.count loop
    update qe_meta_wap_2
       set test_case_seq = wap_bus_case_seq.nextval
     where test_case_seq = test_case_seq_res(i);
  end loop;

解决：因为update是按行执行，虽然是更新是一批，这样每次执行都会触发序列自增，解决办法是把序列单独存储，这样就只调用一边。

例如：

for i in 1 .. test_case_seq_res.count loop
    select wap_bus_case_seq.nextval into a from dual;
  
    update qe_meta_wap_2
       set test_case_seq = a
     where test_case_seq = test_case_seq_res(i);
  end loop;

15.小数缺0

问题：oracle 数据库字段值为小于1的小数时，使用char类型处理，会丢失小数点前面的0 
  例如0.35就变成了.35 

解决:用to_char函数格式化数字显示 
select  to_char(0.338,'fm9999999990.00') from dual; 
  结果:0.34 
这里重点要看 fm9999999999.99,表示整数部分最多10位,小数部分2位,fm表示去 掉转位字符串后前面的空格,不加fm,0.34前面会有空格的. 

16.NULL值参与的计算

问题：id = 1242210的数据存在，但是加上dns_resv_state <> 1就消失了

select *
  from my_test
 where id = 1242210
   and dns_resv_state <> 1

解决：因为id = 1242210的这条记录dns_resv_state为空，所有NULL值参与的计算都是空，NULL只适用于is null 或者 is not null等语句

select *
  from my_test
 where id = 1242210
   and dns_resv_state is null

17.NULL值参与的排序

Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
使用语法如下：
--将nulls始终放在最前
select * from zl_cbqc order by cb_ld nulls first
--将nulls始终放在最后
select * from zl_cbqc order by cb_ld desc nulls last

18.cast(attach_comp_time as date)进行时间类型转换时，是根据timestamp类型的毫秒数进行四舍五入（500毫秒算1秒）