Oracle数据库管理与开发培训

Oracle数据库管理与开发日程安排第一局部 Oracle快速入门、常用工具介绍 Oracle管理：对象、存储、平安第二局部 SQL根底介绍——查询、更新语句 SQL根底介绍——序列、权限等第三局部 PL/SQL根本概念、块结构、流程控制、游标 PL/SQL异常处理，存储过程、函数、触发器第一局部Oracle管理通过学习本课程，您将掌握：快速入门如何安装Oracle数据库软件创立数据库配置监听配置连接回忆常用工具根本Oracle管理根本概念介绍表、视图、索引、函数等模式对象Oracle平安第一章快速入门目标安装Oracle软件创立数据库配置监听配置效劳名卸载Oracle任务1：安装数据库软件目标：在剩余空间最大的分区X上安装Oracle数据库企业版Oracle_Base=X:\OracleOracle_Home_NameOH_DB10gOracle_Sid=orclOracle目录结构Oracle_BaseOracle_HomeOracle_Home\bin(Path环境变量)Oracle_Home\network任务2：创立数据库使用DBCA工具进行创立OracleSIDOracle目录结构Oracle_Base\Admin目录Oracle_Base\Oradata目录任务3：配置监听目的：使客户端可以连接到本数据库任务4：配置网络效劳名创立2个效劳名，一个指向本机〔mydb〕，另一个指向效劳器(pubdb)，以便访问效劳器上的数据库，并进行测试。Oracle的卸载步骤正常卸载1备份数据库；使用exp命令2删除数据库；使用dbca删除数据库使用netca删除监听3卸载软件UniversalInstaller强行卸载删除Oracle相关目录Oracle_Base下及C:\Programfiles\Oracle某些文件必须重新启动后才能删除删除注册表项HKLM\Software\Oracle和效劳相关的注册项常用工具Sqlplus/sqlplusw命令行工具Exp/Imp——导入/导出工具Sqlldr——文本文件导入工具OEM——Oracle企业管理工具Netca——网络配置助手dbca——数据库配置助手UniversalInstaller——安装器Sqlplus登录设置环境执行sql语句DDL语句(数据定义语句，如：表创立等)DML语句〔事务性语句，如：insert、update〕DCL语句〔控制语句，如：grant〕DBCANetcaNetca-效劳命配置样例251=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.251)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb)))OEM工具任务5：利用OEM工具创立表空间创立名为data的表空间，包含2个数据文件，data01.dbf,data02.dbf,大小分别为16M和32M://ligw:1158/em任务6：创立用户创立名为yinhai的用户，口令为password，设置默认表空间为data，在该表空间上的空间限额为20M,默认临时表空间为temp。为yinhai用户赋予connect,resource的角色权限。Exp/Imp工具的使用将oracle数据

二进制文件expuser/pass@svcfile=xxx.dmplog=xxx.log…Impuser/pass@svcfile=xxx.dmplog=xxx.logfull=y…任务5：导出Oracle对象目的：将Oracle某个用户下的所有对象导出到一个二进制文件使用EXP命令expuser/pass@svcnamefile=xxx…重要参数filelogfeedbackquerytables任务6：将dmp文件导入到Oracle目的：将dmp文件导入到Oracle某个用户下使用IMP命令impuser/pass@svcnamefile=xxx…重要参数fullfromusertouserfeedbackindexesrows任务7：利用OEM工具创立表部门表deptDeptnovarchar2(4)primarykey–部门编号Deptnamevarchar2(10)–部门名称员工表emp：Deptnovarchar2(4)–部门编号外键Idnumber(4)primarykey， --使用序列自动编号Id_cardvarchar2(18) --身份证号码Namevarchar2(10) --姓名Birthdaydate --出生日期Gender char(1) --性别男为1，女为2Graduated_From varchar2〔40〕--毕业院校Managernumber(4) --领导IDSalarynumber(7,2) --薪水Sqlldr编写控制文件准备数据文件运行sqlldr是否成功？任务8：将文本文件导入到库表将文本文件内的数据导入到yinhai用户的emp表内101,殷绍昕,2,KM,3,2450102,任海军,1,KM,3,1780103,刘应德,1,KM,3,3290104,郭志,1,CD,2,4580105,扶晓,2,CD,2,980106,房志勇,1,CD,2,1750107,韩萧,2,CD,2,2300108,孟勐,2,KF,1,1400109,王健强,1,KF,1,1600110,王敏,1,KF,1,3200111,钱小峰,1,KF,1,4100Oracle根本概念实例、数据库〔Oracle体系结构〕Oracle存储结构Oracle平安Schema/模式/方案：模式对象：表、视图、索引、过程、函数、包、同义词、序列其它：OracleHome、效劳命名Oracle根本概念——实例与数据库DatabaseInstanceSGARedolog

bufferDatabuffer

cacheSharedpoolDatadict.

cacheLibrarycachePMON

DBW0SMONLGWRCKPTOthersUser

processServer

processPGAControl

filesDatafilesRedolog

