Oracle数据库教案.docx

1、Oracle数据库教案Oracle数据库教案第1章 Oracle基础知识 2第2 章 SQL * Plus命令 6第3章 SQL语句基础 11第4节 Oracle事务处理 25第5节 SQL优化 27第6节 PL( Procedural Language) / SQL编程基础 32第7节 存储过程 46第8节 触发器 50第9节 管理表 55第10节Oracle的逻辑结构 61第11节 创建表空间 64第12节 管理用户 66第13节 管理权限和角色 6813.4 资源配置PROFILE 74第14节 TNS基本配置 76第1章 Oracle基础知识1.1 Oracle简介oracle是殷墟（

2、Yin Xu）出土的甲骨文（oracle bone inscriptions）的英文翻译的第一个单词，在英语里是“神谕”的意思。Oracle的四大创始人Oracle数据库中有个默认用户SCOTT，就是Bruce Scott，而SCOTT用户的默认密码tiger，是当时 Scott养的一只猫的名字。2009年4月Oracle公司以74亿美元收购SUN公司。1.2 Oracle 的版本Oracle 8i(internet) : 表示Oracle公司开始正式进入互联网。Oracle 9i : 与Oracle8i相关，性能更佳，管理更人性化。Oracle 10g(grid): 网格技术Oracle 1

3、1g(grid): 网格技术网格是利用互联网把地理上广泛分布的各种资源（包括计算资源、存储资源、带宽资源、软件资源、数据资源、信息资源、知识资源等）连成一个逻辑整体，就像一台超级计算机一样，为用户提供一体化信息和应用服务（计算、存储、访问等），彻底消除资源“孤岛”，最充分的实现信息共享。1.3 Oracle的安装（演示）重要概念：1. 全局数据库名与SID1) 全局数据库名是数据库的名称，当数据库处于网络中时，为标识该数据库的网络位置，需要用数据库名和网络位置组成其全局数据库名，其命名格式是：database_name.database_domain。例如：，其中sales为数据库名，为数据库

4、域。指定全局数据库名时，尽量为数据库选择能够反映其用途的名称，例如sales。数据库域用于将数据库与分布式环境中的其他数据库区分开来。例如在上海的数据库可以命名为，北京的数据库可以命名为。即使数据库名都相同，但数据库域不同，所以也能区分开。2) SID（数据库实例名）用于对外相连时使用。Oracle实例（Instance）是用来访问数据库文件集的存储结构与后台程序的集合。Oracle数据库其实是磁盘上的一堆文件；为了启动数据库即访问这堆文件，需要在内存中创建它的一个实例，然后由实例加载并打开数据库。用户连接数据库时，实际上是连接到实例，然后由实例负责与数据库通信，再将处理结果返回给用户。Ora

5、cle中一个数据库至少有一个实例与之对应，但一个数据库也可以对应多个实例，被多个实例访问。因此SID主要用于区分同一台计算机上不同的实例。对于单实例数据库，其SID通常与数据库名相同。一个运行着的ORACLE数据库可以看成是一个ORACLE SERVER，该SERVER由数据库(Database)和实例(Instance)组成，一般情况下一个ORACLE SERVER包含一个实例和一个与之对应的数据库，但是在特殊情况下，如8i的OPS，9i的RAC，一个SERVER中一个数据库可以对应多个实例。当某一实例出现故障时，其他实例自动服务。2. Oracle的常用账户用户名角色默认密码说明sys超级

6、管理员change_on_install所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。system普通管理员manager用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。可用来创建其他用户。scott普通用户（练习常用）tiger在默认情况下从Oracle10g开始，scott不能登陆。被禁用了。需要手工解锁。3. 安装后的注意事项： O

7、racle安装完成后至少要启动两个服务：OracleOraDb11g_home1TNSListener : 监听器，监听程序的服务进程。OracleServiceORCL : 主服务，是Oracle数据库实例的服务进程。建议将启动类型改为手动。1.4 Oracle的常用管理工具1. 使用SQL * Plus在Oracle中，用户对数据库的操作主要是通过SQL*Plus工具来实现的。应用举例：(1). 查看当前连接用户SQL show user(2). 查看全局数据库名SQL SELECT * FROM global_name; (4). 清空屏幕SQL cl scr ; 2. 使用Oracle

