oracle培训材料.ppt

Oracle9i数据库培训,2009年5月,培训目录,Oracle9i数据库系统的安装、配置用户数据库的建立和基本维护Oracle9i数据库管理基本知识Sql*plus工具和PL/SQL语言简介,Oracle9i企业版安装环境,Oracle9iEnterpriseEditon环境要求内存：1GB以上硬盘：80G以上CPU：P41.0G以上操作系统：MSWIN2000AS、WIN2003,Oracle9i客户端安装环境,Oracle9i客户端环境要求内存：512MB以上硬盘：30GB以上CPU：P41.0G以上操作系统：win2000/xp以上.,Oracle9i安装目录结构,根目录为：x:oracle主目录为：Ora92在主目录下包含bin、network、assistants等目录；Admin：数据库管理文件目录，包含以数据库命名的子目录；Oradata：数据库文件存储目录；控制文件数据文件联机重做日志文件,Oracle9i安装步骤,1鼠标单击开始安装（Setup.exe），出现下面界面,Oracle9i安装步骤,2单击下一步，确定Oracle主目录名及安装路径。,Oracle9i安装步骤,3单击下一步，选择要安装的产品。,Oracle9i安装步骤,4单击下一步，选择安装类型。,Oracle9i安装步骤,5单击下一步，选择数据库配置。,Oracle9i安装步骤,6单击下一步，输入数据库标识，以前的Oracle版本默认的是Oracl，命名成其他的也可以。,Oracle9i安装步骤,7单击下一步，选择数据文件的存放位置。,Oracle9i安装步骤,8单击下一步，选择数据库字符集，一般中文字符集使用ZHSGBK16，若使用字符集不当的话数据库中会出现很多“？”,Oracle9i安装步骤,9单击下一步，ORACLE会列出安装产品列表。,Oracle9i安装步骤,10单击下一步，进行ORACLE数据库的安装，在安装的过程中会弹出两次对话框，要求插入第二章和第三章光盘。,Oracle9i安装步骤,11单击下一步，生成数据库并进行工具配置。,Oracle9i安装步骤,12安装成功。,Oracle9i服务名配置步骤,1Netmanager的配置,添加服务命名,选择服务命名,点左侧“+”,Oracle9i服务名配置步骤,2填入服务名,Oracle9i配置步骤,3确定网络协议,常用tcp/ip协议,Oracle9i配置步骤,4填写主机名(ip或完整的计算机名),端口号(与监听器一致),Oracle9i配置步骤,5填写全局标识符SID,Oracle9i配置步骤,6可以选择测试,也可直接完成,Oracle9i配置步骤,7测试界面,Oracle9i监听程序配置步骤,1Netmanager的配置,添加服务命名,选择监听程序,点左侧“+”,Oracle9i监听程序配置步骤,2添加地址,Oracle9i监听程序配置步骤,3保存设置,培训目录,Oracle9i的安装、配置用户数据库的建立和基本维护Oracle9i数据库管理基本知识Sql*plus工具和PL/SQL语言简介,建立表空间、用户、导入数据库,1建立两个表空间（PEDIS40、SYS40_SJK）,建立表空间、用户、导入数据库,2点击编辑存储参数,使数据文件已满能够自动扩展，增量为：5M；最大为无限制,建立表空间、用户、导入数据库,3建立用户（PEDIS40和SYS40_SJK）,建立表空间、用户、导入数据库,4分别授权角色为DBA和RESOURCE，系统为SELECTANYTABLE,建立表空间、用户、导入数据库,5导入数据库在开始菜单中运行中输入命令：Imp用户名/密码服务名file=路径文件名log=路径文件名full=y如下：IMPSYS40_SJK/SYS2005ORACLE92FILE=D:SJKBAKSYS40_SJK.DMPLOG=D:SJKBAKSYS40_SJKLG.LOGFULL=YIMPPEDIS40/SJK2005ORACLE92FILE=D:SJKBAKPEDIS40.DMPLOG=D:SJKBAKPEDIS40LG.LOGFULL=Y,Oracle9i数据库卸载,停止Oracle服务;利用Oracle卸载工具进行卸载;在注册表中删除;HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_LOCAL_MACHINESYSTEMCURRENTCONTROLSETSERVICES删除C:ProgramFilesOracle和Oracle的安装目录;重新启动计算机.,Oracle9i数据库的备份与还原,数据文件每一个Oracle数据库有一个或多个物理的数据文件(DataFile)。一个数据库的数据文件包含全部数据库数据。逻辑数据库结构(如表、索引)的数据物理地存储在数据库的数据文件中。数据文件有下列特征：一个表空间（数据库存储的逻辑单位）由一个或多个数据文件组成;一个数据文件仅与一个数据库联系;一个数据文件只属于一个表空间;可以增大数据文件大小来增加存储空间。,Oracle9i数据库的备份与还原,功能：Oracle数据导入导出imp/exp就相当与oracle数据还原与备份;执行环境：可以在Sqlplus.exe或者命令行方式中执行;查找帮助：在命令行中键入c:imp-?C:exp-?,Oracle9i数据库的备份与还原,数据的导出1将数据库Test完全导出,用户名system密码manager导出到D:daochu.dmpexpsystem/managerTESTfile=d:daochu.dmpfull=ylog=d:daochu.log2将数据库中system用户与sys用户的表导出expsystem/managerTESTfile=d:daochu.dmpowner=(system,sys),Oracle9i数据库的备份与还原,数据的导出3将数据库中的表table1、table2导出expsystem/managerTESTfile=d:daochu.dmptables=(table1,table2)4将数据库中的表table1中的字段filed1以00打头的数据导出expsystem/managerTESTfile=d:daochu.dmptables=(table1)query=wherefiled1like00%,Oracle9i数据库的备份与还原,数据的导入1将D:daochu.dmp中的数据导入TEST数据库中。impsystem/managerTESTfile=d:daochu.dmp上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在后面加上ignore=y就可以了。2将d:daochu.dmp中的表table1导入impsystem/managerTESTfile=d:daochu.dmptables=(table1),培训目录,Oracle9i的安装、配置用户数据库的建立和基本维护Oracle9i数据库管理基本知识Sql*plus工具和PL/SQL语言简介,Oracle9i数据库存储管理,创建与修改数据文件可以使用DBA在图形界面中创建与修改数据文件Altertablespaceusersadddatafilefilename1size10mautoextendonnext2mmaxsize50m;Altertablespaceusersadddatafiled:oracleoradatauserdata3.dbfsize50mautoextendoff;,Oracle9i数据库存储管理,数据库,TableSpace1,TableSpace2,DataFile1,DataFile2,DataFile3,Oracle9i数据库管理,模式对象1与每个用户相关的一组数据库对象的集合模式所有者拥有该模式下的所有对象的全部权限一个表空间可存储不同的模式对象，一个模式的不同对象也可以存储在不同的表空间中,模式对象2表视图索引序列数据库链路过程、函数、包和触发器同义词快照聚集,Oracle9i数据库管理,Oracle数据字典表和视图构成；存储oracle系统的活动信息以及所有用户数据库的定义信息；分为静态数据字典和动态性能表。,Oracle9i数据库管理,静态数据字典用户视图：以USER做前缀，包含当前用户所拥有的全部对象信息；扩展用户视图：以ALL做前缀，它除了包含当前用户所拥有的全部对象信息外，还包含公共账户和显示授权用户的全部模式对象；管理员视图：以DBA做前缀，包含整个数据库的所有对象信息；检索用户在数据库所拥有模式对象信息Select*FromUser_Objects;检索用户表、视图、聚集信息Select*FromUser_Tab_Objects;检索用户对象访问权限信息Select*FromUser_Tab_Privs_Made;,Oracle9i数据库管理,动态性能表虚拟表，记录了当前数据库的活动状况和性能参数；动态性能表的所有者为SYS用户，他们均以V$做前缀；V$BGPROCESS列出当前所有后台进程及运行错误数。,Oracle9i数据库管理,Oracle数据库实例系统全局区(SGA)时数据库信息的共享内存区；多个Oracle进程；每个打开的Oracle数据库有一个或多个Oracle实例支撑；Oracle实例高效地管理数据库的所有数据；为用户和应用程序访问数据库提供服务。,Oracle9i数据库管理,Oracle内存结构ORACLE在内存存储下列信息：执行的程序代码；连接的会话信息；程序执行期间所需数据和共享的信息；存储在外存储上的缓冲信息；ORACLE具有下列基本的内存结构：软件代码区；系统全局区,包括数据库缓冲存储区、日志缓冲区和共享池；程序全局区,包括栈区和数据区；排序区。,Oracle9i数据库管理,Oracle后台进程DBWR数据库写入程序LGWR日志写入程序CKPT检查点SMON系统监控PMON进程监控ARCH归档RECO恢复LCKn封锁Dnnn调度进程Snnn服务器,Oracle9i数据库管理,Oracle数据库和实例的启动启动数据库并使它可用，有三步操作：启动一个实例装配数据库打开数据库StartupmountAlterdatabaseopenStartupOracle数据库和实例的关闭关闭数据库卸载数据库停止实例Shutdownnormal|immediate|abort,Oracle9i数据库管理,初始化参数文件在启动一个实例时，ORACLE必须读入一初始化参数文件（initializationparameterfile），该参数文件是一个文本文件，包含有实例配置参数。这些参数置成特殊值，用于初始ORACLE实例的许多内存和进程设置，该参数文件包含：一个实例所启动的数据库名字在SGA中存储结构使用多少内存；在填满在线日志文件后作什么；数据库控制文件的名字和位置；在数据库中专用回滚段的名字。修改初始化文件参数值直接修改数据库初始化参数文件执行altersessionset参数名=值执行altersystemset参数名=值执行altersystemset参数名=值deferred,Oracle9i数据库管理,Oracle网络结构Net9i是Oracle的核心网络部件，它需要同时安装在客户机和服务器上，通过它在客户端与服务器或两个服务器之间建立网络会话。网络服务名：数据库在客户端的逻辑表示格式为:user/pwsdservice_name监听器：运行在oracle服务器上的一个独立进程，负责监听客户的连接请求。,Oracle9i数据库管理,Oracle网络连接示意图,网络服务名称解析(oraclenames,tns,dns),Net9i客户,监听器,Oracle服务器,Oracle9i数据库管理,Net9i网络配置在服务器端配置监听器在客户端建立网络服务名列表网络参数配置文件Listener.orasqlnet.oratnsnames.oranames.ora监听器配置IPC协议：支持外部过程连接TCP/IP、端口1521：支持Net8客户连接TCP/IP、端口2481：支持IIOP客户连接监听器管理：lsnrctl命令,Oracle9i数据库管理,Oracle本地解析配置网络服务器存储到Tnsname.ora文件中网络协议地址信息数据库服务名称测试连接tnsping网络服务名,Oracle9i事务管理,一个事务为工作的一个逻辑单位，由一个或多个SQL语句组成。一个事务是一个原子单位，构成事务的全部SQL语句的结果可被全部提交或者全部回滚。一个事务由第一个可执行SQL语句开始，以提交或回滚结束，可以是显式的，也可是隐式的（执行DDL语句）。在执行一个SQL语句出现错误时，该语句所有影响被回滚，好像该语句没有被执行一样，但它不会引起当前事务先前的工作的丢失。在ORACLE中一个事务是由一个可执行的SQL语句开始一个事务以下列任何一个出现而结束当COMMIT或ROLLBACK（没有SAVEPOINT子句）语句发出。一个DDL语句被执行。在DDL语句执行前、后都隐式地提交。用户撤消对ORACLE的连接（当前事务提交）用户进程异常中止（当前事务回滚）。,Oracle9i事务管理,提交事务对于与回滚段相关的内部事务表记录提交事务，并赋给一个相应的唯一系统修改号（SCN），记录在表中；在SGA的日志缓冲区中日志项由LGWR进程写入到在线日志文件，这是构成提交事务的原子事务；在行上和表上的封锁被释放；该事务标志为完成。Commit,Oracle9i事务管理,回滚事务在回滚整个事务（没有引用保留点）时，有下列情况：在事务中所有SQL语句作的全部修改，利用相应的回滚段被撤消；所有数据的事务封锁被释放；事务结束。Rollback当事务回滚到一保留点（具有Savepoint）时，有下列情况：仅在该保留点之后执行的语句被撤消；该指定的保留点仍然被保留，该保留点之后所建立的保留点被删除；自该保留点之后所获取的全部表封锁和行封锁被释放，但指定的保留点以前所获取的全部数据封锁继续保持；该事务仍可继续。RollbackToSave,Oracle9i事务管理,保留点保留点（Savepoint）是在一事务范围内的中间标志，经常用于将一个长的事务划分为小的部分。保留点可标志长事务中的任何点，允许可回滚该点之后的工作。在应用程序中经常使用保留点；例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个保留点之后，该保持点之后所获得的数据封锁被释放。,Oracle9i方案对象管理,创建表建立表主要指定义下列信息：列定义完整性约束表所在表空间存储特性可选择的聚集从一查询获得数据语法格式：CreateTableTableName(Column1DatatypeDefaultexpressionconstraint,Column2DatatypeDefaultexpressionconstraint,)Storage子句其他子句；,Oracle9i方案对象管理,创建表CreateTableNew_Dept(DPTNONumber(2),DNAMEChar(6),LOCChar(13);,创建视图CreateViewView_NameAsQueryCreateViewEmp_ViewAsSelectEmpNo,Ename,SalFromEmpWhereSal3000;,Oracle9i方案对象管理,序列生成唯一整数的模式对象CreateSequenceSequence_NameStartWithIncrementByMaxvalue|NoMaxvalueMinvalue|NoMinvalueCreateSequenceDept_SeqStartWith60Incrementby2Maxvalue100引用序列的Currval、NextvalInsertIntoDeptValues(Nextval,BeiJing,China);,Oracle9i方案对象管理,同义词表、视图、序列或其他模式对象的别名专用同义词：特定用户模式中使用，该用户控制专用同义词对其他用户的使用公用同义词：对数据库的每个用户都可用语法格式：CREATEPUBLICSYNONYMSY_NAMEFOROBJECT;实例：CREATESYNONYMCUSTFORCUSTOMERS;CREATEPUBLICSYNONYMSALEFORSALESREPS;SELECT*FROMCUST;,Oracle9i方案对象管理,用户管理Createuseruser_nameidentifiedbypasswordGrantpriviatetouser_name,培训目录,Oracle9i的安装、配置用户数据库的建立和基本维护Oracle9i数据库管理基本知识Sql*plus工具和PL/SQL语言简介,SQL*Plus工具,Sql*plus是用户常用的使用程序之一交互式SQL语句编辑、编译、执行环境PL/SQL块编辑、编译、执行环境SQL*PLUS命令编辑、编译、执行环境,SQL*Plus工具,SQL*PLUS启动与关闭启动：Sqlplus-|-?logonfile.extsqlplusscott/tigerora817selectdept其中selectdept:select*fromdeptwheredeptno,=,=,in,notin,any,all,betweenand,notbetweenand,exists,isnull,isnotnull,likeescape,notlikeescape逻辑运算符：and,or,not连接运算符:|集合运算符：union,unionall,intersect,minus,PL/SQL,变量和常量声明变量：v_namedatatypenotnull:=|defaultexpression常量声明：c_nameconstantdatatypenotnull:=|defaultexpression例子：declarev_namechar(8);v_salnumber(7,2):=0;v_genderbooleannotnulldefaulttrue;v_piconstantnumber(8,7):=3.1415926;在声明语句中，一个语句只能声明一个变量或常量：v_name,v_jobchar(8)此语句导致编译错误可使用%type将已经声明的变量或数据库列类型制定给所声明的变量或常量可使用%rowtype将表或游标的数据结构声明为一个记录变量,PL/SQL,变量和常量Declarev_namechar（8）；v_name1v_name%type:=USER1；V_name2emp.ename%type;v_emp1emp%rowtype;cursorc1isselectename,jobfromemp;v_emp2c1%rowtype;使用%type声明可以进行初始化,%rowtype则不能初始化,PL/SQL,变量赋值方法声明时直接初始化:v_nit1integer:=100;用赋值操作符：:=,v_name:=abcd;用select、fetch同时为多个变量赋值declarev_namevarchar2(10);v_jobvarchar2(9);v_salnumber(7,2);cursorc1isselectename,job,salfromemp;Beginselectename,job,salintov_name,v_job,v_salfromempwhereename=SCOTT;dbms_output.put_line(v_name);openc1;fetchc1intov_name,v_job,v_sal;dbms_output.put_line(v_name);,PL/SQL,变量的作用域变量的作用域重声明开始到当前块结束declaredept_rec1dept%rowtype;cursorc1isselectdeptno,dname,locfromdept;beginopenc1;fetchc1intodept_rec1;dbms_output.put_line(dept_rec1.deptno);declaredept_rec2c1%rowtype;begindept_rec2:=dept_rec1;dbms_output.put_line(dept_rec2.dname);end;dbms_outpu.put_line(dept_rec2.dname);End;,PL/SQL,条件语句IFconditionTHENSequence_of_statements;ENDIF;IFconditionTHENSequence_of_statement1;ELSESequence_of_statement2;ENDIF;,IFcondition1THENSequence_of_statement1;ELSIFcondition2THENSequence_of_statement2;ELSIFcondition3THENSequence_of_statement3;else语句ENDIF;,PL/SQL,条件语句例子Declarev_salnumber(7,2)beginselectsalintov_salfromempwhereename=SCOTT;ifv_sal100;v_s:=v_s+v_I;v_I:=v_I+1;endloop;Dbms_output.put_line(v_s);End;,PL/SQL,While_loop循环语法：whileconditionloopstatementendloop;条件循环Declarev_Iinteger:=1;v_sinteger:=0;Beginwhilev_I=100loopv_s:=v_s+v_I;v_I:=v_I+1;endloop;Dbms_output.put_line(v_s);End;,PL/SQL,For_loop循环语法：for循环变量inreverse初始值表达式.终值表达式loop语句endloop;知道循环次数的循环Reverse倒循环Declarev_sinteger:=0;BeginforIin1.100loopv_s:=v_s+I;endloop;dbms_output.put_line(v_s);End;循环变量I不用定义.,PL/SQL,创建过程语法：CREATEORREPLACEPROCEDUREc_name(argin|out|inoutdatatype,)invoke_rightis|asblock;CREATEORREPLACEPROCEDUREproC_demo(dept_nonumberdefault10,sal_sumoutnumber,emp_countoutinteger)isbeginselectsum(sal),count(*)intosal_sum,emp_countfromempwheredeptno=dept_no;Endproc_demo;,PL/SQL,调用过程：declarev_numinteger;v_sumnumber(8,2);beginproc_demo(30,v_sum,v_num);dbms_output.put_line(30号部门工资总和：|v_sum|,人数:|v_num);End;,PL/SQL,包一组相关过程、函数、变量、常量和游标等pl/sql程序设计元素的组合具有面向对象程序设计语言的特点包类似于java和c+语言中的类包中的元素分为共有元素和私有元素包包括包头和包体两个部分，缺一不可,PL/SQL,创建包头包头只是对包内的数据类型、变量、常量、游标、子程序、异常等元素的定义声明，这些元素为公有元素语法：createorreplacepackagepackage_nameauthidcurrent_user|defineris|as公有元素的定义endpackage_name;创建包体包体是对包头定义部分的具体实现，在包体重可以定义包的私有元素(类型、变量、子程序等)语法：createorreplacepackagebodypackage_nameis|as私有元素定义公有元素的实现begin语句；end;,PL/SQL,使用包在包头和包体创建后，在pl/sql程序中使用包格式：包名.元素名称参见包的具体实例,PL/SQL,触发器触发器是特殊类型的储存过程，它也储存在数据库服务器中当指定的触发事件发生时oracle自动执行一个触发器由三部分构成：触发事件、触发约束和触发动作Oracle支持的触发事件包括：DML语句、DDL语句、数据库系统事件或用户事件触发约束为布尔表达式，其值为TRUE,触发事件发生触发动作为触发器要执行的PL/SQL块,PL/SQL,触发器优点可以根据需要限制用户的数据处理和创建对象操作能够实施比FOREIGNKEY、CHECK等数据库声明完整性约束更复杂的检查和操作，从而保证数据数据库的一致性自动产生派生列的列值能够自动建立事件日志、同步复制数据或出版数据库数据等,PL/SQL,创建触发器DML语句触发器CREATEORREPLACETRIGGERschema.trigger_nameBEFORE|AFTER|INSTEADOFINSERT|DELETE|UPDATEOFCOLUMN,COLUMNONSCHEMA.TABLE_NAME|VIEW_NAMEREFERENCINGOLDASOLD|NEWASNEWFOREACHROWWHEN(CONDITION)PL/SQL块|CALL_PROCEDURE,PL/SQL,创建触发器DDL语句触发器CREATEORREPLACETIRGGERschema.Trigger_nameBEFORE|AFTERCREATE|ALTER|DROPORCREATE|ALTER|DROPONschema.SCHEMA|DATABASEPL/SQL块|CALL_PROCEDURE,PL/SQL,创建触发器系统或用户触发器CREATEORREPLACETRIGGERschema.Trigger_nameBEFORE|AFTERSTARTUP|SHUTDOWN|SERVERERROR|LOGON|LOGOFF|ONschema.SCHEMA|DATABASEPL/SQL块|CALL_PROCEDURE,PL/SQL,创建触发器说明BEFORE和AFTER指触发器的触发时序INSTEADOF选项是ORACLE激活触发器，而不再执行触发事件。只能对视图和对象视图建立INSTEADOF触发器在DML语句触发器中，可使用INSERT、DELETE和UPDATE的组合表示，用OR分隔当建立UPDATE触发器时，可用OF进一步限制表中哪些类修改时才激活触发器FOREACHROW选项说明触发器为行触发器REFERENCEING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前行的新、旧劣值，PL/SQL块中应用相关名称时，必须在前面加冒号(:),但在WHEN子句中不加冒号(:)DDL语句触发器中，CREATE、ALTER和DROP分别说明触发器在制定模式(用SCHEMA选项)或数据库(用DATABASE选项)中创建、修改、删除数据库对象时激活的触发器,PL/SQL,创建触发器说明DDL语句触发器支持的数据库对象包括：表空间、聚集、表、视图、索引、序列、同义词、拥护、过程、函数、包等PL/SQL块时触发器的语句体，是触发器要执行的操作在PL/SQL块中不能包含COMMIT,ROLLBACK,SAVEPOINT和SETCONSTRAINT事务控制语句CALL_PROCEDURE调用存储过程语句，格式：CALL模式.类型.包.过程数据库链路(参数1,参数2);参见创建触发器实例启用和禁用触发器：ALTERTRIGGERschema.trigger_nameENABLE|DISABLE;检索触发器信息：利用数据字典USER_TRIGGERS,ALL_TRIGGERS,DBA_TRIGGERS删除触发器:DROPTRIGGERtrig_name,PL/SQL,游标指向sql内存区的指针为应用程序提供了一种对查询结果集的数据行进行单独处理的方法Pl/sql游标分为显示游标和隐式游标在每个用户会话中，可以同时打开多个游标,PL/SQL,游标操作声明游标打开游标提取和处理游标关闭游标,PL/SQL,声明游标CURSORcursor_nameparameter,parameter)RETURNreturn_typeISselect_statement;其中：parameter_nameindatatype:=|defaultexpression在指定数据类型时，不能使用长度约束Cursorc1isselectdname,locfromdeptwheredeptno=30;Cursorc2(dept_nonumberdefault10)isselectdname,locfromdeptwheredeptnovalue);例子：openc1;openc2(30);openc3(dept