Oracle开发笔记1

        常用脚本：
    --1. 循环插入测试语句：

declare
  i int:=1;
begin
    while i<=1000 loop
       insert into t_b values(i,'I love xian'||i);
       i := i+1;
       if (i mod 100 = 0) then
          commit;
       end if;
    end loop;
end;
/

   

* 关于job中的next_date的时间设定：

每1小时： TRUNC(SYSDATE,'hh')+1/24

每2小时： TRUNC(SYSDATE,'hh')+2/24

每1天的七点：  trunc(sysdate,'dd')+1+7/24

 

每个月的十点： add_months(trunc(sysdate,'mm'),1)+10/24

 

    *. PL/SQL (Procedural Language/SQL )是Oracle在标准SQL语言上的过程性扩展。    
    *. 服务名和SID分别是什么概念。
        A: 对应就是DataBase名称，因为Oracle数据库没有Database/Schema的概念，对应的是实例。安装Oracle可以安装多个实例。 [SID]即为具体的数据库实例名。
    * SQL*Plus的语法是 sqlplus user/password @server
       server是网络服务名，或者称连接标识符

            sqlplus  username/password@servicename

    *. 字符串连接采用 || 符号。例如：
       update serviceinfo  
       set wsdlurl = 'http://exchange.xchg.sz.gov/iexchange/services/' || servicename || '?wsdl'

    *. NVL函数用于将NULL变为实际值，其语法格式为 NVL(exp1,exp2)

    *. LIKE操作符的 通配符包括 %和_

    3. 在Oracle的数据库建表中，默认会创建index(index的column值是表主键)。

   4.  当为Oracle创建表的主键或者外键时，如果没有制定外键/主键的名称，则系统会默认起名sys_xxxx的名字。

    5. Oracle服务器更改IP后 listener服务无法启动的解决方案：
       修改服务器端的listen.ora,更改客户端的tnsnames.ora即可。
       一般路径在( E:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\ 下)

   6.  TOAD软件显示乱码问题的解决：
        新建系统环境变量NLS_LANG ，变量值为AMERICAN_AMERICA.ZHS16GBK．

   7. PS/SQL中，使用&可以定义临时变量。

 

      高级查询——

     集合操作符
    UNION ALL,UNION,INTERSECT,MINUS

UNION ALL 是两个集合的简单合并；

UNION则是在合并的基础上进行排序和筛选，删除重复的记录，所以效率比较低。

    其他使用函数
    TRANSLATE(x,from_string,to_string)

DECODE(value,search_value,result,default_value)

  

使用CASE表达式来替代多个查询    

select
count( case when price <13 then 1 else null end ) low,
count( case when price between 13 and 15 then 1 else null end ) med,
count( case when price >15 then 1 else null end ) high
from products;

 

   游标
    

1. 游标的作用

   当需要对获取结果集的每一行记录，进行复杂逻辑的判断。这时候就需要用到游标。例如"MERGE_UD_PAIR"这个存储过程

   游标比批量更新Update的优势：对回滚段的耗用更少。

 

2. 游标的类型和属性

       显示游标和隐式游标。

       对于DML语句和单行SELECT INTO语句来说，Oracle会为她们分配隐式游标。

 

       4种属性:

       %FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN

3. 游标的使用步骤

      

        

4. 对游标的动态参数的赋值

       类似 DBMS_SQL.BIND_VARIABLE(cursor_name8, ':v_imei', v_imei);

    简单示例代码1（包含动态语句）：
    

------------------------------------------

---- 游标以及动态语句的应用

-- SQL处理逻辑：

-- 1. 创建fix_XX_TABLE表，里面有正确数据的IMEI,MANU_ID。(PS:建议加上主键索引)

-- 2. 通过游标方式从fix_XX_TABLE表中取数据，然后一条条地去update超大表XX_TABLE(有一亿数据量以上)

-- 3. 动态SQL语句则使用 using来对参数赋值

------------------------------------------

drop table fix_XX_TABLE;

create table fix_XX_TABLE as 

select t1.imei,t2.manu_id from XX_TABLE t1,model t2

where t1.model_id=t1.manu_id and t2.model_id=t1.model_id ;

 

 

declare

cursor p_cur is select imei,manu_id from fix_XX_TABLE;

v_imei  varchar2(30);

v_manu_Id  number;

 

i number default  0;

begin

write_run_log('update_XX_TABLE','update XX_TABLE start','0');

open p_cur;

loop

  fetch p_cur into v_imei,v_manu_id;

  exit when p_cur%notfound;

  execute immediate 'update XX_TABLE set manu_id=:v_manu_id where imei=:v_imei' 

  using v_manu_id,v_imei;

  i:=i+1;

  if mod(i,10000)=0 then commit;

  write_run_log('update_XX_TABLE','commit times='||to_char(i/10000),sqlcode);

  end if;

end loop;

write_run_log('update_XX_TABLE','update XX_TABLE successed',sqlcode);

exception when others then 

write_run_log('update_XX_TABLE','update XX_TABLE failed',sqlerrm);

end;

/

    简单示例代码2 
     

------------------------------------------

---- 游标以及动态语句的应用

-- SQL处理逻辑：

-- 1. 创建fix_XX_TABLE表，里面有正确数据的IMEI,MANU_ID。(PS:建议加上主键索引)

-- 2. 通过游标方式从fix_XX_TABLE表中取数据，然后一条条地去update超大表XX_TABLE(有一亿数据量以上)

-- 3. 动态SQL语句则使用 using来对参数赋值

------------------------------------------

drop table fix_XX_TABLE;

create table fix_XX_TABLE as 

select t1.imei,t2.manu_id from XX_TABLE t1,model t2

where t1.model_id=t1.manu_id and t2.model_id=t1.model_id ;

 

 

declare

cursor p_cur is select imei,manu_id from fix_XX_TABLE;

v_imei  varchar2(30);

v_manu_Id  number;

 

i number default  0;

begin

write_run_log('update_XX_TABLE','update XX_TABLE start','0');

open p_cur;

loop

  fetch p_cur into v_imei,v_manu_id;

  exit when p_cur%notfound;

  execute immediate 'update XX_TABLE set manu_id=:v_manu_id where imei=:v_imei' 

  using v_manu_id,v_imei;

  i:=i+1;

  if mod(i,10000)=0 then commit;

  write_run_log('update_XX_TABLE','commit times='||to_char(i/10000),sqlcode);

  end if;

end loop;

write_run_log('update_XX_TABLE','update XX_TABLE successed',sqlcode);

exception when others then 

write_run_log('update_XX_TABLE','update XX_TABLE failed',sqlerrm);

end;

/

    

 

     ResultSet与游标：

ResultSet就是结果集游标。

Connection创建Statement有一个方法是：Statement createStatement(int resultSetType, int resultSetConcurrency) 。

第一个参数是结果集游标类型，第二个参数是结果集的并发类型：

     * @param resultSetType a result set type; one of
     *        <code>ResultSet.TYPE_FORWARD_ONLY</code>,
     *        <code>ResultSet.TYPE_SCROLL_INSENSITIVE</code>, or
     *        <code>ResultSet.TYPE_SCROLL_SENSITIVE</code>
     * @param resultSetConcurrency a concurrency type; one of
     *        <code>ResultSet.CONCUR_READ_ONLY</code> or
     *        <code>ResultSet.CONCUR_UPDATABLE</code>

 

一般地读取大数据量，则设置为：

ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)；

若是使用分页读取结果集。则设置为：

ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)；