filesArchivedlogfilesParameterfilePasswordfileOracle根本概念——实例与数据库的关系实例=后台进程+内存结构数据库=数据文件+控制文件+重做日志文件一个实例只能翻开一个数据库Oracle根本概念——存储结构Oracle根本概念——存储——逻辑结构数据库 database表空间 tablespace段 segment区extent块OracleBlockOracle根本概念——平安用户两个特殊用户sys和system数据字典普通用户角色DBA、ConnectOracle模式对象表视图索引序列同义词函数过程程序包Oracle的效劳OracleOracle_HomeTNSListenerOracleServiceDBNamenetstart/stopservice_name对于监听，lsnrctlstart/stop第二局部SQL根底查询〔简单查询、多表、子查询、使用函数、聚集…〕DML(InsertUpdateDelete)表、视图、约束其它数据库对象第一章编写根本的SQLSelect语句查询指定的列/所有列/检索唯一值，使用表达式，使用别名SELECT*|{[DISTINCT]column|expression[alias],…}

FROMtable;SELECT*FROMtable;SELECTa,b,cfromtable;关于SQL语句大小写不敏感可以多行编写关键字不可以缩写或跨越多行通常每个子句占据一行使用缩进增加可读性列的别名〔Alias〕用于重命名列表题紧跟在列名后，列名和别名之间可以加AS〔可选〕如果别名包含大小写或者空格，那么必须用双引号引起 SELECTname“EmployeeName", salary*12"AnnualSalary" FROMemp;连接运算符〔||〕将多个列或文本连接在一起

SELECTname||‘的工资是’||salaryAS"Employees" FROMemp;去除重复行使用DISTINCT关键字去除重复行

SELECTDISTINCTdeptid FROMemp;第二章限制结果级、排序使用where子句对结果进行筛选使用orderby字句对结果进行排序WHERE子句紧跟在FROM子句后语法SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)];字符串和日期Select字句中的表达式和Where条件中的表达式里面都可以使用字符串和日期字符串和日期要用单引号引起，字符串大小写敏感日期有一定的格式，默认是DD-MON-RR使用比较运算符=，>，>=，<，<=，<>BETWEEN…AND…IN(SET)LIKE‘…[%]...[_]...’ESCAPE‘\’ISNULL逻辑运算符ANDSELECTname,salaryFROMempWHEREsalary>=4000ANDgender=‘1’;ORSELECTname,salaryFROMempWHEREsalary>=4000ORgender=‘1’;NOTSELECTname,salaryFROMempWHERENOT(salary>=4000ANDgender=‘1’);优先顺序规那么优先级 操作符0 括号1 算数运算符*/+-2 连接运算符||3 比较条件>,<,=,…4 IS[NOT]NULL,LIKE,[NOT]IN5 [NOT]BETWEEN6 NOT7 AND8 ORORDERBY子句使用ORDERBY字句进行排序

ASC:升序(默认)

