目录
oralce函数大全 ................................................................................................................................ 6
1.求字持串的长度LENGTH ................................................................................................... 6 2.使用SUBSTR函数从字符串中提取子串 ........................................................................... 6 3.在字符串中查找模式 ............................................................................................................ 7 4.替换字符串的一部分 ............................................................................................................ 7 5.删除字符串的空格 ................................................................................................................ 7 6.LPAD左填充函数 ................................................................................................................. 8 7.改变字符串的大小写 ............................................................................................................ 8 8.使用DECODE函数(值转换函数)转换字符串 ................................................................... 8 9.转换字符串为ASCII值 ....................................................................................................... 8 10.当前日期和时间 .................................................................................................................. 8 11.转换日期为字符串 .............................................................................................................. 9 12.转换字符串为日期 .............................................................................................................. 9 13.日期和时间 .......................................................................................................................... 9 14.计算两个日期的差值 .......................................................................................................... 9 15.在Insert\\Update等操作 ...................................................................................................... 9 16.格式化数值字段 .................................................................................................................. 9 17.将字符串转换成数字 .......................................................................................................... 9 18.内部统计函数 .................................................................................................................... 10 19.四舍五入函数和截取函数 ................................................................................................ 10 20.求最大或最小值 ................................................................................................................ 10 21.替换NULL ........................................................................................................................ 10 22.内部聚合函数 .................................................................................................................... 10 23.EXISTS .............................................................................................................................. 10 24.SELECT语句的集合操作 ................................................................................................ 10 26.创建一个简单的视图 ........................................................................................................ 11 28.用%TYPE声明一个变量 ................................................................................................. 11 29.用%ROWTYPE声明一个变量 ........................................................................................ 11 30.一些常见的控制结构 ........................................................................................................ 12 33.系统视图USER_SOURCE，保存有过程、函数、包等 ............................................... 14 34.SELECT和存储函数： .................................................................................................... 15 35.附加PL/SQL数据类型 .................................................................................................... 15 37.SQLCODE和SQLERRM ................................................................................................ 15 38.使用游标的步骤 ................................................................................................................ 15 39.说明游标 ............................................................................................................................ 16 41.从游标中取出行 ................................................................................................................ 16 42.关闭游标 ............................................................................................................................ 16
44.在oracle数据库中查询结果的行号使用伪列ROWNUM表示(从1开始)。!!!数据库分页可以使用它!!! ..................................................................................................................... 16 ABS ......................................................................................................................................... 17 ACOS ...................................................................................................................................... 17 ADD_MONTHS ..................................................................................................................... 18 Ascii ........................................................................................................................................ 18 ASIN ....................................................................................................................................... 18 ATAN ...................................................................................................................................... 19 ATAN2 .................................................................................................................................... 19 AVG ........................................................................................................................................ 19 BFILENAME(dir,file)............................................................................................................. 19 CEIL ........................................................................................................................................ 19 CHARTOROWID ................................................................................................................... 19 CHR ........................................................................................................................................ 20 CONCAT ................................................................................................................................. 20 CONVERT(c,dset,sset) ........................................................................................................... 20 COS ......................................................................................................................................... 20 COSH ...................................................................................................................................... 21 COUNT ................................................................................................................................... 21 CURRENT_DATE .................................................................................................................. 21 CURRENT_TIMESTAMP ..................................................................................................... 22 DBTIMEZONE ....................................................................................................................... 22 DECODE ................................................................................................................................ 22 DEREF .................................................................................................................................... 22 DUMP(s,fmt,start,length) ....................................................................................................... 22 EMPTY_BLOB()和EMPTY_CLOB() .................................................................................. 22 EXP ......................................................................................................................................... 22 EXTRACT .............................................................................................................................. 23 FLOOR ................................................................................................................................... 23 FROM_TZ .............................................................................................................................. 23 GREATEST ............................................................................................................................. 23 GROUPING ............................................................................................................................ 24 HEXTORAW .......................................................................................................................... 24 INITCAP ................................................................................................................................. 24 INSTR(C1,C2,I,J) ................................................................................................................... 24 INSTRB .................................................................................................................................. 24 LAST_DAY ............................................................................................................................ 24 LEAST .................................................................................................................................... 25 LENGTH ................................................................................................................................. 25 LENGTHB .............................................................................................................................. 25 LN ........................................................................................................................................... 25 LOCALTIMESTAMP ............................................................................................................. 26 LOG(n1,n2) ............................................................................................................................. 26 LOWER .................................................................................................................................. 26
LPAD(粘贴字符) .................................................................................................................... 26 LTRIM .................................................................................................................................... 27 MAKE_REF ........................................................................................................................... 27 MAX ....................................................................................................................................... 27 MIN ......................................................................................................................................... 27 MOD(n1,n2) ............................................................................................................................ 27 MONTHS_BETWEEN(date2,date1) ...................................................................................... 27 NEW_TIME(date,'this','that') .................................................................................................. 28 NEXT_DAY(date,'day') .......................................................................................................... 28 NEW_TIME ............................................................................................................................ 28 NLSSORT ............................................................................................................................... 29 NLS_CHARSET_DECL_LEN ............................................................................................... 29 NLS_CHARSET_ID ............................................................................................................... 29 NLS_CHARSET_NAME ....................................................................................................... 29 NLS_INICAP .......................................................................................................................... 29 NLS_LOWER ......................................................................................................................... 29 NLS_UPPER ........................................................................................................................... 29 NVL ........................................................................................................................................ 29 POWER ................................................................................................................................... 29 RAWTOHEX .......................................................................................................................... 30 REF ......................................................................................................................................... 30 REFTOHEX ............................................................................................................................ 30 REPLACE('string','s1','s2') ...................................................................................................... 30 ROUND .................................................................................................................................. 30 ROWID ................................................................................................................................... 31 ROWIDTOCHAR ................................................................................................................... 31 RPAD (粘贴字符) ................................................................................................................... 31 RTRIM .................................................................................................................................... 31 SESSIONTIMEZONE ............................................................................................................ 31 SIGH ....................................................................................................................................... 32 SIGN ....................................................................................................................................... 32 SIN .......................................................................................................................................... 32 SINH ....................................................................................................................................... 32 SOUNDEX ............................................................................................................................. 32 SQRT ....................................................................................................................................... 33 STDDEV ................................................................................................................................. 33 SUBSTR(string,start,count) .................................................................................................... 33 SUBSTRB ............................................................................................................................... 34 SUM ........................................................................................................................................ 34 SYS_EXTRACT_UTC ........................................................................................................... 34 SYSDATE ............................................................................................................................... 34 SYS_CONTEXT ..................................................................................................................... 34 SYS_GUID ............................................................................................................................. 35 SYSTIMESTAMP................................................................................................................... 35
TAN ......................................................................................................................................... 35 TANH ...................................................................................................................................... 36 TO_CHAR(date,'format') ........................................................................................................ 36 TO_DATE(string,'format') ...................................................................................................... 38 TO_LOB ................................................................................................................................. 38 TO_MULTI_BYTE ................................................................................................................. 38 TO_NUMBER ........................................................................................................................ 38 TO_SINGLE_BYTE ............................................................................................................... 38 TO_TIMESTAMP ................................................................................................................... 39 TO_TIMESTAMP_TZ ............................................................................................................ 39 TO_YMINTERVAL ................................................................................................................ 39 TRANSLATE(x,y,z) ................................................................................................................ 39 TRIM(keyword 's' from 'string') .............................................................................................. 39 TRUNC ................................................................................................................................... 39 TZ_OFFSET ........................................................................................................................... 40 UID ......................................................................................................................................... 40 UPPER .................................................................................................................................... 40 USER ...................................................................................................................................... 40 USEREVN .............................................................................................................................. 41 UID ......................................................................................................................................... 41 VSIZE ..................................................................................................................................... 42 VARIANCE ............................................................................................................................ 42 常用技巧......................................................................................................................................... 42
怎么把select出来的结果导到一个文本文件中？ .............................................................. 42 怎样估算SQL执行的I/O数 ? ............................................................................................ 42 常用数据词典 ................................................................................................................................. 43
DBA_ ...................................................................................................................................... 44
Dba_extents ..................................................................................................................... 44 dba_free_space ................................................................................................................ 44 dba_sys_privs .................................................................................................................. 44 dba_tables........................................................................................................................ 45 ALL_ ....................................................................................................................................... 46 USER_ .................................................................................................................................... 46 V$_视图 .................................................................................................................................. 46
v$database ....................................................................................................................... 46 V$session ........................................................................................................................ 47
Oracle优化 ..................................................................................................................................... 49
应用优化 ................................................................................................................................. 49
Like or ............................................................................................................................. 49 数据库优化 ............................................................................................................................. 49 SQL中的单记录函数 ............................................................................................................ 49 2.CHR ..................................................................................................................................... 49 3.CONCAT .............................................................................................................................. 49 4.INITCAP .............................................................................................................................. 49
5.INSTR(C1,C2,I,J) ................................................................................................................ 50 6.LENGTH .............................................................................................................................. 50 7.LOWER ............................................................................................................................... 50 8.UPPER ................................................................................................................................. 50 9.RPAD和LPAD(粘贴字符) ................................................................................................. 50 10.LTRIM和RTRIM ............................................................................................................. 51 11.SUBSTR(string,start,count) ............................................................................................... 51 12.REPLACE('string','s1','s2') ................................................................................................. 51 13.SOUNDEX......................................................................................................................... 51 14.TRIM('s' from 'string') ........................................................................................................ 52 15.ABS .................................................................................................................................... 52 16.ACOS ................................................................................................................................. 52 17.ASIN .................................................................................................................................. 52 18.ATAN ................................................................................................................................. 52 19.CEIL ................................................................................................................................... 52 20.COS .................................................................................................................................... 53 21.COSH ................................................................................................................................. 53 22.EXP .................................................................................................................................... 53 23.FLOOR .............................................................................................................................. 53 24.LN ...................................................................................................................................... 53 25.LOG(n1,n2) ........................................................................................................................ 54 26.MOD(n1,n2) ....................................................................................................................... 54 27.POWER .............................................................................................................................. 54 28.ROUND和TRUNC .......................................................................................................... 54 29.SIGN .................................................................................................................................. 54 30.SIN ..................................................................................................................................... 54 31.SIGH .................................................................................................................................. 55 32.SQRT .................................................................................................................................. 55 33.TAN .................................................................................................................................... 55 34.TANH ................................................................................................................................. 55 35.TRUNC .............................................................................................................................. 55 36.ADD_MONTHS ................................................................................................................ 56 37.LAST_DAY ....................................................................................................................... 56 38.MONTHS_BETWEEN(date2,date1) ................................................................................. 56 39.NEW_TIME(date,'this','that') ............................................................................................. 56 40.NEXT_DAY(date,'day') ..................................................................................................... 57 41.SYSDATE .......................................................................................................................... 57 42.CHARTOROWID .............................................................................................................. 57 43.CONVERT(c,dset,sset) ...................................................................................................... 57 44.HEXTORAW ..................................................................................................................... 58 45.RAWTOHEXT ................................................................................................................... 58 46.ROWIDTOCHAR .............................................................................................................. 58 47.TO_CHAR(date,'format') ................................................................................................... 58 48.TO_DATE(string,'format') ................................................................................................. 58