8、 Enterprise Manager（OEM）Oracle Enterprise Manager（OEM）提供了基于Web界面的、可管理单个数据库的工具。使用步骤：(1) . 启动OracleDBConsoleorcl服务(2) . 启动浏览器，输入OEM的URL地址（https:/主机名:1158/em），或者直接在【开始】菜单的Oracle程序组中选择Database Control orcl命令即可。(3) . 注意事项：只能用sys和system用户登陆，sys必须用SYSDBA身份登陆。3. 使用DBCA创建数据库如果在安装Oracle时选择仅安装数据库服务器软件，而不创建数据库，

9、就需要安装后手动创建数据库。如果在系统中已经存在Oracle数据库，为了充分利用服务器的资源，建议不要再创建一个数据库。DBCA（Database Configuration Assistant）是一个图形化用户界面的工具， DBA通过它可以快速、直观地创建数据库。选择【开始】【程序】Oracle - OraDb11g_ home1|【配置和移置工具】Database Configuration Assistant命令，打开DBCA界面。用户只需要根据DBCA的提示逐步进行设置，就可以根据相应配置创建数据库。第2 章 SQL * Plus命令2.1 用户管理命令1. 更改用户登录命令格式：co

10、nn 用户名/密码 AS SYSDBA 注意：如果连接的是超级管理员（SYS），必须写上 AS SYSDBA2. 用户加锁和解锁加锁：SQL alter user 用户名 account lock; 解锁：SQL alter user 用户名 account unlock;3. 更改用户密码命令格式：SQL alter user 用户名 identified by 密码;注意，如果忘记所有用户的密码，可启动SQL* Plus，输入以下命令：conn / as sysdbaSQL alter user 用户名 identified by 密码;2.2 其他常用命令1. help 命令SQL*Pl

11、us有许多命令，而且每个命令都有大量的选项，要记住每一个命令的所有选项是很困难的。SQL*Plus提供了内建的帮助系统，可以使用HELP命令查询相关的命令信息。命令格式：SQL help 命令名示例：查看conn命令的帮助信息SQL help conn ; 查看SQL*Plus的命令清单SQL help index ; 查看SQL*Plus的关键字清单SQL help reserve words ;2. describe命令describe命令可以缩写为desc，用来列出表或视图各个列的名称以及属性。命令格式：SQL desc object_name ;示例：查看scott用户的emp表的结构

12、SQL desc scott.emp;3. set linesize 命令系统默认每行打印80个字符，当SQL*Plus输出linesize指定数量的字符后，随后的数据就会折叠到下一行显示。命令格式：SQL set linesize number示例：SQL show linesize ;SQL set linesize 800 ;4. set pagesize 命令当SQL*Plus执行查询语句时，set pagesize命令可以设置一页显示的行数。命令格式：SQL set pagesize number 示例：SQL show pagesize ;SQL set pagesize 30 ;

13、5. pause命令如果在SQL*Plus中运行的查询语句可以返回多行数据，以至于无法在窗口中一次显示完，输出窗口会快速滚动显示。可以设置环境变量pause为on来控制显示完一页后暂停显示，直到按回车键才继续显示下一页数据。pause选项还可以设置暂停后显示的字符串，以便提示用户。命令格式：SQL set pause on; SQL set pause 按回车键继续 ;6. 命令用于执行脚本文件。命令格式：SQL 文件名;注意：需写文件路径；sql文件的后缀可以不写。7. 继续使用上次命令命令格式：SQL /2.3 常用数据字典视图数据字典是Oracle数据库的核心组件，是数据库中的所有对象信

14、息的知识库，提供了数据库结构、数据库对象空间分配和数据库用户等有关的信息。任何数据库用户都无法对数据字典中的内容进行修改，但可以查看数据字典中的内容。数据字典中的信息通过表和视图的形式组织。数据字典中的信息实际上保存在基础表中，只有Oracle系统才有权读取和写入基础表。基础表中存储的信息通常是经过加密处理的。而视图是一种虚拟表，它本身并不包含数据，用户可以通过数据字典视图来获取信息，而不需访问数据字典表。数据字典视图分类：视图类型说 明USER视图USER视图的名称以user_为前缀，用来记录用户对象的信息。例如user_tables视图，它记录用户的表信息ALL视图ALL视图的名称以all

