ORACLE笔记语法+例子.docx

ORACLE笔记语法+例子.docx

《ORACLE笔记语法+例子.docx》由会员分享，可在线阅读，更多相关《ORACLE笔记语法+例子.docx（48页珍藏版）》请在冰豆网上搜索。

ORACLE笔记语法+例子

ORACLE笔记

一、创建用户、授权1

二、创建表（DDL）、DML1

三、常用函数2

四、行列转换9

五、Casewhen用法（两种）12

六、connectby用法（递归查询）13

七、mergeinto用法14

八、with高级用法17

九、变量定义及使用17

十、类型定义、数组定义（TYPE）18

十一、创建游标（CURSOR）18

十二、创建序列（SEQUENCE）20

十三、创建方法（FUNCTION）20

十四、创建触发器（TRIGGER）20

十五、创建存储过程（PROCEDURE）23

十六、创建包（PACKAGE）24

十七、创建定时任务（JOB）25

十八、JDBC操作28

1、创建用户、授权

connsystem/oracleassysdba;--以管理员身份登录

createusertestidentifiedbytest;--创建一个新用户dropusertestcascade;

grantconnect,resource,dbatotest;--给新建用户授权revokeconnect,resource,dbafromtest;

grantselectonscott.emptotest;--给test用户授权可以查看表scott.emp的权限

conntest/test;--连接test用户

createtablenew_empas（select*fromscott.emp）;--创建一张表，表结构和数据和scott.emp一样

-------------------表空间相关命令---------------------------------------

--创建临时表空间

createtemporarytablespaceuser_temp

tempfile'D:

\oracle\oradata\Oracle10g\user_temp.dbf'

size50m

autoextendon

next50mmaxsize20480m

extentmanagementlocal;

--创建数据表空间

createtablespacetest_data

logging

datafile'D:

\oracle\oradata\Oracle10g\user_data.dbf'

size50m

autoextendon

next50mmaxsize20480m

extentmanagementlocal;

--创建用户并指定默认表空间

createuserusernameidentifiedbypassword

defaulttablespaceuser_data

temporarytablespaceuser_temp;--指定临时表空间

--删除表空间DROPTABLESPACEdata01INCLINGCONTENTSANDDATAFILES;

--创建表同时指定表空间

createtabletab_a（column1date）tablespacets_a;

-------------------------------------------------------------------------

2、创建表（DDL）、DML

--创建表

createtableuser（

user_idnumber,

user_namevarchar2（30）,

remarkvarchar2（200）

）;

--主键：

altertabletabnameaddconstrainttabname_pkprimarykey（id）enablevalidate;

--组合主键：

altertabletabnameaddconstrainttabname_pkprimarykey（id,另一列名）enablevalidate;

--外键

altertableEMP

addconstraintFK_DEPTNOforeignkey（DEPTNO）

referencesDEPT（DEPTNO）;

--备注

commentontableuseris'用户信息表';

commentoncolumnuser.user_idis'用户编号';

--commentoncolumn表名.列名is'1-使用；2-未使用';

--修改表名

altertableold_table_namerenametonew_table_name;

--增删改查

INSERTINTOtable_name（colmun1,colmun2,...）VALUES（...）;

INSERTINTOtable_nameVALUES（...）;

DELETEFROMtable_nameWHERE条件;

UPDATEtable_nameSETTABLE.column1=?

TABLE.column2=?

WHERE条件;

SELECT*FROMtable_nameWHERE条件;

--distinct用法去除重复项

--orderby（asc、desc）用法排序

--groupby与having用法（having可有可无）分组查询

--子查询、嵌套查询、IN、NOTIN、EXISTS、NOTEXISTS等用法

--创建视图

CREATEORREPLACEviewview_t2AS

SELECTe.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.deptno

FROMempe

WHEREe.mgr=（SELECTe2.empnoFROMempe2WHEREe2.mgrISNULL）;

3、常用函数

--ORACLE内部函数篇

204.如何得到字符串的第一个字符的ASCII值

ASCII（CHAR）

SELECTASCII（'ABCDE'）FROMDUAL;

结果:

65

205.如何得到数值N指定的字符

CHR（N）

SELECTCHR（68）FROMDUAL;

结果:

D

206.如何连接两个字符串

CONCAT（CHAR1,CHAR2）

SELECTCONCAT（'ABC','DEFGH'）FROMDUAL;

结果:

'ABCDEFGH'

207.如何将列中的数值代替为字符串

