高级sql学习
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
18.MODEL 语句 ································································································· 78 19.10G 闪回查询 ································································································· 78 20.专题—行列转换 ······························································································ 78 21.专题—连续值和累计值问题················································································ 78 22.专题—NULL 和 DUAL 详解··············································································· 79
第 3 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com 17.1 背景和目标······························································································· 72 17.2 经常使用的扩展························································································· 72 17.3 向多个目标 INSERT ······················································································ 73 17.4 EXTERNAL TABLES(外部表) ············································································· 77
Oracle advanced sql by dj mailto:dingjunlove@163.com 高级oracle sql编程.txt
作者
文档名称 发布时间
Oracle Advanced Sql 2010/1/21
ห้องสมุดไป่ตู้说明
本文档脚本都经调试通过，10g R2 环境，如果
调试不通过，可能是版本过低，详细见文档
7.1 CASE 简介 ··································································································· 40 7.2 两种 CASE ·································································································· 41 7.3 综合实例 ··································································································· 42 8.ROWNUM—TOP-N 分析 ··················································································· 44 9.相关子查询和非相关子查询 ················································································· 49 10.增强的 GROUP BY ·························································································· 50 11.分析函数(ANALYTICAL FUNCTIONS)································································ 51 11.1 背景知识·································································································· 51 11.2 分析函数基础···························································································· 51 11.3 分析函数的使用························································································· 60 12.ROWID 的使用 ······························································································· 71 13. ORACLE10G 正则表达式 ················································································· 71 14.使用 HINT ····································································································· 71 15.PARITION 分区 ······························································································ 71 16.并行操作 ······································································································· 71 17.扩展 DDL 和 DML 语句 ···················································································· 72
修订次数
共次
丁俊(个人)版权所有
时 间 1/21/2010 时间 时间
版本 V1.0
日期
修订号 描述
文档修订摘要 著者
审阅者 批准人/日期
第 2 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com
目录
1.集合操作 ··········································································································· 5 1.1.UNION 和 UNION ALL························································································· 5 1.2.INTERSECT 交集操作 ························································································ 6 1.3. MINUS 差集操作····························································································· 6 1.4.集合操作实例································································································ 7
22.1 NULL 详解 ································································································· 79 22.1.1 null 基本知识················································································································ 79
4.MERGE INTO 合并资料····················································································· 17 5.层次查询(HIERARICAL RETRIVAL)···································································· 20 6.DECODE 函数和行列互换 ·················································································· 32 7.CASE 表达式 ··································································································· 40
2.EXISTS 和 NOT EXISTS 的使用 ············································································ 9 2.1. EXISTS 的使用 ······························································································· 9 2.2 NOT EXISTS 的使用························································································· 10
3.WITH 子句·······································································································11 3.1 WITH 基础 ····································································································11 3.2 11G R2WITH 新特性······················································································· 16 3.3 WITH 对执行计划的影响 ················································································· 16
Note:本资料未经作者同意禁止用于商业用途，转载请告知作者，谢谢！
第 1 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com
Advanced Sql In Oracle
编写 审核 审批 文档管 理
页码 编号
共页
丁俊 丁俊 （职务）
第 3 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com 17.1 背景和目标······························································································· 72 17.2 经常使用的扩展························································································· 72 17.3 向多个目标 INSERT ······················································································ 73 17.4 EXTERNAL TABLES(外部表) ············································································· 77
Oracle advanced sql by dj mailto:dingjunlove@163.com 高级oracle sql编程.txt
作者
文档名称 发布时间
Oracle Advanced Sql 2010/1/21
ห้องสมุดไป่ตู้说明
本文档脚本都经调试通过，10g R2 环境，如果
调试不通过，可能是版本过低，详细见文档
7.1 CASE 简介 ··································································································· 40 7.2 两种 CASE ·································································································· 41 7.3 综合实例 ··································································································· 42 8.ROWNUM—TOP-N 分析 ··················································································· 44 9.相关子查询和非相关子查询 ················································································· 49 10.增强的 GROUP BY ·························································································· 50 11.分析函数(ANALYTICAL FUNCTIONS)································································ 51 11.1 背景知识·································································································· 51 11.2 分析函数基础···························································································· 51 11.3 分析函数的使用························································································· 60 12.ROWID 的使用 ······························································································· 71 13. ORACLE10G 正则表达式 ················································································· 71 14.使用 HINT ····································································································· 71 15.PARITION 分区 ······························································································ 71 16.并行操作 ······································································································· 71 17.扩展 DDL 和 DML 语句 ···················································································· 72
修订次数
共次
丁俊(个人)版权所有
时 间 1/21/2010 时间 时间
版本 V1.0
日期
修订号 描述
文档修订摘要 著者
审阅者 批准人/日期
第 2 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com
目录
1.集合操作 ··········································································································· 5 1.1.UNION 和 UNION ALL························································································· 5 1.2.INTERSECT 交集操作 ························································································ 6 1.3. MINUS 差集操作····························································································· 6 1.4.集合操作实例································································································ 7
22.1 NULL 详解 ································································································· 79 22.1.1 null 基本知识················································································································ 79
4.MERGE INTO 合并资料····················································································· 17 5.层次查询(HIERARICAL RETRIVAL)···································································· 20 6.DECODE 函数和行列互换 ·················································································· 32 7.CASE 表达式 ··································································································· 40
2.EXISTS 和 NOT EXISTS 的使用 ············································································ 9 2.1. EXISTS 的使用 ······························································································· 9 2.2 NOT EXISTS 的使用························································································· 10
3.WITH 子句·······································································································11 3.1 WITH 基础 ····································································································11 3.2 11G R2WITH 新特性······················································································· 16 3.3 WITH 对执行计划的影响 ················································································· 16
Note:本资料未经作者同意禁止用于商业用途，转载请告知作者，谢谢！
第 1 页 共 90 页
Oracle advanced sql by dj mailto:dingjunlove@163.com
Advanced Sql In Oracle
编写 审核 审批 文档管 理
页码 编号
共页
丁俊 丁俊 （职务）