DESC:降序ORDERBY字句位于SELECT语句的最后ORDERBY子句可以按别名进行排序，也可以用列序号排序ORDERBY1; --按第一列排序ORDERBY后可以指定多列每列可以分别指定ASC或DESC排序列不一定要出现在SELECT子句中第三章单行函数SQL函数类型单行函数，每一行返回一个结果多行函数，对多行才返回一个结果，用于聚集单行函数接受参数并返回一个值作用于select语句返回的每一行每行返回一个结果可以修改数据类型可以嵌套参数可以是某个列或表达式单行函数类别通用类和NULL相关字符类大小写相关LOWER，UPPER，INITCAP字符处理CONCAT，SUBSTR，LENGTH，INSTR，LPAD|RPAD，TRIM，REPLACE数字类ROUND，MOD，TRUNC日期类SYSDATE转换类TO_CHAR,TO_DATE,TO_NUMER对日期的操作对一个日期值加上或减去一个数值〔天数〕得到一个新的日期两个日期相减得到相差的天数日期也可以和小时数进行加减，前提是小时数要除以24日期函数函数含义MONTHS_BETWEEN两个日期间相隔的月数ADD_MONTHS将一个日期加上n个月NEXT_DAY指定日期后的下一个星期X的日期LAST_DAY某月的最后一天ROUND对日期进行四舍五入TRUNC对日期进行截取转换函数TO_DATETO_NUMBERTO_CHARTO_CHAR字符串日期数字日期与字符串间的转换日期——〉字符串TO_CHAR(date,format)字符串——〉日期TO_DATE(string,format)Format的格式YYYY，YY，MONTH，MON，MM，DAY，DD，HH，MI，SS字符串与数字键的转换TO_CHAR(string，format〕9代表一个数字0强制显示0$货币符号L本地货币符号.小数点,千分位使用NULL值定义：不可知、未定义、不可访问的值数字0，字符空格不是NULL表达式中如果包含NULL值，那么结果也为NULL通用类函数——NULL相关NVL(A,B)如果A为null那么返回B，否那么返回ANVL2(A,B,C)如果A不为null那么返回B，否那么返回CNULLIF(A,B)如果A和B相等那么返回NULL，否那么返回ACOALESCE(expr1,expr2,…,exprn)返回清单中第一个非空值条件表达式CASE语句CASEexpr WHENcomparison_expr1THENreturn_expr1 [WHENcomparison_expr2THENreturn_expr2 WHENcomparison_exprnTHENreturn_exprn ELSEelse_expr]ENDDECODE语句(col|expression,search1,result1[,search2,result2,...,][,default])第四章从多表中检索数据目标使用等值连接或非等值连接从多表中检索数据使用外连接检索不满足等值条件的数据行使用自连接将一个表和自身进行连接迪卡尔集产生的原因没有指定连接连接无效第一个表中的每一行都和另外一个表的每一行进行了连接使用WHERE字句防止卡迪尔集卡迪尔集的危害产生n1*n2*…*nn行结果连接类型等值连接非等值连接外连接自连接等值连接SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;多表中的同名列使用表前缀来明确多个表中同名的列使用表前缀可以提高性能使用列别名来区分多个表中同名的列使用表别名简化查询Simplifyqueriesbyusingtablealiases提高性能连接超过2个以上的表如果有n个表进行等值连接，至少需要n-1个连接条件非等值连接使用IN，BETWEENAND，>,<,…外连接使用外连接将那些不匹配的行也显示出来外连接的操作符是(+)(+)放在缺失值得那一边SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);自连接必须使用别名第五章使用分组函数聚集数据分组函数的结果是对记录集进行分组，每一组返回一行常用的分组函数AVG，MAX，MIN，COUNT，SUMSTDDEV，VARIANCE分组函数语法SELECT[column,]group_function(column),...FROMtable[WHEREcondition][GROUPBYcolumn][HAVINGcondition][ORDERBYcolumn];在分组函数中使用DISTINCTCOUNT(DISTINCTexpr)AVG(DISTINCTexpr)分组函数中对NULL值的考虑分组函数会忽略包含NULL值的行使用NVL函数解决此问题GROUPBY子句的用法SELECT子句中没有出现在分组函数中的列或表达式必须也包含在GROUPBY子句中GROUPBY子句中的列，不一定要出现在SELECT子句中GROUPBY子句中可以包含多列〔表达式〕常见的错误非分组函数没有出现在groupby子句SELECTdepartment_id,AVG(salary)FROMemployeesWHEREAVG(salary)>8000GROUPBYdepartment_id;在where子句中使用了分组函数〔应使用having子句来替换〕SELECTdepartment_id,AVG(salary)FROMemployeesWHEREAVG(salary)>8000GROUPBYdepartment_id;HAVING子句Where子句的作用哪些行参加分组？Groupby子句的作用如何进行分组？Having子句的作用哪些分组被显示？HAVING子句举例SELECTjob_id,SUM(salary)PAYROLLFROMemployeesWHEREjob_idNOTLIKE'%REP%'GROUPBYjob_idHAVINGSUM(salary)>13000ORDERBYSUM(salary);分组函数可以进行嵌套SELECTMAX(AVG(salary))FROMemployeesGROUPBYdepartment_id;第六章子查询目标：子查询可以解决哪些问题？如何定义子查询？子查询的不同类型单行/多行的子查询使用子查询来解决一个问题谁的工资比张三高？解题步骤：先得到张三的工资xxx找出工资>xxx的那些人

子查询的语法SELECTselect_listFROMtableWHERE

