使用oracle的一些心得

创建表时注意事项：

1.       表名和列名使用有意义且容易记忆的英文单词

2.       表名大写，列名大写，统一使用下划线

3.       使用单数名称定义列名。

4.       创建良好的关系。

创建查询时注意事项：

1.       select ,from , where .,order by, having,group by 正文中采用小写

2.       IN ， BETWEEN ， UPPER 和函数等采用大写

3.       列名 pascal 大小写形式

4.       表名采用大写
cerate table NEWS{

Feature VARCHAR2(15) not null

};

快速复制一个表和数据：

1.         create table TEST3 as select * from TEST

2.         insert into TEST3 select * from TEST

创建序列：

create sequence JT_J_SPXX_SEQ
minvalue 1
maxvalue 9999999999999999
start with 1
increment by 1
cache 20
cycle ;

一些函数的应用：

1.       连接 2 个列
select spxxid||spbh as aa ,txm from jt_j_spxx
select CONCAT(spxxid,spbh) as aa ,txm from jt_j_spxx

2.       select RTRIM(LastName,'''"$*"') from test
sql 里的单引号用 2 个单引号表示
select TRIM(LastName) from test //trim 只能 trim 单个字符，不能是字符组， 格式也不同 TRIM （ ’”’ ， from LastName ）

3.       INSTR 方法的格式为
INSTR( 源字符串 , 目标字符串 , 起始位置 , 匹配序号 ) // 起始位置为非下标，而是实际位置
例如： INSTR('CORPORATE FLOOR','OR', 3, 2) 中，源字符串为 'CORPORATE FLOOR', 目标字符串为 'OR' ，起始位置为 3 ，取第 2 个匹配项的位置。
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
所以 SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL 的显示结果是
Instring
——————
14

4.       oracle 的 substr 函数的用法
  取得字符串中指定起始位置和长度的字符串    substr( string, start_position, [ length ] )
  如 : // 起始位置为非下标，而是实际位置
     substr('This is a test', 6, 2)     would return 'is'
     substr('This is a test', 6)     would return 'is a test'
     substr('TechOnTheNet', -3, 3)     would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The'

5.       清除左侧的字母（不能直接用 LTRIM ，可配合 TRIM 、 LTRIM 、 RTRIM 去除所有要过滤掉的字符）
select DECODE(SUBSTR(LastName,1,3),'THE',SUBSTR(LastName,4,Length(LastName)-3),LastName) from test

6.       nvl 用法
通过查询获得某个字段的合计值，如果这个值位 null 将给出一个预设的默认值 select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1 这里关心的 nvl 的用法， nvl （ arg,value ）代表如果前面的 arg 的值为 null 那么返回的值为后面的 value 。
　　另一个有关的有用方法
　　 declare 
　　 i integer
　　 select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1 这样就可以把获得的合计值存储到变量 i 中，如果查询的值为 null 就把它的值设置为默认的 1 。

7.       日期的列必须使用时必须 ToDate （ string ， ’YYYY-MM-DD’ ）一下

SQL 语句删除表中重复字段 :

1 、查找表中多余的重复记录，重复记录是根据单个字段（ peopleId ）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2 、删除表中多余的重复记录，重复记录是根据单个字段（ peopleId ）来判断，只留有 rowid 最小的记录
delete from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3 、查找表中多余的重复记录（多个字段）  
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4 、删除表中多余的重复记录（多个字段），只留有 rowid 最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5 、查找表中多余的重复记录（多个字段），不包含 rowid 最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

比方说在 A 表中存在一个字段 “name” ，而且不同记录之间的 “name” 值有可能会相同，
现在就是需要查询出在该表中的各记录之间， “name” 值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

触发器：

1.       例

create or replace trigger "TRI_JT_J_SPXX_UPDATE"
 before update on jt_j_spxx 
 for each row
declare
  -- local variables here
begin
 begin
    Update jt_webk_spflmx Set pm=:New .pm,nrjj=:New .spjj,DJ=:New .DJ,xj=nvl(:New .WSJG,:new .dj) Where spxxid = :New .spxxid;
 end ;
 begin
    update JT_WEBK_PHFLMX set pm=:new .pm,dj=:new .dj,xj=nvl(:new .wsjg,:new .dj) where spxxid = :new .spxxid;
 end ;
 begin
    update JT_WEBK_ROOMSPFLMX set pm=:new .pm,nrjj=:new .spjj,dj=:new .dj,xj=nvl(:new .wsjg,:new .dj) where spxxid = :new .spxxid;
 end ;
 begin
    update JT_WEBK_ROOMSPMX set pm=:new .pm,nrjj=:new .spjj,dj=:new .dj,xj=nvl(:new .wsjg,:new .dj) where spxxid = :new .spxxid;
 end ;
 begin
    Update jt_webk_dzdmx Set pm=:New .pm,nrjj=:New .spjj,DJ=:New .DJ,xj=nvl(:New .WSJG,:new .dj) Where spxxid = :New .spxxid;
 end ;
end TRI_JT_J_SPXX_UPDATE;

分页存储过程：

procedure PagePro
 (
 iPagesize number , -- 每页记录数
 iCurrentpage number , -- 当前页码
 strSql varchar2 ,    -- 最终语句
 iRecordCount out number , -- 总记录数
 iPagenum out number ,  -- 输出总页码
 oFlag out number ,    -- 返回操作标志
 v_cur out refCursor,
 errDesc out varchar2
)
as
 tmpSql varchar2 ( 8000 );
 newSql varchar2 ( 8000 );
 tmpNum number ( 9 , 2 );
 tmpRow number ;
 tmpPageSize number ;
 pos number ;
 autoPageNumber number ;
begin

 iRecordCount := 0 ;
 iPagenum := 1 ;
-- execute immediate 'select count(1) from ('|| tmpSql ||')' into iRecordCount;
     tmpSql := 'select rownum iRow,a.* from (' || strSql || ') a' ;
 execute immediate 'select count(1) from (' || tmpSql || ')' into iRecordCount;

 tmpSql:= 'select * from (select rownum iRow,a.* from (' || strSql || ') a)' ;
  --execute immediate tmpSql;


  --p_return_count@xhsd(strSql,iRecordCount);

   if iRecordCount= 0 then   -- 没有记录
    oFlag:= 16 ;
    iPagenum:= 0 ;
    --cailintai2006 －3－3修改以下的内容
    /*tmpSql := upper(strSql);
    pos := instr(tmpSql,' WHERE ');
    if pos > 0 then
       newSql := substr(tmpSql,0,pos-1);-- 取得查询语句where前面的选择语句
       tmpSql := newSql || ' where rownum < 1';--加上条件
    else
        tmpSql := strSql;
    end if;
    open v_cur for tmpSql; */ -- 返回空纪录
    open v_cur for  strSql;
    return ;
 end if ;

 if iPagesize= 0 then
    -- select SETVALUE into autoPageNumber from sysparameter@demotemplate where parametername='@@AutoPageSize';
     tmpPageSize:= 1000 ;
 else
     tmpPageSize:=iPagesize;
 end if ;

 if iRecordCount<tmpPageSize then   -- 所有记录只有一页
    oFlag:= 17 ;
    iPagenum:= 1 ;
        open v_cur for strSql;
    return ;
 end if ;

  -- 计算总页数
 tmpNum:=iRecordCount/tmpPageSize;
 if tmpNum>trunc(tmpNum) then
    iPagenum:=trunc(tmpNum)+ 1 ;
 else
    iPagenum:=trunc(tmpNum);
 end if ;
 oFlag:= 18 ;
 if (iCurrentpage>iPagenum or iCurrentpage< 1 ) then   -- 页码不对
    oFlag:= 19 ;
    oFlag:= 3 ;   -- 页码不对
    open v_cur for 'select 1 from dual' ;
    return ;
 end if ;

  -- 计算第一个iRow
 if iCurrentpage= 1 then
    tmpRow:= 1 ;
    tmpPageSize:=tmpPageSize+ 1 ;
    tmpSql:=tmpSql || ' where rownum<' || tmpPageSize || ' and iRow>=' || tmpRow;
 else
    tmpRow:=(iCurrentpage- 1 )*tmpPageSize+ 1 ;
    tmpPageSize:=tmpPageSize+ 1 ;
    tmpSql:=tmpSql || ' where rownum<' || tmpPageSize || ' and iRow>=' || tmpRow;
 end if ;
  --tmpPageSize := tmpRow + iPageSize + 1;

 open v_cur for tmpSql;
 oFlag:=iPagenum;

 exception
    when others then
      begin
        --oFlag:=0;   -- 操作失败
        open v_cur for 'select 1 from dual where rownum<1' ;
        errDesc := sqlerrm ;
      end ;
end ;

存储过程调用存储过程

CREATE   OR   REPLACE   PROCEDURE   A  
  (  
      an_in1               IN           NUMBER,  
      an_in2               IN           NUMBER,  
      avc_out1           OUT         VARCHAR2,  
      avc_out2           OUT         VARCHAR2  
  )  
  begin  
  ...  
  end  
  -------------------------------------------------------  
  CREATE   OR   REPLACE   PROCEDURE   B  
  (  
      an_in1               IN           NUMBER,  
      an_in2               IN           NUMBER,  
      avc_out1           OUT         VARCHAR2,  
      avc_out2           OUT         VARCHAR2)  
  begin  
  ...  
   
  A(an_v1,an_v2,avc_out1,avc_out2);  
   
  end;

 出自---http://www.cnblogs.com/yuanws/archive/2007/09/24/904411.html