15、_为前缀，是USER视图的扩展。用来记录用户对象的信息以及被授权访问的对象信息。例如all_synonyms视图，它记录用户可以存取的所有同义词信息DBA视图DBA视图的名称以dba_为前缀，用来记录数据库实例的所有对象的信息。例如dba_tables视图，通过它可以访问所有用户的表信息V$视图V$视图的名称以v$为前缀，用来记录与数据库活动相关的性能统计动态信息。例如v$datafile视图，它记录有关数据文件的统计信息GV$视图GV$视图的名称以gv$为前缀，用来记录分布式环境下所有实例的动态信息。例如gv$lock视图，它记录出现锁的数据库实例的信息基本数据字典视图字典名称说 明dba_

16、tables所有用户的所有表的信息dba_tab_columns所有用户的表的字段信息dba_views所有用户的所有视图信息dba_synonyms所有用户的同义词信息dba_sequences所有用户的序列信息dba_constraints所有用户的表的约束信息dba_indexes所有用户的表的索引简要信息dba_ind_columns所有用户的索引的字段信息dba_triggers所有用户的触发器信息dba_sources所有用户的存储过程信息dba_segments所有用户的段的使用空间信息dba_extents所有用户的段的扩展信息dba_objects所有用户对象的基本信息cat

17、当前用户可以访问的所有基表tab当前用户创建的所有基表、视图和同义词等dict构成数据字典的所有表的信息与数据库组件相关的数据字典数据库组件数据字典中的表或视图说 明数据库v$datafile记录系统的运行情况表空间dba_tablespaces记录系统表空间的基本信息dba_free_space记录系统表空间的空闲空间的信息控制文件v$controlfile记录系统控制文件的基本信息v$controlfile_record_section记录系统控制文件中记录文档段的信息v$parameter记录系统各参数的基本信息数据文件dba_data_files记录系统数据文件以及表空间的基本信息v$

18、filestat记录来自控制文件的数据文件信息v$datafile_header记录数据文件头部分的基本信息段dba_segments记录段的基本信息数据区dba_extents记录数据区的基本信息日志v$thread记录日志线程的基本信息v$log记录日志文件的基本信息v$logfile记录日志文件的概要信息归档v$archived_log记录归档日志文件的基本信息v$archive_dest记录归档日志文件的路径信息数据库实例v$instance记录实例的基本信息v$system_parameter记录实例当前有效的参数信息内存结构v$sga记录SGA区的大小信息v$sgastat记录SG

19、A的使用统计信息v$db_object_cache记录对象缓存的大小信息v$sql记录SQL语句的详细信息v$sqltext记录SQL语句的语句信息v$sqlarea记录SQL区的SQL基本信息后台进程v$bgprocess显示后台进程信息v$session显示当前会话信息常用动态性能视图视图名称说 明v$fixed_table显示当前发行的固定对象的说明v$instance显示当前实例的信息v$latch显示锁存器的统计数据v$librarycache显示有关库缓存性能的统计数据v$rollstat显示联机的回滚段的名字v$rowcache显示活动数据字典的统计v$sga显示有关系统全局区的

20、总结信息v$sgastat显示有关系统全局区的详细信息v$sort_usage显示临时段的大小及会话v$sqlarea显示SQL区的SQL信息v$sqltext显示在SGA中属于共享游标的SQL语句内容v$stsstat显示基本的实例统计数据v$system_event显示一个事件的总计等待时间v$waitstat显示块竞争统计数据应用举例：1 . 查看所有用户SQL SELECT username, account_status FROM dba_users ; * OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；LOCKED表示账户为锁定状态。2

21、. 查看SIDSQL SELECT instance_name FROM v$instance;3. 查看当前用户所有的表信息SQL SELECT * FROM tab ;* table（表）view（视图）synonym（别名，与视图类似）4. 查看所有用户的表信息SQL SELECT table_name , owner FROM dba_tables ; 5. 查看指定用户的表信息SQL SELECT table_name , owner FROM dba_tables where owner=SCOTT ;第3章 SQL语句基础scott用户有4个案例表，可在日常练习中使用。3.1 O

22、racle内置字段数据类型1. 字符型CHAR型：定长字符串，短则用空格填充，长则出错。VARCHAR2型：变长字符串。字段长度根据实际字符串长度自动调整，不用空格填充。2.数值型NUMBER(PRECISION,SCALE)精度PRECISION指定所有数字位的个数，范围SCALE指定小数的位数，两个参数均是可选的。如果插入的字段数据超过指定位数，将自动四舍五入。3.日期时间数据类型DATE可以存储日期和时间的组合数据。ORACLE默认的日期格式是 DD-MON-YY。4.LOB数据类型用于大型的、未被结构化的数据，如二进制文件、图片文件等。LOB数据类型又分为BLOB、CLOB和BFILE