exproperator(SELECTselect_listFROMtable);子查询本卷须知用括号将子查询括起子查询置于比较操作符右侧子查询内部不需要ORDERBY子句，除非你要进行Top-N分析对于单行子查询使用单行操作符，对于多行子查询使用多行操作符单行子查询只返回一行使用单行比较操作符<,<=,>,>=,<>使用多个子查询哪些人和张三在同一个部门，但工资比李四高？SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary>(SELECTsalaryFROMemployeesWHEREemployee_id=143);子查询中使用分组函数SELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);HAVING子句中使用子查询SELECTdepartment_id,MIN(salary)FROMemployeesGROUPBYdepartment_idHAVINGMIN(salary)>(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=50);找错SELECTemployee_id,last_nameFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployeesGROUPBYdepartment_id);找错SELECTlast_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHERElast_name='Haas');多行子查询返回多行多行操作符INANYALL使用ANYSELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ANY(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';使用ALLSELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ALL(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';子查询中的NULL--下面的语句为什么不能返回行？SELECTemp.last_nameFROMemployeesempWHEREemp.employee_idNOTIN(SELECTmgr.manager_idFROMemployeesmgr);第七章使用SQL*PLUS目标使用替代变量定制SQL*PLUS环境输出具可读性的报表创立和执行脚本文件SQL*PLUS能做什么？描述数据库对象的结构编辑/执行SQL语句将SQL语句保存到文件中调用已经编写好的SQL输出报表替代变量的作用存储临时数据&&&DEFINE命令在SQL语句间传递参数动态更改页眉和页脚的值使用替代变量SELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREemployee_id=&employee_num;使用替代变量——日期和字符串对于这两种类型的替代变量必须用单引号括起来SELECTlast_name,department_id,salary*12FROMemployeesWHEREjob_id='&job_title';指定列名、表达式和文本在以下子句后可以使用替代变量WHERE子句ORDERBY子句列或表达式表名整个SELECT语句定义替代变量使用DEFINE命令定义替代变量DEFINEvariable=value如果变量的值包含空格，需要用单引号引起定义的变量只在当前会话有效用UNDEFINE命令去除变量&&替代变量此种替代变量SQL*PLUS会自动用DEFINE命令生成一个变量，用户只得到一次提示SELECTemployee_id,last_name,job_id,&&column_nameFROMemployeesORDERBY&column_name;定制SQL*PLUS环境语法SETvariablevalueSetserveroutputonSHOWvariable常用的变量pagesize,linesizeheading,feedback格式命令COLUMNCOL[UMN][{column|alias}[option]]CLEAR/FORMAT/NOPRINT/NULL/HEADINGTTITLETTITLEtextBTITILEBTITLEtextBREAKBREAKON创立一个打印报表的脚本编辑好查询语句设定显示格式，将这些命令放在SELECT语句之前在SELECT语句之后去除格式设定保存该脚本文件调用执行SQLVSSQLPlusSQLSQL*Plus•一种语言•一个运行SQL命令的环境•ANSI标准•Oracle公司开发•关键字不可以简写•关键字可以缩写•处理数据库中对象的定义和数据•相关命令不会影响到数据库第八章处理数据目标描述DML语句插入数据更新数据删除数据合并数据处理事务DML语句当发生以下情况时会执行DML语句往表中添加新行修改表中现有的行删除表中的数据事务是能够实现一个功能的一组DML语句INSERT语句语法INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);一次只能插入一行如果column不指定，那么按表中列的顺序依次输入值；可以指定只插入哪些列在INSERT语句中使用特殊值插入NULL值，使用NULL或直接略去该字段插入SYSDATE使用to_date函数使用替代变量DEFAULT(也可用于UPDATE语句)从另外一个表复制数据INSERTINTOaSELECT*FROMb;使用这种方法，不能使用VALUES关键字，列必须配对更新语句——UPDATE语法UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];使用子查询UPDATEemployeesSETjob_id=( SELECTjob_id FROMemployees WHEREemployee_id=205),salary=( SELECTsalary FROMemployees WHEREemployee_id=205)WHEREemployee_id=114;基于另外一个表进行更新UPDATEcopy_empSETdepartment_id= (SELECTdepartment_id FROMemployees WHEREemployee_id=100)WHEREjob_id= (SELECTjob_id FROMemployees WHEREemployee_id=200);完整性约束更改一个表的外键时，如果对应的主键不存在会引发此错误UPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;删除语句——DELETE语法DELETE[FROM]table[WHEREcondition];在DELETE语句中使用子查询DELETEFROMemployeesWHEREdepartment_id=(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameLIKE'%Public%');删除时的完整性约束如果删除父表时，子表还包含记录的情况下会引发此错误DELETEFROMdepartmentsWHEREdepartment_id=60;MERGE语句根据条件进行动态插入或更新MERGEINTOtable_nameAStable_aliasUSING(table|view|sub_query)ASaliasON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGE样例MERGEINTOcopy_empAScUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,emission_pct,e.manager_id,e.department_id);数据库事务管理有3种事务DDLDMLDCL数据库事务当开始执行第一个DML语句时事务开始当发生以下事件时事务结束:执行了COMMIT或ROLLBACK语句当DML语句后遇到DDL/DCL语句，事务结束并且自动提交用户退出SQL*PLUS系统崩溃SAVEPOINT设置保存点，以便可以回滚到指定点UPDATE...SAVEPOINTupdate_done;Savepointcreated.INSERT...ROLLBACKTOupdate_done;Rollbackcomplete.隐含的事务处理当遇到以下情况事务自动提交遇到DDL语句遇到DCL语句正常退出SQL*PLUS，但没有明确输入COMMIT或ROLLBACK当遇到以下情况事务自动回滚不正常退出SQL*PLUS系统失败事务提交或回滚前的状态数据可以恢复，使用ROLLBACK可以使用SELECT语句查看修改情况其它用户看不到当前用户所做的修改其它用户不能修改被当前用户修改的数据行，除非当前用户执行COMMIT或者ROLLBACK事务提交后的数据状态被修改后的数据永久保存在数据库中之前的状态永久丧失所有用户可以看到提交后的数据在行上的数据锁释放，其它用户可以进行DML操作所有的保存点被删除事务回滚后的数据状态对数据所做的修改被撤销恢复到之前一个状态行上的锁被释放语句级回滚如果单个DML语句执行失败，那么只有当前语句回滚Oracle效劳器隐含的设置了一个保存点所有其它DML语句所做的修改仍然保存用户应当显式指定COMMIT或者ROLLBACK第九章创立和管理表目标描述主要的数据库对象创立表描述可用的列类型更改表的定义删除、重命名、截取表Oracle主要的数据库对象表根本的存储单元，由行和列组成视图从一个或多个表中的数据展现子集序列产生连续的数字索引用于提高查询性能同义词给对象定义别名命名规那么标名和列名命名规那么:以字母开头最大30个字符只能包含大小写字母、数字、下划线、$、#不能和当前用户下的其它对象重名不能使用Oracle保存字创立表的前提拥有CreateTable权限CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);拥有存储空间创立表时要指定表名、列名、列的类型、字段长度等在其它用户下创立表要在表名前加用户名username.table为列指定默认值可以使用表达式，普通文本或SQL函数等不可以引用另外一个列名或伪列默认值的数据类型必须和列类型一致Oracle数据库中的表包含两种类型用户表由用户创立并维护包含用户的信息数据字典描述用户表的表/视图由Oracle效劳器创立并维护包含的是数据库的信息查询数据字典查询当前用户拥有哪些表查询当前用户拥有哪些类型的对象查询当前用户拥有哪些表、视图、同义词以及序列列的数据类型数据类型描述VARCHAR2(size)变长的字符串CHAR[(size)]固定长度的字符串NUMBER[(p,s)]变长数字DATE日期和时间值LONG变长字符数据，最大可达2GBCLOB字符数据，最大可达4GBRAWandLONGRAWRawbinarydataBLOB二进制数据，最大可达4GBBFILE存储在外部的文件（存储在文件系统中），最大可达4GBROWID16进制的字符串，代表行在表中的唯一地址通过子查询创立表创立表并同时生成数据CREATETABLEtable[(column,column...)]ASsubquery;复制表时非常有用ALTERTABLE语句使用该语句实现添加新列修改现有列为列设置默认值删除列删除、重命名、截取表删除表DROPTABLE重命名表RENAMEtableATOtableB;截取表TRUNCATETABLEtable;注释表注释COMMENTONTABLEtableIS‘comments’;列注释COMMENTONCOLUMNtable.columnIS‘comments’;查看以下数据字典获得注释信息ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS第十章约束目标描述约束创立并维护约束什么是约束约束是表一级的强制规那么约束可以防止数据被删除如果存在依赖关系约束的类型非空约束唯一约束主键约束外键约束检查约束约束系统产生的约束格式为SYS_CN可以在创立表的同时创立约束，也可以在创立完表后创立约束约束可以定义在表一级或列级通过数据字典查询约束定义约束列一级约束:column[CONSTRAINTconstraint_name]constraint_type,表一级约束:column,...[CONSTRAINTconstraint_name]constraint_type(column,...),非空约束——NOTNULL确保空值不能插入到列中CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL,唯一约束——UNIQUE确保表中的列值没有重复CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...CONSTRAINTemp_email_ukUNIQUE(email));主键约束——PRIMARYKEY可以定义在列一级或表一级CREATETABLEdepartments(department_idNUMBER(4),department_nameVARCHAR2(30)CONSTRAINTdept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINTdept_id_pkPRIMARYKEY(department_id));外键——FOREIGNKEY同样可以定义在列或表一级CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...department_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email));外键——FOREIGNKEYREFERENCES:要引用父表的列ONDELETECASCADE:级联删除ONDELETESETNULL检查约束——CHECK定义每行数据必须满足的条件错误的用法使用CURRVAL,NEXTVAL,LEVEL,ROWNUM伪列调用SYSDATE,UID,USER,和USERENV引用其它行的值使用ALTERTABLE命令创立约束语法ALTERTABLEtableADD[CONSTRAINTconstraint]type(column);添加或删除约束激活或禁用约束使用MODIFY子句添加NOTNULL约束添加外键约束为员工表的manager字段添加外键ALTERTABLEemployeesADDCONSTRAINTemp_manager_fkFOREIGNKEY(manager_id)REFERENCESemployees(employee_id);删除约束ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name;删除主键的同时删除外键ALTERTABLEtable_nameDROPPRIMARYKEYCASCADE;禁用、激活约束激活约束ALTERTABLEemployeesENABLECONSTRAINTemp_emp_id_pk;禁用约束ALTERTABLEemployeesDISABLECONSTRAINTemp_emp_id_pkCASCADE;删除列时级连删除约束删除列时指定CASCADE时，会删除和此列相关的所有约束，包括多列约束〔主键〕ALTERTABLEtable_nameDROPCOLUMNcolumn_nameCASCADE;查询约束数据字典USER_CONSTRAINTS数据字典USER_CONS_COLUMNS第十一章创立视图目标描述视图创立、修改视图的定义，删除视图通过视图检索数据通过视图插入、修改、删除数据创立、使用内联视图TOP-N分析什么是视图什么是视图表的一个透视图逻辑概念，不占据存储为什么使用视图显示数据的访问简化复杂查询提供数据独立性为同一数据提供不同展现创立视图在CREATEVIEW后跟随一个子查询CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery为视图列命名子查询中可以使用别名视图名后跟别名列表查询视图SELECT*FROMview_name;同从表检索数据没有区别修改视图仍然使用CREATEORREPLACEVIEWView_name后的别名列表必须和子查询的字段列表意义对应创立一个稍微复杂的视图找出各个部门的最高、最低工资、平均工资，按部门名称分组CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name;在视图上进行DML语句的规那么可以在简单视图上进行DML操作如果视图包含以下情形时不能从视图中删除行分组函数GROUPBY子句DISTINCT关键字伪列ROWNUM在视图上进行DML语句的规那么如果视图包含以下情形时不能在视图中更新行分组函数GROUPBY子句DISTINCT关键字伪列ROWNUM列的定义是一个表达式在视图上进行DML语句的规那么如果视图包含以下情形时不能在视图中添加行分组函数GROUPBY子句DISTINCT关键字伪列ROWNUM列的定义是一个表达式基表中包含非空字段，但不包含在视图中删除视图DROPVIEWview_name;内联视图指在SQL语句内的某个查询，包含有别名，可以被该SQL语句引用SELECTa.last_name,a.salary,a.department_id,b.maxsalFROMemployeesa,(SELECTdepartment_id,max(salary)maxsalFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salary<b.maxsal;Top-n分析工资最高的前十名员工SELECTROWNUMasRANK,last_name,salaryFROMemployeesORDERBYsalaryDESC)WHEREROWNUM<=3;第十二章其它数据库对象目标创立、使用、管理序列创立、管理同义词创立私有和公有的同义词什么是序列一个序列可以：自动产生唯一的数字是可共享的对象主要用于产生主键的值减少应用中产生主键的代码将序列环存在内存中可以提高性能创立序列CREATESEQUENCEsequence[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}];创立序列为dept表的主键创立一个序列CREATESEQUENCEdept_deptid_seqINCREMENTBY10STARTWITH120MAXVALUE9999NOCACHENOCYCLE;确认已经创立序列通过查询数据字典进行确认SELECTsequence_name,min_value,max_value,increment_by,last_numberFROMuser_sequences;NEXTVAL和CURRVAL伪列NEXTVAL返回下一可用的序列值每次被引用时都是返回唯一的值，即使是被不同的用户引用CURRVAL用于获得当前的序列值至少要调用NEXTVAL一次，CURRVAL才会包含值使用序列INSERTINTOdepartments(department_id,department_name,location_id)VALUES(dept_deptid_seq.NEXTVAL,'Support',2500);使用序列将序列缓存到内存中以提高性能当发生以下情况时，序列会发生“断层”发生回滚系统崩溃序列在其它表中被引用如果序列创立时指定NOCACHE,可以在数据字典USER_SEQUENCES中查询下一可用序列值。修改序列修改序列的增量、最大、最小值，循环和混存选项ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE999999NOCACHENOCYCLE;使用序列时的本卷须知修改序列时必须是序列的属主或者拥有修改序列的权限如果序列被修改，只有后续的序列值被影响如果希望序列以另外一个不同的数字开头，那么必须先删除序列，然后重建使用序列时会进行一些校验删除序列DROPSEQUENCEseq_name;什么是索引索引是:一种模式对象引入索引是为了查询时提高性能较少磁盘I/O独立于表创立后，由Oracle效劳器自动维护索引是如何创立的自动创立:当定义了一个主键或唯一约束时会自动创立唯一索引手动创立:用户可以创立非唯一的索引以加快对列的访问创立索引CREATEINDEXindexONtable(column[,column]...);何时该创立索引你应当创立索引，如果发生以下情况:一个列的取值范围比较广列包含了大量的空值一个或多个列经常在WHERE或连接子句中被一起使用表非常大，但每次的查询的数据不超过总数据的2%——4%何时不该创立索引以下情况无需创立索引：小表没有经常在条件中使用的列大局部的查询都会查询超过2——4%的数据表会经常被更新索引列被作为表达式的一局部被引用确认索引USER_INDEXES数据字典包含了索引的信息USER_IND_COLUMNS包含索引的名字，相关的表名、列。基于函数的索引基于函数的索引只基于表达式的索引索引表达式根据表列，常数，SQL函数和用户自定义的函数创立而成CREATEINDEXupper_dept_name_idxONdepartments(UPPER(department_name));Indexcreated.SELECT*FROMdepartmentsWHEREUPPER(department_name)='SALES';删除索引DROPINDEXidx_name;同义词通过创立同义词，可以简化对数据库对象的访问,相当于给对象起了另外一个名字使用同义词，可以简化对另外一个用户下对象的访问减少了数据库对象的名字创立同义词的语法CREATE[PUBLIC]SYNONYMsynonymFORobject;创立和删除同义词为DEPT_SUM_VU视图创立一个同义词，使用更短的名字.CREATESYNONYMd_sumFORdept_sum_vu;删除同义词DROPSYNONYMd_sum;第十三章控制用户权限目标创立用户创立角色来简化对权限模型管理使用GRANT和REVOKE语句进行权限授权和回收创立并访问数据库链接权限数据库权限:系统平安数据平安系统权限:获得对数据库的访问权对象权限:对数据库对象的内容的访问模式:对象的集合系统权限有100多个系统权限.数据库管理员用户比较高级的权限:创立新用户删除用户删除表备份表创立用户DBA使用CREATEUSER语句创立用户CREATEUSERusernameIDENTIFIEDBYpassword；创立用户后，DBA必须赋予一些系统权限GRANTprivilege[,privilege...]TOuser[,user|role,PUBLIC...];修改用户密码ALTERUSERusernameIDENTIFIEDBYpassword;什么是角色角色相当于一个权限的集合，给角色赋予一定的权限，然后将权限赋给用户，简化了权限管理。创立角色将系统权限、对象权限赋给角色将角色赋给用户对象权限对象权限