DECODE（CHAR,N1,CHAR1,N2,CHAR2...）

SELECTDECODE（DAY,1,'SUN',2,'MON'）FROMDUAL;

208.INITCAP（CHAR）

将字符串CHAR的第一个字符为大写,其余为小写.

SELECTINITCAP（'ABCDE'）FROMDUAL;

209.LENGTH（CHAR）

取一字符串CHAR的长度.

SELECTLENGTH（'ABCDE'）FROMDUAL;

210.LOWER（CHAR）

将字符串CHAR全部变为小写.

SELECTLOWER（'ABCDE'）FROMDUAL;

211.LPAD（CHAR1,N,CHAR2）

用字符串CHAR2包括的字符左填CHAR1,使其长度为N.

SELECTLPAD（'ABCDEFG',10'123'）FROMDUAL;

结果:

'123ABCDEFG'

212.LTRIM（CHAR,SET）

从字符串CHAR的左边移去字符串SET中的字符,直到第一个不是SET中的字符为止.

SELECT（'CDEFG','CD'）FROMDUAL;

结果:

'EFG'

213.NLS_INITCAP（CHAR）

取字符CHAR的第一个字符大写,其余字符为小写.

SELECTNLS_INITCAP（'ABCDE'）FROMDUAL;

214.NLS_LOWER（CHAR）

将字符串CHAR包括的字符全部小写.

SELECTNLS_LOWER（'AAAA'）FROMDUAL;

215.NLS_UPPER（CHAR）

将字符串CHAR包括的字符全部大写.

SELECTNLS_UPPER（'AAAA'）FROMDUAL;

216.REPLACE（CHAR1,CHAR2,CHAR3）

用字符串CHAR3代替每一个列值为CHAR2的列,其结果放在CHAR1中.

SELECTREPLACE（EMP_NO,'123','456'）FROMDUAL;

217.RPAD（CHAR1,N,CHAR2）

用字符串CHAR2右填字符串CHAR1,使其长度为N.

SELECTRPAD（'234',8,'0'）FROMDUAL;

218.RTRIM（CHAR,SET）

移去字符串CHAR右边的字符串SET中的字符,直到最后一个不是SET中的字符为止.

SELECTRTRIM（'ABCDE','DE'）FROMDUAL;

219.SUBSTR（CHAR,M,N）

得到字符串CHAR从M处开始的N个字符.双字节字符,一个汉字为一个字符的.

SELECTSUBSTR（'ABCDE',2,3）FROMDUAL;

220.SUBSTRB（CHAR,M,N）

得到字符串CHAR从M处开始的N个字符.双字节字符,一个汉字为二个字符的.

SELECTSUBSTRB（'ABCDE',2,3）FROMDUAL;

221.TRANSLATE（CHAR1,CHAR2,CHAR3）

将CHAR1中的CHAR2的部分用CHAR3代替.

SELECTTRANSLATE（'ABCDEFGH','DE','MN'）FROMDUAL;

222.UPPER（CHAR）

将字符串CHAR全部为大写.

223.ADD_MONTHS（D,N）

将N个月增加到D日期.

SELECTADD_MONTHS（SYSDATE,5）FROMDUAL;

224.LAST_DAY（D）

得到包含D日期的月份的最后的一天的日期.

SELECTLAST_DAY（SYSDATE）FROMDUAL;

225.MONTH_BETWEEN（D1,D2）

得到两个日期之间的月数.

SELECTMONTH_BETWEEN（D1,D2）FROMDUAL;

226.NEXT_DAY（D,CHAR）

得到比日期D晚的由CHAR命名的第一个周日的日期.

SELECTNEXT_DAY（TO_DATE（'2003/09/20'）,'SATDAY'）FROMDUAL;

227.ROUNT（D,FMT）

得到按指定的模式FMT舍入到的最进的日期.

SELECTROUNT（'2003/09/20',MONTH）FROMDUAL;

228.SYSDATE

得到当前系统的日期和时间.

SELECTSYSDATEFROMDUAL;

229.TO_CHAR（D,FMT）

将日期D转换为FMT的字符串.

SELECTTO_CHAR（SYSDATE,'YYYY/MM/DD'）FROMDUAL;

230.TO_DATE（CHAR,FMT）

将字符串CHAR按FMT的格式转换为日期.

SELECTTO_DATE（'2003/09/20','YYYY/MM/DD'）FROMDUAL;

231.ABS（N）

