ORACLE基础学习培训课件

ORACLE基础学习

企业信息化部

2015-01-15课程内容数据库基本概念介绍SQL程序语言SQL语言数据库基本概念-（1）Oracle服务器：Oracle服务器中包含多种文件结构、进程结构和内存结构；但是，处理SQL语句时，并非所有这些结构都会用到。某些结构用于提高数据库的性能，确保该数据库在遇到软件或硬件错误时可以恢复，或者执行维护该数据库所需的其它任务。Oracle服务器包括一个Oracle例程和一个Oracle数据库。Oracle例程：Oracle例程是后台进程和内存结构的组合。只有启动例程后，才能访问数据库中的数据。每次启动例程时，会分配系统全局区(SGA)并启动Oracle后台进程。后台进程代表调用进程执行各种功能。它们把为每个用户运行的多个Oracle程序所处理的功能统一起来。后台进程执行输入/输出(I/O)，并监视其它Oracle进程来提高并行性，从而使性能和可靠性更加优越。数据库基本概念-（2）Oracle数据库：Oracle数据库包含操作系统文件（也称为数据库文件），这些文件为数据库信息提供了实际的物理存储。数据库文件用于确保数据一致性并能在例程失败时得以恢复。表空间：是实际存储数据的地方。数据库存储分配就是表空间的定义过程，因此表空间的设计对数据库物理设计极为重要，其参数设定好坏对数据库的性能及维护影响巨大。数据库基本概念-（3）表：表是由确定的列数和可变的行数组成的逻辑结构。是存放数据的基本单元。数据库基本概念-（4）视图：视图提供了在一个表或多个表中查看数据的替代方法，它是表上的一个动态窗口。视图并没有存储数据，它只是将一个查询结果集表示成一个虚拟表。索引：索引是为了加快查询的速度而在表上创建的对象。课程内容数据库基本概念介绍SQL程序语言SQL语言SQL数据类型数据类型描述VARCHAR2(SIZE)可变长字符串类型。SIZE默认单位为BYTE，最大长度为4000，最小为1NUMBER(PRECISION,SCALE)可变长度的数字类型，PRECISION是数字可用的最大位数。如有不指定PRECISION,SCALE，可以提供PRECISION和SCALE为38位的数字DATE日期类型，从公元前4712年1月1日到公元9999年12月31日ROWID用一个十六进制的数来标明当前行在表内的唯一地址BLOB存储非结构化二进制文件。常用于图片，视频的存储CLOB存储单字节或者多字节字符数据。常用语文本存储SQL语言SQL语言－创建表使用CREATETABLE语句创建表，需指定表名、列名和数据类型。可通过tablespace指定表空间。SQL语言言－创创建表表CREATE建表示示例：：比较常常用的的建表表方法法改为““ulocal_tbs”SQL语言言－创创建视视图使用CREATEVIEW语句创创建视视图，，以限限制对对表的的数据据访问问或代代替常常用的的SQL查询语语句视图查查询方方式SQL语言言－修修改表表SQL语言言－修修改表表SQL语言言－删删除表表在ORACLE10版本以以后，，如果果DROP不带PURGE选项，，则会会把表表先放放在回回收站站DROPTABLEDEPTPURGE;SELECT*FROMUSER_RECYCLEBIN;PURGETABLEDEPT;SQL语言言－RENAME要更改改表、、视图图、序序列或或同义义词的的名称称，可可以执执行RENAME语句RENAME的另一一种写写法。。SQL语言言－注注释可以使使用COMMENT语句向向表或或列添添加备备注SQL语言言－约约束常用的的约束束有四四种：：非空空约束束，主主键约约束，，外键键约束束和唯唯一性性约束束。SQL语言言－约约束非空约约束：：为表表的列列添加加非空空约束束后，，再为为该表表添加加数据据时就就不可可以插插入空空置了了。主键约约束：：主键键约束束在一一个表表中，，只能能定义义一个个，定定义为为主键键约束束的可可以是是列或或者列列的组组合，，其中中不能能包含含重复复值，，并且且不能能包含含NULL值。外键约约束：：是让让两个个表通通过外外键建建立关关系。。在使使用外外键约约束时时，被被引用用的列列应该该具有有主键键约束束或者者具有有唯一一性约约束。。唯一性性约束束：用用来保保证表表中的的某一一列或或者表表中的的某几几列组组合起起来不不重复复的一一种手手段。。SQL语言言－约约束除了在在表创创建时时，定定义约约束关关系，，也可可以在在表创创建后后建立立或取取消约约束关关系，，语句句如下下：SQL语言言-插插入数数据使用INSERT语句为为表添添加数数据SQL语言言-插插入数数据更新数数据更新数数据删除数数据查询语语句Subselect语法：：Select-cause：查询语语句-选择择列查询语语句-选择择行查询语语句-选择择行查询语语句-选择择行可以使使用AND和OR来指定定任意意多个个查询询条件件。查询语句-谓词查询语句-谓词查询语句-谓词查询语句-排序查询语句-去除重复复行查询语句-运算次序序查询语句的的运算次序序如下，一一个子句的的输出是下下一个子句句的输入。。只有运算算次序在后后面的子句句才能引用用运算次序序在前面的的子句。查询语句-给表达式式命名查询语句-子查询查询语句-分组函数数分组函数对对列中的一一组值进行行运算以得得到单个结结果值。函数名称功能AVG返回某一组中的值除以该组中值得个数的和COUNT返回一组行或值的个数MAX返回一组值中的最大值MIN返回一组值中的最小值SUM返回一组值的和查询语句-数学函数数数学函数函数名称功能ABS(N)返回数字N的绝对值CEIL(N)返回大于等于数字N的最小整数FLOOR(N)返回小于等于数字N的最大整数MOD(M,N)返回两个数字相除后的余数。如果N为0，则返回MROUND(N[,M])执行四舍五入运算SIGN(N)N大于0，返回1，N等于0，返回0，N小于0，返回-1TRUNC(N[,M])对数字进行截取操作查询语句-字符函数数字符函数函数名称功能CONCAT(S1,S2)连接两个字符串INSTR(S1,S2[,n[,m]])取得子串S2在字符串S1中的位置，N为起始搜索位置，M为子串出现次数，N,M默认为1LENGTH(S)返回字符串的长度LOWER返回字符串,并将所有的字符小写UPPER返回字符串,并将所有的字符大写SUBSTR(S,N[,M])取子字符串,从N开始,取M个REPLACE(S,S1,S2)将字符串S中的子串S1用S2替换查询语句-日期函数数日期函数函数名称功能ADD_MONTHS(D,N)返回指定日期时间D之前或者之后的N个月所对应的时间LAST_DAY(D)返回指定日期所在月份的最后一天MONTHS_BETWEEN(D1,D2)返回日期D1和D2之间相差的月数NEXT_DAY(D,'day')返回指定日期D后的第一个工作日（由day指定）所对应的日期SYSDATE用来得到系统的当前日期TRUNC(D[,FMT])按指定格式格式化日期查询语句-转换函数数转换函数函数名称功能TO_CHAR(D[,FMT])按指定格式将日期转变为字符串TO_CHAR(N[,FMT])按指定格式将数字转变为字符串TO_DATE(S[,FMT])按指定格式将符合特定日期格式的字符串转变为DATE类型的值TO_NUMBER(S[,FMT])按指定格式将符合特定数字格式的字符串转变为数字值查询语句字符串使用用单引号,如:’Clerk’ORDERBY指定多列时时,先按第一列列排序,对于第一列列相同的行行,再按第二列列排序.不同列可以以指定不同同的排序,如:SELECTDISTINCTDEPT,JOBFROMSTAFFWHEREDEPT<30ORDERBYDEPTASC,JOBDESC查询语句多个搜索条条件:SELECTDEPT,NAME,JOBFROMSTAFFWHEREJOB='Clerk'AND(DEPT=20ORDEPT=21)查询语句-分组将WHERE子句与GROUPBY子句一起使使用查询语句-分组在GROUPBY子句之后使使用HAVING子句查询语句-标量全查查询标量全查询询返回一行行，且该行行只有一个个值。查询语句-条件表达达式查询语句-条件表达达式查询语句-相关子查查询查询语句-集合运算算集合运算的的查询语句句必须要有有相同的数数据类型和和列数。查询语句-集合运算算查询语句-集合运算算INTERSECT相当于取两两个集合的的交集MINUS相当于取两两个集合的的差集，就就是“去同同留异”查询语句-连接查询语句-连接查询语句-连接查询语句-连接查询语句-连接查询语句-层次查询询当表具有层层次结构数数据时，通通过使用层层次查询可可以更直观观的显示数数据结果，，并显示其其数据之间间的层次关关系STARTWITH：用于指定定层次查询询的根行CONNECTBY：用于指定定父行和子子行之间的的关系，在在其后的表表达式中，，必须使用用PRIOR引用父行。。课程内容数据库基本本概念介绍绍SQL程序语言SQL语言PL/SQL支持的流程程控制结构构：条件控制IF语句CASE语句循环控制LOOP循环WHILE循环FOR循环顺序控制GOTO语句NULL语句PL/SQL支持的流程程控制结构构流程控制结结构-条件控制IF语句根据条条件执行一一系列语句句，有三种种形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIFCASE语句用于根根据单个变变量或表达达式与多个个值进行比比较执行CASE语句前，先先计算选择择器的值流程控制结结构-条件控制流程控制结结构-条件控制LOOP-无条件循环环WHILE-根据条件循循环FOR-循环固定的的次数循环退出控控制命令：：EXIT流程控制结结构-条件控制GOTO语句-无条件地转转到标签指指定的语句句因为GOTO语句会增加加程序的复复杂性，并并且使得程程序的可读读性变差，，一般不建建议使用。。NULL语句-什么也不做做的空语句句通常用在异异常语句中中，对于发发生的异常常生么都不不做的时候候可以加一一句NULL。动态SQL动态SQL是指在PL/SQL程序执行时时生成的SQL语句编译程序对对动态SQL不做处理，，而是在程程序运行时时动态构造造语句、对对语句进行行语法分析析并执行DDL语句命令和和会话控制制语句不能能在PL/SQL中直接使用用，但是可可以通过动动态SQL来执行执行动态SQL的语法：EXECUTEIMMEDIATEdynamic_sql_string[INTOdefine_variable_list][USINGbind_argument_list];异常在运行程序序时出现的的错误叫做做异常发生异常后后，语句将将停止执行行，控制权权转移到PL/SQL块的的异异常常处处理理部部分分异常常有有两两种种类类型型：：预定定义义异异常常-当PL/SQL程序序违违反反Oracle规则则或或超超越越系系统统限限制制时时隐隐式式引引发发用户户定定义义异异常常-用户户可可以以在在PL/SQL块的的声声明明部部分分定定义义异异常常，，自自定定义义的的异异常常通通过过RAISE语句句显显式式引引发发异常常ORACLE常用用的的异异常常异常常在异异常常发发生生时时，，可可以以使使用用ORACLE内置置的的两两个个变变量量：：SQLCODE获取取异异常常错错误误号号，，SQLERRM获取取异异常常错错误误消消息息。。在在程程序序中中总总会会有有未未预预料料到到的的异异常常，，故故要要在在异异常常处处理理部部分分最最后后加加上上WHENOTHERS增强强程程序序的的健健壮壮性性。。Oracle服务器执行行PL/SQL程序序内存存单单元元保存存到到游游标标中中一次次处处理理一一行行检索索行行提取取行行游标标简简介介逐行行处处理理查查询询结结果果，，以以编编程程的的方方式式访访问问数数据据游标标的的类类型型：：隐式式游游标标REF游标标显式式游游标标在PL/SQL程序序中中执执行行DMLSQL语句句时时自自动动创创建建隐隐式式游游标标。。显式式游游标标用用于于处处理理返返回回多多行行的的查查询询。。REF游标标用用于于处处理理运运行行时时才才能能确确定定的的动动态态SQL查询询的的结结果果游标标类类型型游标标简简介介游标标简简介介在PL/SQL中使使用用DML语句句时时自自动动创创建建隐隐式式游游标标隐式式游游标标自自动动声声明明、、打打开开和和关关闭闭，，其其名名为为SQL通过过检检查查隐隐式式游游标标的的属属性性可可以以获获得得最最近近执执行行的的DML语句句的的信信息息隐式式游游标标的的属属性性有有：：%FOUND––SQL语句句影影响响了了一一行行或或多多行行时时为为TRUE%NOTFOUND––SQL语句句没没有有影影响响任任何何行行时时为为TRUE%ROWCOUNT––SQL语句句影影响响的的行行数数%ISOPEN-游标标是是否否打打开开，，始始终终为为FALSE隐式式游游标标SQL>SETSERVEROUTPUTONSQL>BEGINUPDATEprodSETprice=270WHEREpid=‘‘0005';IFSQL%FOUNDTHENDBMS_OUTPUT.PUT_LINE(‘‘数据据已已修修改改');ENDIF;END;/游标标简简介介游标标简简介介隐式式游游标标循环环游游标标用用于于简简化化游游标标处处理理代代码码当用用户户需需要要从从游游标标中中提提取取所所有有记记录录时时使使用用循环环游游标标的的语语法法如如下下：：FOR<record_index>IN<cursor_name>LOOP<executablestatements>ENDLOOP;游标标简简介介显式式游游标标在在PL/SQL块的的声声明明部部分分定定义义查查询询，，该该查查询询可可以以返返回回多多行行显式式游游标标的的操操作作过过程程：：数据据库库打开开游游标标30George344Roger245James1Stud_mrksStud_namestud_no提取取行行变量量关闭闭游游标标显式式游游标标游标标简简介介带参参数数的的显显式式游游标标声明明显显式式游游标标时时可可以以带带参参数数以以提提高高灵灵活活性性声明明带带参参数数的的显显式式游游标标的的语语法法如如下下：：CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;游标标简简介介使用用显显式式游游标标更更新新行行2-1允许使用游标标删除或更新新活动集中的的行声明游标时必必须使用SELECT…FORUPDATE语句CURSOR<cursor_name>ISSELECTstatementFORUPDATE;UPDATE<table_name>SET<set_clause>WHERECURRENTOF<cursor_name>程序子程序命名的PL/SQL块，编译并存存储在数据库库中。子程序的各个个部分：声明部分可执行部分异常处理部分分(可选)子程序的分类类：过程－执执行某些操作作函数－执执行操作并返返回值过程创建过程的语语法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;程序程序过程过程参数的三三种模式：IN用于接受调用用程序的值默认的参数模模式OUT用于向调用程程序返回值INOUT用于接受调用用程序的值，，并向调用程程序返回更新新的值程序函数函数是可以返返回值的命名名的PL/SQL子程序。创建函数的语语法：CREATE[ORREPLACE]FUNCTION<functionname>[(param1,param2)]RETURN<dat