表

视图

序列

存储过程

ALTER

√

√

DELETE

√

√

EXECUTE

√

INDEX√

INSERT√

√

REFERENCES

√

√

SELECT

√

√

√

UPDATE√

√

对象权限不同的对象有不同的对象权限属主拥有所有该用户下对象的权限属主可以将所属对象的权限赋给其他用户GRANTobject_priv[(columns)]ONobjectTO{user|role|PUBLIC}[WITHGRANTOPTION];对象权限的赋权将对象的查询权限赋予他人将对象的更新权限赋予他人授权时使用WITHGRANTOPTION将对象权限赋予PUBLIC相关的数据字典数据字典视图描述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赋给用户的系统权限如何收回系统权限使用REVOKE收回赋给用户的权限通过

WITHGRANTOPTION语句赋出去的权限也被收回语法REVOKE{privilege[,privilege...]|ALL}ONobjectFROM{user[,user...]|role|PUBLIC}[CASCADECONSTRAINTS];数据库链接让本地用户可以访问远程数据库的一种手段数据库链接创立数据库链接CREATEPUBLICDATABASELINKpub_dbUSING‘pub_db’;在SQL语句中使用数据库链接SELECT*FROMemployee@pub_db;第十三章集合操作目标描述集合操作使用集合操作将多个查询合并为一个查询控制返回行的顺序集合操作并——UNION/UNIONALL交——INTERSECT差——MINUSUNION操作符返回两个查询中的所有记录，但要去除重复记录UNIONALL返回两个查询的所有记录，包括重复行INTERSECT返回同时包含在两个查询中的记录MINUS返回包含在第一个查询，但不在第二个查询中的记录第三局部PL/SQLPL/SQL程序设计简介PL/SQL块结构和组成元素PL/SQL流程控制语句游标的使用异常错误处理存储函数和过程包的创立和应用触发器一、PL/SQL程序设计简介PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。PL/SQL是对SQL的扩展PL/SQL是第三代编程语言和第四代编程语言的完美结合。分为数据库PL/SQL和工具PL/SQL，如proCPL/SQL的使用在PL/SQL中只能使用DML语句：

