使用oracle的一些心得

本文介绍了SQL编程中的实用技巧和最佳实践,包括表创建规范、数据查询优化、常见函数应用及复杂问题解决策略,如去除重复数据和实现高效分页。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

创建表时注意事项:

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

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

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

4.       创建良好的关系。

创建查询时注意事项:

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

2.       INBETWEENUPPER 和函数等采用大写

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.       oraclesubstr 函数的用法
 
取得字符串中指定起始位置和长度的字符串    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 ,可配合 TRIMLTRIMRTRIM 去除所有要过滤掉的字符)
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 的用法, nvlarg,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.       日期的列必须使用时必须 ToDatestring ’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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值