得到N的绝对值.

SELECTABS（-6）FROMDUAL;

232.CEIL（N）

得到大于或等于N的最大整数.

SELECTCEIL（5.6）FROMDUAL;

233.COS（N）

得到N的余弦值.

SELECTCOS

（1）FROMDUAL;

234.SIN（N）

得到N的正弦值.

SELECTSIN

（1）FROMDUAL;

235.COSH（N）

得到N的双曲余弦值.

SELECTCOSH

（1）FROMDUAL;

236.EXP（N）

得到N的E的N次幂.

SELECTEXP

（1）FROMDUAL;

237.FLOOR（N）

得到小于或等于N的最小整数.

SELECTFLOOR（5.6）FROMDUAL;

238.LN（N）

得到N的自然对数.

SELECTLN

（1）FROMDUAL;

239.LOG（M,N）

得到以M为底N的对数.

SELECTLOG（2,8）FROMDUAL;

240.MOD（M,N）

得到M除以N的余数.

SELECTMOD（100,7）FROMDUAL;

241.POWER（M,N）

得到M的N幂.

SELECTPOWER（4,3）FROMDUAL;

242.ROUND（N,M）

将N舍入到小数点后M位.

SELECT（78.87653,2）FROMDUAL;

243.SIGN（N）

当N<0时,得到-1;

当N>0时,得到1;

当N=0时,得到0;

SELECTSIGN（99）FROMDUAL;

244.SINH（N）

得到N的双曲正弦值.

SELECTSINH

（1）FROMDUAL;

245.SORT（N）

得到N的平方根,N>=0

SELECTSORT（9）FROMDUAL;

246.TAN（N）

得到N的正切值.

SELECTTAN（0）FROMDUAL;

247.TANH（N）

得到N的双曲正切值.

SELECTTANH（0）FROMDUAL;

248.TRUNC（N,M）

得到在M位截断的N的值.

SELECTTRUNC（7.7788,2）FROMDUAL;

249.COUNT（）

计算满足条件的记录数.

SELECTCOUNT（*）FROMTABLE1WHERECOL1='AAA';

250.MAX（）

对指定的列求最大值.

SELECTMAX（COL1）FROMTABLE1;

251.MIN（）

对指定的列求最小值.

SELECTMIN（COL1）FROMTABLE1;

252.AVG（）

对指定的列求平均值.

SELECTAVG（COL1）FROMTABLE1;

253.SUM（）

计算列的和.

SELECTSUM（COL1）FROMDUAL;

254.TO_NUMBER（CHAR）

将字符转换为数值.

SELECTTO_NUMBER（'999'）FROMDUAL;

--ORACLE内部函数篇

255.CHARTOROWID（CHAR）

将包含外部语法ROWID的CHAR或VARCHAR2数值转换为内部的二进制语法,参数CHAR必须是包含外部语法的ROWID的18字符的字符串.

SELECTNAMEFROMBSEMPMSWHEREROWID=CHARTOROWID（'AAAAfZAABAAACp8AAO'）;

NAME:

LEIXUE

256.CONVERT（CHAR,DEST_CHAR_SET,SOURCE_CHAR_SET）

CONVERT将字符串CHAR中的字符从SOURCE_CHAR_SET标识的字符集转换为由DEST_CHAR_SET标识的字符集

SELECTCONVERT（'GroB','US7ASCII','WE8HP'）'CONVERSION'FROMPUBS;

CONVERSION:

Gross

257.HEXTORAW（CHAR）

将包含十六进制的CHAR转换为一个RAW数值.

INSERTINTOBSEMPMS（RAW_COLUMN）SELECTHEXTORAW（'7D'）FROMTEST;

258.RAWTOHEX（RAW）

将RAW数值转换为一个包含十六进制的CHAR值.

SELECTRAWTOHEX（RAW_COLUMN）'CONVERSION'FROMBSEMPMS;

CONVERSION:

7D

259.ROWIDTOCHAR（ROWID）

将一个ROWID数值转换为VARCHAR2数据类型.

SELECTROWIDFROMBSEMPMSWHEREROWIDTOCHAR（ROWID）LIKE'%BR1AAB%';

260.TO_MULTI_BYTE（CHAR）

将CHAR中的单字节转换为等价的多字节字符.

SELECTTO_MULTI_BYTE（'ASFDFD'）FROMTEST;

261.TO_SINGLE_BYTE（CHAR）

将CHAR中的多字节转换为等价的单字节字符.

