使用指示符变量向ORACLE表中插入空值的问题

li602714 2009-04-02 12:04:59
各位大大:
我使用OCI接口访问ORACLE,在向表中插入记录时要根据变量值确定是否插入空值,因此我使用了指示符变量,代码如下:
bool COracle::InsertIntoTable(char* pcName, int& iAge)
{
//SQL语句
char chrSqlStr[512] = "INSERT INTO T_TABLE (NAME,AGE) VALUES (:1,:2)";
sb2 Flag;
//定义绑定句柄
OCIBind* bindhp_Prompt;
OCIBind* bindhp_Remark;

OCIStmtPrepare(mystmthp, myerrhp, (text *)chrSqlStr, strlen(chrSqlStr), OCI_NTV_SYNTAX, OCI_DEFAULT)) )

//位置绑定
if ( strlen(pcName) >= 0 ) Flag=-1; else Flag=0;
OCIBindByPos(mystmthp, &bindhp_Prompt, myerrhp, 1, (dvoid *)pcName, 15, SQLT_STR, (dvoid*)&Flag, 0, 0, 0, 0, OCI_DEFAULT);

if ( iAge <= 0 ) Flag=-1; else Flag=0;
OCIBindByPos(mystmthp, &bindhp_Remark, myerrhp, 2, (dvoid *)&iAge, sizeof(int), SQLT_INT, (dvoid*)&Flag, 0, 0, 0, 0, OCI_DEFAULT);

//执行
OCIStmtExecute(mysvchp, mystmthp, myerrhp, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS);
return true;
}

我是原意是当iAge的值小于1时,AGE字段就插入空值,这段代码在大部份时候不能正确插入空值,但偶尔也可以插入空值,不明白是为什么?
请教各位大大,谢谢

另外请问,我的程序里要提供ACCESS, SQLSERVER,ORACLE三种数据库的接口,是使用一种访问方式好,还是各用各的访问方式好,像ORACLE我就一直用的OCI,但其它的显然不能用OCI,怎么样更好一点?
...全文
633 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
li602714 2009-04-13
  • 打赏
  • 举报
回复
知道的同志来看看啊
li602714 2009-04-07
  • 打赏
  • 举报
回复
在OCI中,SQL语句中的占位符变量用于指示数据必须存储的实际位置,占位符变量通过冒号来表示,如:1
使用占位符可以动态插入多条记录,提高SQL语句的使用效率。如向学生信息表中插入多个学生的资料。
对于每个占位符,可以调用OCI函数来绑定其对应的变量地址。在执行SQL语句时,ORACLE会将得到的输入数据、绑定数据或变量值传给服务器端,与服务器端实时交互。
绑定占位符主要有以下几种方式
1、OCIBindByName()
2、OCIBindByPos()
3、OCIBindObject()
4、OCIBindDynamic()
5、OCIBindArrayOfStruct()
jdsnhan 2009-04-06
  • 打赏
  • 举报
回复
我提供的方法仅从oracle角度讲
oci不熟悉。
cheng_fengming 2009-04-06
  • 打赏
  • 举报
回复
oracle里面插入语句不是这样的么
INSERT INTO T_TABLE (NAME,AGE) VALUES ('1',2);
这样传值可以吗?
INSERT INTO T_TABLE (NAME,AGE) VALUES (:1,:2);
li602714 2009-04-03
  • 打赏
  • 举报
回复
用1楼的方法试了一下,
"INSERT INTO T_TABLE (NAME,AGE) VALUES (:1,case when :2 <1 then null else :2 end)";
发现OCIStmtExecute时会出错,调用OCIErrorGet得到信息是"ORA-01008:并非所有变量都已绑定",但我是绑定了的,指示符变量的位置写的是"0"
是我什么地方没注意到吗?在end后面加逗号也不行

后来偶然试到用如下绑定语句可以实现
OCIBindByPos(mystmthp, &bindhp_Remark, myerrhp, 2, (dvoid *)&iAge, sizeof(int), SQLT_INT, &Flag, 0, 0, 0, 0, OCI_DEFAULT);
少了对批示符变量地址的强制转换(dvoid*),可我不确定这是不是偶尔能实现,因为我最初按照参考的方法在调的时候也是
能实现的,但上了系统后就出问题了
用同样的方法作输出变量是否空值的判断也得不到正确结果,看来ORACLE参考上的说法我还没弄清楚
jdsnhan 2009-04-02
  • 打赏
  • 举报
