• 1. Oracle数据库管理与开发银海软件 李光伟 2008年3月
  • 2. 日程安排第一部分 Oracle 快速入门、常用工具介绍 Oracle管理:对象、存储、安全 第二部分 SQL基础介绍——查询、更新语句 SQL基础介绍——序列、权限等 第三部分 PL/SQL 基本概念、块结构、流程控制、游标 PL/SQL 异常处理,存储过程、函数、触发器2
  • 3. 第一部分 Oracle管理通过学习本课程,您将掌握: 快速入门 如何安装Oracle数据库软件 创建数据库 配置监听 配置连接 回顾常用工具 基本Oracle管理 基本概念 介绍表、视图、索引、函数等模式对象 Oracle安全3
  • 4. 第一章 快速入门目标 安装 Oracle软件 创建数据库 配置监听 配置服务名 卸载Oracle4
  • 5. 任务 1:安装数据库软件目标:在剩余空间最大的分区X上安装Oracle数据库企业版 Oracle_Base=X:\Oracle Oracle_Home=X:\Oracle\product\10.2.0 Oracle_Home_Name OH_DB10g Oracle_Sid=orcl5
  • 6. Oracle目录结构Oracle_Base Oracle_Home Oracle_Home\bin (Path环境变量) Oracle_Home\network 6
  • 7. 任务2:创建数据库使用DBCA工具进行创建 Oracle SID 7
  • 8. Oracle目录结构Oracle_Base\Admin目录 Oracle_Base\Oradata目录 8
  • 9. 任务3:配置监听目的:使客户端可以连接到本数据库9
  • 10. 任务4:配置网络服务名创建2个服务名,一个指向本机(mydb),另一个指向服务器(pubdb),以便访问服务器上的数据库,并进行测试。 10
  • 11. Oracle的卸载步骤正常卸载 1 备份数据库; 使用exp命令 2 删除数据库; 使用dbca删除数据库 使用netca删除监听 3 卸载软件 Universal Installer 强行卸载 删除Oracle相关目录Oracle_Base下及C:\Program files\Oracle 某些文件必须重新启动后才能删除 删除注册表项 HKLM\Software\Oracle 和服务相关的注册项11
  • 12. 常用工具Sqlplus/sqlplusw 命令行工具 Exp/Imp——导入/导出工具 Sqlldr——文本文件导入工具 OEM——Oracle企业管理工具 Netca——网络配置助手 dbca——数据库配置助手 Universal Installer——安装器12
  • 13. Sqlplus登录 设置环境 执行sql语句 DDL语句(数据定义语句,如:表创建等) DML语句(事务性语句,如:insert、update) DCL语句(控制语句,如:grant)13
  • 14. DBCA14
  • 15. Netca15
  • 16. Netca-服务命配置样例251 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb) ) )16
  • 17. OEM工具17
  • 18. 任务5:利用OEM工具创建表空间创建名为data的表空间,包含2个数据文件,data01.dbf,data02.dbf,大小分别为16M和32M http://ligw:1158/em18
  • 19. 任务6:创建用户创建名为yinhai的用户,口令为password,设置默认表空间为data,在该表空间上的空间限额为20M,默认临时表空间为temp。为yinhai用户赋予connect,resource的角色权限。19
  • 20. Exp/Imp工具的使用将oracle数据   二进制文件 exp user/pass@svc file=xxx.dmp log=xxx.log … Imp user/pass@svc file=xxx.dmp log=xxx.log full=y …20
  • 21. 任务5:导出Oracle对象目的:将Oracle某个用户下的所有对象导出到一个二进制文件 使用EXP命令 exp user/pass@svcname file=xxx … 重要参数 file log feedback query tables21
  • 22. 任务6:将dmp文件导入到Oracle目的:将dmp文件导入到Oracle某个用户下 使用IMP命令 imp user/pass@svcname file=xxx … 重要参数full fromuser touser feedback indexes rows 22
  • 23. 任务7:利用OEM工具创建表部门表dept Deptno varchar2(4) primary key –部门编号 Deptname varchar2(10) –部门名称 员工表emp: Deptno varchar2(4) –部门编号 外键 Id number(4) primary key, --使用序列自动编号 Id_card varchar2(18) --身份证号码 Name varchar2(10) --姓名 Birthday date --出生日期 Gender char(1) --性别男为1,女为2 Graduated_From varchar2(40)--毕业院校 Manager number(4) --领导ID Salary number(7,2) --薪水23
  • 24. Sqlldr编写控制文件 准备数据文件 运行sqlldr 是否成功? 24
  • 25. 任务8: 将文本文件导入到库表 将文本文件内的数据导入到yinhai用户的emp表内 101,殷绍昕,2,KM,3,2450 102,任海军,1,KM,3,1780 103,刘应德,1,KM,3,3290 104,郭志,1,CD,2,4580 105,扶晓,2,CD,2,980 106,房志勇,1,CD,2,1750 107,韩萧,2,CD,2,2300 108,孟勐,2,KF,1,1400 109,王健强,1,KF,1,1600 110,王敏,1,KF,1,3200 111,钱小峰,1,KF,1,410025
  • 26. Oracle基本概念实例、数据库(Oracle体系结构) Oracle存储结构 Oracle安全 Schema/模式/方案:模式对象:表、视图、索引、过程、函数、包、同义词、序列 其它:Oracle Home、服务命名 26
  • 27. Oracle基本概念——实例与数据库 DatabaseInstance SGA Redo log bufferData buffer cacheShared pool Data dict. cacheLibrary cache PMON DBW0SMON LGWRCKPTOthersUser processServer processPGAControl filesData files Redo log filesArchived log filesParameter filePassword file27
  • 28. Oracle基本概念——实例与数据库的关系实例=后台进程+内存结构 数据库=数据文件+控制文件+重做日志文件 一个实例只能打开一个数据库28
  • 29. Oracle基本概念——存储结构29
  • 30. Oracle基本概念——存储——逻辑结构数据库 database 表空间 tablespace 段 segment 区 extent 块 Oracle Block30
  • 31. Oracle基本概念——安全用户 两个特殊用户sys和system 数据字典 普通用户 角色 DBA、Connect31
  • 32. Oracle模式对象表 视图 索引 序列 同义词 函数 过程 程序包32
  • 33. Oracle的服务OracleOracle_HomeTNSListener OracleServiceDBName net start/stop service_name 对于监听,lsnrctl start/stop33
  • 34. 第二部分 SQL基础查询(简单查询、多表、子查询、使用函数、聚集…) DML(Insert Update Delete) 表、视图、约束 其它数据库对象 34
  • 35. 第一章 编写基本的SQL Select语句查询指定的列/所有列/检索唯一值,使用表达式,使用别名 SELECT *|{[DISTINCT] column|expression [alias],…} FROM table; SELECT * FROM table; SELECT a,b,c from table; 35
  • 36. 关于SQL语句大小写不敏感 可以多行编写 关键字不可以缩写或跨越多行 通常每个子句占据一行 使用缩进增加可读性36
  • 37. 列的别名(Alias)用于重命名列表题 紧跟在列名后,列名和别名之间可以加AS(可选) 如果别名包含大小写或者空格,则必须用双引号引起 SELECT name “Employee Name", salary*12 "Annual Salary" FROM emp; 37
  • 38. 连接运算符(||)将多个列或文本连接在一起 SELECT name || ‘的工资是’ || salary AS "Employees" FROM emp;38
  • 39. 去除重复行使用DISTINCT关键字去除重复行 SELECT DISTINCT deptid FROM emp; 39
  • 40. 第二章 限制结果级、排序使用where子句对结果进行筛选 使用order by字句对结果进行排序40
  • 41. WHERE子句紧跟在FROM子句后 语法 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; 41
  • 42. 字符串和日期Select字句中的表达式和Where条件中的表达式里面都可以使用字符串和日期 字符串和日期要用单引号引起, 字符串大小写敏感 日期有一定的格式,默认是DD-MON-RR42
  • 43. 使用比较运算符=,>,>=,<,<=,<> BETWEEN … AND … IN (SET) LIKE ‘…[%]...[ _ ]...’ ESCAPE ‘\’ IS NULL43
  • 44. 逻辑运算符AND SELECT name, salary FROM emp WHERE salary >=4000 AND gender=‘1’; OR SELECT name, salary FROM emp WHERE salary >=4000 OR gender=‘1’; NOT SELECT name, salary FROM emp WHERE NOT (salary >=4000 AND gender=‘1’);44
  • 45. 优先顺序规则优先级 操作符 0 括号 1 算数运算符*/+- 2 连接运算符|| 3 比较条件>,<,=,… 4 IS [NOT] NULL, LIKE, [NOT] IN 5 [NOT] BETWEEN 6 NOT 7 AND 8 OR 45
  • 46. ORDER BY子句使用ORDER BY字句进行排序 ASC: 升序 (默认) DESC: 降序 ORDER BY字句位于SELECT语句的最后46
  • 47. ORDER BY子句可以按别名进行排序,也可以用列序号排序 ORDER BY 1; --按第一列排序 ORDER BY后可以指定多列 每列可以分别指定ASC或DESC 排序列不一定要出现在SELECT子句中47
  • 48. 第三章 单行函数SQL函数类型 单行函数,每一行返回一个结果 多行函数,对多行才返回一个结果,用于聚集48
  • 49. 单行函数接受参数并返回一个值 作用于select语句返回的每一行 每行返回一个结果 可以修改数据类型 可以嵌套 参数可以是某个列或表达式 49
  • 50. 单行函数类别通用类 和NULL相关 字符类 大小写相关 LOWER,UPPER,INITCAP 字符处理 CONCAT,SUBSTR,LENGTH,INSTR,LPAD | RPAD,TRIM,REPLACE 数字类 ROUND,MOD,TRUNC 日期类 SYSDATE 转换类 TO_CHAR,TO_DATE,TO_NUMER50
  • 51. 对日期的操作对一个日期值加上或减去一个数值(天数)得到一个新的日期 两个日期相减得到相差的天数 日期也可以和小时数进行加减,前提是小时数要除以24 51
  • 52. 日期函数函数含义MONTHS_BETWEEN两个日期间相隔的月数ADD_MONTHS将一个日期加上n个月NEXT_DAY指定日期后的下一个星期X的日期LAST_DAY某月的最后一天ROUND对日期进行四舍五入TRUNC对日期进行截取52
  • 53. 转换函数TO_DATETO_NUMBERTO_CHARTO_CHAR字符串日期数字53
  • 54. 日期与字符串间的转换日期——〉字符串 TO_CHAR(date,format) 字符串——〉日期 TO_DATE(string,format) Format的格式 YYYY,YY,MONTH,MON,MM,DAY,DD,HH,MI,SS54
  • 55. 字符串与数字键的转换TO_CHAR(string,format) 9 代表一个数字 0 强制显示0 $ 货币符号 L 本地货币符号 . 小数点 , 千分位55
  • 56. 使用NULL值定义: 不可知、未定义、不可访问的值 数字0,字符空格不是NULL 表达式中如果包含NULL值,那么结果也为NULL 56
  • 57. 通用类函数——NULL相关NVL(A,B) 如果A为null则返回B,否则返回A NVL2(A,B,C) 如果A不为null则返回B,否则返回C NULLIF(A,B) 如果A和B相等则返回NULL,否则返回A COALESCE(expr1,expr2,…,exprn) 返回清单中第一个非空值57
  • 58. 条件表达式CASE语句 CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END DECODE语句 (col|expression, search1, result1 [, search2, result2,...,] [, default]) 58
  • 59. 第四章 从多表中检索数据目标 使用等值连接或非等值连接从多表中检索数据 使用外连接检索不满足等值条件的数据行 使用自连接将一个表和自身进行连接 59
  • 60. 迪卡尔集产生的原因 没有指定连接 连接无效 第一个表中的每一行都和另外一个表的每一行进行了连接 使用WHERE字句避免卡迪尔集 卡迪尔集的危害 产生n1*n2*…*nn行结果60
  • 61. 连接类型等值连接 非等值连接 外连接 自连接61
  • 62. 等值连接SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; 62
  • 63. 多表中的同名列使用表前缀来明确多个表中同名的列 使用表前缀可以提高性能 使用列别名来区分多个表中同名的列63
  • 64. 使用表别名简化查询Simplify queries by using table aliases 提高性能 64
  • 65. 连接超过2个以上的表如果有n个表进行等值连接,至少需要n-1个连接条件65
  • 66. 非等值连接使用IN,BETWEEN AND,>,<,…66
  • 67. 外连接使用外连接将那些不匹配的行也显示出来 外连接的操作符是(+) (+)放在缺失值得那一边 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);67
  • 68. 自连接必须使用别名68
  • 69. 第五章 使用分组函数聚集数据分组函数的结果是对记录集进行分组,每一组返回一行 常用的分组函数 AVG,MAX,MIN,COUNT,SUM STDDEV,VARIANCE 69
  • 70. 分组函数语法SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [HAVING condition] [ORDER BY column]; 70
  • 71. 在分组函数中使用DISTINCTCOUNT( DISTINCT expr ) AVG(DISTINCT expr )71
  • 72. 分组函数中对NULL值的考虑分组函数会忽略包含NULL值的行 使用NVL函数解决此问题 72
  • 73. GROUP BY子句的用法SELECT子句中没有出现在分组函数中的列或表达式必须也包含在GROUP BY子句中 GROUP BY子句中的列,不一定要出现在SELECT子句中 GROUP BY子句中可以包含多列(表达式)73
  • 74. 常见的错误非分组函数没有出现在group by子句 SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; 在where子句中使用了分组函数(应使用having子句来替换) SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; 74
  • 75. HAVING子句Where子句的作用 哪些行参加分组? Group by子句的作用 如何进行分组? Having子句的作用 哪些分组被显示?75
  • 76. HAVING子句举例SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);76
  • 77. 分组函数可以进行嵌套SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; 77
  • 78. 第六章 子查询目标: 子查询可以解决哪些问题? 如何定义子查询? 子查询的不同类型 单行/多行的子查询78
  • 79. 使用子查询来解决一个问题谁的工资比张三高? 解题步骤: 先得到张三的工资xxx 找出工资>xxx的那些人 79
  • 80. 子查询的语法SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 80
  • 81. 子查询注意事项用括号将子查询括起 子查询置于比较操作符右侧 子查询内部不需要ORDER BY 子句,除非你要进行Top-N分析 对于单行子查询使用单行操作符,对于多行子查询使用多行操作符81
  • 82. 单行子查询只返回一行 使用单行比较操作符 <,<=,>,>=,<> 82
  • 83. 使用多个子查询哪些人和张三在同一个部门,但工资比李四高? SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);83
  • 84. 子查询中使用分组函数SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); 84
  • 85. HAVING子句中使用子查询SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);85
  • 86. 找错SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); 86
  • 87. 找错SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');87
  • 88. 多行子查询返回多行 多行操作符 IN ANY ALL88
  • 89. 使用ANYSELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';89
  • 90. 使用ALLSELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 90
  • 91. 子查询中的NULL--下面的语句为什么不能返回行? SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); 91
  • 92. 第七章 使用SQL*PLUS目标 使用替代变量 定制SQL*PLUS环境 输出具可读性的报表 创建和执行脚本文件92
  • 93. SQL*PLUS能做什么?描述数据库对象的结构 编辑/执行SQL语句 将SQL语句保存到文件中 调用已经编写好的SQL 输出报表 93
  • 94. 替代变量的作用存储临时数据 & && DEFINE命令 在SQL语句间传递参数 动态更改页眉和页脚的值94
  • 95. 使用替代变量SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num; 95
  • 96. 使用替代变量——日期和字符串对于这两种类型的替代变量必须用单引号括起来 SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title'; 96
  • 97. 指定列名、表达式和文本在下列子句后可以使用替代变量 WHERE子句 ORDER BY子句 列或表达式 表名 整个SELECT语句97
  • 98. 定义替代变量使用DEFINE命令定义替代变量 DEFINE variable = value 如果变量的值包含空格,需要用单引号引起 定义的变量只在当前会话有效 用UNDEFINE命令清除变量 98
  • 99. &&替代变量此种替代变量SQL*PLUS会自动用DEFINE命令生成一个变量,用户只得到一次提示 SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name; 99
  • 100. 定制SQL*PLUS环境语法 SET variable value Set serveroutput on SHOW variable 常用的变量 pagesize,linesize heading,feedback100
  • 101. 格式命令COLUMN COL[UMN] [{column|alias} [option]] CLEAR/FORMAT/NOPRINT/NULL/HEADING TTITLE TTITLE text BTITILE BTITLE text BREAK BREAK ON101
  • 102. 创建一个打印报表的脚本编辑好查询语句 设定显示格式,将这些命令放在SELECT语句之前 在SELECT语句之后清除格式设定 保存该脚本文件 调用执行102
  • 103. SQL VS SQL PlusSQLSQL*Plus• 一种语言• 一个运行SQL命令的环境• ANSI标准• Oracle公司开发• 关键字不可以简写• 关键字可以缩写• 处理数据库中对象的定义和数据• 相关命令不会影响到数据库103
  • 104. 第八章 处理数据目标 描述DML语句 插入数据 更新数据 删除数据 合并数据 处理事务104
  • 105. DML语句当发生下列情况时会执行DML语句 往表中添加新行 修改表中现有的行 删除表中的数据 事务是能够实现一个功能的一组DML语句105
  • 106. INSERT语句语法 INSERT INTO table [(column [, column...])] VALUES (value [, value...]); 一次只能插入一行 如果column不指定,则按表中列的顺序依次输入值; 可以指定只插入哪些列106
  • 107. 在INSERT语句中使用特殊值插入NULL值,使用NULL或直接略去该字段 插入SYSDATE 使用to_date函数 使用替代变量 DEFAULT(也可用于UPDATE语句) 107
  • 108. 从另外一个表复制数据INSERT INTO a SELECT * FROM b; 使用这种方法,不能使用VALUES关键字,列必须配对108
  • 109. 更新语句——UPDATE语法 UPDATE table SET column = value [, column = value, ...] [WHERE condition]; 109
  • 110. 使用子查询UPDATE employees SET job_id = ( SELECT job_id FROM employees WHERE employee_id = 205), salary = ( SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 110
  • 111. 基于另外一个表进行更新UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 111
  • 112. 完整性约束更改一个表的外键时,如果对应的主键不存在会引发此错误 UPDATE employees SET department_id = 55 WHERE department_id = 110; 112
  • 113. 删除语句——DELETE语法 DELETE [FROM] table [WHERE condition]; 113
  • 114. 在DELETE语句中使用子查询DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 114
  • 115. 删除时的完整性约束如果删除父表时,子表还包含记录的情况下会引发此错误 DELETE FROM departments WHERE department_id = 60; 115
  • 116. MERGE语句根据条件进行动态插入或更新 MERGE INTO table_name AS table_alias USING (table|view|sub_query) AS alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); 116
  • 117. MERGE样例MERGE INTO copy_emp AS c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, ... c.department_id = e.department_id WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);117
  • 118. 数据库事务管理有3种事务 DDL DML DCL118
  • 119. 数据库事务当开始执行第一个DML语句时事务开始 当发生下列事件时事务结束: 执行了COMMIT或ROLLBACK语句 当DML语句后遇到DDL/DCL语句,事务结束并且自动提交 用户退出SQL*PLUS 系统崩溃 119
  • 120. SAVEPOINT设置保存点,以便可以回滚到指定点 UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete.120
  • 121. 隐含的事务处理当遇到下列情况事务自动提交 遇到DDL语句 遇到DCL语句 正常退出SQL*PLUS,但没有明确输入COMMIT或ROLLBACK 当遇到下列情况事务自动回滚 不正常退出SQL*PLUS 系统失败121
  • 122. 事务提交或回滚前的状态数据可以恢复,使用ROLLBACK 可以使用SELECT语句查看修改情况 其它用户看不到当前用户所做的修改 其它用户不能修改被当前用户修改的数据行,除非当前用户执行COMMIT或者ROLLBACK122
  • 123. 事务提交后的数据状态被修改后的数据永久保存在数据库中 之前的状态永久丢失 所有用户可以看到提交后的数据 在行上的数据锁释放,其它用户可以进行DML操作 所有的保存点被删除 123
  • 124. 事务回滚后的数据状态对数据所做的修改被撤销 恢复到之前一个状态 行上的锁被释放 124
  • 125. 语句级回滚如果单个DML语句执行失败,那么只有当前语句回滚 Oracle服务器隐含的设置了一个保存点 所有其它DML语句所做的修改仍然保留 用户应当显式指定COMMIT或者ROLLBACK125
  • 126. 第九章 创建和管理表目标 描述主要的数据库对象 创建表 描述可用的列类型 更改表的定义 删除、重命名、截取表126
  • 127. Oracle主要的数据库对象表 基本的存储单元,由行和列组成 视图 从一个或多个表中的数据展现子集 序列 产生连续的数字 索引 用于提高查询性能 同义词 给对象定义别名127
  • 128. 命名规则标名和列名命名规则: 以字母开头 最大30个字符 只能包含大小写字母、数字、下划线、$、# 不能和当前用户下的其它对象重名 不能使用Oracle保留字 128
  • 129. 创建表的前提拥有Create Table权限 CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]); 拥有存储空间 创建表时要指定表名、列名、列的类型、字段长度等 在其它用户下创建表要在表名前加用户名username.table129
  • 130. 为列指定默认值可以使用表达式,普通文本或SQL函数等 不可以引用另外一个列名或伪列 默认值的数据类型必须和列类型一致130
  • 131. Oracle数据库中的表包含两种类型 用户表 由用户创建并维护 包含用户的信息 数据字典 描述用户表的表/视图 由Oracle服务器创建并维护 包含的是数据库的信息131
  • 132. 查询数据字典查询当前用户拥有哪些表 查询当前用户拥有哪些类型的对象 查询当前用户拥有哪些表、视图、同义词以及序列132
  • 133. 列的数据类型数据类型描述VARCHAR2(size) 变长的字符串CHAR[(size)] 固定长度的字符串NUMBER[(p,s)] 变长数字DATE 日期和时间值LONG 变长字符数据,最大可达2GBCLOB 字符数据,最大可达4GBRAW and LONG RAWRaw binary dataBLOB 二进制数据,最大可达4GBBFILE 存储在外部的文件(存储在文件系统中),最大可达4GBROWID 16进制的字符串,代表行在表中的唯一地址133
  • 134. 通过子查询创建表创建表并同时生成数据 CREATE TABLE table [(column, column...)] AS subquery; 复制表时非常有用 134
  • 135. ALTER TABLE语句使用该语句实现 添加新列 修改现有列 为列设置默认值 删除列 135
  • 136. 删除、重命名、截取表删除表 DROP TABLE 重命名表 RENAME tableA TO tableB; 截取表 TRUNCATE TABLE table;136
  • 137. 注释表注释 COMMENT ON TABLE table IS ‘comments’; 列注释 COMMENT ON COLUMN table.column IS ‘comments’; 查看以下数据字典获得注释信息 ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS137
  • 138. 第十章 约束目标 描述约束 创建并维护约束138
  • 139. 什么是约束约束是表一级的强制规则 约束可以防止数据被删除如果存在依赖关系 约束的类型 非空约束 唯一约束 主键约束 外键约束 检查约束139
  • 140. 约束系统产生的约束格式为SYS_CN 可以在创建表的同时创建约束,也可以在创建完表后创建约束 约束可以定义在表一级或列级 通过数据字典查询约束140
  • 141. 定义约束列一级约束: column [CONSTRAINT constraint_name] constraint_type , 表一级约束: column,... [CONSTRAINT constraint_name] constraint_type(column, ...), 141
  • 142. 非空约束——NOT NULL确保空值不能插入到列中 CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL, 142
  • 143. 唯一约束——UNIQUE确保表中的列值没有重复 CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... CONSTRAINT emp_email_uk UNIQUE(email));143
  • 144. 主键约束——PRIMARY KEY可以定义在列一级或表一级 CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id));144
  • 145. 外键——FOREIGN KEY同样可以定义在列或表一级 CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email)); 145
  • 146. 外键——FOREIGN KEYREFERENCES: 要引用父表的列 ON DELETE CASCADE: 级联删除 ON DELETE SET NULL 146
  • 147. 检查约束——CHECK定义每行数据必须满足的条件 错误的用法 使用CURRVAL, NEXTVAL, LEVEL,ROWNUM伪列 调用SYSDATE, UID, USER, 和 USERENV 引用其它行的值 147
  • 148. 使用ALTER TABLE命令创建约束语法 ALTER TABLE table ADD [CONSTRAINT constraint] type (column); 添加或删除约束 激活或禁用约束 使用MODIFY子句添加NOT NULL约束 148
  • 149. 添加外键约束为员工表的manager字段添加外键 ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); 149
  • 150. 删除约束ALTER TABLE table_name DROP CONSTRAINT constraint_name; 删除主键的同时删除外键 ALTER TABLE table_name DROP PRIMARY KEY CASCADE; 150
  • 151. 禁用、激活约束激活约束 ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; 禁用约束 ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE; 151
  • 152. 删除列时级连删除约束删除列时指定CASCADE时,会删除和此列相关的所有约束,包括多列约束(主键) ALTER TABLE table_name DROP COLUMN column_name CASCADE; 152
  • 153. 查询约束数据字典USER_CONSTRAINTS 数据字典USER_CONS_COLUMNS153
  • 154. 第十一章 创建视图目标 描述视图 创建、修改视图的定义,删除视图 通过视图检索数据 通过视图插入、修改、删除数据 创建、使用内联视图 TOP-N分析154
  • 155. 什么是视图什么是视图 表的一个透视图 逻辑概念,不占据存储 为什么使用视图 显示数据的访问 简化复杂查询 提供数据独立性 为同一数据提供不同展现155
  • 156. 创建视图在CREATE VIEW 后跟随一个子查询 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery 为视图列命名 子查询中可以使用别名 视图名后跟别名列表 156
  • 157. 查询视图SELECT * FROM view_name; 同从表检索数据没有区别157
  • 158. 修改视图仍然使用CREATE OR REPLACE VIEW View_name后的别名列表必须和子查询的字段列表意义对应158
  • 159. 创建一个稍微复杂的视图找出各个部门的最高、最低工资、平均工资,按部门名称分组 CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;159
  • 160. 在视图上进行DML语句的规则可以在简单视图上进行DML操作 如果视图包含下列情形时不能从视图中删除行 分组函数 GROUP BY子句 DISTINCT关键字 伪列ROWNUM160
  • 161. 在视图上进行DML语句的规则如果视图包含下列情形时不能在视图中更新行 分组函数 GROUP BY子句 DISTINCT关键字 伪列ROWNUM 列的定义是一个表达式 161
  • 162. 在视图上进行DML语句的规则如果视图包含下列情形时不能在视图中添加行 分组函数 GROUP BY子句 DISTINCT关键字 伪列ROWNUM 列的定义是一个表达式 基表中包含非空字段,但不包含在视图中 162
  • 163. 删除视图DROP VIEW view_name; 163
  • 164. 内联视图指在SQL语句内的某个查询,包含有别名,可以被该SQL语句引用 SELECT a.last_name, a.salary, a.department_id, b. maxsal FROM employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary < b.maxsal;164
  • 165. Top-n分析工资最高的前十名员工 SELECT ROWNUM as RANK, last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3; 165
  • 166. 第十二章 其它数据库对象目标 创建、使用、管理序列 创建、管理同义词 创建私有和公有的同义词166
  • 167. 什么是序列一个序列可以: 自动产生唯一的数字 是可共享的对象 主要用于产生主键的值 减少应用中产生主键的代码 将序列环存在内存中可以提高性能167
  • 168. 创建序列CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 168
  • 169. 创建序列为dept表的主键创建一个序列 CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; 169
  • 170. 确认已经创建序列通过查询数据字典进行确认 SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;170
  • 171. NEXTVAL和CURRVAL 伪列NEXTVAL 返回下一可用的序列值 每次被引用时都是返回唯一的值,即使是被不同的用户引用 CURRVAL 用于获得当前的序列值 至少要调用NEXTVAL一次,CURRVAL才会包含值 171
  • 172. 使用序列INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500); 172
  • 173. 使用序列将序列缓存到内存中以提高性能 当发生下列情况时,序列会发生“断层” 发生回滚 系统崩溃 序列在其它表中被引用 如果序列创建时指定NOCACHE, 可以在数据字典USER_SEQUENCES中查询下一可用序列值。173
  • 174. 修改序列修改序列的增量、最大、最小值,循环和混存选项 ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;174
  • 175. 使用序列时的注意事项修改序列时必须是序列的属主或者拥有修改序列的权限 如果序列被修改,只有后续的序列值被影响 如果希望序列以另外一个不同的数字开头,那么必须先删除序列,然后重建 使用序列时会进行一些校验175
  • 176. 删除序列DROP SEQUENCE seq_name;176
  • 177. 什么是索引索引是: 一种模式对象 引入索引是为了查询时提高性能 较少磁盘I/O 独立于表 创建后,由Oracle服务器自动维护 177
  • 178. 索引是如何创建的自动创建: 当定义了一个主键或唯一约束时会自动创建唯一索引 手动创建: 用户可以创建非唯一的索引以加快对列的访问 178
  • 179. 创建索引CREATE INDEX index ON table (column[, column]...); 179
  • 180. 何时该创建索引你应当创建索引,如果发生下列情况: 一个列的取值范围比较广 列包含了大量的空值 一个或多个列经常在WHERE或连接子句中被一起使用 表非常大,但每次的查询的数据不超过总数据的2%——4% 180
  • 181. 何时不该创建索引下列情况无需创建索引: 小表 没有经常在条件中使用的列 大部分的查询都会查询超过2——4%的数据 表会经常被更新 索引列被作为表达式的一部分被引用 181
  • 182. 确认索引USER_INDEXES数据字典包含了索引的信息 USER_IND_COLUMNS 包含索引的名字,相关的表名、列。 182
  • 183. 基于函数的索引基于函数的索引只基于表达式的索引 索引表达式根据表列,常数,SQL函数和用户自定义的函数创建而成 CREATE INDEX upper_dept_name_idx ON departments(UPPER(department_name)); Index created. SELECT * FROM departments WHERE UPPER(department_name) = 'SALES'; 183
  • 184. 删除索引DROP INDEX idx_name;184
  • 185. 同义词通过创建同义词,可以简化对数据库对象的访问,相当于给对象起了另外一个名字 使用同义词,可以 简化对另外一个用户下对象的访问 减少了数据库对象的名字 创建同义词的语法 CREATE [PUBLIC] SYNONYM synonym FOR object;185
  • 186. 创建和删除同义词为DEPT_SUM_VU视图创建一个同义词,使用更短的名字. CREATE SYNONYM d_sum FOR dept_sum_vu; 删除同义词 DROP SYNONYM d_sum;186
  • 187. 第十三章 控制用户权限目标 创建用户 创建角色来简化对权限模型管理 使用GRANT和REVOKE语句进行权限授权和回收 创建并访问数据库链接 187
  • 188. 权限数据库权限: 系统安全 数据安全 系统权限:获得对数据库的访问权 对象权限: 对数据库对象的内容的访问 模式:对象的集合 188
  • 189. 系统权限有100多个系统权限. 数据库管理员用户比较高级的权限: 创建新用户 删除用户 删除表 备份表 189
  • 190. 创建用户DBA使用CREATE USER语句创建用户 CREATE USER username IDENTIFIED BY password; 创建用户后,DBA必须赋予一些系统权限 GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]; 修改用户密码 ALTER USER username IDENTIFIED BY password; 190
  • 191. 什么是角色角色相当于一个权限的集合,给角色赋予一定的权限,然后将权限赋给用户,简化了权限管理。 创建角色 将系统权限、对象权限赋给角色 将角色赋给用户191
  • 192. 对象权限对象权限 表 视图 序列 存储过程 ALTER √ √ DELETE √ √ EXECUTE √ INDEX √ INSERT √ √ REFERENCES √ √ SELECT √ √ √ UPDATE √ √ 192
  • 193. 对象权限不同的对象有不同的对象权限 属主拥有所有该用户下对象的权限 属主可以将所属对象的权限赋给其他用户 GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; 193
  • 194. 对象权限的赋权将对象的查询权限赋予他人 将对象的更新权限赋予他人 授权时使用WITH GRANT OPTION 将对象权限赋予PUBLIC194
  • 195. 相关的数据字典数据字典视图 描述 ROLE_SYS_PRIVS 赋给角色的系统权限ROLE_TAB_PRIVS 赋给角色的表权限USER_ROLE_PRIVS 用户可用的角色USER_TAB_PRIVS_MADE 赋给用户的对象权限USER_TAB_PRIVS_RECD 赋给用户的对象权限USER_COL_PRIVS_MADE 赋给用户列上的对象权限 USER_COL_PRIVS_RECD 赋给用户列上的对象权限USER_SYS_PRIVS 赋给用户的系统权限195
  • 196. 如何收回系统权限使用 REVOKE 收回赋给用户的权限 通过 WITH GRANT OPTION语句赋出去的权限也被收回 语法 REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];196
  • 197. 数据库链接让本地用户可以访问远程数据库的一种手段197
  • 198. 数据库链接创建数据库链接 CREATE PUBLIC DATABASE LINK pub_db USING ‘pub_db’; 在SQL语句中使用数据库链接 SELECT * FROM employee@pub_db;198
  • 199. 第十三章 集合操作目标 描述集合操作 使用集合操作将多个查询合并为一个查询 控制返回行的顺序 199
  • 200. 集合操作并——UNION/UNION ALL 交——INTERSECT 差——MINUS200
  • 201. UNION操作符返回两个查询中的所有记录,但要去除重复记录201
  • 202. UNION ALL返回两个查询的所有记录,包括重复行 202
  • 203. INTERSECT返回同时包含在两个查询中的记录203
  • 204. MINUS返回包含在第一个查询,但不在第二个查询中的记录204
  • 205. 第三部分 PL/SQLPL/SQL 程序设计简介 PL/SQL块结构和组成元素 PL/SQL流程控制语句 游标的使用 异常错误处理 存储函数和过程 包的创建和应用 触发器 205
  • 206. 一、PL/SQL 程序设计简介PL/SQL是 Procedure Language & Structured Query Language 的缩写。 PL/SQL是对SQL的扩展 PL/SQL是第三代编程语言和第四代编程语言的完美结合。 分为数据库PL/SQL和工具PL/SQL,如proC206
  • 207. PL/SQL的使用在PL/SQL中只能使用DML语句: INSERT、UPDATE、DELETE、SELECT INTO、COMMIT、ROLLBACK 不能使用DDL语句,为什么? 使用范围 SQLPLUS 高级语言207
  • 208. 二、PL/SQL块结构和组成元素PL/SQL块 PL/SQL结构 标识符 PL/SQL 变量类型 运算符和表达式(数据定义) 变量赋值 变量作用范围及可见性 注释 简单例子 208
  • 209. PL/SQL块由三部分组成 DECLARE /* 声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 */ BEGIN /* 执行部分: 过程及SQL 语句 , 即程序的主要部分 ,必须 */ EXCEPTION /* 执行异常部分: 错误处理 */ END;209
  • 210. PL/SQL块的类型无名块 临时性的语句,以Declare开头 子程序 过程,以Create or replace procudue 函数,以Create or replace function 程序包,Create or Replace Package 触发器 create or replace trigger210
  • 211. 一个无名块的例子Set serveroutput on Begin dbms_output.put_line('当前时间是'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); End;211
  • 212. PL/SQL标识符PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有: 标识符名不能超过30字符; 第一个字符必须为字母; 不分大小写; 不能用’-‘(减号); 不能是SQL保留字。 提示: 不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.212
  • 213. 建议的变量命名规则标识符命名规则(前缀)例子参数变量p_p_table_name程序变量v_v_id游标变量c_c_emp异常标识e_e_no_data_found记录类型t_t_student213
  • 214. PL/SQL数据类型类型说 明ORACLE限制CHAR定长字符串,默认长度为1,最大长度327672000字节VARCHAR2可变字符串,最大长度327674000NUMBER(p,s)数字型,p表示精度最大38,s刻度从-84到127DATE日期型BOOLEAN布尔型不使用ROWID存放数据库行号表类型相当于一个记录类型,结构同某个表列类型类型和某个表的列相同214
  • 215. 注释单行注释 以双减号开头,--开始注释 多行注释 /*开始注释 第二行注释 结束注释*/215
  • 216. 举例:在PL/SQL中使用SQL语句直接在begin end之间输入sql语句 在sql语句内使用变量 在语句后添加commit;或者rollback;216
  • 217. 三、PL/SQL流程控制语句条件语句 CASE 表达式 循环 标号和GOTO NULL 语句 217
  • 218. IF语句第一种 IF <布尔表达式> THEN PL/SQL 和 SQL语句 END IF; 第二种 IF <布尔表达式> THEN PL/SQL 和 SQL语句 ELSE 其它语句 END IF; 第三种 IF <布尔表达式> THEN PL/SQL 和 SQL语句 ELSIF < 其它布尔表达式> THEN 其它语句 ELSIF < 其它布尔表达式> THEN 其它语句 ELSE 其它语句 END IF;218
  • 219. CASE语句CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 WHEN expressionN THEN resultN [ ELSE resultN+1] END; 相当于decode函数219
  • 220. 简单循环LOOP 要执行的语句; EXIT WHEN <条件语句> /*条件满足,退出循环语句*/ END LOOP;220
  • 221. WHILE循环WHILE <布尔表达式> LOOP 要执行的语句; END LOOP;221
  • 222. FOR循环FOR 计数器 IN [ REVERSE ] 下限 .. 上限 LOOP 要执行的语句; END LOOP;222
  • 223. 标号和GOTOGOTO label; . . . . . . <
  • 224. NULL语句NULL; //什么都不做224
  • 225. 四、游标的使用什么是游标? 官方解释:指向上下文区的句柄或指针。 我的解释:一个指向查询结果集的变量,用于对结果集进行遍历。 分为显式游标和隐式游标225
  • 226. 处理显式游标的步骤声明游标 CURSOR c_name IS select_statement; 打开游标 OPEN CURSOR c_name; 用游标检索值 FETCH c_name INTO v_name; 关闭游标 CLOSE c_name;226
  • 227. 游标的几个属性%FOUND %NOTFOUND %ROWCOUNT227
  • 228. 隐式游标SQL游标 由PL/SQL引擎打开或关闭,所以没有OPEN等命令 具有同样的游标属性 ISOPEN始终为FALSE 228
  • 229. 使用循环遍历游标简单循环 LOOP EXIT WHEN c_name%NOTFOUND /* do something*/ END LOOP WHILE循环 While c_name%FOUND LOOP /* do something */ END LOOP FOR循环 FOR v_name IN c_name LOOP /* do something */ END LOOP229
  • 230. NO_DATA_FOUND与%NOTFOUND的区别前者异常 游标属性 前者在Select into没有成功是触发,后者在update或delete没有匹配where字句时被置为TRUE 230
  • 231. FOR UPDATE自动加行锁 CURRENT OF c_name231
  • 232. 五、异常错误处理异常处理概念 异常错误传播 异常错误处理编程 在 PL/SQL 中使用 SQLCODE, SQLERRM 232
  • 233. 异常的定义及分类用来处理正常执行过程中未预料的事件 分类 预定义 ( Predefined )错误 ORACLE预定义的异常情况大约有24个。对这种异常情况的处理,无需在程序中定义,由ORACLE自动将其引发。 非预定义 ( Predefined )错误 即其他标准的ORACLE错误。对这种异常情况的处理,需要用户在程序中定义,然后由ORACLE自动将其引发。 用户定义(User_define) 错误 程序执行过程中,出现编程人员认为的非正常情况。对这种异常情况的处理,需要用户在程序中定义,然后显式地在程序中将其引发。 233
  • 234. 异常的结构EXCEPTION WHEN first_exception THEN WHEN second_exception THEN WHEN OTHERS THEN END;234
  • 235. 典型的预定义异常错误号异常错误信息名称说明ORA-1001Invalid-CURSOR试图使用一个无效的游标ORA-1012Not-logged-on没有连接到ORACLEORA-1017Login-denied无效的用户名/口令ORA-1403No_data_foundSELECT INTO没有找到数据ORA-1422Too_many_rowsSELECT INTO 返回多行ORA-1476Zero-divide试图被零除ORA-1722Invalid-NUMBER转换一个数字失败ORA-6511CURSOR-already-OPEN试图打开一个已存在的游标ORA-6530Access-INTO-null试图为null 对象的属性赋值235
  • 236. 非预定义的异常处理 在PL/SQL 块的定义部分定义异常情况: <异常情况> EXCEPTION; 将其定义好的异常情况,与标准的ORACLE错误联系起来,使用EXCEPTION_INIT语句: PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>); 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。236
  • 237. 用户自定义的异常处理在PL/SQL 块的定义部分定义异常情况: <异常情况> EXCEPTION; RAISE <异常情况>; 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。237
  • 238. 在 PL/SQL 中使用 SQLCODE, SQLERRM SQLCODE 返回错误代码数字, SQLERRM 返回错误信息.238
  • 239. 六、存储函数和过程 创建函数 存储过程 239
  • 240. 什么是存储过程/函数定义:存储在数据库中的PL/SQL程序,具有输入/输出或输入输出参数(参数具有方向性,单入、单出或是双向) 存储过程与函数除了返回结果有区别外,其实是一样的,存储过程不返回结果,函数要返回结果。240
  • 241. 函数的结构CREATE [OR REPLACE] FUNCTION function_name [(argment [ { IN| IN OUT }] type, argment [ { IN | OUT | IN OUT } ] type)] RETURN return_type { IS | AS } <类型.变量的说明> BEGIN FUNCTION_body EXCEPTION 其它语句 END;241
  • 242. 存储过程结构CREATE [OR REPLACE] PROCEDURE Procedure_name [ (argment [ { IN | IN OUT }] Type, argment [ { IN | OUT | IN OUT } ] Type) ] { IS | AS } <类型.变量的说明> BEGIN <执行部分> EXCEPTION <可选的异常错误处理程序> END;242
  • 243. 参数传递方法方式说明IN当过程被调用时,实际参数被传递给过程。在过程内,形式参数起着PL/SQL常量的作用——它被认为是只读的,不能被改变。当过程结束,控制返回到调用环境时,实际参数没有被改变。OUT当过程被调用时,实际参数的任何值都被忽略。在过程内,形式参数起着未初始化的PL/SQL变量的作用,因此有一个NULL值。它可以被读出,也可以被写入。当过程结束,控制返回到调用环境中,形式参数的值被赋给实际参数。IN OUT这种方式是IN和OUT方式的组合。当过程调用时,实际参数的值被传递给过程。在过程内,起着一个已初始化变量的作用,可以被读出和写入。当调用结束,返回到调用环境时,形式参数的内容赋值给实际参数。243
  • 244. 函数/存储过程的授权GRANT CREATE/DROP ANY PROCEDURE TO username CREATE OR REPLACE PUBLIC SYNONYM job FOR dbms_job GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION 244
  • 245. 七、包的创建和应用包的定义 包的开发步骤 包定义的说明 子程序重载 删除过程、函数和包 包的管理 245
  • 246. 包的定义包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合,它具有面向对象程序设计语言的特点,是对这些PL/SQL 程序设计元素的封装。 可以把包想象成一个类,具有方法(过程、函数)和成员变量(公共变量、私有变量)246
  • 247. 包的组成包定义(PACKAGE):包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素,这些元素为包的公有元素。 包主体(PACKAGE BODY):包主体则是包定义部分的具体实现,它定义了包定义部分所声明的游标和子程序,在包主体中还可以声明包的私有元素。247
  • 248. 创建包CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [公有数据类型定义[公有数据类型定义]…] [公有游标声明[公有游标声明]…] [公有变量、常量声明[公有变量、常量声明]…] [公有子程序声明[公有子程序声明]…] END [package_name];248
  • 249. 创建包体CREATE OR REPLACE PACKAGE BODY pkg_name IS FUNCTION 实现 PROCEDURE实现 …… END pkg_name249
  • 250. 子程序重载包里面允许对函数或过程进行重载,同样的函数/过程,不同个数或类型的参数250
  • 251. 删除包DROP PACKAGE pkg_name251
  • 252. 八、触发器 触发器类型 创建触发器 删除和激活触发器 触发器和数据字典 252
  • 253. 触发器概述当某个事件发生时被执行的一段PL/SQL代码。 函数和存储过程是显式调用执行,触发器是指定事件发生时(发生前或发生后)才能运行,触发器不能接受参数 事件类型: 对数据库表的INSERT、UPDATE、DELETE ORACLE数据库事件,如启动与关闭253
  • 254. 触发器的类型DML触发器 可以在DML操作前或操作后进行触发,并且可以对每个行或语句操作上进行触发 替代触发器 由于不能直接对由两个以上的表建立的视图进行操作,引入替代触发器解决此问题。 系统触发器 在ORACLE数据库系统的事件中进行触发,如ORACLE系统的启动与关闭等 254
  • 255. 触发器的组成触发事件 即在何种情况下触发TRIGGER; 例如:INSERT, UPDATE, DELETE。 触发时间 即该TRIGGER 是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该TRIGGER 的操作顺序。 触发器本身 即该TRIGGER 被触发之后的目的和意图,正是触发器本身要做的事情。 例如:PL/SQL 块。 触发频率 说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 语句级(STATEMENT)触发器 是指当某触发事件发生时,该触发器只执行一次; 行级(ROW)触发器 是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。255
  • 256. 创建触发器CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } {INSERT | DELETE | UPDATE [OF column [, column …]]} ON [schema.] table_name [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] [FOR EACH ROW ] [WHEN condition] trigger_body;256
  • 257. 触发器的触发顺序每张表可以创建12个触发器(3*2*2) 执行 BEFORE语句级触发器; 对与受语句影响的每一行: 执行 BEFORE行级触发器 执行 DML语句 执行 AFTER行级触发器  执行 AFTER语句级触发器257
  • 258. 触发器的注意事项CREATE TRIGGER语句文本的字符长度不能超过32KB; 触发器体内的SELECT 语句只能为SELECT … INTO …结构,或者为定义游标所使用的SELECT 语句。 触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句; 由触发器所调用的过程或函数也不能使用数据库事务控制语句; 触发器中不能使用LONG, LONG RAW 类型; 触发器内可以参照LOB 类型列的列值,但不能通过 :NEW 修改LOB列中的数据;258
  • 259. 触发器中引用新旧值当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值. :NEW 修饰符访问操作完成后列的值 :OLD 修饰符访问操作完成前列的值特性INSERTUPDATEDELETEOLDNULL有效有效NEW有效有效NULL259
  • 260. 管理触发器编译 ALTER TRIGGER [schema.] trigger_name COMPILE 删除触发器 DROP TRIGGER trigger_name; 激活/禁用 触发器 ALTER TIGGER trigger_name [DISABLE | ENABLE ]; ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS; 260
  • 261. 笔试勘误笔试C卷选择题第6题E选项不要 261
  • 262. 上机要求在D:\train目录,并在train目录下创建以你的名字全拼,如zhumingzhou)命名的文件夹,重名者加上部门 第一部分需要产生emp.dmp和emp.ctl这两个文件,放在d:\train\zhumingzhou\目录下 只能在SQL*PLUS环境下答题,登录后运行spool d:\train\zhumingzhou\exam.txt,答完时输入spool off262
  • 263. 上机题勘误ABC卷 第二部分第4题,从1到10就可以了,不用到1000 现有的表包含注释 那题,dept改为emp A卷 第二部分第3题,只把函数创建好就行了,不需要对emp表进行操作 B卷 第二部分第一题,要求创建包的那个题多余263