INSERT、UPDATE、DELETE、SELECTINTO、COMMIT、ROLLBACK不能使用DDL语句，为什么？使用范围SQLPLUS高级语言二、PL/SQL块结构和组成元素PL/SQL块PL/SQL结构标识符PL/SQL变量类型运算符和表达式(数据定义)变量赋值变量作用范围及可见性注释简单例子PL/SQL块由三局部组成DECLARE/*声明局部:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/BEGIN/*执行局部:过程及SQL语句,即程序的主要局部，必须*/EXCEPTION/*执行异常局部:错误处理*/END;PL/SQL块的类型无名块临时性的语句，以Declare开头子程序过程，以Createorreplaceprocudue函数，以Createorreplacefunction程序包,CreateorReplacePackage触发器createorreplacetrigger一个无名块的例子SetserveroutputonBegindbms_output.put_line('当前时间是'||to_char(sysdate,'yyyy-mm-ddhh24:mi:ss'));End;PL/SQL标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：标识符名不能超过30字符；第一个字符必须为字母；不分大小写；不能用’-‘(减号);不能是SQL保存字。提示:不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.建议的变量命名规那么标识符命名规则(前缀）例子参数变量p_p_table_name程序变量v_v_id游标变量c_c_emp异常标识e_e_no_data_found记录类型t_t_studentPL/SQL数据类型类型说

明ORACLE限制CHAR定长字符串，默认长度为1，最大长度327672000字节VARCHAR2可变字符串，最大长度327674000NUMBER(p,s)数字型，p表示精度最大38，s刻度从-84到127DATE日期型BOOLEAN布尔型不使用ROWID存放数据库行号表类型相当于一个记录类型，结构同某个表列类型类型和某个表的列相同注释单行注释以双减号开头，--开始注释多行注释/*开始注释第二行注释结束注释*/举例：在PL/SQL中使用SQL语句直接在beginend之间输入sql语句在sql语句内使用变量在语句后添加commit;或者rollback;三、PL/SQL流程控制语句条件语句CASE表达式循环标号和GOTONULL语句IF语句第一种IF<布尔表达式>THEN PL/SQL和SQL语句ENDIF;第二种IF<布尔表达式>THEN PL/SQL和SQL语句ELSE

其它语句ENDIF;第三种IF<布尔表达式>THEN PL/SQL和SQL语句ELSIF<其它布尔表达式>THEN

其它语句ELSIF<其它布尔表达式>THEN

其它语句ELSE

其它语句ENDIF;CASE语句CASEselector WHENexpression1THENresult1 WHENexpression2THENresult2 WHENexpressionNTHENresultN [ELSEresultN+1]END;相当于decode函数简单循环LOOP

要执行的语句;EXITWHEN<条件语句>/*条件满足，退出循环语句*/ENDLOOP;WHILE循环WHILE<布尔表达式>LOOP

要执行的语句;ENDLOOP;FOR循环FOR计数器IN[REVERSE]下限..上限LOOP

要执行的语句;ENDLOOP;标号和GOTOGOTOlabel;......<<label>>

/*标号是用<<

>>括起来的标识符*/NULL语句NULL;//什么都不做四、游标的使用什么是游标？官方解释：指向上下文区的句柄或指针。我的解释：一个指向查询结果集的变量，用于对结果集进行遍历。分为显式游标和隐式游标处理显式游标的步骤声明游标CURSORc_nameISselect_statement;翻开游标OPENCURSORc_name;用游标检索值FETCHc_nameINTOv_name;关闭游标CLOSEc_name;游标的几个属性%FOUND%NOTFOUND%ROWCOUNT隐式游标SQL游标由PL/SQL引擎翻开或关闭，所以没有OPEN等命令具有同样的游标属性ISOPEN始终为FALSE使用循环遍历游标简单循环LOOPEXITWHENc_name%NOTFOUND/*dosomething*/ENDLOOPWHILE循环Whilec_name%FOUNDLOOP/*dosomething*/ENDLOOPFOR循环FORv_nameINc_nameLOOP/*dosomething*/ENDLOOPNO_DATA_FOUND与%NOTFOUND的区别前者异常 游标属性前者在Selectinto没有成功是触发，后者在update或delete没有匹配where字句时被置为TRUEFORUPDATE自动加行锁CURRENTOFc_name五、异常错误处理异常处理概念异常错误传播异常错误处理编程在PL/SQL中使用SQLCODE,SQLERRM异常的定义及分类用来处理正常执行过程中未预料的事件分类预定义(Predefined)错误ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。非预定义(Predefined)错误即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。用户定义(User_define)错误程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。异常的结构EXCEPTIONWHENfirst_exceptionTHEN<codetohandlefirstexception>WHENsecond_exceptionTHEN<codetohandlesecondexception>WHENOTHERSTHEN<codetohandleothersexception>END;典型的预定义异常错误号异常错误信息名称说明ORA-1001Invalid-CURSOR试图使用一个无效的游标ORA-1012Not-logged-on没有连接到ORACLEORA-1017Login-denied无效的用户名/口令ORA-1403No_data_foundSELECTINTO没有找到数据ORA-1422Too_many_rowsSELECTINTO返回多行ORA-1476Zero-divide试图被零除ORA-1722Invalid-NUMBER转换一个数字失败ORA-6511CURSOR-already-OPEN试图打开一个已存在的游标ORA-6530Access-INTO-null试图为null对象的属性赋值非预定义的异常处理在PL/SQL块的定义局部定义异常情况：<异常情况>EXCEPTION;将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：PRAGMAEXCEPTION_INIT(<异常情况>,<错误代码>)；在PL/SQL块的异常情况处理局部对异常情况做出相应的处理。用户自定义的异常处理在PL/SQL块的定义局部定义异常情况：<异常情况>EXCEPTION;RAISE<异常情况>；在PL/SQL块的异常情况处理局部对异常情况做出相应的处理。在PL/SQL中使用SQLCODE,SQLERRM

SQLCODE返回错误代码数字,SQLERRM返回错误信息.六、存储函数和过程创立函数存储过程什么是存储过程/函数定义：存储在数据库中的PL/SQL程序，具有输入/输出或输入输出参数〔参数具有方向性，单入、单出或是双向〕存储过程与函数除了返回结果有区别外，其实是一样的，存储过程不返回结果，函数要返回结果。函数的结构CREATE[ORREPLACE]FUNCTIONfunction_name [(argment[{IN|INOUT}]type, argment[{IN|OUT|INOUT}]type)]RETURNreturn_type{IS|AS} <类型.变量的说明>

BEGIN FUNCTION_bodyEXCEPTION

其它语句END;存储过程结构CREATE[ORREPLACE]PROCEDUREProcedure_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type)]{IS|AS} <类型.变量的说明>BEGIN <执行局部>EXCEPTION <可选的异常错误处理程序>END;参数传递方法方式说明IN当过程被调用时，实际参数被传递给过程。在过程内，形式参数起着PL/SQL常量的作用——它被认为是只读的，不能被改变。当过程结束，控制返回到调用环境时，实际参数没有被改变。OUT当过程被调用时，实际参数的任何值都被忽略。在过程内，形式参数起着未初始化的PL/SQL变量