23、三种。BLOB类型：用于存储二进制对象。如图像、音频、视频。CLOB类型：用于存储字符格式的大型对象。Oracle将数据转换成Unicode格式。BFILE类型：将二进制文件作为操作系统文件存储在数据库外部，BFILE类型的字段仅保存二进制文件的指针。5.ROWID类型亦称伪列类型，用于保存表中每条记录的物理地址。每条记录都有唯一的rowid。ORACLE自动为每个表建立名称为ROWID的字段。可以对该字段进行查询。rowid确定了每条记录属于哪一个数据对象、数据文件、块、行。是基于64位编码的18个字符显示。其格式如下： 示例：ROWID是隐含的，检索表时不会看到，须显式指定名称。SQL S

24、ELECT rowid , ename FROM emp ; 3.2 SQL简介SQL(Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。SQL语言分为3类：1数据定义语言（DDL）用来定义和管理数据库中的对象（如表、视图、存储过程、触发器）。由CREATE、ALTER、DROP命令构成。2数据操纵语言（DML）针对数据库中存储的数据进行相关操作，主要包括增加（insert）、删除（delete）、更新（update）和查询（select）四种操作。在实际工作中增加、删除和更新所占使用DML比例为

25、20%，而查询所占比例为80%。3数据控制语言（DCL）用来管理用户对指定数据库对象的使用权限。常用操作有分配（grant）和回收（revoke）。3.3 SELECT语句的用法在众多SQL语句中，使用频率最高的是SELECT语句，该语句主用于检索数据。虽然在前面已经使用了一些SELECT语句，但这些使用是零散的、不完整的。本节将对SELECT语句进行系统地、完整地介绍。SELECT语句的完整语法格式如下：SELECT ALL | DISTINCT * | expression | column1_name , column2_name , . FROM table1_name | ( sub

26、query ) alias , table2_name | ( subquery ) alias , . WHERE condition CONNECT BY condition START WITH condition GROUP BY expression ,. HAVING condition ,. UNION | INTERSECT | MINUS ORDER BY expression ASC | DESC , . FOR UPDATE OF schema. table_name | view column NOWAIT ;3.3.1 检索单表数据检索单表数据是指从单个表中检索数据，

27、检索的结果都是来自于同一个表中。在检索数据的过程中，既可以检索所有的列，也可以检索部分列。数据将按照SELECT子句后面指定的列的顺序显示。如果使用星号*，则表示检索所有的列，这时数据将按照定义表时指定的列的顺序显示。示例：SQL SELECT * FROM emp ; SQL SELECT empno , ename , job , sal , deptno FROM emp ; SQL SELECT ename AS 姓名 , job AS 职位 , hiredate AS 工作日期 , sal AS 工资 FROM emp ;SQL SELECT ename 姓名 , job 职位 ,

28、hiredate 工作日期 , sal 工资 FROM emp ;SQL SELECT 编号是 |empno| 的雇员,姓名是 |ename| 的工作是 |job FROM emp;SQL SELECT DISTINCT job FROM emp ;3.3.2 过滤数据在SELECT语句中可以使用WHERE子句过滤数据，只检索那些满足过滤条件的数据。通过过滤数据，可以从大量的数据中获取自己所需要的数据。1.比较运算符比较操作符说 明=等于、!=不等于=大于等于大于 SELECT ename , job , hiredate , sal FROM emp WHERE empno=7521 ; S

29、QL SELECT ename , job , hiredate , sal FROM emp WHERE job=any(CLERK,ANALYST) ;2.SQL运算符SQL运算符说 明BETWEEN指定条件在两个值之间，包括边界值LIKE匹配的字符样式，一般用于模糊查询IN匹配的一个列表值IS NULL匹配空值注意：表中SQL运算符可以与NOT运算符取反处理，例如，NOT LIKE，NOT BETWEEN和IS NOT NULL等。示例：SQL SELECT empno , ename , job , sal FROM emp WHERE ename LIKE S% ;SQL SELECT empno , ename , job , sal FROM emp WHERE empno IN (7369 , 7521 , 7789) ; SQL SELECT empno ,