Oracle笔记.docx
《Oracle笔记.docx》由会员分享，可在线阅读，更多相关《Oracle笔记.docx（29页珍藏版）》请在冰豆网上搜索。
Oracle笔记
查看当前用户所有表：
Select*fromtab;
连接符：
||
空值：
isnull
除去重复行：
distinct
查询结果排序：
orderby排序字段asc（desc）
比较运算符：
><（!
=or<>）betweenand
in操作notin
模糊查询Like使用：
“%”：
代表匹配任意长度的任意字符。
“_”：
代表匹配一个长度的任意字符。
特殊字符使用ESCAPE标示：
select*fromposgoodswherepgcnamelike'%*_%'escape'*';
查询posgoods表中pgcname字段中有‘_’字符的结果。
escape'*'表示*字符后面的是字符代表其实际意思，不做转义字符。
Oracle10g支持正则表达式的四个新函数分别是：
REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。
它们使用POSIX正则表达式代替了老的百分号（%）和通配符（_）字符。
特殊字符：
'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$'匹配输入字符串的结尾位置。
如果设置了RegExp对象的Multiline属性，则$也匹配'\n'或'\r'。
'.'匹配除换行符\n之外的任何单字符。
'?
'匹配前面的子表达式零次或一次。
'*'匹配前面的子表达式零次或多次。
'+'匹配前面的子表达式一次或多次。
'（）'标记一个子表达式的开始和结束位置。
'[]'标记一个中括号表达式。
'{m,n}'一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
'|'指明两项之间的一个选择。
例子'^（[a-z]+|[0-9]+）$'表示所有小写字母或数字组合成的字符串。
\num匹配num，其中num是一个正整数。
对所获取的匹配的引用。
正则表达式的一个很有用的特点是可以保存子表达式以后使用，被称为Backreferencing.允许复杂的替换能力
如调整一个模式到新的位置或者指示被代替的字符或者单词的位置.被匹配的子表达式存储在临时缓冲区中，缓冲区从左到右编号,通过\数字符号访问。
下面的例子列出了把名字aabbcc变成cc,bb,aa.
SelectREGEXP_REPLACE（'aabbcc','（.*）（.*）（.*）','\3,\2,\1'）FROMdual；
REGEXP_REPLACE（'ELLENHILDISMIT
cc,bb,aa
'\'转义符。
字符簇：
[[:
alpha:
]]任何字母。
[[:
digit:
]]任何数字。
[[:
alnum:
]]任何字母和数字。
[[:
space:
]]任何白字符。
[[:
upper:
]]任何大写字母。
[[:
lower:
]]任何小写字母。
[[unct:
]]任何标点符号。
[[:
xdigit:
]]任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
（）,（?
（?
=）,[]圆括号和方括号
*,+,?
{n},{n,},{n,m}限定符
^,$,\anymetacharacter位置和顺序
|“或”操作
--测试数据
createtabletest（mcvarchar2（60））;
insertintotestvalues（'112233445566778899'）;
insertintotestvalues（'221133445566778899'）;
insertintotestvalues（'331122445566778899'）;
insertintotestvalues（'441122335566778899'）;
insertintotestvalues（'551122334466778899'）;
insertintotestvalues（'661122334455778899'）;
insertintotestvalues（'771122334455668899'）;
insertintotestvalues（'881122334455667799'）;
insertintotestvalues（'991122334455667788'）;
insertintotestvalues（'aabbccddee'）;
insertintotestvalues（'bbaaaccddee'）;
insertintotestvalues（'ccabbddee'）;
insertintotestvalues（'ddaabbccee'）;
insertintotestvalues（'eeaabbccdd'）;
insertintotestvalues（'ab123'）;
insertintotestvalues（'123xy'）;
insertintotestvalues（'007ab'）;
insertintotestvalues（'abcxy'）;
insertintotestvalues（'Thefinaltestisisishowtofindduplicatewords.'）;
commit;
一、REGEXP_LIKE
select*fromtestwhereregexp_like（mc,'^a{1,3}'）;
select*fromtestwhereregexp_like（mc,'a{1,3}'）;
select*fromtestwhereregexp_like（mc,'^a.*e$'）;
select*fromtestwhereregexp_like（mc,'^[[:
lower:
]]|[[:
digit:
]]'）;
select*fromtestwhereregexp_like（mc,'^[[:
lower:
]]'）;
SelectmcFROMtestWhereREGEXP_LIKE（mc,'[^[:
digit:
]]'）;
SelectmcFROMtestWhereREGEXP_LIKE（mc,'^[^[:
digit:
]]'）;
二、REGEXP_INSTR
SelectREGEXP_INSTR（mc,'[[:
digit:
]]$'）fromtest;
SelectREGEXP_INSTR（mc,'[[:
digit:
]]+$'）fromtest;
SelectREGEXP_INSTR（'Thepriceis$400.','\$[[:
digit:
]]+'）FROMDUAL;
SelectREGEXP_INSTR（'onetwothree','[^[[:
lower:
]]]'）FROMDUAL;
SelectREGEXP_INSTR（',,,,,','[^,]*'）FROMDUAL;
SelectREGEXP_INSTR（',,,,,','[^,]'）FROMDUAL;
三、REGEXP_SUBSTR
SELECTREGEXP_SUBSTR（mc,'[a-z]+'）FROMtest;
SELECTREGEXP_SUBSTR（mc,'[0-9]+'）FROMtest;
SELECTREGEXP_SUBSTR（'aababcde','^a.*b'）FROMDUAL;
四、REGEXP_REPLACE
SelectREGEXP_REPLACE（'JoeSmith','（）{2,}',','）ASRX_REPLACEFROMdual;
SelectREGEXP_REPLACE（'aabbcc','（.*）（.*）（.*）','\3,\2,\1'）FROMdual
四个函数是:
regexp_like.regexp_instr.regexp_substr.与regexp_replace.它们在用法上与oraclesql函数like.instr.substr与replace用法,但是它们使用posix正则表达式代替了老的百分号（%）与通配符（_）字符.
单行函数
Upper（Str1）
将Str1串的内容转换为大写。
selectupper（'Abc'）fromdual;
Lower（Str1）
将Str1串的内容转换为小写。
selectlower（'ABC'）fromdual;
Initcap（Str1）
将Str1字符串第一个字母转换为大写的
selectinitcap（'abc'）fromdual;
Concat（Str1,Str2）
将Str1和Str2连接起来。
selectconcat（'abc','123'）fromdual;
select'111'||'222'fromdual;
Substr（Str1,index[,length]）
获得一个子字符串，截取字符串Str1从index位置开始，长度为length的子字符串
selectsubstr（'abcde',2,5）fromdual;
Length（Str1）
获得Str1字符串的长度。
selectlength（'1231321'）lengthfromdual;
Replace（Str1,old,new）
将字符串Str1中old字符串替换为new字符串
selectreplace（'name','a','B'）fromdual;
Instr（Str1,str[,n]）
获取第n个字符串str在Str1中位置。
selectinstr（'HelloWorld','or'）fromdual;
Lpad（Str1,length,str）;
在字符串Str1左边填充str至长度为length。
selectlpad（'Stream',10,'+12'）fromdual;
Rpad（Str1,length,str）;
在字符串Str1右边填充str至长度为length。
selectrpad（'Stream',10,'+'）fromdual;
Trim（Str1）
过滤字符串str1两边的空格。
selecttrim（'1231'）fromdual;
数值函数：
Round（Num,n）
将数值Num按n位精确度进行四舍五入
selectround（123.126,-2）fromdual;
Mod（Num1,Num2）
将数值Num1按Num2取模。
selectmod（13,5）fromdual;
Trunc（Num1,n）
将数值Num1按n位截取。
selecttrunc（123.356,1）fromdual;
日期函数：
Months_between（date1，date2）
比较日期date1和date2之间相差的月份。
selectmonths_between（sysdate,rqsj）fromsalehead;
Add_Months（date1,n）
在日期date1上添加n月。
selectadd_months（sysdate,1）fromdual;
Next_day（date1,’星期一’）
下个星期一的时间。
selectnext_day（sysdate,'星期一'）fromdual;
Last_day（date1）
当前日期月份的最后一天。
selectlast_day（sysdate）fromdual;
转换函数
To_char（Str1，mode）
将Str1（可以是日期，数值）按mode模式转换成字符串
selectto_char（sysdate,'yyyy-mm-dd'）fromdual;
fm表示转换的日期中月份或日期第一个值若为0，则不显示
selectto_char（sysdate,'fmyyyy-mm-dd'）fromdual;
将数值按指定格式显示，9代表数字，L代表本地化钱币符号
selectto_char（27892342,'L99,999,999'）fromdual;
selectto_char（127892342,'999,999,999'）fromdual;
获得指定日期对应的星期（星期日，1代替，其它以此类推）
selectto_char（sysdate+2,'D'）fromdual;--每星期以星期日为起始日期，数字1代表
To_number（str1）
将str1字符串转换成数值
selectto_number（'123'）+to_number（'12'）fromdual;
To_date（str1,mode）
将字符串str1按mode模式转换成日期
selectto_date（'2014101212:
14:
23','yyyyMMddHH:
mi:
ss'）fromdual;
HH24中24代表24小时制
selectto_date（'2005-01-0113:
14:
20','YYYY-MM-ddHH24:
mi:
ss'）fromdual;
通用函数：
NVL（str,default）
当Str代表的对象或者字符串的内容为空，则返回默认的default值。
selectcode,paravalue,NVL（memo,'Ye'）fromsysparawherecode='13'orcode='OC'orderbycodedesc;
NVL2（str，expr1,expr2）
当str指定的内容为空（null）时，返回expr2的值，当不为NULL时，返回expr1的内容。
selectcode,paravalue,NVL2（memo,'Stream','Liu'）fromsysparawherecode='13'orcode='OC'orderbycodedesc;
COALESCE（expression_1,expression_2,...,expression_n）
依次参考各参数表达式，遇到非null值即停止并返回该值。
如果所有的表达式都是空值，最终将返回一个空值。
使用COALESCE在于大部分包含空值的表达式最终将返回空值。
selectcoalesce（null,null,null,3）fromdual;
CASE表达式
selectcode,
name,
casecode
when'13'then
'北京店'
when'14'then
'201'
else
'未知参数'
endvalue
fromsyspara
Decode（value,if1,then1,if2,then2,if3,then3,...else）
当value值等于if1，则返回then1的值，当value值等于if2，则返回then2...否则返回默认值。
selectcode,name,decode（code,'13','北京店','14','201','未知参数'）fromsyspara;
分组函数：
Count（）求查询内容的条数
Sum（）求字段对应值的合计
Avg（）求字段对应值的平均值
Max（）求字段对应值中最大的值
Min（）求字段对应值中最小值
GROUPBY分组:
只有表中某个字段存在重复的内容才有可能考虑到分组
selectcatid,count（catid）,sum（lsj）,Round（avg（lsj）,2）AVG,min（lsj）MIN,max（lsj）MAx
fromBusiness_goodsgroupbycatidhavingavg（lsj）>200;
selects.mkt,s.paravalue,i.count,i.sum
fromsysparas,
（selectb.mktmkt,count（b.mkt）count,sum（b.lsj）SUM
fromBusiness_goodsb
whereb.mkt<>'101'
groupbyb.mkt
havingsum（b.lsj）>10000
orderbySUMasc）i
wheres.code='13'
ands.mkt=i.mkt;
selects.mkt,s.paravalue,i.sum,i.min,g.name
fromsysparas,
（selectmkt,sum（lsj）sum,min（lsj）min
frombusiness_goods
groupbymkt）i,
business_goodsg
wheres.code='13'
ands.mkt=i.mkt
andi.min=g.lsj;
1.如果SQL语句使用了分组函数，则有两种可以使用的情况：
*SQL语句中存在了GROUPBY分组条件，则可以将分组条件一起查询出来。
Selectmkt,count（mkt）fromBusiness_goodsgroupbymkt;
*如果不使用GROUPBY分组条件，则只能单独的使用分组函数。
Selectsum（lsj）,avg（lsj）fromBusiness_goods;
2.在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。
3.在SELECT列表中的字段，如果不包含在分组函数中，那么该字段必须同时出现在GROUPBY子句中。
包含在GROUP子句中的字段则不必出现在SELECT列表中
多表查询
1.笛卡尔集：
Select*fromempty,dept;
2.等值连接:
Selectempno,ename,sal,emp.deptno,dnamefromemp,deptwhereemp.deptno=dept.deptno;
3.集合操作
*UNION：
并集，所有的内容都查询，重复的显示一次。
Select*fromempUNIONselect*fromemp20
*UNIONALL：
并集，所有的内容都显示，包括重复的
*INTETSECT：
交集，只显示重复的
*MINUS:
差集，只显示对方没有的（跟顺序是有关系的）
序列，同义词
createsequenceTEST
minvalue1
maxvalue9999999
startwith1
incrementby1
cache20;
--Createsequence
createsequenceTEST
minvalue1--最小值
maxvalue9999999--最大值
startwith1--开始值
incrementby1--增长值
cache20;--缓存大小
Selecttest.nextvalfromdual;
Selecttest.currvalfromdual;
必须先有nextval，才能有currval
同以词
Createsynonymdeptforsocct.dept;--（创建私有，创建者才能使用）
Dropsynonymdept;
Createpublicsynonymdeptforsocct.dept;（公有）
Droppublicsynonymdept;
PL/SQL块
PL/SQL块，类似编程代码块
DECLARE
声明部分
BEGIN
逻辑处理
EXCEPTION
END;
PL/SQL可接受用户的输入。
输入信息使用“&”表示。
emp.empno%TYPE:
表示以emp表中的empno字段的类型定义变量。
表示定义一个变量，变量类型和emp表中的empno字段的类型定义的变量一致。
Deptrdept%rowtype:
表示定义一个接收dept的行数据的变量。
常见预定义异常
ORACLE定义了他们的错误编号和异常名字，常见的预定义异常处理Oracle常见的错误
NO_DATA_FOUNDSELECT...INTO...时，没有找到数据
DUL_VAL_ON_INDEX试图在一个有惟一性约束的列上存储重复值
CURSOR_ALREADY_OPEN试图打开一个已经打开的游标
TOO_MANY_ROWSSELECT...INTO...时，查询的结果是多值
ZERO_DIVIDE零被整除
OTHERS其他未知错误
在 PL/SQL 中使用 SQLCODE, SQLERRM异常处理函数获得异常信息
whenNO_DATA_FOUNDthen
dbms_output.put_line（'NO_DATA_FOUNDcode:
'||SQLCODE||'error:
'||
SQLERRM）;
错误号
异常错误信息名称
说明
ORA-0001
Dup_val_on_index
违反了唯一性限制
ORA-0051
Timeout-on-resource
在等待资源时发生超时
ORA-0061
Transaction-backed-out
由于发生死锁事务被撤消
ORA-1001
Invalid-CURSOR
试图使用一个无效的游标
ORA-1012
Not-logged-on
没有连接到ORACLE
ORA-1017
Login-denied
无效的用户名/口令
ORA-1403
No_data_found
SELECT INTO没有找到数据
ORA-1422
Too_many_rows
SELECT INTO 返回多行
ORA-1476
Zero-divide
试图被零除
ORA-1722
Invalid-NUMBER
转换一个数字失败
ORA-6500
Storage-error
内存不够引发的内部错误
ORA-6501
Program-error
内部错误
ORA-6502
Val
升级会员 