使用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;
.net的编码问题
