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,JOBFROMSTAFFWHEREDEPT30ORDERBYDEPTASC,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查询的结果游标类型游标简介游标简介1.在PL/SQL中使用DML语句时自动创建隐式游标2.隐式游标自动声明、打开和关闭，其名为SQL3.通过检查隐式游标的属性可以获得最近执行的DML语句的信息隐式游标的属性有：%FOUND–SQL语句影响了一行或多行时为TRUE%NOTFOUND–SQL语句没有影响任何行时为TRUE%ROWCOUNT–SQL语句影响的行数%ISOPEN-游标是否打开，始终为FALSE隐式游标SQLSETSERVEROUTPUTONSQLBEGINUPDATEprodSETprice=270WHEREpid=‘0005';IFSQL%FOUNDTHENDBMS_OUTPUT.PUT_LINE(‘数据已修改');ENDIF;END;/游标简介游标简介隐式游标循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下：FORrecord_indexINcursor_nameLOOPexecutablestatementsENDLOOP;游标简介显式游标在PL/SQL块的声明部分定义查询，该查询可以返回多行显式游标的操作过程：数据库打开游标30