回复
1、建议方法,在语句中处理
char chrSqlStr[512] = "INSERT INTO T_TABLE (NAME,AGE) VALUES (case when :1<1 then null else :1 end ,:2)";
2、各使用各的接口
CruiseYoung提供的带有详细书签的电子书籍目录 http://blog.csdn.net/fksec/article/details/7888251 该资料是《Oracle SQL高级编程》的源代码 对应的书籍资料见: Oracle SQL高级编程(资深Oracle专家力作,OakTable团队推荐) 基本信息 原书名: Pro Oracle SQL 原出版社: Apress 作者: (美)Karen Morton    Kerry Osborne    Robyn Sands    Riyaj Shamsudeen    Jared Still    译者: 朱浩波 丛书名: 图灵程序设计丛书 出版社:人民邮电出版社 ISBN:9787115266149 上架时间:2011-11-9 出版日期:2011 年11月 开本:16开 页码:502 版次:1-1 主编推荐     资深Oracle专家力作,OakTable团队推荐     全面、独到、翔实,题材丰富     Oracle开发人员和DBA必备 内容简介     Oracle 数据库中的SQL是当今市场上功能最强大的SQL实现之一,而本书全面展示了这一工具的威力。如何才能让更多人有效地学习和掌握SQL呢?Karen Morton及其团队在本书中提供了专业的方案:先掌握语言特性,再学习Oracle为提升语言效率而加入的支持特性,进而将两者综合考虑并在工作中加以应用。作者通过总结各自多年的软件开发和教学培训经验,与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在,内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧:     掌握Oracle数据库中独有的SQL强大特征;     读取并理解SQL执行计划;     快速分析并改进表现欠佳的SQL;     通过提示及配置文件等来控制执行计划;     在程序中优化查询而无需改动代码。     作为Oracle SQL经典著作之一,本书为SQL开发人员指明了前行的方向,赋予了他们不断开拓的动力。 作者简介     KAREN MORTON 研究人员、教育家及顾问,Fidelity信息服务公司的资深数据库管理员和性能调优专家。她从20世纪90年代初就开始使用Oracle,从事 Oracle的教学工作也已经超过10年的时间。她是Oracle ACE,也是OakTable(Oracle社区中著名的“Oracle科学家”的非正式组织)的成员,经常在技术会议上演讲。她的著作还包括 Expert Oracle Practices和Beginning Oracle SQL,博客主页是karenmorton.blogspot.com。     KERRY OSBORNE  专注于Oracle咨询的Enkitec公司的创始人之一。从1982年开始使用Oracle(第2版)。他当过开发人员,也做过DBA,目前是 Oracle ACE总监和OakTable成员。最近几年,他专注于研究Oracle内部原理以及解决性能问题。他的博客主页是 kerryosborne.oracle-guy.com。     ROBYN SANDS 思科公司的软件工程师,为思科的客户设计开发嵌入式Oracle数据库产品。从1996年开始使用Oracle,在应用开发、大型系统实现以及性能评估方面具有丰富的经验。她是OakTable的成员,同时是Expert Oracle Practices (2010年 Apress出版)一书的合著者。     RIYAJ SHAMSUDEEN 专注于性能/数据恢复/电子商务的咨询公司OraInternals的首席数据库管理员和董事长。有近20年使用Oracle技术产品以及Oracle数据库管理员/Oracle数据库应用管理员的经验,是真正应用集群、性能调优以及数据库内部属性方面的专家。同时是一位演讲家及Oracle ACE。     JARED STILL 从1994年就开始使用Oracle。他认为对于SQL的学习是永无止境的,相信每一个查询Oracle数据库的人都需要精通SQL语言,才能写出高效的查询。他参与本书的编写就是为了帮助别人实现这一目标。 目录 封面 -11 封底 -10 扉页 -9 版权 -8 版权声明 -7 致谢 -6 目录 -5 第1章 SQL核心 1 1.1 SQL语言 1 1.2 数据库的接口 2 1.3 SQL*Plus 回顾 3 1.3.1 连接到数据库 3 1.3.2 配置SQL*Plus环境 4 1.3.3 执行命令 6 1.4 5 个核心的SQL语句 8 1.5 SELECT语句 8 1.5.1 FROM子句 9 1.5.2 WHERE子句 11 1.5.3 GROUP BY子句 11 1.5.4 HAVING子句 12 1.5.5 SELECT列表 12 1.5.6 ORDERBY子句 13 1.6 INSERT语句 14 1.6.1 单表插入 14 1.6.2 多表插入 15 1.7 UPDATE语句 17 1.8 DELETE语句 20 1.9 MERGE语句 22 1.10 小结 24 第2章 SQL执行 25 2.1 Oracle架构基础 25 2.2 SGA-共享池 27 2.3 库高速缓存 28 2.4 完全相同的语句 29 2.5 SGA-缓冲区缓存 32 2.6 查询转换 35 2.7 视图合并 36 2.8 子查询解嵌套 39 2.9 谓语前推 42 2.10 使用物化视图进行查询重写 44 2.11 确定执行计划 46 2.12 执行计划并取得数据行 50 2.13 SQL执行——总览 52 2.14 小结 53 第3章 访问和联结方法 55 3.1 全扫描访问方法 55 3.1.1 如何选择全扫描操作 56 3.1.2 全扫描与舍弃 59 3.1.3 全扫描与多块读取 60 3.1.4 全扫描与高水位线 60 3.2 索引扫描访问方法 65 3.2.1 索引结构 66 3.2.2 索引扫描类型 68 3.2.3 索引唯一扫描 71 3.2.4 索引范围扫描 72 3.2.5 索引全扫描 74 3.2.6 索引跳跃扫描 77 3.2.7 索引快速全扫描 79 3.3 联结方法 80 3.3.1 嵌套循环联结 81 3.3.2 排序-合并联结 83 3.3.3 散列联结 84 3.3.4 笛卡儿联结 87 3.3.5 外联结 88 3.4 小结 94 第4章 SQL是关于集合的 95 4.1 以面向集合的思维方式来思考 95 4.1.1 从面向过程转变为基于集合的思维方式 96 4.1.2 面向过程vs.基于集合的思维方式:一个例子 100 4.2 集合运算 102 4.2.1 UNION和UNION ALL 103 4.2.2 MINUS 106 4.2.3 INTERSECT 107 4.3 集合与空值 108 4.3.1 空值与非直观结果 108 4.3.2 集合运算中的空值行为 110 4.3.3 空值与GROUP BY和ORDER BY 112 4.3.4 空值与聚合函数 114 4.4 小结 114 第5章 关于问题 116 5.1 问出好的问题 116 5.2 提问的目的 117 5.3 问题的种类 117 5.4 关于问题问题 119 5.5 关于数据的问题 121 5.6 建立逻辑表达式 126 5.7 小结 136 第6章 SQL执行计划 137 6.1 解释计划 137 6.1.1 使用解释计划 137 6.1.2 理解解释计划可能达不到目的的方式 143 6.1.3 阅读计划 146 6.2 执行计划 148 6.2.1 查看最近生成的SQL语句 149 6.2.2 查看相关执行计划 149 6.2.3 收集执行计划统计信息 151 6.2.4 标识SQL语句以便以后取回计划 153 6.2.5 深入理解DBMS_XPLAN的细节 156 6.2.6 使用计划信息来解决问题 161 6.3 小结 169 第7章 高级分组 170 7.1 基本的GROUP BY用法 171 7.2 HAVING子句 174 7.3 GROUP BY的“新”功能 175 7.4 GROUP BY的CUBE扩展 175 7.5 CUBE的实际应用 179 7.6 通过GROUPING()函数排除空值 185 7.7 用GROUPING()来扩展报告 186 7.8 使用GROUPING_ID()来扩展报告 187 7.9 GROUPING SETS与ROLLUP() 191 7.10 GROUP BY局限性 193 7.11 小结 196 第8章 分析函数 197 8.1 示例数据 197 8.2 分析函数剖析 198 8.3 函数列表 199 8.4 聚合函数 200 8.4.1 跨越整个分区的聚合函数 201 8.4.2 细粒度窗口声明 201 8.4.3 默认窗口声明 202 8.5 Lead和Lag 202 8.5.1 语法和排序 202 8.5.2 例1:从前一行中返回一个值 203 8.5.3 理解数据行的位移 204 8.5.4 例2:从下一行中返回一个值 204 8.6 First_value和Last_value 205 8.6.1 例子:使用First_value来计算最大值 206 8.6.2 例子:使用Last_value来计算最小值 207 8.7 其他分析函数 207 8.7.1 Nth_value(11gR2) 207 8.7.2 Rank 209 8.7.3 Dense_rank 210 8.7.4 Row_number 211 8.7.5 Ratio_to_report 211 8.7.6 Percent_rank 212 8.7.7 Percentile_cont 213 8.7.8 Percentile_disc 215 8.7.9 NTILE 215 8.7.10 Stddev 216 8.7.11 Listagg 217 8.8 性能调优 218 8.8.1 执行计划 218 8.8.2 谓语 219 8.8.3 索引 220 8.9 高级话题 221 8.9.1 动态SQL 221 8.9.2 嵌套分析函数 222 8.9.3 并行 223 8.9.4 PGA大小 224 8.10 组织行为 224 8.11 小结 224 第9章 Model子句 225 9.1 电子表格 225 9.2 通过Model子句进行跨行引用 226 9.2.1 示例数据 226 9.2.2 剖析Model子句 227 9.2.3 规则 228 9.3 位置和号引用 229 9.3.1 位置标记 229 9.3.2 号标记 230 9.3.3 FOR循环 231 9.4 返回更新后的行 232 9.5 求解顺序 233 9.5.1 行求解顺序 233 9.5.2 规则求解顺序 235 9.6 聚合 237 9.7 迭代 237 9.7.1 一个例子 238 9.7.2 PRESENTV与空值 239 9.8 查找表 240 9.9 空值 242 9.10 使用Model子句进行性能调优 243 9.10.1 执行计划 243 9.10.2 谓语前推 246 9.10.3 物化视图 247 9.10.4 并行 249 9.10.5 Model子句执行中的分区 250 9.10.6 索引 251 9.11 子查询因子化 252 9.12 小结 253 第10章 子查询因子化 254 10.1 标准用法 254 10.2 SQL优化 257 10.2.1 测试执行计划 257 10.2.2 跨多个执行的测试 260 10.2.3 测试查询改变的影响 263 10.2.4 寻找其他优化机会 266 10.2.5 将子查询因子化应用到PL/SQL中 270 10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY的功能 277 10.4.1 LEVEL伪列 278 10.4.2 SYS_CONNECT_BY_PATH函数 279 10.4.3 CONNECT_BY_ROOT运算 281 10.4.4 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数 284 10.4.5 CONNECT_BY_ISLEAF伪列 287 10.5 小结 291 第11章 半联结和反联结 292 11.1 半联结 292 11.2 半联结执行计划 300 11.3 控制半联结执行计划 305 11.3.1 使用提示控制半联结执行计划 305 11.3.2 在实例级控制半联结执行计划 308 11.4 半联结限制条件 310 11.5 半联结必要条件 312 11.6 反联结 312 11.7 反联结执行计划 317 11.8 控制反联结执行计划 326 11.8.1 使用提示控制反联结执行计划 326 11.8.2 在实例级控制反联结执行计划 327 11.9 反联结限制条件 330 11.10 反联结必要条件 333 11.11 小结 333 第12章 索引 334 12.1 理解索引 335 12.1.1 什么时候使用索引 335 12.1.2 列的选择 337 12.1.3 空值问题 338 12.2 索引结构类型 339 12.2.1 B-树索引 339 12.2.2 位图索引 340 12.2.3 索引组织表 341 12.3 分区索引 343 12.3.1 局部索引 343 12.3.2 全局索引 345 12.3.3 散列分区与范围分区 346 12.4 与应用特点相匹配的解决方案 348 12.4.1 压缩索引 348 12.4.2 基于函数的索引 350 12.4.3 反转键索引 353 12.4.4 降序索引 354 12.5 管理问题的解决方案 355 12.5.1 不可见索引 355 12.5.2 虚拟索引 356 12.5.3 位图联结索引 357 12.6 小结 359 第13章 SELECT以外的内容 360 13.1 INSERT 360 13.1.1 直接路径插入 360 13.1.2 多表插入 363 13.1.3 条件插入 364 13.1.4 DML错误日志 364 13.2 UPDATE 371 13.3 DELETE 376 13.4 MERGE 380 13.4.1 语法和用法 380 13.4.2 性能比较 383 13.5 小结 385 第14章 事务处理 386 14.1 什么是事务 386 14.2 事务的ACID属性 387 14.3 事务隔离级别 388 14.4 多版本读一致性 390 14.5 事务控制语句 391 14.5.1 Commit(提交) 391 14.5.2 Savepoint(保存点) 391 14.5.3 Rollback(回滚) 391 14.5.4 Set Transaction(设置事务) 391 14.5.5 Set Constraints(设置约束) 392 14.6 将运算分组为事务 392 14.7 订单录入模式 393 14.8 活动事务 399 14.9 使用保存点 400 14.10 序列化事务 403 14.11 隔离事务 406 14.12 自治事务 409 14.13 小结 413 第15章 测试与质量保证 415 15.1 测试用例 416 15.2 测试方法 417 15.3 单元测试 418 15.4 回归测试 422 15.5 模式修改 422 15.6 重复单元测试 425 15.7 执行计划比较 426 15.8 性能测量 432 15.9 在代码中加入性能测量 432 15.10 性能测试 436 15.11 破坏性测试 437 15.12 通过性能测量进行系统检修 439 15.13 小结 442 第16章 计划稳定性与控制 443 16.1 计划不稳定性:理解这个问题 443 16.1.1 统计信息的变化 444 16.1.2 运行环境的改变 446 16.1.3 SQL语句的改变 447 16.1.4 绑定变量窥视 448 16.2 识别执行计划的不稳定性 450 16.2.1 抓取当前所运行查询的数据 451 16.2.2 查看一条语句的性能历史 452 16.2.3 按照执行计划聚合统计信息 454 16.2.4 寻找执行计划的统计方差 454 16.2.5 在一个时间点附近检查偏差 456 16.3 执行计划控制:解决问题 458 16.3.1 调整查询结构 459 16.3.2 适当使用常量 459 16.3.3 给优化器一些提示 459 16.4 执行计划控制:不能直接访问代码 466 16.4.1 选项1:改变统计信息 467 16.4.2 选项2:改变数据库参数 469 16.4.3 选项3:增加或移除访问路径 469 16.4.4 选项4:应用基于提示的执行计划控制机制 470 16.4.5 大纲 470 16.4.6 SQL概要文件 481 16.4.7 SQL执行计划基线 496 16.4.8 基于提示的执行计划控制机制总结 502 16.5 结论 502 媒体评论     本书作者全部是OakTable的成员,且具有15~29年丰富的Oracle开发经验。在研究一些被其他专门讨论Oracle SQL语言的参考书直接忽略的问题时,这种对Oracle数据库的长期钻研无疑是一个巨大的优势。     ——亚马逊读者评论 精彩内容     SQL核心     凯伦?莫顿(Karen Morton)     不管你是刚开始写SQL语句还是已经写过很多年了,学会写出“好的”SQL这个过程都需要具有很扎实的SQL核心语法和概念基础知识。本章对SQL语言的核心概念及其性能做了回顾,同时还描述了一些你应该已经很熟悉的常用SQL命令。对于那些以前曾经使用过SQL并且基础知识相当牢靠的读者来说,本章就是一个简要的复习,让你为后面更详细的SQL论述做好准备。如果你是一位SQL新人,你可能想要先阅读Beginning Oracle SQL这本书以确保掌握SQL的基础。不管是哪种情况,第1章的目的就是通过对5个核心SQL语句的快速浏览来衡量一下你的SQL水平,同时还概述了我们用来执行SQL语句的工具:SQL*Plus。     1.1  SQL语言     SQL语言最早是IBM公司于20世纪70年代开发出来的,称为结构化英文查询语言,简称为SEQUEL。该语言是基于E.F.Codd在1969年提出的关系型数据库管理系统(RDBMS)的。后来因为商标的纠纷,其简称又进一步缩写为SQL。1986年和1987年,ANSI(美国国家标准化组织)和ISO(国际标准化组织)先后将SQL语言采纳为标准语言。而人们并不熟悉的是,ANSI官方曾将SQL语言的读音确定为“S-Q-L”。绝大多数人,包括我本人,都还在使用“sequel”的读音,只是因为这样读起来更顺口一些。     SQL的目的就是简单地提供一个到数据库的接口,在本书指的是Oracle数据库。每一条SQL语句对于数据库来说就是一条命令或指令。SQL与其他编程语言(如C或Java)的区别就在于它是要处理数据集合而不是一行一行的数据。语言本身也不需要你提供如何导航到数据的指令——这是在后台透明地进行的。但你将在后面的章节中看到,如果想在Oracle中写出高效的SQL语句,了解数据及其在数据库中的存储方式与存储位置是很重要的。     由于不同的供应商(例如甲骨文、IBM和微软)实现SQL核心功能的机制相差无几,所以基于某一种数据库所学的技巧同样可以应用到其他类型的数据库上。你基本上可以利用同样的SQL语句来进行数据的查询、插入、更新和删除,以及创建、修改和删除对象,而不必管数据库的供应商是哪家。     尽管SQL是各种关系型数据库管理系统的标准语言,但实际上它并不一定是关系型的。在本书后面我将就这一点稍作扩展。如果想要了解更多的细节,我推荐大家阅读C.J.Date的SQL and Relational Theory一书。需要铭记于心的一点是SQL语言并不总是严格遵守关系模型的——它根本就没有实现关系模型的某些要素,同时还不恰当地实现了一些要素。事实上,既然SQL是基于关系模型的,那么要想写出尽可能正确高效的SQL语句,你不仅必须要理解SQL语言,还要理解关系模型。     1.2  数据库的接口     多年以来人们开发出多种途径来传递SQL语句到数据库并获得结果。Oracle数据库的本地接口界面是Oracle调用界面(OCI)。OCI将由Oracle内核传送而来的查询语句发送到数据库。当使用某种Oracle工具如SQL*Plus或者SQL Developer时,你都在使用OCI。其他的Oracle工具如SQL*Loader、数据泵(Data Pump)以及Real Application Testing (RAT)既使用OCI,也可以使用语言特定的接口,如Oracle JDBC-OCI、ODP.Net、Oracle预编译器、Oracle ODBC以及Oracle C++调用接口(OCCI)驱动器。     当使用编程语言(如COBOL或C语言)时,你所写的语句被称为嵌入式的SQL语句并且在应用程序编译之前会由SQL预处理器进行预处理。代码清单1-1是一段可以在C/C++程序块中使用的SQL语句的例子。     代码清单1-1  C/C++程序块中所嵌入的SQL语句     其他工具,例如SQL*Plus和SQL Developer,都是交互式的工具。你输入并执行命令,然后获得相应的输出。交互式工具并不需要在运行代码前先精确编译,你只需要输入想要执行的命令即可。代码清单1-2是一段使用SQL*Plus执行语句的例子。     代码清单1-2  使用SQL*Plus执行SQL语句     在本书中,为了保持一致性我们所用的示例代码清单都使用SQL*Plus工具,但需要记住的是,不管你是用什么方法或工具来输入和执行SQL语句,所有的事情最后都要通过OCI来传递到数据库。这里的主旨就是不管你所使用的是什么工具,其本地接口都是一样的。     1.3  SQL*Plus回顾     SQL*Plus是一个不管采用哪个安装平台(Windows或Unix)都会提供的命令行工具。它是一个用来输入和执行SQL语句并显示输出结果的纯文本环境。用该工具可以直接输入、编辑命令,可以一条条地保存和执行命令或者通过脚本文件来进行,然后将输出结果以很精美格式的报表输出。要启动SQL*Plus你只需要在主机的命令提示后敲入sqlplus即可。     1.3.1  连接到数据库     有多种方法可以通过SQL*Plus连接数据库。然而在连接之前,你还需要在$ORACLE_HOME/ network/admin/tnsnames.ora这个文件中登记想要连接的数据库。有两种通常使用的方法,或者如代码清单1-3所示那样在启动SQL*Plus时提供连接信息,或者如代码清单1-4所示那样在启动SQL*Plus以后使用connect命令。     代码清单1-3  通过窗口命令提示连接到SQL*Plus     如果想要启动SQL*Plus而又不显示登录到数据库后的提示,可以在启动SQL*Plus时使用/nolog选项。     代码清单1-4  通过SQL>提示连接SQL*Plus并登录到数据库     1.3.2  配置SQL*Plus环境     SQL*Plus有很多的命令可以让你来定制工作环境和显示选项。代码清单1-5所示是在SQL>提示下输入help index命令后显示出来的可用的命令。     代码清单1-5  SQL*Plus命令列表     set命令是用来定制工作环境的最基本的命令。代码清单1-6为set命令的帮助文本。     代码清单1-6  SQL*Plus的SET命令     有了上面这些可用命令,你就能够很轻松地定制最适合你的运行环境了。但有一点要铭记于心的就是当你退出或关闭SQL*Plus的时候,这些设置命令就不再被保留了。为了避免每次使用SQL*Plus时都重新敲入一遍这些设置命令,你可以创建一个login.sql文件。事实上每次启动SQL*Plus的时候它都会默认去读两个文件。第一个是$ORACLE_HOME/sqlplus/admin目录下的glogin.sql文件。如果找到了这个文件,它就会被读进来,文件中的命令语句也会被执行。这样就可以把那些定制你的会话体验的SQL*Plus命令和SQL语句保存起来。     在读取glogin.sql文件以后,SQL*Plus会进一步寻找login.sql文件。这个文件必须在SQL*Plus的启动文件夹中或者包含在环境变量SQLPATH所指向的文件夹路径中。在login.sql文件中的所有命令优先级都比glogin.sql文件中的命令高。从10g开始,Oracle在每次你启动SQL*Plus或者从SQL*Plus里执行connect命令的时候都会同时去读取glogin.sql和login.sql这两个文件。在Oracle 10g之前,login.sql脚本文件只有在SQL*Plus启动的时候才会被执行。代码清单1-7是一个常见的login.sql文件内容。     代码清单1-7  一个常见的login.sql文件     注意这里在SET SQLPROMPT中使用变量_user和_connect_identifier。它们是预定义变量的两个示例。你可以在login.sql文件中或者任何你创建的脚本文件中使用下面这些预定义变量:     ·_connect_identifier     ·_date     ·_editor(这个变量指定了当你使用edit命令的时候启动哪个编辑器)     ·_o_version     ·_o_release     ·_privilege     ·_sqlplus_release     ·_user     1.3.3  执行命令     有两种命令可以在SQL*Plus中执行:SQL语句和SQL*Plus命令。代码清单1-5和代码清单1-6中所列出的SQL*Plus命令对于SQL*Plus来说是特有的命令,可以用来定制运行环境并且可以运行SQL*Plus特有的命令,例如DESCRIBE和CONNECT。要想执行一个SQL*Plus命令,你只需在命令提示后输入该命令然后敲回车,命令会自动被执行。另一方面,如果要执行SQL语句,就必须使用一个特定字来表明你想要执行输入的语句,分号(;)或者斜线(/)都可以。使用分号的话可以直接放在输入命令的后面或者放在接下来的空行中,而斜线则必须放在接下来的空行中才可以被识别。代码清单1-8展示了如何使用这两种号。     代码清单1-8  执行字的用法     注意第5个在语句最后面加了一个斜线(/)的例子。光标移动到了下一行而不是立即执行语句命令。接下来,如果你再按一下回车键,语句就会被放入SQL*Plus的缓冲器中,但是也不执行。如果想要查看SQL*Plus缓冲器中的内容,可以使用list命令(也可以简写为l)。接下来如果你想在缓冲器中通过使用斜线(/)来执行语句[尽管斜线(/)命令本来就是这样来用的]在这里也将会返回一个错误。这是因为你最初在SQL语句的结尾敲入了一个斜线(/),而斜线(/)并不是一个有效的SQL命令,从而在语句想要执行的时候报错。     另外一种执行命令的方法是把命令放到一个文件中。你可以在SQL*Plus之外直接用文本编辑器生成这些文件,也可以在SQL*Plus中使用EDIT命令来直接调用编辑器。如果已经有了一个文件,EDIT命令可以打开这个文件,如果没有的话就会创建新的文件。文件必须放在默认文件夹中,否则你必须指定文件的全路径。想要设定所选择的编辑器,你只需要利用命令define_ editor='//myeditor.exe'来设置预定义变量_editor。具有.sql扩展名的文件在执行的时候不必敲入扩展名,通过@或START命令都可以执行。代码清单1-9中列出了这两个命令的用法。     代码清单1-9  执行.sql脚本文件     SQL*Plus具有很多特性和选项,以致于多得在这里不能一一列举。就本书需要而言,这种概述就已经足够了。但是,Oracle文档对SQL*Plus的用法给出了指导,而且很多的书,比如Beginning Oracle SQL,都对SQL*Plus作了更为深入的阐述,如果感兴趣你可以参考。     1.4  5个核心的SQL语句     SQL语言有很多不同的语句,但在整个职业生涯中,你可能只会用到其中很少的一部分。不过你所使用的几乎其他任何产品不也是这样的吗?据说有一个统计结果是,绝大多数人都仅使用了他们常用的软件产品或编程语言所有功能的20%甚至更少。我不知道这个统计真实与否,但以我的经验来看,这似乎是很准确的。我发现同样的基本SQL语句格式在大多数应用中使用了将近20年了。极少数的人使用过SQL提供的所有功能——即使对于那些他们确实经常使用的功能也常常用得不是很恰当。显而易见,我们不可能覆盖SQL语言的所有语句以及它们的选项。本书的目的在于让你能够深入理解那些最常用的SQL语句并帮助你更高效地使用它们。     在本书中,我们将重点讨论5个最常用的SQL语句,它们分别为SELECT、INSERT、UPDATE、DELETE以及MERGE。尽管这些核心语句都将逐个讲解,但重中之重还是SELECT语句。将这5个语句用好了将会为你在日常工作中用好SQL语言打下坚实的基础。     1.5  SELECT语句     SELECT语句用来从一个或多个表中或者其他数据库对象中提取数据。你应该已经很熟悉SELECT语句的基础知识了,所以我将不再从一个初学者的角度来介绍SELECT语句,而是首先回顾一下SELECT语句的执行逻辑。对于如何来写一个基本的SELECT语句你应该已经学习过了,但为了培养基本的思维模式,你要一直写出合语法规则的高效SQL语句,你需要理解SQL语句是如何执行的。     一个查询语句在逻辑上的处理方式可能会与实际物理处理过程大相径庭。Oracle基于查询成本的优化器(cost-based optimizer , CBO)用来产生实际的执行计划。我们在后面的章节中将会讲解优化器是干什么的,如何来实现其功能的以及为什么要进行优化。目前,我们需要关心的是优化器将会决定如何访问表、按照什么样的顺序来处理它们,以及如何将多个表联结起来及如何使用筛选器。查询的处理在逻辑上是按照特定的顺序进行的,但是,优化器所选择的物理执行计划可能会按照完全不同的顺序来实际执行这些步骤。代码清单1-10是一段包含SELECT语句的主要子句的查询片段,在其中标出了每一个子句的逻辑处理顺序。     代码清单1-10  查询语句的逻辑处理顺序     你应该立刻注意到SQL有别于其他编程语言的一点在于首先处理的并不是写在第一行的语句(SELECT语句),而是FROM子句。注意在这个代码清单中我给出了两个不同的FROM子句。标记为1.1的那个FROM子句表示的是当使用ANSI语法时的不同。我们可以把处理过程中的每一个步骤想象为生成一个临时的数据集。随着每个处理步骤的进行,这个数据集被不断地操作直到生成最终的处理结果。查询返回给调用者的就是这个最终结果数据集。     为了更详细地了解SELECT语句的每个部分,你可以参考代码清单1-11所示的查询语句,该语句返回的结果集为下订单超过4次的女顾客的列表。     代码清单1-11  下订单超过4次的女顾客查询语句     1.5.1  FROM子句     FROM子句列出了所查询数据的源对象。这个子句可以包含表、视图、物化视图、分区或子分区,或者你可以建立一个子查询来生成子对象。如果使用了多个源对象,其逻辑处理阶段也将会应用到每一个联结类型以及谓词ON(如步骤1.1所示)。在本书后面的章节中你将会进一步了解联结类型的更多细节,但注意在处理联结语句的时候是按照下面的顺序来进行的:     (1) 交叉联结,也称为笛卡儿乘积;     (2) 内联结;     (3) 外联结。     在代码清单1-11所示的查询例子中,FROM子句列出了两张表:customers和orders,通过customer_id列来联结。因此,当处理这一信息时,FROM子句所生成的初始数据集将会包含这两张表中customer_id相匹配的行。在本例中结果集将会包含105行。为了验证这一点,只要执行例子中的前4行,如代码清单1-12所示。     代码清单1-12  仅通过FROM子句的部分查询语句的执行     注意 为了使之很好地适应页面我手工调整了输出结果,实际输出结果在页面上超过105行。     1.5.2  WHERE子句     WHERE子句提供了一种方法,可以按照条件来限制查询最终返回结果集的行数。每个条件或者谓语都是以两个值或表达式相比较的形式出现的。比较的结果要么是匹配(值为TRUE)要么是不匹配(值为FALSE)。如果比较的结果是FALSE,那么相应的行不会被包含在最终结果集中。     这里我需要稍微偏离一下主题,来谈一谈与这一步相关的SQL中的一个重要方面。事实上,SQL中逻辑比较的可能结果是TRUE、FALSE以及未知。当其中包含空值(null)的时候比较的结果就会是未知。空值与任何值比较或者用在表达式中都会得到空值,或者是未知。一个空值代表一个相应值的缺失,并且可能因为SQL语言中的不同部分对空值的处理不同而令人费解。关于空值是如何影响SQL语句执行的话题将会贯穿本书,但在这里我不得不先提及一下这个话题。我之前所说的还是基本正确的,一个比较的返回值将会是TRUE或者FALSE。你会发现当进行筛选的比较条件中包含空值的时候,将作为FALSE来对待。     在我们的例子中,只有一个将结果限定为下了订单的女性消费者的谓语。如果你查看FROM子句执行之后的中间结果(见代码清单1-12),你会发现105行中仅有31行是由女性消费者所下的订单(gender = 'F')。因此,在应用了WHERE子句以后,中间结果集将从105行减少到31行。     应用WHERE子句以后得到了更精确的结果集。注意,在这里使用的是“精确的结果集”。我的意思是说现在已经得到了能够满足你查询需求的数据行。其他子句(GROUP BY, HAVING)也许可以用来聚合并且进一步限制调用程序会接收到的最终的结果集,但需要注意的很重要的一点是,目前已经得到了查询计算最终结果所需的所有数据。     WHERE子句的目的是限制或者减小结果集。你所使用的限制条件越少,最终返回的结果集中包含的数据就会越多。你需要返回的数据越多,执行查询的时间也就越长。     1.5.3  GROUP BY子句     GROUP BY子句将执行FROM和WHERE子句后得到的经过筛选后的结果集进行聚合。查询出来的结果按照GROUP BY子句中列出的表达式进行分组,来为每一个分组得出一行汇总结果。你可以按照FROM子句中所列出对象的任意字段进行分组,即使你并不想在输出结果列表中显示该列。相反,Select列表中的任何非聚合字段都必须包括在GROUP BY表达式中。     GROUP BY子句中还可以包含两个附加的运算:ROLLUP 和CUBE。ROLLUP运算用来产生部分求和值,CUBE运算用来求得交互分类值。当你使用这两种运算中任意一个的时候,你将会得到不止一行的汇总信息。在第7章中将会对这两个运算进行更详细的讨论。     在示例查询中,需要按照customer_id来进行分组。这就意味着对于每一个唯一的customer_id只会返回一行值。在WHERE子句执行后所得到的代表下订单的女性消费者的31行订单中,有11个独特的customer_id值,如代码清单1-13所示。     代码清单1-13  截至GROUP BY子句的部分查询执行     你会发现查询的结果是经过分组的,但并没有排序。表面上看结果好像是按照order_ct字段排序的,但这仅仅是个巧合而不是确定的行为。需要记住的很重要的一点是:GROUP BY子句并不确定结果数据的排序。如果你需要结果按照特定的顺序排列,则必须指定一个order by子句。     1.5.4  HAVING子句     HAVING子句将分组汇总后的查询结果限定为只有该子句中的条件为真的数据行。除非你使用HAVING子句,否则将返回所有的汇总行。事实上,GROUP BY子句和HAVING子句的位置是可以互换的,谁先谁后都无关紧要。但是,似乎在编码中将GROUP BY子句放在前面更有意义一些,因为GROUP BY子句在逻辑上是先执行的。从本质上来说,HAVING子句是在GROUP BY子句执行后用来筛选汇总值的第二个WHERE子句。     在我们的查询例子中,HAVING子句HAVING COUNT(o.order_id) > 4,将分组数据从11行减少到2行。这一点你可以通过查看GROUP BY子句应用后返回的结果行来确认,如代码清单1-13所示。注意仅有146和147号消费者所下的订单数超过4次。这样就产生了组成最终结果集的两行数据。     1.5.5  SELECT列表     SELECT列表列出查询的返回最终结果集中需要显示哪些列。这些列可以是数据表中一个实际的列、一个表达式,或者甚至是一个SELECT语句的结果,如代码清单1-14所示。     代码清单1-14  展现SELECT列表各种可能情况的查询实例     SQL> select.customer_id, c.cust_first_name||''||c.cust_last_name,     .     当使用另外一个SELECT语句来产生结果中的一列的值的时候,这个查询必须只能返回一行一列的值。这种类型的子查询被称为标量子查询。尽管这可能是一个非常有用的语法,但需要牢记于心的是标量查询在结果集中的每一行结果产生时都要执行一遍。在某些情况下可以进行优化以减少标量子查询的重复执行,但更糟糕的场景是每一行都需要标量子查询执行。你可以想象如果你的结果集中有几千行甚至上百万行数据的时候所需要付出的查询代价!在后面的章节中我们还将回顾标量子查询并讨论如何更好地来使用它们。     在SELECT列表中你还有可能用到的一个选项是DISTINCT子句。在例子中并没有使用它,但我想要简要地提及一下。DISTINCT子句用来在其他子句执行完毕以后从结果集中去除重复的行。     SELECT列表执行完以后,你就得到了最终的查询结果集。所剩的唯一需要做的事情,如果包含了的话,就是将查询结果集按照所需的顺序排序。     1.5.6  ORDER BY子句     ORDER BY子句用来对查询最终返回的结果集进行排序。在本例中,需要按照orders_ct和customer_id进行排序。orders_ct这一列是通过GROUP BY子句中的COUNT聚合函数计算得到的值。如代码清单1-13中所示,有两个消费者的订单超过4个。由于这两个消费者的订单数都是5份,orders_ct这一列的值是相同的,所以要由第二个排序列来确定最终结果的显示顺序。如代码清单1-15中所示,该查询的最终经过排序的输出结果是按照customer_id排序的两行数据集。     代码清单1-15  示例查询的最终输出     当输出结果需要排序的时候,Oracle必须在其他所有子句都执行完之后按照指定的顺序对最终结果集进行排序。需要排序的数据量大小是非常重要的。我这里所说的大小是指结果集中所包含的总字节数。你可以通过用行数乘以每一行的字节数来估计数据集的大小。每行所包含的字节数通过将选择列表中包含的每一列的平均长度相加来确定。     上面的查询实例在选择列表中仅需要列出customer_id 和orders_ct两列的值。我们可以估算每一行输出值的字节数为10。在第6章中我将阐述从哪里能找到优化器所估计的值。因此,如果我们在结果集中只有两行数据,排序的大小实际上是很小的,大约20字节。请记住这仅仅是估算,但这样的估算也是很重要的。     较小的排序会完全在内存中来实现,而较大的排序将不得不使用临时磁盘空间来完成。如你可能推断的那样,在内存中完成的排序比必须使用磁盘的排序要快。因此,当优化器估算排序数据的影响时,它必须要考虑排序数据集的大小,以此来调整如何能够以最有效的方法来获得查询的结果。一般来说,排序是查询过程中开销相当大的一个处理步骤,尤其是当返回结果集很大的时候。     1.6  INSERT语句     INSERT语句用来向表、分区或视图中添加行。可以向单表或者多个表方法中添加数据行。单表插入将会向一个表中插入一行数据,这行数据可以显式地列出插入值也可以通过一个子查询来获取。多表插入将会向一个或多个表中插入行,并且会通过子查询获取值来计算所插入行的值。     1.6.1  单表插入     代码清单1-16中的第一个例子阐明了使用values子句实现的单表插入。每一列的值都显式地输入。如果你要插入表中所定义的所有列的值,那么列的列表是可选的。但是,如果你只想提供部分列的值,则必须在列的列表中指明所需的列名。好的做法是不管是不是需要插入所有列的值,都把所有列的列表列出来。这样做就像该语句的自述文件一样,并且也可以减少将来别人要插入一个新列到表中的时候可能出现的错误。     代码清单1-16  单表插入     第二个例子阐述了通过子查询来实现插入。这是插入数据行的一个非常灵活的选项。所写的子查询可以返回一行或多行数据。返回的每一行都会用来生成需要插入的新行的列值。根据你的需要这个子查询可以很简单也可以很复杂。在本例中,我们使用子查询实现了在现有薪水的基础上为每一位员工发放10%奖金的计算。事实上奖金表包含4列,但在这个插入中我们只列出了3个字段。comm这一列在子查询中并没有占据一列并且我们也没有将它包括在列表中。因为我们没有包含这一列,它的值将会是null。注意如果comm列具有非空约束,那么可能已返回一个约束错误,语句的执行也已失败。     1.6.2  多表插入     代码清单1-17所示的多表插入的例子阐明了一个子查询返回的数据行是如何被用来插入多个表中的。我们从3个表开始:small_customers、medium_customers以及large_customers。我们想要按照每位消费者所下订单的总金额来将数据分别插入这些表。子查询将每一位消费者的order_total列求和来确定该消费者的消费金额是小(所有订单的累加金额小于10 000美元)、中等(介于10 000美元与99 999.99美元之间)还是大(大于等于100 000美元),然后按照条件将这些行插入对应的表中。     代码清单1-17  多表插入     注意INSERT关键字后面ALL子句的使用。当指定了ALL子句的时候,这个语句就会执行无条件的多表插入。也就意味着每一个WHEN子句按照子查询所返回的每一行来确定值而不管前一个条件的输出结果是什么。因此,你需要注意如何来指定每个条件。例如,如果我使用WHEN sum_orders < 100 000这个条件而不是像上面一样列出范围,插入medium_customers表中的行有可能也会插入small_customers表中。     你需要指明FIRST选项来实现每一个WHEN子句按照其出现在语句中的顺序进行评估,并且对于一个给定的子查询行跳过接下来的WHEN子句评估。关键在于要记住哪一个选项能够更好地满足你的需要,ALL还是FIRST,然后使用最适合的选项。     1.7  UPDATE语句     UPDATE语句的作用是改变表中原有行的列值。这个语句的语法由3部分组成:UPDATE、SET和WHERE。UPDATE子句用来指定要更新的表,SET子句用来指明哪些列改变了以及调整的值,WHERE子句用来按条件筛选需要更新的行。WHERE子句是可选的,如果忽略了这个子句的话,更新操作将针对指定表中的所有行进行。     代码清单1-18列出了几种UPDATE语句的不同写法。首先,我建立了一个employees表的副本,名称为employees2,然后我将执行几个完成基本相同任务的不同更新操作:将90部门的员工工资增加10%。在例5中,commission_pct这一列也进行了更新。下面就是采用的不同方法。     例1:使用表达式更新一个单列的值。     例2:通过子查询更新一个单列的值。     例3:通过在WHERE子句中使用子查询确定要更新的数据行来更新单列的值。     例4:通过使用SELECT语句定义表及列的值来更新表。     例5:通过子查询更新多列。     代码清单1-18  UPDATE语句的例子     1.8  DELETE语句     DELETE语句用来从表中移除数据行。该语句的语法结构由3部分组成:DELETE、FROM和WHERE。DELETE关键字是单独列出的。除非你决定使用我们后面将会讨论到的提示(hint),没有其他选项与DELETE关键字相结合。FROM子句用来指定要从哪个表中删除数据行。如代码清单1-19中的例子所示,这个表可以直接指定也可以通过子查询来确定。WHERE子句提供筛选条件有助于确定哪些行是要删除的。如果忽略了WHERE子句,删除操作将删除指定表中的所有数据行。     代码清单1-19展示出了DELETE语句的几种不同写法。注意,在这些例子中我使用了代码清单1-18中创建的employees2表。下面你将看到的就是这些不同的删除方法。     例1:使用WHERE子句中的筛选条件来从指定表中删除行。     例2:使用FROM子句中的子查询来删除行。     例3:使用WHERE子句中的子查询来从指定表中删除行。     代码清单1-19  DELETE语句的例子     1.9  MERGE语句     MERGE语句具有按条件获取要更新或插入表中的数据行,然后从1个或多个源头对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据,但它的应用不仅限于数据仓库环境下。这个语句提供的一个很大的附加值在于你可以很方便地把多个操作结合成一个。这就使你可以避免使用多个INSERT、UPDATE以及DELETE语句。并且,在本书后面的内容中你将看到,如果你避免去做那些不是必须做的事情,响应时间可能得到相应的改善。     MERGE语句的语法是:     为了说明MERGE语句的用法,代码清单1-20展示出了如何建立一个测试表,然后恰当地利用MERGE条件来向表中插入或更新行。     代码清单1-20  MERGE语句例子     MERGE语句完成了下面这些事情。     ·插入了两行(员工id 106和107)。     ·更新了一行(员工id 105)。     ·删除了一行(员工id 103)。     ·一行保持不变(员工id 104)。     如果没有MERGE语句,你必须最少写3条不同的语句来完成同样的事情。     1.10  小结     正如你可以从到目前为止的例子中看出的,SQL语言提供了很多不同的选择来得到同样的结果集。你可能还注意到了一点就是这5个核心的SQL语句都可以使用类似的构造,例如子查询。关键是需要搞清楚在各种不同的使用场景下哪种构造是最高效的。我们将在本书后面的内容中阐述如何做到这一点。     如果你对本章的例子的理解有任何困难,请一定花点时间复习Beginning Oracle SQL或者Oracle文档中的SQL Reference Guide。在本书中接下来的部分我们假设你已经很好地理解了5个核心SQL语句的基本构造:SELECT、INSERT、UPDATE、DELETE和MERGE。
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视为电子化的文件柜——存储电子文件的处所,用户可以对文件中的数据运行新增、截取、更新、删除等操作。 常见的数据模型 1. 层次结构模型: 层次结构模型实质上是一种有根结点的定向有序树,IMS(Information Manage-mentSystem)是其典型代表。 2. 网状结构模型:按照网状数据结构建立的数据库系统称为网状数据库系统,其典型代表是DBTG(Data Base Task Group)。 3. 关系结构模型:关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。常见的有Oracle、mssql、mysql等 二、 主流数据库 数据库名 公司 特点 工作环境 mssql 微软 只能能运行在windows平台,体积比较庞大,占用许多系统资源, 但使用很方便,支持命令和图形化管理,收费。 中型企业 Mysql 甲骨文 是个开源的数据库server,可运行在多种平台, 特点是响应速度特别快,主要面向中小企业 中小型企业 PostgreSQL 号称“世界上最先进的开源数据库“,可以运行在多种平台下,是tb级数据库,而且性能也很好 中大型企业 oracle 甲骨文 获得最高认证级别的ISO标准安全认证,性能最高, 保持开放平台下的TPC-D和TPC-C的世界记录。但价格不菲 大型企业 db2 IBM DB2在企业级的应用最为广泛, 在全球的500家最大的企业中,几乎85%以上用DB2数据库服务器。收费 大型企业 Access 微软 Access是一种桌面数据库,只适合数据量少的应用,在处理少量 数据和单机访问的数据库时是很好的,效率也很高 小型企业 三、 Oracle数据库概述 ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。  拉里•埃里森  就业前景 从就业与择业的角度来讲,计算机相关专业的大学生从事oracle方面的技术是职业发展中的最佳选择。 其一、就业面广:全球前100强企业99家都在使用ORACLE相关技术,中国政府机构,大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深:如果期望进入IT服务或者产品公司(类似毕博、DELL、IBM等),Oracle技术能够帮助提高就业的深度。 其三、职业方向多:Oracle数据库管理方向、Oracle开发及系统架构方向、Oracle数据建模数据仓库等方向。 四、 如何学习 认真听课、多思考问题、多动手操作、有问题一定要问、多参与讨论、多帮组同学 五、 体系结构 oracle的体系很庞大,要学习它,首先要了解oracle的框架。oracle的框架主要由物理结构、逻辑结构、内存分配、后台进程、oracle例程、系统改变号 (System Change Number)组成  物理结构 物理结构包含三种数据文件: 1) 控制文件 2) 数据文件 3) 在线重做日志文件  逻辑结构 功能:数据库如何使用物理空间 组成:表空间、段、区、块的组成层次 六、 oracle安装、卸载和启动  硬件要求 物理内存:1GB 可用物理内存:50M 交换空间大小:3.25GB 硬盘空间:10GB  安装 1. 安装程序成功下载,将会得到如下2个文件: 解压文件将得到database文件夹,文件组织如下: 点击setup.exe执行安装程序,开始安装。 2. 点击安装程序将会出现如下安装界面,步骤 1/9:配置安全更新 填写电子邮件地址(可以不填),去掉复选框,点击下一步 3. 步骤2/9:选择安装选项 勾选第一个,安装和配置数据库,点击下一步 4. 步骤3/8:选择系统类 勾选第一个:桌面类,点击下一步 5. 步骤4/8:配置数据库安装 选择安装路径,选择数据库版本(企业版),选择字集(默认值) 填写全局数据库名,管理口令 6. 步骤5/8:先决条件检查 如果你的电脑满足要求但仍然显示检查失败,这时候直接忽略,勾选全部忽略 7. 步骤6/8:概要信息 核对将要安装数据的详细信息,并保存响应文件,以备以后查看。然后点击完成数据库安装 8. 步骤7/8:安装产品 产品安装过程中将会出现以上2个界面 9. 步骤8/8:完成安装  卸载Oracle 1. 在运行services.msc打开服务,停止Oracle的所有服务。 2. oracle11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat 3. 运行该批处理程序将自动完成oracle卸载工作,最后手动删除\app文件夹(可能需要重启才能删除) 4. 运行regedit命令,打开注册表窗口。删除注册表中Oracle相关的内容,具体如下:  删除HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE目录。  删除HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services中所有以oracle或OraWeb为开头的键。  删除HKEY_LOCAL_MACHINE/SYSETM/CurrentControlSet/Services/Eventlog/application中所有以oracle开头的键。  删除HKEY_CLASSES_ROOT目录下所有以Ora、Oracle、Orcl或EnumOra为前缀的键。  删除HKEY_CURRENT_USER/SOFTWARE/Microsoft/windows/CurrentVersion/Explorer/MenuOrder/Start Menu/Programs中所有以oracle 开头的键。  删除HKDY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI中除Microsoft ODBC for Oracle注册表键以外的所有含有Oracle的键。  删除环境变量中的PATHT CLASSPATH中包含Oracle的值。  删除“开始”/“程序”中所有Oracle的组和图标。  删除所有与Oracle相关的目录,包括: C:\Program file\Oracle目录。 ORACLE_BASE目录。 C:\Documents and Settings\系统用户名、LocalSettings\Temp目录下的临时文件。 七、 oracle中的数据库 八、 常用的工具  Sql Plus  Sql Developer  Oracle Enterprise Manager   第二章 用户和权限 一、 用户介绍 ORACLE用户是学习ORACLE数据库中的基础知识,下面就介绍下类系统常用的默认ORACLE用户: 1. sys用户:超级用户,完全是个SYSDBA(管理数据库的人)。拥有dba,sysdba,sysoper等角色或权限。是oracle权限最高的用户,登录时不能用normal。 2. system用户:超级用户,默认是SYSOPT(操作数据库的人),不过它也能以SYSDBA的权限登陆。拥有普通dba角色权限。 3. scott用户:是个演示用户,是让你学习Oracle用的。 二、 常用命令 学习oracle,首先我们必须要掌握常用的基本命令,oracle中的命令比较多,常用的命令如下: 1. 登录命令(sqlplus) 说明:用于登录到oracle数据库 用法:sqlplus 用户名/密码 [as sysdba/sysoper] 注意:当用特权用户登录时,必须带上sysdba或sysoper 例子: 普通用户登录 sys用户登录 操作系统的身份登录 2. 连接命令(conn) 说明:用于连接到oracle数据库,也可实现用户的切换 用法:conn 用户名/密码 [as sysdba/sysoper] 注意:当用特权用户连接时,必须带上sysdba或sysoper 例子: 3. 断开连接(disc) 说明:断开与当前数据库的连接 用法:disc 4. 显示用户名(show user) 说明:显示当前用户名 用法:show user 5. 退出(exit) 说明:断开与当前数据库的连接并会退出 用法:exit 6. 编辑脚本(edit/ed) 说明:编辑指定或缓冲区的sql脚本 用法:edit [文件名] 列子: 7. 运行脚本 (start/@) 说明:运行指定的sql脚本 用法:start/@ 文件名 列子: 8. 印刷屏幕 (spool) 说明:将sql*plus屏幕中的内容输出到指定的文件 用法:开始印刷->spool 文件名 结束印刷->spool off 列子: 文件内容 9. 显示宽度 (linesize) 说明:设置显示行的宽度,默认是80个字 用法:set linesize 120 10. 显示页数 (pagesize) 说明:设置每页显示的行数,默认是14页 用法:set pagesize 20 三、 用户管理 1. 创建用户 说明:Oracle中需要创建用户一定是要具有dba(数据库管理员)权限的用户才能创建,而且创建的新用户不具备任何权限,连登录都不可以。 用法:create user 新用户名 identified by 密码 例子: 2. 修改密码 说明:修改用户密码一般有两种方式,一种是通过命令password修改,另一种是通过语句alter user实现,如果要修改他人的密码,必须要具有相关的权限才可以 用法: 方式一 password [用户名] 方式二 alert user 用户名 identified by 新密码 例子: 修改当前用户(方式一) 修改当前用户(方式二) 修改其他用户(方式一) 修改其他用户(方式二) 3. 用户禁用与启用 说明:Oracle中想要禁用或启用一个账户也同样是使用alter user 命令来完成,只是语法和修改密码有所不同。 用法: 禁用 alert user 用户名 account lock 启用 alert user 用户名 account unlock 4. 删除用户 说明:Oracle中要删除一个用户,必须要具有dba的权限。而且不能删除当前用户,如果删除的用户有数据对象,那么必须加上关键字cascade。 用法:drop user 用户名 [cascade] 四、 用户权限与角色 1. 权限 Oracle中权限主要分为两种,系统权限和实体权限。  系统权限:系统规定用户使用数据库的权限。(系统权限是对用户而言)。  DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。  RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。  CONNECT:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。 注意: 对于普通用户:授予connect, resource权限。 对于DBA管理用户:授予connect,resource, dba权限。  授予系统权限 说明:要实现授予系统权限只能由DBA用户授出。 用法:grant 系统权限1[,系统权限2]… to 用户名1[,用户名2]…. 例子:  系统权限回收: 说明:系统权限只能由DBA用户回收 用法:revoke 系统权限 from 用户名 例子:  实体权限:某种权限用户对其它用户的表或视图的存取权限。(是针对表或视图而言的)。主要包括select, update, insert, alter, index, delete, all其中all包括所有权限。  授予实体权限 用法:grant 实体权限1[,实体权限2]… on 表名 to用户名1[,用户名2]…. 例子:  实体权限回收 用法:revoke 实体权限 on 表名from 用户名 例子:  查询用户拥有哪里权限: SQL> select * from role_tab_privs;//查询授予角色的对象权限 SQL> select * from role_role_privs;//查询授予另一角色的角色 SQL> select * from DBA_tab_privs;//查询直接授予用户的对象权限 SQL> select * from dba_role_privs;//查询授予用户的角色 SQL> select * from dba_sys_privs;//查询授予用户的系统权限 SQL> select * from role_sys_privs;//查询授予角色的系统权限 SQL> Select * from session_privs;// 查询当前用户所拥有的权限 2. 角色 角色。角色是一组权限的集合,将角色赋给一个用户,这个用户就拥有了这个角色中的所有权限。  系统预定义角色 预定义角色是在数据库安装后,系统自动创建的一些常用的角色。下面我们就简单介绍些系统角色:  CONNECT, RESOURCE, DBA这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划,而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。  DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE这些角色主要用于访问数据字典视图和包。  EXP_FULL_DATABASE, IMP_FULL_DATABASE这两个角色用于数据导入导出工具的使用。  自定义角色 Oracle建议我们自定义自己的角色,使我们更加灵活方便去管理用户  创建角色 SQL> create role admin;  授权给角色 SQL> grant connect,resource to admin;  撤销角色的权限 SQL> revoke connect from admin;  删除角色 SQL> drop role admin;   第三章 Sql查询与函数 一、 SQL概述 SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。  SQL语言主要包含5个部分  数据定义语言Data Definition Language(DDL),用来建立数据库、数据对象和定义其列。例如:CREATE、DROP、ALTER等语句。  数据操作语言Data Manipulation Language(DML),用来插入、修改、删除、查询,可以修改数据库中的数据。例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句  数据查询语言 (Data Query Language, DQL) 是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。例如:SELECT(查询)  数据控制语言Data Controlling Language(DCL),用来控制数据库组件的存取允许、存取权限等。例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句。  事务控制语言(Transactional Control Language,TCL),用于维护数据的一致性,包括COMMIT(提交事务)、ROLLBACK(回滚事务)和SAVEPOINT(设置保存点)3条语句 二、 Oracle的数据类型 类型 参数 描述 字类型 char 1~2000字节 固定长度字串,长度不够的用空格补充 varchar2 1~4000字节 可变长度字串,与CHAR类型相比,使用VARCHAR2可以节省磁盘空间,但查询效率没有char类型高 数值类型 Number(m,n) m(1~38) n(-84~127) 可以存储正数、负数、零、定点数和精度为38位的浮点数,其中,M表示精度,代表数字的总位数;N表示小数点右边数字的位数 日期类型 date 7字节 用于存储表中的日期和时间数据,取值范围是公元前4712年1月1日至公元9999年12月31日,7个字节分别表示世纪、年、月、日、时、分和秒 二进制数据类型 row 1~2000字节 可变长二进制数据,在具体定义字段的时候必须指明最大长度n long raw 1~2GB 可变长二进制数据 LOB数据类型 clob 1~4GB 只能存储字数据 nclob 1~4GB 保存本地语言字集数据 blob 1~4GB 以二进制信息保存数据 三、 DDL语言 1. Create table命令 用于创建表。在创建表时,经常会创建该表的主键、外键、唯一约束、Check约束等  语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1,column2,…..column_n) REFERENCES tablename(column1,column2,…..column_n) )  例子: create table student( stuNo char(32) primary key,--主键约束 stuName varchar2(20) not null,--非空约束 cardId char(20) unique,--唯一约束 sex char(2) check(sex='男' or sex='女'),--检查约束 address varchar2(100) default '地址不详'--默认约束 ) create table mark( mid int primary key,--主键约束 stuNo char(32) not null, courseName varchar2(20) not null,--非空约束 score number(3) not null check(score>=0 and scoreselect * from em--查询所有数据 SQL>select ename,job from em--查询指定的字段数据 SQL> select * from emp where sal>1000--加条件 2. 聚合函数 聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。不能在 WHERE 子句中使用组函数。  AVG(expression): 返回集合中各值的平均值 --查询所有人都的平均工资 select avg(sal) from emp  COUNT(expression): 以 Int32 形式返回集合中的项数 --查询工资低于2000的人数 select count(*) from emp where sal2000 5. 连接查询 连接查询是关系数据库中最主要的查询,主要包括内连接、外连接和交叉连接等。通过连接运算可以实现多个表查询。  内连接 内连接也叫连接,是最早的一种连接。还可以被称为普通连接或者自然连接,内连接是从结果表中删除与其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。  等值连接: select * from emp inner join dept on emp.deptno=dept.deptno select * from emp,dept where emp.deptno=dept.deptno  不等值连接: select * from emp inner join dept on emp.deptno!=dept.deptno  外连接 外连接分为三种:左外连接,右外连接,全外连接。对应SQL:LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成:LEFT/RIGHT/FULL JOIN。  左外连接(left join): 是以左表的记录为基础的 select * from emp left join dept on emp.deptno=dept.deptno  右外连接(right join): 和left join的结果刚好相反,是以右表(BL)为基础的 select * from emp right join dept on emp.deptno=dept.deptno  全外连接(full join): 左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充 select * from emp full join dept on emp.deptno=dept.deptno  交叉连接 交叉连接即笛卡儿乘积,是指两个关系中所有元组的任意组合。一般情况下,交叉查询是没有实际意义的。 select * from cross full join dept 6. 常用查询  like模糊查询 --查询姓名首字母为S开始的员工信息 select * from emp where ename like 'S%' --查询姓名第三个字母为A的员工信息 select * from emp where ename like '__A%'  is null/is not null 查询 --查询没有奖金的雇员信息 select * from emp where comm is null --查询有奖金的雇员信息 select * from emp where comm is not null  in查询 --查询雇员编号为7566、7499、7844的雇员信息 select * from emp where empno in(7566,7499,7844)  exists/not exists查询(效率高于in) --查询有上级领导的雇员信息 select * from emp e where exists (select * from emp where empno=e.mgr) --查询没有上级领导的雇员信息 select * from emp e where not exists (select * from emp where empno=e.mgr)  all查询 --查询比部门编号为20的所有雇员工资都高的雇员信息 select * from emp where sal > all(select sal from emp where deptno=20)  union合并不重复 select * from emp where comm is not null union select * from emp where sal>3000  union all合并重复 select * from emp where comm is not null union all select * from emp where sal>3000 7. 子查询 当一个查询是另一个查询的条件时,称之为子查询。子查询是一个 SELECT 语句,它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。  在CREATE TABLE语句中使用子查询 --创建表并拷贝数据 create table temp(id,name,sal) as select empno,ename,sal from emp  在INSERT语句中使用子查询 --当前表拷贝 insert into temp(id,name,sal) select * from temp --从其他表指定字段拷贝 insert into temp(id,name,sal) select empno,ename,sal from emp  在DELETE语句中使用子查询 --删除SALES部门中的所有雇员 delete from emp where deptno in (select deptno from dept where dname='SALES')  在UPDATE语句中使用子查询 --修改scott用户的工资和smith的工资一致 update emp set sal=(select sal from emp where ename='SMITH') where ename='SCOTT' --修改black用户的工作,工资,奖金和scott一致 update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SCOTT') where ename='BLAKE'  在SELECT语句中使用子查询 --查询和ALLEN同一部门的员工信息 select * from emp where deptno in (select deptno from emp where ename='ALLEN') --查询工资大于部门平均工资的雇员信息 select * from emp e (select avg(sal) asal,deptno from emp group by deptno) t where e.deptno=t.deptno and e.sal>t.asal 六、 TCL语言 1. COMMIT commit --提交事务 2. ROLLBACK rollback to p1 --回滚到指定的保存点 rollback --回滚所有的保存点 3. SAVEPOINT savepoint p1 --设置保存点 4. 只读事务 只读事务是指只允许执行查询的操作,而不允许执行任何其它dml操作的事务,它的作用是确保用户只能取得某时间点的数据。 set transaction read only 七、 oracle函数 1. 字串函数 字串函数是oracle中比较常用的,下面我们就介绍些常用的字串函数:  concat:字串连接函数,也可以使用’||’ --将职位和雇员名称显示在一列中 select concat(ename,concat('(',concat(job,')'))) from emp select ename || '(' || job || ')' from emp  length:返回字串的长度 --查询雇员名字长度为5个字的信息 select * from emp where length(ename)=5  lower:将字串转换成小写 --以小写方式显示雇员名 select lower(ename) from emp  upper:将字串转换成大写 --以大写方式显示雇员名 select upper (ename) from emp  substr:截取字串 --只显示雇员名的前3个字母 select substr(ename,0,3) from emp  replace:替换字串 --将雇员的金额显示为*号 select ename,replace(sal,sal,’*’) from emp  instr:查找字串 --查找雇员名含有’LA’字的信息 select * from emp where instr(ename,’LA’)>0 2. 日期函数  sysdate:返回当前session所在时区的默认时间 --获取当前系统时间 select sysdate from dual  add_months:返回指定日期月份+n之后的值,n可以为任何整数 --查询当前系统月份+2的时间 select add_months(sysdate,2) from dual --查询当前系统月份-2的时间 select add_months(sysdate,-2) from dual  last_day:返回指定时间所在月的最后一天 --获取当前系统月份的最后一天 select last_day(sysdate) from dual  months_between:返回月份差,结果可正可负,当然也有可能为0 --获取入职日期距离当前时间多少天 select months_between(sysdate, hiredate) from emp  trunc:为指定元素而截去的日期值 --获取当前系统年,其他默认 select trunc(sysdate,'yy') from dual --查询81年2月份入职的雇员 select * from emp where trunc(hiredate,'mm')=trunc(to_date('1981-02','yyyy-mm'),'mm') 3. 转换函数  to_char:将任意类型转换成字串 --日期转换 select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual --数字转换 select to_char(-100.789999999999,'L99G999D999') from dual  数字格式控制 号 描述 9 代表一位数字,如果当前位有数字,显示数字,否则不显示(小数部分仍然会强制显示) 0 强制显示该位,如果当前位有数字,显示数字,否则显示0 $ 增加美元号显示 L 增加本地货币号显示 . 小数点号显示 , 千分位号显示  to_date:将字串转换成日期对象 --字转换成日期 select to_date('2011-11-11 11:11:11', 'yyyy-mm-dd hh24:mi:ss') from dual  to_number:将字转换成数字对象 --字转换成数字对象 select to_number('209.976')*5 from dual select to_number('209.976', '9G999D999')*5 from dual 4. 数学函数  abs:返回数字的绝对值 select abs(-1999) from dual  ceil:返回大于或等于n的最小的整数值 select ceil(2.48) from dual  floor:返回小于等于n的最大整数值 select floor(2.48) from dual  round:四舍五入 select round(2.48) from dual select round(2.485,2) from dual  bin_to_num:二进制转换成十进制 select bin_to_num(1,0,0,1,0) from dual   第四章 锁 一、 概述 锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Locks,即S锁)。当数据对象被加上排它锁时,其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取,但不能修改。 根据保护的对象不同,Oracle数据库锁可以分为以下几大类:  DML锁(data locks,数据锁),用于保护数据的完整性  DDL锁(dictionary locks,字典锁),用于保护数据库对象的结构,如表、索引等的结构定义  内部锁和闩(internal locks and latches),保护数据库的内部结构 二、 DML锁 DML锁的目的在于保证并发情况下的数据完整性,在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁,TX锁称为事务锁或行级锁。 1. 行级锁 当事务执行数据库插入、更新、删除操作时,该事务自动获得操作表中操作行的排它锁 --不允许其他用户对雇员表的部门编号为20的数据进行修改 select * from emp where deptno=20 for update --不允许其他用户对雇员表的所有数据进行修改 select * from emp for update --如果已经被锁定,就不用等待 select * from emp for update nowait --如果已经被锁定,更新的时候等待5秒 select * from emp for update wait 5 2. 锁模式  0(none)  1(null)  2(rs):行共享  3(rx):行排他  4(s):共享  5(srx):共享行排他  6(x):排他 数字越大,锁级别越高 3. 表级锁 当事务获得行锁后,此事务也将自动获得该行的表锁(行排他),以防止其它事务进行DDL语句影响记录行的更新  行共享锁(RS锁):允许用户进行任何操作,禁止排他锁 lock table emp in row share mode  行排他锁(RX锁):允许用户进行任何操作,禁止共享锁 lock table emp in row exclusive mode  共享锁(R锁):其他用户只能看,不能修改 lock table emp in share mode  排他锁(X锁):其他用户只能看,不能修改,不能加其他锁 lock table emp in exclusive mode  共享行排他(SRX锁):比行排他和共享锁级别高,不能添加共享锁 lock table emp in share row exclusive mode 4. 锁兼容性 S X RS RX SRX N/A S Y N Y N N Y X N N N N N Y RS Y N Y Y Y Y RX N N Y Y N Y SRX N N Y N N Y N/Y Y Y Y Y Y Y 5. 死锁 当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就出现死锁。 1) 用户A修改A表,事务不提交 2) 用户B修改B表,事务不提交 3) 用户A修改B表,阻塞 4) 用户B修改A表,阻塞 Oracle系统能自动发现死锁,并会自动选择工作量最少的事务进行撤销和释放所有锁 6. 悲观锁和乐观锁 数据的锁定分为两种方法,第一种叫做悲观锁,第二种叫做乐观锁  悲观锁:就是对数据的冲突采取一种悲观的态度,也就是说假设数据肯定会冲突,所以在数据开始读取的时候就把数据锁定住。  乐观锁:就是认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让用户返回错误的信息,让用户决定如何去做。 三、 DDL锁 1. 排它DDL锁 创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。 2. 共享DDL锁 需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁 3. 分析锁 分析锁是一种独特的DDL锁类型,ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系 四、 内部锁和闩 这是ORACLE中的一种特殊锁,用于顺序访问内部系统结构。当事务需向缓冲区写入信息时,为了使用此块内存区域,ORACLE首先必须取得这块内存区域的闩锁,才能向此块内存写入信息。   第五章 数据库对象 一、 概述 ORACLE数据库主要有如下数据库对象:  tablespace and datafile(表空间和数据文件)  table(表)  constraints(约束)  index(索引)  view(试图)  sequence(序列)  synonyms(同义词)  DB-link(数据库链路) 二、 表空间和数据文件 表空间是数据库的逻辑组成部分,从物理上讲,数据库数据是存放在数据文件中,从逻辑上讲数据库则是存放在表空间中,表空间是由一个或多个数据文件组成。  表空间  某一时刻只能属于一个数据库  由一个或多个数据文件组成  可进一步划分为逻辑存储  表空间主要分为两种  System表空间  随数据库创建  包含数据字典  包含system还原段  非system表空间  用于分开存储段  易于空间管理  控制分配给用户的空间量  数据文件  只能属于一个表空间和一个数据库  是方案对象数据的资料档案库  创建表空间  语法 CREATE TABLESPACE tablespacename [DATAFILE clause] [MINIMUM EXTENT integer[k|m]] [BLOCKSIZE integer[k]] [LOGGING|NOLOGGING] [DEFAULT storage_clause] [ONLINE|OFFLINE] [PERMANENT|TEMPORARY] [extent_management_clause] [segment_management_clause]  例子 --创建本地管理表空间 create tablespace firstSpance datafile 'e:/firstspance.dbf'size 100M extent management local uniform size 256k --修改文件大小 alter database datafile 'e:/firstspance.dbf' resize 110m --删除表空间 drop tablespace firstSpance INCLUDING CONTENTS and datafiles --使用数据库表空间 --创建用户指定表空间 create user guest identified by 123456 default tablespace firstSpance --表中指定表空间 create table account( accountid number(4), accountName varchar2(20) )tablespace firstSpance --表空间脱机 alter tablespace firstSpance offline --表空间联机 alter tablespace firstSpance online --表空间只读,不能进行dml操作 alter tablespace firstSpance read only 三、 同义词 Oracle数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名,经常用于简化对象访问和提高对象访问的安全性。Oracle同义词有两种类型,分别是公用Oracle同义词与私有Oracle同义词。  公有同义词  语法 CREATE [OR REPLACE] PUBLIC SYNONYM sys_name FOR [SCHEMA.] object_name  创建(需拥有CREATE PUBLIC SYNONYM权限才可以创建) --创建同义词 create public synonym syn_emp for scott.emp --访问同义词 select * from syn_emp  删除 drop public synonym syn_emp  私有同义词  语法 CREATE [OR REPLACE] SYNONYM sys_name FOR [SCHEMA.] object_name  创建 --创建同义词 create synonym syn_pri_emp for emp --访问同义词 select * from syn_ pri _emp  删除 drop public synonym syn_emp 四、 表分区 当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。  优点:  改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。  增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;  维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;  均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。  使用场合  表的大小超过2GB  表中包含历史数据,新的数据被增加都新的分区中  常见分区方法:  范围 --- 8  Hash --- 8i  列表 --- 9i  组合 --- 8i 1. 范围分区 范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。  特点:  最早、最经典的分区算法  Range分区通过对分区字段值的范围进行分区  Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。  数据管理能力强(数据迁移、数据备份、数据交换)  范围分区的数据可能不均匀  范围分区与记录值相关,实施难度和可维护性相对较差  例子  按值划分 --创建 CREATE TABLE book ( bookid NUMBER(5), bookname VARCHAR2(30), price NUMBER(8) )PARTITION BY RANGE (price)--分区字段 ( PARTITION P1 VALUES LESS THAN (4) TABLESPACE system, PARTITION P2 VALUES LESS THAN (8) TABLESPACE system, PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE system, ) --MAXVALUE代表了一个不确定的值,这个值高于其它分区中的任何分区键的值  按日期划分 CREATE TABLE student ( stuno NUMBER(5), stuname VARCHAR2(30), birthday date )PARTITION BY RANGE (birthday)--分区字段 ( PARTITION P1990 VALUES LESS THAN (to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system, PARTITION P1991 VALUES LESS THAN (to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system ); 2. Hash分区(散列分区) 这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型。如果你要使用hash分区,只需指定分区的数量即可。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。  特点  基于分区字段的HASH值,自动将记录插入到指定分区。  分区数一般是2的幂  易于实施  总体性能最佳  适合于静态数据  HASH分区适合于数据的均匀存储  数据管理能力弱  HASH分区对数据值无法控制  例子 CREATE TABLE classes ( clsno NUMBER(5), clsname VARCHAR2(30) )PARTITION BY HASH(clsno)--分区字段 ( PARTITION ph1 tablespace system, PARTITION ph2 tablespace system ) 3. List分区(列表分区) 该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。  特点  List分区通过对分区字段的离散值进行分区  List分区是不排序的,而且分区之间也没有关联  List分区适合于对数据离散值进行控制  List分区只支持单个字段  List分区具有与range分区相似的优缺点  数据管理能力强  各分区的数据可能不均匀  例子 CREATE TABLE users ( userid NUMBER(5), username VARCHAR2(30), province char(5) )PARTITION BY list(province)--分区字段 ( PARTITION pl1 values('广东') tablespace system, PARTITION pl2 values('江西') tablespace system, PARTITION pl3 values('广西') tablespace system, PARTITION pl4 values('湖南') tablespace system ); 4. 组合分区 常见的组合分区主要有范围散列分区和范围列表分区  特点  既适合于历史数据,又适合于数据均匀分布  与范围分区一样提供高可用性和管理性  实现粒度更细的操作  组合范围列表分区 这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。  例子 CREATE TABLE student ( stuno NUMBER(5), stuname VARCHAR2(30), birthday date, province char(5) )PARTITION BY RANGE (birthday) --主分区字段 subpartition BY LIST(province)--子分区字 ( PARTITION P1990 VALUES LESS THAN(to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system ( SUBPARTITION pl1 values('广东') tablespace system, SUBPARTITION pl2 values('江西') tablespace system, SUBPARTITION pl3 values('广西') tablespace system, SUBPARTITION pl4 values('湖南') tablespace system ), PARTITION P1991 VALUES LESS THAN(to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system ( SUBPARTITION p21 values('广东') tablespace system, SUBPARTITION p22 values('江西') tablespace system, SUBPARTITION p23 values('广西') tablespace system, SUBPARTITION p24 values('湖南') tablespace system ) );  组合范围散列分区 这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。  例子 CREATE TABLE student ( stuno NUMBER(5), stuname VARCHAR2(30), birthday date )PARTITION BY RANGE(birthday) --主分区字段 SUBPARTITION BY HASH(stuno)--子分区字 ( PARTITION P1990 VALUES LESS THAN(to_date('1990-01-01','yyyy-mm-dd')) TABLESPACE system ( SUBPARTITION ph12 tablespace system, SUBPARTITION ph13 tablespace system ), PARTITION P1991 VALUES LESS THAN(to_date('1991-01-01','yyyy-mm-dd')) TABLESPACE system ( SUBPARTITION ph21 tablespace system, SUBPARTITION ph22 tablespace system ) ); 5. 表分区常用操作  添加分区 --添加主分区 alter table book add partition p4 values less than(maxvalue) tablespace system --添加子分区 ALTER TABLE student MODIFY PARTITION P1990 ADD SUBPARTITION pl5 values('福建')  删除分区 --删除主分区 ALTER TABLE student DROP PARTITION P1990 --删除子分区 ALTER TABLE student DROP SUBPARTITION p15  重命名表分区 ALTER TABLE student RENAME PARTITION P21 TO P2  显示数据库所有分区表的信息 select * from DBA_PART_TABLES  显示当前用户所有分区表的信息 select * from USER_PART_TABLES  查询指定表分区数据 select * from users partition(pl2)--主分区 select * from users subpartition(phl2)--子分区  删除分区表一个分区的数据 alter table book truncate partition p11   第六章 视图 一、 概述 视图是基于一个表或多个表或视图的逻辑表,本身不包含数据,通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。 通过创建视图可以提取数据的逻辑上的集合或组合。  为什么使用视图  控制数据访问  简化查询  数据独立性  避免重复访问相同的数据  使用修改基表的最大好处是安全性,即保证那些能被任意人修改的列的安全性  Oracle中视图分类  关系视图  内嵌视图  对象视图  物化视图 二、 关系视图 关系视图是作为数据库对象存在的,创建之后也可以通过工具或数据字典来查看视图的相关信息。关系视图是4种视图中最简单,同时也最常用的视图。  语法 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 1. OR REPLACE:若所创建的试图已经存在,ORACLE自动重建该视图 2. FORCE:不管基表是否存在ORACLE都会自动创建该视图 3. NOFORCE:只有基表都存在ORACLE才会创建该视图 4. Alias:为视图产生的列定义的别名 5. subquery:一条完整的SELECT语句,可以在该语句中定义别名 6. WITH CHECK OPTION:插入或修改的数据行必须满足视图定义的约束 7. WITH READ ONLY:该视图上不能进行任何DML操作  例子 create or replace view view_Account_dept as select * from emp where deptno=10 --只读视图 create or replace view view_Account_dept as select * from emp where deptno=10 order by sal with read only --约束视图 create or replace view view_Account_dept as select * from emp where deptno=10 with check option  查询视图 select * from emp where view_Account_dept  修改视图 通过OR REPLACE 重新创建同名视图即可  删除视图 DROP VIEW VIEW_NAME语句删除视图  视图上的DML 操作原则 1. 简单视图可以执行DML操作; 2. 在视图包含GROUP函数,GROUP BY子句,DISTINCT关键字时不能执行delete语句 3. 在视图包含GROUP函数,GROUP BY子句,DISTINCT关键字,ROWNUM为例,列定义为表达式时不能执行update语句 4. 在视图包含GROUP函数,GROUP BY子句,DISTINCT关键字,ROWNUM为例,列定义为表达式,表中非空的列子视图定义中未包括时不能执行insert语句 5. 可以使用WITH READ ONLY来屏蔽DML操作 三、 内嵌视图 内嵌视图是在from语句中的可以把表改成一个子查询。内嵌视图不属于任何用户,也不是对象,内嵌视图是子查询的一种。  例子 Select * from (select * from emp where deptno=10) where sal>2000 四、 对象视图 对象类型在数据库编程中有许多好处,但有时,应用程序已经开发完成。为了迎合对象类型而重建数据表是不现实的。对象视图正是解决这一问题的优秀策略。 五、 物化视图 常用于数据库的容灾,不是传统意义上虚拟视图,是实体化视图,和表一样可以存储数据、查询数据。主备数据库数据同步通过物化视图实现,主备数据库通过data link连接,在主备数据库物化视图进行数据复制。当主数据库垮掉时,备数据库接管,实现容灾。  语法 create materialized view materialized_view_name build [immediate|deferred] --1.创建方式 refresh [complete|fast|force|never] --2.物化视图刷新方式 on [commit|demand] --3.刷新触发方式 start with (start_date) --4.开始时间 next (interval_date) --5.间隔时间 with [primary key|rowid] --默认 primary key ENABLE QUERY REWRITE --7.是否启用查询重写 as --8.关键字 select statement; --9.基表选取数据的select语句 1. 创建方式  immediate(默认):立即  deferred:延迟,至第一次refresh时,才生效 2. 物化视图刷新方式  force(默认):如果可以快速刷新,就执行快速刷新,否则,执行完全刷新  complete:完全刷新,即刷新时更新全部数据,包括视图中已经生成的原有数据  fast:快速刷新,只刷新增量部分。前提是,需要在基表上创建物化视图日志。该日志记录基表数据变化情况,所以才能实现增量刷新  never:从不刷新 3. 刷新触发方式  on commit:基表有commit动作时,刷新视图,不能跨库执行(因为不知道别的库的提交动作)  on demand,在需要时刷新,根据后面设定的起始时间和时间间隔进行刷新,或者手动调用dbms_mview包中的过程刷新时再执行刷新。 4. 开始时间和间隔时间  4和5即开始刷新时间和下次刷新的时间间隔。如:start with sysdate next sysdate+1/1440表示马上开始,刷新间隔为1分钟。(与 on commit选项冲突) 5. 创建模式  primary key(默认):基于基表的主键创建  rowed:不能对基表执行分组函数、多表连结等需要把多个rowid合成一行的操作 6. 是否启用查询重写  如果设置了初始化参数query_rewrite_enabled=true则默认就会启用查询重写。但是,数据库默认该参数为false。并且,不是什么时候都应该启用查询重写。所以,该参数应该设置为false,而在创建特定物化视图时,根据需要开启该功能。 7. 注意  如果选择使用了上面第4,5选项,则不支持查询重写功能(原因很简单,所谓重写,就是将对基表的查询定位到了物化视图上,而4、5选项会造成物化视图上部分数据延迟,所以,不能重写)。  例子 --创建增量刷新的物化视图时应先创建存储的日志空间 --在scott.emp表中创建物化视图日志 create materialized view log on emp tablespace users with rowid; --开始创建物化视图 --方式一 create materialized view mv_emp tablespace users --指定表空间 build immediate --创建视图时即生成数据 refresh fast --基于增量刷新 on commit --数据DML操作提交就刷新 with rowid --基于ROWID刷新 as select * from emp --方式二 create materialized view mv_emp2 tablespace users --指定表空间 refresh fast --基于增量刷新 start with sysdate --创建视图时即生成数据 next sysdate+1/1440 /*每隔一分钟刷新一次*/ with rowid --基于ROWID刷新 as select * from emp --删除物化视图日志 drop materialized view mv_emp   第七章 索引 一、 概述 索引是建立在表上的可选对象,设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担,进行影响系统的性能。 索引一旦建立后,当在表上进行DML操作时,Oracle会自动维护索引,并决定何时使用索引。 索引的使用对用户是透明的,用户不需要在执行SQL语句时指定使用哪个索引及如何使用索引,也就是说,无论表上是否创建有索引,SQL语句的用法不变。用户在进行操作时,不需要考虑索引的存在,索引只与系统性能相关。  索引的原理 当在一个没有创建索引的表中查询合某个条件的记录时,DBMS会顺序地逐条读取每个记录与查询条件进行匹配,这种方式称为全表扫描。全表扫描方式需要遍历整个表,效率很低。  索引的类型 Oracle支持多种类型的索引,可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类,以满足各种表和查询条件的要求。  单列索引和复合索引  B树索引  位图索引  函数索引  创建索引 CREATE [UNIQUE] | [BITMAP] INDEX index_name ON table_name([column1 [ASC|DESC],column2 [ASC|DESC],…] | [express]) [TABLESPACE tablespace_name] [PCTFREE n1] [STORAGE (INITIAL n2)] [NOLOGGING] [NOLINE] [NOSORT]  UNIQUE:表示唯一索引,默认情况下,不使用该选项。  BITMAP:表示创建位图索引,默认情况下,不使用该选项。  PCTFREE:指定索引在数据块中的空闲空间。对于经常插入数据的表,应该为表中索引指定一个较大的空闲空间。  NOLOGGING:表示在创建索引的过程中不产生任何重做日志信息。默认情况下,不使用该选项。  ONLINE:表示在创建或重建索引时,允许对表进行DML操作。默认情况下,不使用该选项。  NOSORT:默认情况下,不使用该选项。则Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的,则可以使用该选项。 二、 单列索引和复合索引 一个索引可以由一个或多个列组成。基于单个列所创建的索引称为单列索引,基于两列或多列所创建的索引称为多列索引。 三、 B树索引 B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时,默认创建的索引就是B树索引。B树索引就是一棵二叉树,它由根、分支节点和叶子节点三部分构成。叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表,因此可以对其进行任何方面的范围扫描。 B树索引中所有叶子节点都具有相同的深度,所以不管查询条件如何,查询速度基本相同。另外,B树索引能够适应各种查询条件,包括精确查询、模糊查询和比较查询。  例子 --创建B树索引,属于单列索引 create index idx_emp_job on emp(job) --创建B树索引,属于复合索引 create index idx_emp_nameorsal on emp(ename,sal) --创建唯一的B树索引,属于单列索引 create unique index idx_emp_ename on emp(ename) --删除索引 drop index idx_emp_job drop index idx_emp_nameorsal drop index idx_emp_ename --如果表已存在大量的数据,需要规划索引段 create index idx_emp_nameorsal on emp(ename,sal) pctfree 30 tablespace system 四、 位图索引 在B树索引中,保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说,这样做并不能显著提高查询的速度。所谓基数,是指某个列可能拥有的不重复值的个数。比如性别列的基数为2(只有男和女)。 因此,对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言,如果要建立索引,应该建立位图索引,而不是默认的B树索引。  例子 --创建位图索引,单列索引 create bitmap index idx_bm_job on emp(job) --创建位图索引,复合索引 create bitmap index idx_bm_jobordeptno on emp(job,deptno) --删除位图索引 drop index idx_bm_job drop index idx_bm_jobordeptno 五、 函数索引 函数索引既可以使用B树索引,也可以使用位图索引,可以根据函数或表达式的结果的基数大小来进行选择,当函数或表达式的结果不确定时采用B树索引,当函数或表达式的结果是固定的几个值时采用位图索引。  例子 --合并索引 alter index idx_emp_ename COALESCE 六、 并和重建索引 表在使用一段时间后,由于用户不断对其进行更新操作,而每次对表的更新必然伴随着索引的改变,因此,在索引中会产生大量的碎片,从而降低索引的使用效率。有两种方法可以清理碎片:合并索引和重建索引。  合并索引就是将B树叶子节点中的存储碎片合并在一起,从而提高存取效率,但这种合并并不会改变索引的物理组织结构。 --创建B树类型的函数索引 create index idx_fun_emp_hiredate on emp(to_char(hiredate,'yyyy-mm-dd')) --创建位图类型的函数索引 create index idx_fun_emp_job on emp(upper(job))  重建索引相当于删除原来的索引,然后再创建一个新的索引,因此,CREAT INDEX语句中的选项同样适用于重建索引。如果在索引列上频繁进行UPDATE和DELETE操作,为了提高空间的利用率,应该定期重建索引。 七、 管理索引的原则 使用索引的目的是为了提高系统的效率,但同时它也会增加系统的负担,进行影响系统的性能,因为系统必须在进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引,而是建议在创建表的时候用主键替代。因此,为了防止使用索引后反而降低系统的性能,应该遵循一些基本的原则: 1. 小表不需要建立索引。 2. 对于大表而言,如果经常查询的记录数目少于表中总记录数目的15%时,可以创建索引。这个比例并不绝对,它与全表扫描速度成反比。 3. 对于大部分列值不重复的列可建立索引。 4. 对于基数大的列,适合建立B树索引,而对于基数小的列适合建立位图索引。 5. 对于列中有许多空值,但经常查询所有的非空值记录的列,应该建立索引。 6. LONG和LONG RAW列不能创建索引。 7. 经常进行连接查询的列上应该创建索引。 8. 在使用CREATE INDEX语句创建查询时,将最常查询的列放在其他列前面。 9. 维护索引需要开销,特别时对表进行插入和删除操作时,因此要限制表中索引的数量。对于主要用于读的表,则索引多就有好处,但是,一个表如果经常被更改,则索引应少点。 10. 在表中插入数据后创建索引。如果在装载数据之前创建了索引,那么当插入每行时,Oracle都必须更改每个索引。 八、 ROWID和ROWNUM 1. ROWID rowid是一个伪列,是用来确保表中行的唯一性,它并不能指示出行的物理位置,但可以用来定位行。rowid是存储在索引中的一组既定的值(当行确定后)。我们可以像表中普通的列一样将它选出来, 利用rowid是访问表中一行的最快方式。rowid的是基于64位编码的18个字显示(数据对象编号(6)+文件编号(3) +块编号(6)+行编号(3)=18位) select rowid from emp  ROWID的使用 --快速删除重复的记录 delete from temp t where rowid not in( select max(rowid) from temp where t.id=id and t.name=name and t.sal = sal ) 2. ROWNUM ROWNUM是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类推。 select rownum,emp.* from emp  ROWID的使用 --取前3条记录 select * from emp where rownum<=3--方式一 select * from emp where rownum!=4--方式二 --分页 select * from emp where empno not in( select empno from emp where rownum<5--方式一 ) and rownum <4   第八章 PL/SQL编程 一、 介绍 PL/SQL是oracle在标准sql语言上的扩展,PL/SQL不仅允许嵌入sql语言,还可以定义变量和常量,允许使用例外处理各种错误,这样使它的功能变得更加强大。 PL/SQL也是一种语言,叫做过程化sql语言(procedural language/sql),通过此语言可以实现复杂功能或者复杂的计算。  优点 1. 提高应用程序的运行性能 2. 模块化的设计思想 3. 减少网络传输量 4. 提高安全性  缺点 1. 可移植性差 2. 违反MVC设计模式 3. 无法进行面向对象编程 4. 无法做成通用的业务逻辑框架 5. 代码可读性差,相当难维护  分类 二、 PL/SQL基础 1. 编写规范 1) 注释 --单行注释 /*块注释*/ 2) 标识的命名规范  定义变量:建议用v_作为前缀v_price  定义常量:建议用c_作为前缀c_pi  定义游标:建议用_cursor作为后缀emp_cursor  定义例外:建议用e_作为前缀e_error 2. 块结构 PL/SQL块由三个部分组成:定义部分、执行部分、例外处理部分 Declare /* 定义部分(可选):定义常量、变量、游标、例外,复杂数据类型 */ begin /* 执行部分(必须):要执行的PL/SQL语句和SQL语句 */ exception /*例外部分(可选):处理运行各种错误*/ end 案例一 :只定义执行部分 begin /* dbms_output是oracle提供的包(类似java开发包) 该包包含一些过程,put_line就是其一个过程 */ dbms_output.put_line('HELLO WORLD'); --控制台输出 end; 案例二 :定义声明部分和执行部分 declare --声明变量 v_name varchar2(20); v_sal number(7,2); begin --执行查询 select ename,sal into v_name,v_sal from emp where rownum=1; --控制台输出 dbms_output.put_line('用户名:' || v_name); dbms_output.put_line('工资:' || v_sal); end; 案例三 :定义声明部分、执行部分和例外部分 declare --声明变量 v_name varchar2(20); v_sal number(7,2); begin --执行查询,条件中的&表示从控制接受数据 select ename,sal into v_name,v_sal from emp where empno=&no; --控制台输出 dbms_output.put_line('用户名:' || v_name); dbms_output.put_line('工资:' || v_sal); exception --例外处理(no_data_found) when no_data_found then dbms_output.put_line('执行查询没有结果'); end; 3. 预定义例外 1) case_not_found预定义例外 在开发pl/sql块中编写case语句时,如果在when子句中没有包含必须的条件分支,就会触发case_not_found例外。 2) cursor_already_open预定义例外 当重新打开已经打开的游标时,会隐含的触发cursor_already_open例外。 3) dup_val_on_index预定义例外 在唯一索引所对应的列上插入重复的值时,会隐含的触发例外 4) invalid_cursorn预定义例外 当试图在不合法的游标上执行操作时,会触发该例外 5) invalid_number预定义例外 当输入的数据有误时,会触发该例外 6) no_data_found预定义例外 当执行select into没有返回行,就会触发该例外 7) too_many_rows预定义例外 当执行select into语句时,如果返回超过了一行,则会触发该例外 8) zero_divide预定义例外 当执行2/0语句时,则会触发该例外 9) value_error预定义例外 当在执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该例外value_error 10) others 4. 变量类型分类 在编写PL/SQL时,可以定义变量和常量,常用的类型主要有:  标量类型(scalar)  复合类型(composite)  参照类型(reference)  lob(large object) 5. 标量类型:常用类型 declare --定义一个变长字串 v_name varchar2(20); --定义小数,并赋值 v_sal number(7,2) :=9.8; --定义整数 v_num number(4); --定义日期 v_birthday date; --定义布尔类型,不能为空,初始值为false v_flg boolean not null default false; --使用%type类型 v_job emp.job%type; begin v_flg := true; v_birthday :=sysdate; dbms_output.put_line('当前时间:' || v_birthday); end; 6. 复合类型:可以存放多个值。主要包括PL/SQL记录、PL/SQL表、嵌入表和varray这四种类型 记录类型:类似于c中的结构体 declare --定义记录类型 type emp_record_type is record( empno emp.empno%type, ename emp.ename%type, sal emp.sal%type ); --定义变量引用记录类型 v_record emp_record_type; begin --使用记录类型 select empno,ename,sal into v_record from emp where rownum=1; --控制台输出 dbms_output.put_line('雇员编号:' || v_record.empno); dbms_output.put_line('雇员姓名:' || v_record.ename); dbms_output.put_line('雇员工资:' || v_record.sal); end; 表类型:类似于java语言中的数组 declare --声明表类型 type emp_table_type is table of varchar2(20) index by PLS_INTEGER;--表示表按整数来排序 v_enames emp_table_type;--定义变量引用表类型 begin select ename into v_enames(0) from emp where rownum=1; select ename into v_enames(1) from emp where empno=7499; select ename into v_enames(2) from emp where empno=7698; --输出 dbms_output.put_line('下标0:' || v_enames(0)); dbms_output.put_line('下标1:' || v_enames(1)); dbms_output.put_line('下标2:' || v_enames(2)); end; varray类型:可变长数组 declare --定义varray类型 type varray_list is varray(20) of number(4); --定义变量引用varray类型 v_list varray_list:=varray_list(7369,7499,7566); begin --for i in v_list.first..v_list.last for i in 1..v_list.count loop dbms_output.put_line(v_list(i)); end loop; end; PL/SQL集合方法 1) exists():用于确定特定集合元素是否存在 2) count:用于返回集合变量的元素总个数 3) limit:用于返回varray变量所允许的最大元素个数 4) first:用于返回集合变量中的一个元素的下标 5) last:用于返回集合变量中最后一个元素的下标 6) prior():返回当前元素前一个元素的下标 7) next():返回当前元素后一个元素的下标 8) extend:为集合变量添加元素,此方法适合用于嵌套表和varray 9) trim:从集合变量尾部删除元素,此方法适用于嵌套表和varray 10) delete:从集合变量中删除特定的元素,此方法适用于嵌套表和index-by表 7. 参照类型:类似c语言中的指针,oracle的游标 三、 PL/SQL控制语句 1. 条件分支语句 1) if—then declare --声明变量 v_empno emp.empno%type; v_sal emp.sal%type; begin --根据雇员编号查询工资 select empno,sal into v_empno,v_sal from emp where empno=&no; --如果工资小于2000就加100 if v_sal<2000 then --工资加100 update emp set sal = sal+100 where empno=v_empno; --提交 commit; end if; end; 2) if—then—else declare --声明变量 v_loginname varchar2(10); v_password varchar2(10); begin --从控制台接收数据 v_loginname := '&ln'; v_password := '&pw'; if v_loginname = 'admin' and v_password = '123456' then dbms_output.put_line('用户登录成功!'); else dbms_output.put_line('用户登录失败!'); end if; end; 3) if—then—elsif—else declare --声明变量 v_empno emp.empno%type; v_job emp.job%type; begin --根据雇员编号查询职位 select empno,job into v_empno,v_job from emp where empno=&no; /*如果雇员所属职位是manager工资加1000 职位是salesman工资加500 其他职位加200 */ if v_job = 'MANAGER' then --MANAGER职位工资加1000 update emp set sal = sal+1000 where empno=v_empno; elsif v_job = 'SALESMAN' then --SALESMAN职位工资加500 update emp set sal = sal+500 where empno=v_empno; else --其他职位工资加200 update emp set sal = sal+200 where empno=v_empno; end if; --提交 commit; end; 4) case declare --声明变量 v_mark number(4); v_outstr varchar2(40); begin --从控制台接收成绩 v_mark := &m; case when v_mark=90 then v_outstr := '优秀'; when v_mark=80 then v_outstr := '良好'; when v_mark=70 then v_outstr := '中等'; when v_mark=60 then v_outstr := '及格'; when v_mark=0 then v_outstr := '不及格'; else v_outstr := '成绩输入有误'; end case; --控制台输出 dbms_output.put_line(v_outstr); end; 2. 循环语句 1) loop LOOP 要执行的语句; EXIT WHEN /*条件满足,退出循环语句*/ END LOOP; 其中:EXIT WHEN 子句是必须的,否则循环将无法停止。 declare v_num number(4):=1; begin --从控制台接收数据并插入到account表中 loop insert into account values(v_num,'&name'); exit when v_num =10; v_num :=v_num+1; end loop; end; 2) while WHILE LOOP要执行的语句;END LOOP; 其中:  循环语句执行的顺序是先判断的真假,如果为真则循环执行,否则退出循环  在WHILE循环语
先登陆服务器: telnet 192.168.0.23 公帐号: openlab-open123 tarena-tarena 再进入SQL:sqlplus sd0807/sd0807 帐号:sd0807-密码同样 公帐号:openlab-open123 设置环境变量ORACLE_SID=oral10g\ --变局部变量 export ORACLE_SID --变全局变量 unset ORACLE_SID --卸载环境变量 ORACLE_HOME=... --安装路径;直接用一句语句也可以,如下 export ORACLE_HOME=/oracledata/.../bin: 一、注意事项: 大小写不敏感,即不区分大小写。提倡关键字大写,便于阅读和调式。 “!”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成;这些英语单词称为关键字/保留字,不做它用。SQL由多个关键字构成。 SQL语句由子句构成,有些子句是必须的,有些是可选的。 在处理SQL语句时,其中所有的空格都被忽略(空格只用来分开单词,连续多个空格当一个用)。 SQL语句可以在一行上写出,建议多行写出,便于阅读和调试。 多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号结束 二、写子句顺序 Select column,group_function From table [Where condition] [Group by group_by_expression] [Having group_condition] …… [Order by column]; --最后 三、常用简单语句: clear screen:清屏 edit:编辑刚才的一句。 desc/describe:(列出所有列名称) 用法: DESCRIBE [schema.]object[@db_link] dual:亚表,临时用。如:desc dual;/from dual; rollback:回溯,回溯到上次操作前的状态,把这次事务操作作废,只有一次(DDL和DCL语句会自动提交,不能回溯)。 可以用commit语句提交,这样就回溯不回了。 set pause on\off :设置分屏(设置不分屏) set pause "please put an enter key" 且 set pause on:设置带有提示的分屏 oerr ora 904 :查看错误 set head off :去掉表头 set feed off :去掉表尾 保存在oracle数据库中的所有操作细节: spool oracleday01.txt :开始记录 spool off :开始保存细节 四、SELECT语句:选择操作、投影操作。 select:从一个或多个表中检索一个或多个数据列。包含信息:想选择什么表,从什么地方选择。必须要有From子句。(最常用) 当从多张表里查询的时候,会产生笛卡尔积;可用条件过滤它。 当两个表有相同字段时必须加前缀,列名前需加表名和“.”,如“s_emp.id”。 1、用法:SELECT columns,prod2,prod3<列> FROM Table1,table2<表名> 分号结束 如: select id from s_emp; select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--列表每人所在部门 SELECT * FROM Products; --检索所有列。 数据太多时,最好别使用上句,会使DBMS降低检索和应用程序的性能。(*通配) 2、对数据类型的列可进行运算(如加减乘除)。 3、对列起别名:有直接起别名,加AS起别名,用双引号起别名等三种方法 (单引号,引起字串;双引号,引起别名。起别名有号,或者区分大小写时,必须用双引号) 多表查询时,可给表起别名。(给列起别名,列<空格>列别名;给表起别名,表<空格>表别名;)。 如:Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E; 4、字段的拼接,可用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作;拼接的字段同样可以起别名。 如:Select first_name ||' '|| last_name || ', '|| title "Employees" From s_emp; 排他锁:Select id,salary From s_emp where id=1 For Update; 可以阻止他人并发的修改,直到你解锁。 如果已有锁则自动退出:Select id,salary From s_emp where id=1 For Update NoWait; FOR UPDATE :可以再加 OF 精确到某格。如: ... For Update OF salary ... 注意要解锁。 五、ORDER BY 子句,排序 Order by:按某排序列表(默认升序 asc,由低到高;可加 desc,改成降序由高到低) 检索返回数据的顺序没有特殊意义,为了明确地排序用 SELECT 语句检索出的数据,可使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字。 对空值,按无穷大处理(升序中,空值排最后;降序中排最前)。 1、用法:Select prod_id,prod_price,prod_name From Products Order By prod_price,prod_name; (从左到右执行排序,先排price) ORDER BY子句中使用的列将是为显示所选择的列,但是实际上并不一定要这样,用非检索的列排序数据是完全合法的。 为了按多个列排序,列名之间用逗号分开。 2、支持按相对列位置进行排序。 输入 SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3 --(2指price,3指name) 3、升序、降序。默认是升序(asc,从小到大排序),想降序时用desc。 如:SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC; 注意:DESC 关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序,必须对每个列指定DESC关键字。 升序是默认的,可不写,但降序必须写。 六、WHERE子句,选择、过滤 其后只能跟逻辑语句,返回值只有ture或false 如: select last_name,salary from s_emp where salary=1000;--找出工资1000的人 WHERE子句操作: 1、逻辑比较运算 = 等于 != 不等于,还有(<> ^= 这两个同样表示不等于) > 大于 >= 大于等于 < 小于 <= 小于等于 2、SQL 比较运算 between…and… :在两者之间。(BETWEEN 小值 AND 大值) 如:select last_name,salary from s_emp where salary between 1000 and 1500; --工资1000到1500的人,包括1000和1500。 in(列表):在列表里面的。 如:select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人 like : 包含某内容的。模糊查询 可以利用通配创建比较特定数据的搜索模式,通配只能用于文本,非文本数据类型不能使用通配。 通配在搜索模式中任意位置使用,并且可以使用多个通配。 通配%表示任何字出现任意次数;还能代表搜索模式中给定位置的0个或多个字。下划线匹配单个任意字。 如:select table_name from user_tables where table_name like 'S\_%' escape'\'; ' 找出“S_“开头的,由于下划线有任意字的含义,故需另外定义转移。 但习惯用“\”,为方便其他程序员阅读和检测,一般不改用其他的。 like 'M%':M开头的 like '_a%':第二个字是a的 like '%a%'所有含a的 (“_”表示一个任意字;“%”表示任意多个任意字。) 单引号里面的内容,大小写敏感。单引号用来限定字串, 如果将值与串类型的列进行比较,则需要限定引号;用来与数值列进行比较时,不用引号。 is null:是空。(NULL表示不包含值。与空格、0是不同的。) 如:SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL; 七、高级检索(逻辑运算): 通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件 计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序,使用圆括号()没有坏处,它能消除二义性。 and:条件与 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’ or:条件或 (注: and 的优先级比 or 更高,改变优先级可用括号) 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’ not:条件非。否定它之后所跟的任何条件 否定的SQL 比较运算: NOT BETWEEN; NOT IN; NOT LIKE; IS NOT NULL: (注意,按英语习惯用 is not,而不是 not is) NOT 与 IN 在一起使用时,NOT 是找出与条件列表不匹配的行。 IN 列表里有 NULL 时不处理,不影响结果;用 NOT IN 时,有 NULL 则出错,必须排除空值再运算。 in :选择列表的条件 使用IN操作的优点: 在长的选项清单时,语法直观; 计算的次序容易管理; 比 OR 操作清单执行更快;最大优点是可以包含其他 SELECT 语句,使用能够动态地建立 WHERE 子句。 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’); 八、单行函数: 函数一般在数据上执行,它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。 函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。 加入注释是一个使用函数的好习惯。 大多数SQL实现支持以下类型的函数: 文本处理, 算术运算, 日期和时间, 数值处理。 Null:空值 空值当成无穷大处理,所有空值参与的运算皆为空。 空值空值并不相等,因为空值不能直接运算。 如:prod_price="" 这种写法是错的(不要受到corejava的影响) prod_price=NULL 这种写法是错的(不要受到corejava的影响) prod_price IS NULL 这种写法才是对的 NVL:处理空值,把空值转化为指定值。可转化为日期、字、数值等三种(注意:转化时,两参数必须要同类型) 如:NVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0) 错误写法: Select last_name,title,salary*commission_pct/100 COMM From s_emp;--没提成的人没法显示工资 正确写法: Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--把提成是空值的转化为0 DISTINCT:过滤重复 把重复的行过滤掉;多个字段组合时,只排除组合重复的。 DISTINCT必须使用列名,不能使用计算或者表达式。 所有的聚合函数都可以使用。如果指定列名,则DISTINCT只能用于COUNT(列名),DISTINCT不能用于COUNT(*)。 如:Select Distinct name From s_dept; Select Distinct dept_id,title From s_emp; 文本处理: TRIM()/LTRIM()/RTIRM():去空格。只能去掉头和尾的空格,中间的不理。 trim(' heo Are fdou ') --> heo Are fdou 输入:select trim(' heo Are fdou ') from dual; -->:heo Are fdou LOWER:转小写 lower('SQL Course') --> sql course UPPER:转大写 upper(' SQL Course') --->SQL COURSE INITCAP:首字母转大写,其余转小写 initcap(SQL Course') '--> Sql Course CONCAT:合成。双竖线只能在select语句里面用,这个可用于任何语句。 Concat('Good','String') --> GoodString SUBSTR:截取。 Substr('String', 1 ,3) --> Str 第一个数字“1”,表示从第几个开始截取;若要从倒数第几个开始,用负数,如“-2”表示倒数第2个。 上式中第2个数字“3”表示截取多少个。 LENGTH:统计长度。 Length('String') --> 6 NVL:转换空值 日期和时间处理: Oracle日期格式:DD-MMM-YYYY (D代表日期date,M代表月month,Y代表年year) 如:SELECT prod_name (DAY表示完整的星期几,DY显示星期的前三个字母) FROM Products WHERE prod_time BETWEEN to_date(’01-JAN-2008’) AND to_date(’31-DEC-2008’); 日期可以进行加减,默认单位是1天。日期与日期可以相减,得出天数;日期与日期但不能相加。 sysdate -> 系统的当天 Months_Between('01-Sep-95','11-Jan-94') --> 19.774194 相差多少个月,Between里面也可以填函数。 Add_months('11-Jan-94',6) --> 11-Jul-94 增加多少个月 Next_day('01-Sep-95','Friday') --> '08-Sep-95' 下一个星期五。其中的'Friday'可用6替代,因为星期日=1 Last_day('01-Sep-95') --> '30-Sep-95' 这个月的最后一天 数值处理:可以运用于代数,三角,几何 ROUND:四舍五入 Round(45.925,2) -> 45.93 Round(45.925,0) -> 46 Round(45.925,-1) -> 50 逗号前一个数是要处理的数据源,后一个参数表示保留多少位小数。 后一参数是负数时,表示舍去小数点前的几位,例3是舍去个位及其后的。不写后一参数时,默认不保留小数。 TRUNC:舍去末位。直接舍去,不会进位。 Trung(45.925,2) -> 45.92 Trung(45.925,2) -> 45.92 Trung(45.925,2) -> 45.92 日期的舍取: 常用的数值处理函数有: ABS() 绝对值 ABS(-5741.5854) --> 5741.5854 PI() 圆周率 注意:oracle中不支持 PI()函数;MYSql 支持PI()函数。 SIN() 正统值 Oracle还支持COS()、ASIN()、ACOS()函数 SQRT() 平方根 转化: TO_CHAR(number,'fmt'):把数值转换成字串 显示数字的命令 9:正常显示数字; 0:显示包括0的数值形式,空位强制补0; $:以美元号显示货币; L:按当前环境显示相关的货币号; . 和,:在固定位置出现“.”点 和“,”逗号;不够位时,四舍五入。 例题:SQL> select 'Order'||To_char(id)|| 2 'was filled for a total of' 3 ||To_char(total,'fm$9,999,999') 4 from s_ord 5 where ship_date ='21-SEP-92'; TO_NUMBER(char):把字转换成数字 九、链接 内链接:严格匹配两表的记录。 外链接分左链接和右链接: 会使用一方表中的所有记录去和另一格表中的记录按条件匹配,空值也会匹配,这个表中的所有记录都会显示, 数据库会模拟出记录去和那些不匹配的记录匹配。 左链接 加号在右面 如:有 TABLE1 TABLE2 1的一条记录在2里面没有匹配上,那么1里面的记录保留 2的一条记录在1里面没有匹配上 ,那么2丢弃 右链接正好相反 --例题:哪些人是领导。 select distinct b.id,b.last_name manager from s_emp a,s_emp b where a.manager_id=b.id(+); 左右顺序有区别,这是另外新建一个表,要显示的是第二个表格的内容。 +放在没有匹配行的表一侧,令表格能完整显示出来。 标准写法:内连接用INNER,左连接用LEFT,右连接用RIGHT。 select distinct b.id,b.last_name manager from s_emp a LEFT join s_emp b ON a.manager_id=b.id; 十、组函数: 分组允许将数据分为多个逻辑组,以便能对每个组进行聚集计算。 Group:分组 Group by:分组。(默认按升序对所分的组排序;想要降序要用 order by)可以包括任意数目的列。 如果嵌入了分组,数据将在最后规定的分组上进行汇总。 GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数。 *如果在SELECT 中使用表达式,则必须在GROUP BY子句中指定相同的表达式,不能使用别名。 除聚合计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL,它们将分为一组。 Having:过滤。分组之后,不能再用where,要用having 选择过滤。Having不能单独存在,必须跟在group by后面。 WHERE在数据分组前进行过滤,HAVING在数据分组后过滤。 可以在SQL中同时使用 WHERE和HAVING,先执行WHERE,再执行HAVING。 聚合函数: AVG:平均值 (忽略值为NULL的行,但不能用 AVG(*)) COUNT:计数 (Count(列)不计算空值;但 COUNT(*)表示统计表中所有行数,包含空值) MAX:最大值 (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值, 在作用于文本数据时,如果数据按照相应的列排序,则 MAX()返回最后一行。) MIN:最小值 (忽略值为 NULL 的行。不能用 MIN(*)。一般是找出数值或者日期值的最小值。 但有些DBMS还允许返回文本列中的最小值,这时返回文本最前一行) SUM:求和 (忽略值为 NULL 的值。SUM 不能作用于字串类型,而 MAX(),MIN()函数能。也不能 SUM(*)) 子查询:查询语句的嵌套 可以用于任意select 语句里面,但子查询不能出现 order by。 子查询总是从内向外处理。作为子查询的SELECT 语句只能查询单个列,企图检索多个列,将会错误。 如:找出工资最低的人select min(last_name),min(salary) from s_emp; 或者用子查询select last_name,salary from s_emp where salary=(select min(salary) from s_emp); E-R图:属性: E(Entity) -R(Relationship) * (Mandatory marked 强制的) 强制的非空属性 o (Optional marked 可选的) 可选属性(可以有值也可以没有) #* (Primary marked ) 表示此属性唯一且非空 约束:针对表中的字段进行定义的。 PK:primary key (主键约束,PK=UK+NN)保证实体的完整性,保证记录的唯一 主键约束,唯一且非空,并且每一个表中只能有一个主键,有两个字段联合作为主键, 只有两个字段放在一起唯一标识记录,叫做联合主键(Composite Primary Key)。 FK:foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值, 只能够引用主键会唯一键的值,被引用的表,叫做parent table(父表),引用方的表叫做child table(子表); child table(子表),要想创建子表,就要先创建父表,后创建子表,记录的插入也是如此,先父表后子表, 删除记录,要先删除子表记录,后删除父表记录, 要修改记录,如果要修改父表的记录要保证没有被子表引用。要删表时,要先删子表,后删除父表。 U:unique key(唯一键 UK),值为唯一,不能重复。 在有唯一性约束的列,可以有多个空值,因为空值不相等。 NN:NOT NULL,不能为空。 index(索引)是数据库特有的一类对象,实际应用中一定要考虑索引,view(示图) 数量关系: 一对一关系 多对一关系 一对多关系 多对多关系 范式: 好处:降低数据冗余;减少完整性问题;标识实体,关系和表 第一范式(First normal form:1Nf),每一个属性说一件事情。所有的属性都必须是单值,也就是属性只表示单一的意义。 (记录可以重复,会有大量冗余,没有任何限制) 第二范式(2N范式),最少有一个属性要求唯一且非空PK,其他跟他有关联(记录不可重复,但是数据可能会出现冗余)。 第三范式(3N范式),非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据) 一般情况会做到第三范式。 创建表: Create Table 表名 (字段名1 类型(数据长度)(default ...) 约束条件, 字段名2 类型(数据长度) 约束条件 ); 建表的名称: 必须字母开头;最多30字;只能使用“A~Z、a~z、0~9、_、$、#”; 同一目录下不能有同名的表;表名不能跟关键字、特殊含意字同样。 如:create table number_1 (n1 number(2,4), n2 number(3,-1), n3 number); create table t_sd0808(id number(12) primary key,name varchar(30) not null); MySQL的: create table student (oid int primary key, ACTNO varchar(20) not null unique, BALANCE double); --MySQL的number类型分小类了,Oracle只有number,且MySQL的数值型不用定大小 Oracle的: create table t_ad (oid number(15) primary key, ACTNO varchar(20) not null unique,BALANCE number(20)); INSERT:插入(或添加)行到数据库表中的关键字。 插入方式有以下几种:插入完整的行;插入行的一部分;插入某些查询的结果。 对于INSERT操作,可能需要客户机/服务器的DBMS中的特定的安全权限。 插入行(方式一) INSERT INTO products VALUES(2008,’TV’,222.22,’US’); 依赖于表中定义的顺序,不提倡使用。有空值时需要自己补上。 插入行(方式二) INSERT INTO products(id,name,price,vend_name) VALUES(2008,’TV’,222.22,’US’); 依赖于逻辑顺序,会自动补上空值,提倡使用插入检索出的数据:可以插入多条行到数据库表中 INSERT INTO products(*,*,*,*) SELECT *,*,*,* FROM products_copy; 如果这个表为空,则没有行被插入,不会产生错误,因为操作是合法的。 可以使用WHERE加以行过滤。 复制表: 将一个表的内容复制到一个全新的表(在运行中创建,开始可以不存在) CREATE TABLE 新表名 AS SELECT * FROM 表名; INSERT INTO 与 CREATE TABLE AS SELECT 不同,前者是导入数据,而后者是导入表。 任何SELECT选项和子句都可以使用,包括WHERE和GROUP BY。 可利用联接从多个表插入数据。不管从多少个表中检索数据,数据都只能插入到单个表中。 更新数据 UPDATE 语句 需要提供以下信息:要更新的表;列名和新值;确定要更新的哪些行的过滤条件。 UPDATE 表名 SET vend_name = ‘HP’, prod_name = ‘NEWCOMPUTER’ WHERE vend_name = ‘IBM’; --UPDATE 语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为 NULL。 删除数据 DELETE 语句 DELETE FROM products WHERE prod_name = ‘COMPUTER’; 全行删除,不要省略WHERE,注意安全。 DELETE不需要列名或通配。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。 如果想从表中删除所有内容,可以使用TRUNCATE TABLE语句(清空表格),它更快。 数字字典表: Sequence:排列。存储物理地址 Index:索引。依附于表,为提高检索速度。 View:视图。看到表的一部分数据。 限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。 Create[or Relace][Force|noForce] View 视图名 [(alias[,alias]…)] 别名列表 As subquery [With Check Option [Constraint ……]] [With Read Only] 注意:有些DBMS不允许分组或排序视图,不能有 Order by 语句。可以有 Select 语句。 删除视图: DROP VIEW 视图名 Rownum:纬列。内存里排序的前N个。 在where语句中,可以用=1,和<=N 或 N。 因为这是内存读取,没有1就丢弃再新建1。只能从1开始。需要从中间开始时,需二重子rownum语句需取别名。 经典应用: Top-n Analysis (求前N名或最后N名) Select [查询列表], Rownum From (Select [查询列表(要对应)] From 表 Order by Top-N_字段) Where Rownum <= N 分页显示: --取工资第5~10名的员工(二重子rownum语句,取别名) select rn,id,last_name,salary From ( select id,last_name,salary,Rownum rn From (Select id,last_name,salary from s_emp order by salary desc) where rownum <= 10) where rn between 5 and 10; Union:合并表 Select … Union Select… 把两个Select语句的表合并。 要求两表的字段数目和类型按顺序对应。合并后的表,自动过滤重复的行。 Intersect:交。 同上例,把两个Select表相交。 Minus:减。 把相交的内容减去。 not exists 除运算。 添加字段(列): Alter Table 表名 Add (column dataype [Default expr][Not Null] [,column datatype]…); 添加有非空限制的字段时,要加Default语句 字段名字不可以直接改名,需要添加新字段,再复制旧字段后删除旧字段。 添加约束: Alter Table 表名 Add [CONSTRAINT constraint] type (column); 添加非空约束时,要用Modify语句。 查看约束名时,可以违反约束再看出错提示;或者查看约束字典desc user_constraints 减少字段: Alter Table 表名 Drop (column [,column]…); 删除约束: Alter Table 表名 Drop CONSTRAINT column; 或: Alter Table 表名 Drop Primary Key Cascade; 暂时关闭约束,并非删除: Alter Table 表名 Disable CONSTRAINT column Cascade; 打开刚才关闭的约束: Alter Table 表名 Enable CONSTRAINTcolumn; 修改字段: Alter Table 表名 Modify (column dataype [Default expr][Not Null] [,column datatype]…); 修改字段的类型、大小、约束、非空限制、空值转换。 删除表: 会删除表的所有数据,所有索引也会删除,约束条件也删除,不可以roll back恢复。 Drop Table 表名 [Cascade Constraints]; 加 [Cascade Constraints] 把子表的约束条件也删除;但只加 [Cascade]会把子表也删除。 改表名: Rename 原表名 To 新表名; 清空表格: TRUNCATE TABLE 表名; 相比Delete,Truncate Table清空很快,但不可恢复。清空后释放内存。 Delete 删除后可以roll back。清空后不释放内存。

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