《Oracle基础知识分析和总结.docx》由会员分享，可在线阅读，更多相关《Oracle基础知识分析和总结.docx（37页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Orcale导读：文档适合oracle基础学习。文档中用口括起来的代码局部一般都为可选项。红色标示的局部为本人觉得自己可能应该注意的地方或是容易犯错的地方。文档大局部来至于网络论坛，由本人整理完成，加入了本人测试案例以及 理解，有不对的或是描述不详的地方希望大家一起交流学习。后续 Java 与 Oracle 待更新中。Orcale.1一、相关知识21.1、 关系型数据库(目前主流)21.2、 对象型数据库(新型数据库)21.3、 oracle2二、数据库基本操作22.1 数据库语言分类22.2 用户操作32.3 数据类型32.4 内置函数42.5 基本对象操作72.6 高级查询15三、PL/S
2、ql 编程173.1 基础局部173.2 存储过程 procedure233.3 函数(function)263.4 游标283.5 触发器(trigger)30视图的操作工、创立视图create or replace force | noforce view 视图名（字段别名 1 ）as sqlQuerywith check option 约束名with read only其中：or replace :假设创立的视图已存在，oracle将自动重建该视图（覆盖）force :不管基表是否存在oracle都会创立改视图noforce :只有基表存在的情况下oracle才会创立该视图sqlQuer
3、y : 一条完整的select语句也可以在该语句中定义别名with check option :通过视图插入或修改数据行必须满足视图定义的约束with read only :该视图不可以进行任何DML操作例：SQL create or replace view test_vi （姓名，班级编号，班级名称）as select s. sname, c. cid, c. cname from2 student s, class cwhere s. cname=ame;视图已创立。2、视图定义原那么1 .视图的查询可以使用复杂的select语法，包括链接/分组查询和子查询.在没有with check
4、option和with read only的情况下，查询中不能使 用order by子句2 .如果没有为with check option约束命名，系统会自动为之命名，形式 为 sys_cn;3、查询视图和查询表一样。select * from 视图名;例:例:SQL select * from test_vi;姓名班级编号班级名称cOlc02cOlc02一一一一班班班班4、修改视图可以直接通过or replace关键字覆盖原视图。5、删除视图drop view视图名。删除视图不会影响基表中的数据。只有视图所有者和drop view权限的用户才可以删除视图。 视图被删除后基于被删除视图的其他视
5、图或应用将无效。6、查看视图库义select 显示字段from usejviews where view_name=视图名;例：SQL select view_name , text from user_views where view_namezz，TEST_VI;VIEW.NAME必智木写不管定义时是大写 还是小写TEXTTEST_VIselect s sname, c. cid, c. cname fromstudent s, class cwhere s. cname=c. cname2.5,4序列序列简介工、定义：序列是oracle提供用于产生一系列唯一数据的对象。2、特点：1、自动
6、提供唯一数值2、共享对象（创立一个序列可以由多个对象（表）调用）3、由于他的唯一性我们主要用于提供主键值4、将序列值装入内存可以提高访问效率序列的操作创立create sequence 序列名increment by n 递增值是n如果n是正数就递增负数反之默认1start with n 开始值递增默认minvalue递减默认maxvaluemaxvalue n | nomaxvalue 最大值minvalue n | nominvalue 最小值cycle | nocycle 循环不循环当到达最大值的时候是否从最小值继 续取值，如果用于做主键建议不采用循环cache n | nocache;
7、 分配并存入内存一般不采用缓冲2、属性nexval返回序列下一个有效值currval返回序列当前值注：nexval应在currval之前指定，二者应同时有效234567increment by 1 start with 1 maxvalue 3 minvalue 1 cycle nocache;SQL selecttest_seq. nextvalfromdual;例:SQL create sequence test_seq此处我设置的是循环取值，在测试的时候会发现当循环到 3的时候，再次取值会返回一个1。所有建议大家在使用序列 做主键的时候尽量不要使用循环取值。数据量大的时候尤其 得注意。序
8、列已创立。NEXTVALSQL selectSQL selecttest_seq. nextvalfromdual;NEXTVALSQL selectSQL selecttest_seq. nextvalfromdual;NEXTVALSQL selectSQL selecttest_seq. nextvalfromdual;NEXTVAL3、序列实例SQL234567create sequence test_seq调用序列产生工Dincrement by 1Start with 1注：序列不可用。工replace关键字来创立maxvalue 99999minvalue 1nocyclenoc
9、ache;序列已创立。SQL insert into myemp values(test_seq. nextval,? zhangsan,);已创立1行。SQL select * from myemp2 ：EID ENAME1 zhangsan注：多个表掉用一个序列时可能会产生序列裂缝，所以建议尽量不要多个表 调用一个序列来产生主键4、删除序列drop sequence 序歹U名SQL drop sequence test_seq;序列已删除。表空间表空简介：1、定义:Oracle数据库与其他数据库之间最大的区别要属表空间设计。Oracle数据库开创性地提出了表空间的设计理念，这为Oracle
10、数据库的高性能做出了不可磨灭的贡献。可以这么说，Oracle中很多优化都 是基于表空间的设计理念而实现的。ORACLE数据库被划分成称作为表空间的逻辑区域一一形成 ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个 表空间，而一个表空间那么对应着一个或多个物理的数据库文件。表空间 是ORACLE数据库恢复的最小单位，容纳着许多数据库实体,如表、视图、 索引、聚簇、回退段和临时段等。表空间创立（此处偷懒了，网上找的案例）/*分为四步*/*第1步：创立临时表空间*/create temporary tablespace user_teinptempfile D:oracleora
11、dataCracle9iuser_temp, dbf1 size 50mautoextend onnext 50m maxsize 20480m extent management local;/第2步：创立数据表空间/ create tablespace user_data loggingdatafile *D:oracleoradataOracle9iuser_data.dbf1size 50mautoextend onnext 50m maxsize 20480m extent management local;/第3步：创立用户并指定表空间/create user username i
12、dentified by password default tablespace user_datateinporary tablespace user_temp;/第4步：给用户授予权限*/grant connect,resourceF dba to username;2.6高级查询 group by 子句注：group by要和多行聚合函数一起使用，否那么没有意义。1、简单分组查询例：select did,sum(sal) from emp group by did;解释：根据部门ID(did)分组查询emp表，显示部门ID和部门总薪水。2、group by 和 having 子句例：se
13、lect did ,sum(sal) from emp group by did having sum(sal)100000;解释：根据部门ID(did)分组查询emp表，显示部门总薪水大于100000 的部门ID和部门总薪水。 order by 子句排序查询select * from emp order by sal desc;解释：查询emp表并按薪水(sal)降序排序。desc表示排序方式为降序 不写默认为升序。 链接查询(这里只说左/右连接)1左链接left join例： select e.ename , e.sa I, d.dname fromemp e left join dept
14、 d on e.did = d.didwhere e.sal3000;解释：员工表左链接(left join)部门表,显示员工工资大于3000的员工 姓名、工资和部门。其中on后边跟的判断表达式为连接条件。注：左链接查询左表中的数据会全部显示，右表的数据假设不满足连接条 件将不会被显示。以此条查询为例，如果有个员工没有部门那么他 不满足查询条件，但是他还是会显示，如果有个部门没有员工那么 这个部门不满足连接条件，这个部门将不会显示在结果中。(右连接与此反之，下面不做解释)2、右连接 right join例：select e.ename , e.sa I, d.dname fromemp e r
15、ight join dept d on e.did = d.didwhere e.sal3000;其实将上面的左连接中的dept表和emp交换位置和此条记录是 一样的结果。三、PL/Sql编程基础局部3.1.1 什么是 PL/Sql 编程(procedural language sql)定义：在开发过程中，开发人员会遇到一些复杂的查询，一些正常的做法无法完成时, 我们就需要PL/Sql语言来完成。PL/Sql是结合了 sql与oracle自身过程控制于一 体的语言。PL/Sql不但支持全部的sql语句，而且支持自身控制语言，如：变量声 明，赋值，选择，循环等。过程控制结构与sql数据处理可无缝
16、对接。也可以使用 PL/Sql进行创立存储过程以及程序包。 优点：安全性、移植性、提高效率3.1.2 基本结构与语法结构：declare变量声明局部 包括变量、常量begin执行代码块局部exception异常处理局部end;变量声明语法：变量名变量类型；变量名变量类型:=值；plsql中赋值用，:=，变量名变量类型default值；default取默认值 PL/Sql中的变量类型类型说明varchar2（长度）可变长度字符串number（精度，小数点保存位）数值类型date时间timestamp时间戳char（长度）定长字符long略boolean值：true、false nullplsjn
17、tegerpl/sql附加的整型数据类型naturalplsjnteger子类型，所有自然数naturaln与natural 一样,不能为nullbinaryjnteger整数string与 varchar2 一样% rowtype结果集类型（游标中常用）%type字段类型（游标中常用） PL/SQL中的特殊符号类型符号说明赋值*对变量、常量等赋值字符串连接IIa | b单行注释-这里是单行注释多行注释/*.*/*这里是多行注释*/符号间隔标示特殊位置，用在goto所指向的位置范围符 1.10 for循环中循环条件会用到算术运算+ - * /加减乘除嘉运算*3*2=9关系运算略逻辑运算or a
18、nd not略 顺序（goto、null）goto和null是plsql中的两个特殊语句goto是无条件跳转，null是将执行权转 给下一行。1 goto3.6 包34一、相关知识1、关系型数据库（目前主流）低级：access中级：mysqk sqlserver高级：db2、Sybase oracle1.2 、对象型数据库（新型数据库）Nosql1.3 oracleoracle是基于对象关系型数据库，服务收费，产品免费。为什么只创立一个数据库：oracle允许一台服务器有多个数据库，但是每一 个数据库都会占用很大的资源，所以一般情况下，一台服务器只建一个数据 库，名字默认为orci,可以通过对
19、用户进行权限分派的方式来让用户对不同 数据库对象（表、视图、索引等）进行控制。二、数据库基本操作2.1数据库语言分类DML： update delete insert into （数据操控语言）DDL： create、alert、drop （数据库定义语言）DCL：grant revoke （数据控制语言）SQL declare2 num number：=10;begin3 if nuni10 thengoto printOl;4 elsegoto print02;5 end if;print016 dbn）s_output. put_line （大于 10）;print027 dbms.ou
20、tput. put_line （小于或等于 10）;end;8 /小于或等于102、null没什么好说的就是无条件将执行权交给下一行控制if then elsif then elseSQL declare2 num number：=10;begin3 if num10 thendbms_output. put_line （大于 10）;4 elsif num declare2 sex varchar2(4);begin3 select student, stusex into sex from student where stuname=lisi ;case sex4 when 男then d
21、bms_output. put_line (J is boy);when 女then dbms_output. put_line (? is girT );5 else dbms_output. put_line (性别不详);end case;6 end;/is boyPL/SQL过程已成功完成。case when then 赋值SQL declare2 abc char (1):=upper ( &abc,);abcl varchar2(20);3 beginabcl：=678910case when when when elseabc=A abc=B abc=C ， cicithen h
22、ehethenthenhaha，xixi111213end; dbms end:_output. putlineC abc is： |labelabcl is：abcl):4入值值C1输原新ababc的值:2: abc2: abc is：A, abclAchar (1):=upper (char (1):=upper (is：hehe&abc A);PL/SQL过程已成功完成。循环loop循环 无条件循环 通过exit关键字跳出循环SQL declare2 numl number(4)：=0;3 num2 number(4)：=0;4 begin5 loop6 numl ：=nuni 1+1;
23、7 num2: =num2+nuni 1;8 if numl10 then9 exit;10 end if;11 end loop;12 dbms_output. put_linef numl is :| |T0_CHAR(numl) | | nun)2 is : I | TO_CHAR (num2);13 end;14 /numl is ：11 num2 is ： 66PL/SQL过程已成功完成。2、while 循环SQL declare2 numl number (4):=0;3 num2 number (4)：=0;4 begin5 while numl二10 loop6 numl ：=
24、nuni 1+1;7 num2 ： =num2+nuni 1;8 end loop;9 dbms_output. put_line C numl is / | | TO_CHAR (numl) | |J num2 is / | |T0_CHAR(nun2);10 end;11 /numl is ：11 num2 is ：66PL/SQL过程已成功完成。3、for循环SQL declarenuml number(4)：=0;2 num2 number(4)：=0;begin3 for numl in 1.10 loopnum2：=num2+num1;4 end loop;dbms_output.
25、 put_line ( numl is : I I TO_CHAR(num2);5 end;/numl is ：55PL/SQL过程已成功完成。3.2存储过程procedure 32存储过程简介定义：存储过程（Stored Procedure ）是一组为了完成特定功能的SQL语句集， 经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该 存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一 个设计良好的数据库应用程序都应该用到存储过程。存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应 用程序使用时只要调用即可。在ORAC
26、LE中，假设干个有联系的过程可以组合 在一起构成程序包。优点：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译， 而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行o2当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、 Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结存储过程日以重复使用,可减少数据库开发人员的工作量。4 .安全性高，可设定只有某用户才具有对指定存储过程的使用权语法及案例语法create or replace procedure 过程名（参数1参数类型，参数2类型）is变量变量类型；
27、begin执行代码块end;例表:SQL select * from student;例：根据输入的名字查询出总成绩SIDSNAMESEXYWSX1zhangsanm78.785.22lisim89.5803xiaohongf9876.8SQL create or replace procedure test_proc(name in varchar2) is2 yw_score number;sx_score number;3 beginselect yw, sx into yw_score, sx_score from student where sname=name;4 dbms_out
28、put. put_line(yw_score+sx_score);end;5 /过程已创立。SQL execute test_proc ( zhangsan);163；9例：带输入和输出参数的过程SQL create or replace procedure test_proc(name in varchar2 , count_score out number)is2 yw_score number;sx_score number;3 beginselect yw, sx into yw_score, sx_score from student where sname=name;4 count
29、_score：= yw_score + sx_score ;end;5 /过程已创立。SQL declaresname varchar2(20);6 stuscore number;begin7 sname：=zhangsan;test_proc(sname, stuscore);8 dbms_output. put_line(stuscore);end;9 /163.9PL/SQL过程已成功完成。调用的两种方式execute过程名；只是简单的执行过程如果是过程有输出参数时这种 方式就不可以实现。SQL set serveroutput on;SQL execute test_proc ( 3
30、); xiaohongPL/SQL过程已成功完成。SQL1、 begin过程名end;存储过程的基本维护（常用的2种）工、删除存储过程；drop procedure 过程名2、查看过程源代码select * from user_source where type=zprocedurez;3.3 函数(function)3.3.1 自定义函数简介定义及作用用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调 用时如同系统函数一样，如max (value)函数，其中，value被称为参数。函数 参数有3种类型。参数类型描述1、in表示输入给函数的参数2、out表示参数在函数中被赋值，可
31、以传给函数调用程序3、in out表示参数，即可以被传值也可以被赋值3.3,2语法无参的函数语法：create or replace function 函数名(参数 参数输入(in)/输出(out)类型 参数类型,)return返回值类型is返回值返回值类型begin返回值赋值执行代码块return返回值end函数名例无参的函数：返回学生姓名的函数SQL create or replace function return_snamereturn varchar22 isstuname varchar2(20);3 beginselect sname into stuname from stud
32、ent where sid =1;4 return stuname;end return sname;5 /函数已创立。SQL select return_snanie from dual;RETURN_SNAMEzhangsan例：带有in参数的函数 返回学生姓名SQL create or replace function return_sname(stuid in varchar2)2 return varchar23 is4 stuname varchar2(20);5 begin6 select sname into stuname from student where sid=stu
33、id;7 return stuname;8 end return sname;9 /函数已创立。SQL select return_sname(1) from dual;RETURN.SNAME ( 1)zhangsan例：带有输入in和输出out参数SQLcreateorreplacefunctionreturn_cname(stuidinvarchar2, clanameoutvarchar2)2 returnvarchar2is3 classname varchar2(20);begin4 select cname into classname from student where si
34、d=stuid;return classname;5 end return_cname;6 /函数已创立。3.4游标游标的简介作用：开发人员通过使用游标，可以控制表中的数据，并且可以像其他的编程 语言一样来实现操作和运算。 分类：静态游标（显示游标、隐藏游标）、动态游标。为什么使用游标常用的select、update,等语句，很难实现对单条记录的控制，而游标 可以从几何性质的结果中提取单条记录。什么是游标可以把游标看做成一个变动的指针，这个指针可以指向记录集中任意一条记 录，这样就可以指向该条记录的所有数据。初始化的指针默认指向记录集中 的第一条。游标下移时，记录集自动指向下一条。针对每条记录
35、，游标也提 供了访问每条记录的方法。游标的分类：静态游标和动态游标两类：静态游标又分为显式和隐式游标。注：游标不是数据库对象，它仅仅只是保存在内存当中。显示游标.显示游标的定义（相当于java中ResultSet）显示游标:是指在使用之前必须由明确的游标声明和相应定义，这样的游标定 义会关联相关数据的查询语句，通常会返回一行或多行数据。显示游标要求 用户自己写代码完成，一切将由用户控制。1 .语法4步1、声明游标cursor 游标名 is select 字段 from 表 where;2、翻开游标open游标名；3、提出数据fetch游标名into变量名；4、关闭close游标名；.游标的属性
36、%found 相当于 ResultSet 中的 next（）;DQL： select、order by、group by(数据查询语言)TCL： commit rollback savepoint (事务控制语言)22用户操作查看当前登陆用户:select user from dual;select user from user users;当前用户角色:select * from user_role_privs;当前用户权限:select * from session_privs;创立create user 用户名 identified by 密码；修改alert user 用户名 iden
37、tified by 新密码；删除drop user用户名;授权grant权限名to用户名例：授予连接数据库权限grant connect to testOOl2.3数据类型常用数据类型类型说明描述char(n)n=l to 2000 字节定长字符串缺省值为1varchar2(n)n=l to 4000 字节可变字符串%notfound%rowcount结果行数%isopen判断游标是否翻开例：查询出性别为m(男)的学生的语文(yw)成绩SQL declarecursor test_cur is2 select * from student where sex=m,;stus student%r
38、owtype;3 beginif test_cur%isopen then2 3 4Ta Ta tx6 7 8名名1 1 1姓姓null;elseopen test_cur;end if;fetch test_cur into stus;while test_cur%found loop dbms_output. put_line (姓名： fetch test_cur into stus; end loop;close test_cur;end;/zhangsan 成绩：78. 7lisi 成绩：89.5stus. snameII成绩：I |TO_CHAR(stus. yw);PL/SQL过
39、程已成功完成。3.5 触发器(trigger)3.5触发器的定义与分类定义触发器是在特定的事件出现的时候，自动执行的代码块，类似于存储过程但是用户不能直接调用。触发器必须依赖于事件。分类触发器分为三类1、dml触发器(本文档只给出dml触发器实例)ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触 发，并且可以对每个行或语句操作上进行触发。2、替代触发器(instead)由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。 所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种 处理方法。3、系统触发器它可以在ORACLE数据库系统的事件中进行触
40、发，如ORACLE系统的启 动与关闭等。触发器的组成1、触发事件：引发触发器被触发的事件，例如：dml语句2、触发时间：及触发器是在触发事件之前(brfore)还是之后(after)执行3、触发操作：触发器执行代码块，也就是触发器本身要做的事4、触发对象：如：表、视图只有在这些对像上发生了符合触发条件的触发 事件，才会执行操作。5、触发条件：由when子句指定一个逻辑表达式。只有当表达式为true时触 发器才会被执行。6、触发频率：触发器执行的次数，即语句statement触发器和行级(row)触发 器。语句级(statement)触发器：是指当某事件发生时，该触发 器值执行一次。行级(row
41、)触发器：是指当某触发事件发生时, 对受到该操作影响的每一行数据，触发器都会单独执行一次。考前须知1、触发器不接受参数2、一个表上最多只能有12个触发器，但同一时间、同一类型、同一事件的触发器只有有一个。并且触发器之前不能有矛盾。3、一个表上的触发器越多，对该表的DML操作性能影响越大4、触发器的执行局部只能使用DML语句，不能使用DDL语句5、触发器中不能包含事务控制语句（commit,rollback,savepoint）。因为触发器 是触发语句的一局部，触发语句被提交、回滚时，该触发器也会被提交、 回滚。6、在触发器主体中调用的任何过程、函数都不能使用事务控制语句。7、在触发器中不能声明
42、任何Long和blob变量。新值new和旧值old也不能 是表中的long和blob列。8、不同类型的触发器（如：dml触发器、instead触发器、系统触发器）之间的 语法格式和作用有较大的区别（这里恕本人无能，替代触发器、系统触发器 本人尚在研究中。）o触发器语法及案例create or replace trigger 触发器名after | brfore 定义触发时间 create or replace trigger test_tri2 after update of cname on class 定:在他己fa，5表他的cn字左 后也3 for each row -定义此触发器为行级
43、触发器4 begin打印出新旧值5 dbms_output. put_line(, old name : I I ：old. enamel | new name : I I ：new. cname);6 update student set cname=:new. cname where cname=：old. cname;7 end;8 /例2：但员工被删除时，把删除记录写入到员工删除日志表中CREATE OR REPLACE TRIGGER tr_del_empBEFORE DELETE T旨定触发时机为删除操作前触发ON scott.empFOR EACH ROW 说明创立的是行级触发器
44、BEGIN7寻修改前数据插入到日志记录表deLemp，以供监督使用。INSERT INTO emp_his(deptno , empno, ename , job ,mgr, sal, comm , hiredate )VALUES( :old.deptno, zold.empno, :old.ename , :old.job;old.mgr, :old.sal, zold m, :old.hiredate ); END;例3：限制只对部门id为80的记录继续触发器操作CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pctOR DELETEON HR.employeesFOR EACH ROWWHEN