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