SELECTTO_SINGLE_BYTE（'ASFDFD'）FROMTEST;

262.TRANSLATEUSING（TEXTUSING{CHAR_CS|NCHAR_CS}）

将文本TEXT按照指定的转换方式转换成数据库字符集和民族字符集.

其中TEXT是待转换的.

USINGCHAR_CS参数转换TEXT为数据库字符集,输出数据类型是VARCHAR2.

USINGNCHAR_CS参数转换TEXT为数据库字符集,输出数据类型是NVARCHAR2.

CREATETABLETEST（CHAR_COLCHAR（20）,NCHAR_COLNCHAR（20））;

INSERTINTOTESTVALUES（'HI','NBYE'）;

SELECT*FROMTEST;

263.DUMP（EXPR,RETURN_FORMAT,START_POSITION,LENGTH）

返回一个包含数据类型代码,字节长度等内部表示信息的VARCHAR2值.返回结果是当前数据库字符集,数据类型按照下面规定的内部数据类型的编码作为一个数字进行返回:

代码数据类型

0VARCHAR2

1NUMBER

8LONG

12DATE

23RAW

24LONGRAW

69ROWID

96CHAR

106MSSLABEL

参数RETUEN_FORMAT指定按照下面的基数表示返回的数值.

RETURN_FORMATRESULT

88进制

1010进制

1616进制

17单字符表示

如果参数RETURN_FORMAT没有指定,则按十进制表示返回.

如果参数START_POSITION和LENGTH被指定,则从START_POSITION开始的长为LENGTH的字节将被返回,缺省是返回整数表示.

SELECTDUMP（'ABC',1016）FROMTEST;

selectdump（ename,8,3,2）'example'fromempwherename='ccbzzp';

264.empty_b|clob（）

返回一个空的LOB定位符,用在初始化LOB变量,或用在INSERT及UPDATE声明去初始化LOB列或将其属性置为空.

INSERTINTOTABLE1VALUES（EMPTY_BLOB（））;

UPDATETABLE1SETCLOB_COL=EMPTY_BLOB（）;

265.BFILENAME（'DIRECTORY','FILENAME'）

返回一个BFILE定位符,相关的二进制LOB物理文件在服务器的文件系统上.目录DIRECTORY是指在服务器的文件系统上实际搜索路径全名的别名.FILENAME是指服务器的文件系统的文件名.

INSERTINTOFILE_TABVALUES（BFILENAME（'LOB_DIR','IMAGE1.GIF'））;

266.GREATEST（EXPR,EXPR,...）

GREATEST返回参数的最大值.

SELECTGREATEST（'HARRY','HARRIOT','HAROLD'）'SAMPLE'FROMTABLE1;

267.LEAST（EXPR,EXPR,...）

LEAST返回参数的最小值.

SELECTLEAST（'HARRY','HARRIOT','HAROLD'）'SAMPLE'FROMTABLE1;

268.NLS_CHARSET_DECL_LEN（BYTECNT,CSID）

返回一个NCHAR列的宽度.

SELECTNLS_CHARSET_DECL_LEN（200,NLS_CHARSET_ID（'JA16EEFDFDF'））FROMTABLE1;

269.NLS_CHARSET_ID（TEXT）

返回相应于NLS字符集名字的NLS字符集ID数.

SELECTNLS_CHARSET_D（'JADFDFFDF'）FROMTABLE1;

270.NLS_CHARSET_NAME（N）

返回相应于ID数N的NLS字符集名称.

SELECTNLS_CHARSET_NAME

（2）FROMTABLE1;

271.NVL（EXPR1,EXPR2）

若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.

SELECTNAME,NVL（TO_CHAR（COMM）,'NOTAPPLICATION'）FROMTABLE1;

272.UID

返回唯一标识当前数据库用户的整数.

SELECTUIDFROMTABLE1;

273.USER

用VARCHAR2数据类型返回当前ORACLE用户的名称.

SELECTUSER,UIDFROMTABLE1;

274.USERENV（OPTION）

返回当前的会话信息.

OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.

OPTION='LANGUAGE'返回数据库的字符集.

OPTION='SESSIONID'为当前会话标识符.

OPTION='ENTRYID'返回可审计的会话标识符.

OPTION='LANG'返回会话语言名称的ISO简记.

OPTION='INSTANCE'返回当前的实例.

SELECTUSERENV（'LANGUAGE'）FROMDUAL;

275.VSIZE（EXPR）