oracle 学习知识点速查

1、简介：Oracle是一个对象关系数据库管理系统（ORDBMS）。它既提供关系数据库系统的功能，又提供面向对象数据库系统的功能。每个 Oracle数据库对应唯一的一个实例名SID，Oracle数据库服务器启动后，一般至少有以下几个用户：Internal，它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA用户使用来完成数据库的管理任务，包括启动和关闭数据库；Sys，它是一个 DBA用户名，具有最大的数据库操作权限；System，它也是一个 DBA用户名，权限仅次于 Sys用户Oracle服务器是一个数据库管理系统，它提供开放、全面和综合的信息管理。Oracle服务器由

2、Oracle数据库和Oracle实例组成。后台进程和内存结构的集合称为Oracle实例。每一个运行的Oracle数据库都与一个Oracle实例相联系。一 安装及体系结构概述注意：环境变量的设置问题。接触oracle几个月中，经常重复出现一些问题，其原因就是我没有设置环境变量等相关的东西相关的设置：？安装数据库服务器时配置的名称：全局数据库名：SID：安装客服端过程中，会出现Oracle Net Configuration Assistant(Oracle网络配置助手)的【欢迎使用】界面：【监听程序配置】：【本地Net服务名配置】中：服务名：输入全局数据库名。网络服务名（Net服务名）：自己随便

3、填！客户机怎么连接数据库？1.2 体系结构数据库的逻辑结构Oracle9i数据库的逻辑结构主要指从数据库使用者的角度来考察的数据库的组成，如下图。自上先下，数据库的逻辑结构共有6层。数据块（Data Block）。数据区间（Data Extent）。数据段（Data Segment）。逻辑对象（Logic Object）。表空间（Tablespace）。注：Oracle 9i 安装完毕后自动建立9个默认的表空间，如下表：名称主要作用CWMLITE用于联机分析处理（OLAP）DRSYS用于存放于工作空间设置有关的信息。EXAMPLE实例表空间，存放实例信息。INDEX索引表空间，存放数据库索引信

4、息。SYSTEM系统表空间，存放表空间名称、所含数据文件等管理信息。TOOLS工具表空间，存放数据库工具软件所需要的数据库对象。UNDOTBS回滚表空间，存放数据库恢复信息。USERS用户表空间，存放用户私有信息。数据库（Database）1.3 登陆【管理服务器】从【Oracle企业管理控制台】界面进入，如下所示：（只能是sysman账号，其它的好像不行哦？）1.4 数据库的启动和关闭二 SQL*PLUS基础、实例的启动与关闭2.1 登陆问题环境：数据库实例：testdb。密码：testSql*plus连接在命令行下,sys用户登陆：sqlplus "sys/test as sys

5、dba"实例没有打开,使用【sqlplus "sys/test as sysdba"】执行将错误ORA-12560:TNS:协议适配器错误！所以使用【set oracle_sid=testdb】先打开实例。如果目前实例没有打开，我们可以使用nolog的形式登陆：sqlplus/nolog注意：无论如何都要先【set oracle_sid=testdb】，先选择数据库实例！操作如下：2.1.1 ORA-12560: TNS:protocol adapter error正确的连接方式更改登录为sys：SQL> conn sys/sys服务名 as sysdba;

6、 演示：C:Documents and Settingsguocw>sqlplus /nologSQL*Plus: Release .0 - Production on 星期四 6月 18 09:12:17 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys/testtestdb as sysdba;已连接。SQL> exit从Oracle9i Enterprise Edition Release .0 - Production

7、With the Partitioning, OLAP and Oracle Data Mining optionsJServer Release .0 - Production中断开C:Documents and Settingsguocw>sqlplus/nologSQL*Plus: Release .0 - Production on 星期四 6月 18 09:17:42 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn scott/tig

8、ertestdb已连接。SQL>启动实例的命令：Startup：正常的命令，分为三个环节，首先启动实例；在打开控制文件，即database mount；最后打开数据文件，就是database openStartup mount：启动数据库实例的时候，同时启动控制文件，但是不启动数据文件！我们进行热备份的时候，是不允许进行归档/非归档方式！。.?Startup nomount:启动数据库实例的时候，不启动控制文件，不启动数据文件！关闭实例：Shutdown：正常关闭，要等待所有客户断开连接后，才能真正关闭。Shutdown immeditale：迫使所有的用户执行完当前的sql后。Shut

9、down mount:Shutdown about:强行关闭。（有丢失数据的危险，当如火灾发生、地震的时候才使用）Sqlplus的所有的命令：SQL> help indexEnter Help topic for help. COPY PAUSE SHUTDOWN DEFINE PRINT SPOOL / （执行缓冲区） DEL PROMPT SQLPLUS ACCEPT DESCRIBE（desc） QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE R

10、EPFOOTER TIMING BREAK EXIT REPHEADER TTITLE BTITLE GET RESERVED WORDS (SQL) UNDEFINE CHANGE （c） HELP RESERVED WORDS (PL/SQL) VARIABLE CLEAR HOST RUN WHENEVER OSERROR COLUMN（col） INPUT SAVE WHENEVER SQLERROR COMPUTE LIST SET CONNECT PASSWORD SHOWEDIT（默认记事本打开缓冲区，可以对缓冲区的sql编辑，然后保存关闭即可！）SQL> list 1*

11、select * from dept where deptno=20SQL> edit已写入文件 afiedt.buf 1* select * from deptSQL> col deptno heading "编号"SQL> / 编号 DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DESCRIBE（desc）:对于表，我们想查看列对应的字段我们用describe（desc缩写）报表：？几个相关命令Oracle数据

12、库的几种启动和关闭方式有以下几种启动方式：1、startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库 读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。 2、startup mount dbname 安装启动，这种方式启动下可执行： 数据库日志归档、 数据库介质恢复、 使数据文件联机或脱机， 重新定位数据文件、重做日志文件。 执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置， 但此时不对数据文件和日志文件进行校验检查。3、startup open dbname 先执行“nom

13、ount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件， 这种方式下可访问数据库中的数据。4、startup，等于以下三个命令 startup nomount alter database mount alter database open5、startup restrict 约束方式启动 这种方式能够启动数据库，但只允许具有一定特权的用户访问 非特权用户访问时，会出现以下提示： ERROR： ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用6、startup force 强制启动方式 当不能关闭数据库时，可以

14、用startup force来完成数据库的关闭 先关闭数据库，再执行正常启动数据库命令7、startup pfile=参数文件名 带初始化参数文件的启动方式 先读取参数文件，再按参数文件中的设置启动数据库 例：startup pfile=E:Oracleadminoradbpfileinit.ora8、startup EXCLUSIVE=有三种启动方式：1、shutdown normal 正常方式关闭数据库。2、shutdown immediate 立即方式关闭数据库。 在SVRMGRL中执行shutdown immediate，数据库并不立即关闭， 而是在Oracle执行某些清除工作后才关闭

15、（终止会话、释放会话资源）， 当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。3、shutdown abort 直接关闭数据库，正在访问数据库的会话会被突然终止， 如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。三 SQL3.1 三类语言：数据定义DDL：create、alter、drop。数据控制语言DCL：grant、revoke。数据操作语言DML：select、insert、delete、update。演示：-SQL> create table abc(a varchar2(

16、10),b char(10);表已创建。SQL> alter table abc add c number;表已更改。SQL> alter table abc drop c;alter table abc drop c *ERROR 位于第 1 行:ORA-00905: 缺少关键字SQL> alter table abc drop column c;表已更改。-3.2 oracle常用系统函数3.2.1 字符3.2常用系统函数3.2.1 字符length,ltrim,replace,rtrim,substr,trim演示：select length('abcdef&#

17、39;) from dual;查询字符数 如本语句结果是6select length('abc好def') from dual;7select lengthb('abc好def') from dual;查询字节数，为8；select ltrim(' abc好def') from dual; 去除左边空格select rtrim('abc好ef ')from dual; 去除左边右边空格select trim (' abc ')from dual; 去除两边的空格select length(trim (' a

18、bc ')from dual; 3select substr('abcdefg',2,3) from dual;从第二个开始取串取三个即得 bcdselect substr('abcdefg',1,3) from dual;取开始3个select substr('abcdefg',length('abcdefg')- 3 + 1,3) from dual;取末尾3个alter session set NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss' 设定时间格式select s

19、ysdate from dual; 结果：2009-6-17 11:32:313.2.2 日期sysdate，curtent_date,next_dayselect sysdate from dual; 2009-6-17 11:40:49select current_date from dual;2009-6-17 11:41:02 alter session set nls_date_format='dd-mon-yyyy hh:mi:ss'当前时间2009-6-17 11:41:02 星期三 select next_day(sysdate,'星期四') f

20、rom dual; 下个星期四 2009-6-18 11:43:24select next_day(sysdate,'星期三') from dual; 下个星期三:2009-6-24 11:43:493.2.3 转换to_char,to_date,to_number select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 转换成字符2009-06-17 11:48:14select length(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from

21、 dual; 19select to_date('2009-06-17 11:48:14','yyyy-mm-dd hh24:mi:ss') from dual;将字符串转换成日期2009-6-17 11:48:14select to_number('123') from dual;将字符串转换成数字(多大溢出？)3.2.4聚集函数sum,avg,max,min,count select max(price) from book; 求最大值select min(price) from book; 求最小值select avg(price) fro

22、m book; 求平均值select count(price) from book; select count(*) from book;查询有几条记录 求数据总个数select sum(price) from book; 求总数select * from book where price > 20; 3.2.5其他user,decode,nvlselect user from abc; 查看用户select sum(decode(sex,'男'，1，0) 男人数,sum(decode(sex,'女',1,0)女人数 from e; sum(decode(

23、sex,'男'，1，0)sex字段是男加1，否则加0。select a1,nvl(a2,'未输入')a2 from aa; 在null的地方写上'未输入'select * from aa where a2 is null; 为空select * from aa where a2 is not null; 不为空select * from aa order by a1 asc; 升序select * from aa order by a1 desc; 降序select distinct a1 from aa;没个a1 只显示一个，不重复。selec

24、t all a1 frm aa;3.3 分组语句用户汇总一些数据的时候：求合计select sum(price*qty) from books; qty数量列按出版社（pub）分组查询的时候：select sum(price*qty) from books group by pub; 或select pub, sum(price*qty) from books group by pub; 注意：select显示的列一定要在group by 中，否则错误！select pub,book_name, sum(price*qty) from books group by pub; 正确的语句应该是：

25、select pub,book_name, sum(price*qty) from books group by pub，book_name; 语法上必须要求这样做。group by可以多个，但是必须与前面的select中得到允许。可以增加where条件：select pub, sum(price*qty) from books where price>30 group by pub; 如果这样写：select pub, sum(price*qty) from books where sum(price*qty)>30 group by pub 就要报错了！为什么呢？因为聚集函数不

26、能作为条件出现在where子句后面！要实现类似的功能，我们使用having子句去改造，group by要出现在having子句前面。select pub, sum(price*qty) from books group by pub having sum(price*qty)>30;除此以外，我们很少使用having子句。示例：select pub,count(pub) from book group by pub having count(pub)>1;select ftype from tcmo group by ftype having count(moid)>10;

27、3.4 表的连接内连接：又叫等值夺标查询？ 将按照等值的条件查询多个数据表中关联的数据。要求关联的多个数据表的某些字段具有相同的属性，即有相同的数据类型、宽度和取值范围。正规的写法select * from tcmo a join tcmotype b on a.ftype=b.fmotype；select * from tcmo a , tcmotype b where a.ftype=b.fmotype;外连接有两种：左（外）连接和右（外）连接左连接:正规的写法select * from tcmo a left join tcmotype b on a.ftype=b.fmotype;se

28、lect * from tcmo a , tcmotype b where a.ftype=b.fmotype(+);右连接：正规的写法select * from tcmo a right join tcmotype b on a.ftype=b.fmotype;select * from tcmo a , tcmotype b where a.ftype(+)=b.fmotype;还可以在查询中添加其他条件：两种写法有一些差别select * from tcmo a right join tcmotype b on a.ftype=b.fmotype where a.ftype='L

29、INUX'select * from tcmo a , tcmotype b where a.ftype(+)=b.fmotype and a.ftype='LINUX'3.5 子查询IN、EXISTS相关子查询无关子查询有的时候，子查询与连接查询都能得到相同的结果：谁的效率更高呢？3.6 where与havingwhere检查每条记录是否符合条件，having是检查分组后的各族是否满足条件。Having语句只能配合group by语句使用，没有group by时不能使用having，单可以使用where。3.7 嵌套更新语法：update 数据表set 字段名1=(s

30、elect 字段列 from 数据表 where 条件),字段名2=(select 字段列 from 数据表 where 条件),Where 更新条件;四 PL/SQLPL/SQL是Oracle对sql的一种扩展语言！命令行下要显示输出行：set serveroutput on size 10000 (最大可设置一万)！4.1 第一个例子declare i number :=1;x varchar2(20):='初值'begin dbms_output.put_line('打印一行.初值i='|i); dbms_output.put_line(x); i:=10

31、; x:='this is .' -字符串连接|。行注释- -快注释/* 注释内容*/ dbms_output.put_line('打印一行.i='|i); dbms_output.put_line(x);end; 4.2 分支语句4.2.1 if分支declare i number ;str varchar2(20);begin i:=2; if i=1 then str:='sno001' elsif i=2 then str:='sno002' else str:='else' end if; dbms_ou

32、tput.put_line(str);end; 4.2.3 case分子declare i number ;str varchar2(20);begin i:=2; case when i=1 then str:='sno001' when i=2 then str:='sno002' else str:='else' end case; dbms_output.put_line(str);end; 4.3 循环语句4.3.1 基本循环（loop）示例 1：declare i number:=0;begin loop i:=i+1; if(i&g

33、t;=100)then exit; end if; dbms_output.put_line('loop内i='|i); end loop; dbms_output.put_line('loop外i='|i);end;示例 2declare i number:=0;begin loop i:=i+1; exit when i>=3 ; dbms_output.put_line('loop内i='|i); end loop; dbms_output.put_line('loop外i='|i);end;4.3.2 while循环

34、declare i number:=0;begin while i<=3 loop i:=i+1; dbms_output.put_line('loop内i='|i); end loop; dbms_output.put_line('loop外i='|i);end;4.3.3 for循环在oracle的for循环中无论“从小到大循环”还是“从大到小的循环”，我们对应的地增量不由我们控制，oracle的for循环的递增量只能是1。但是我们可以使用goto进行跳转，需要一个标记。Oracle支持goto。实例3将给我们演示。示例 1 ：从小到大begin fo

35、r i in 1.5 loop dbms_output.put_line('i='|i); end loop; dbms_output.put_line('end of for loop.');end;实例 2 ：从大到小，我们在in后面加上一个reverse。begin for i in reverse 1.5 loop dbms_output.put_line('i='|i); end loop; dbms_output.put_line('end of for loop.');end;实例 3 ：用goto语句实现循环dec

36、lare i number:=0;begin -<<标记>> <<repeat_loop>> i:=i+1; dbms_output.put_line('i='|i); if(i<=3)then goto repeat_loop; end if;end;4.3 异常处理在PL/SQL中的一个告警或错误的情形都被称为异常。包括：编译时刻错误或运行时刻错误。所以异常分类为：系统异常；自定义异常。常见系统异常：预定义异常描述DUP_VAL_ON_INDEX向有唯一约束的表中插入重复行NO_DATA_FOUND在一个select i

37、nto语句中无返回值TOO_MANY_ROWSSelect into 语句返回了多行VALUE_ERROR一个算法、转换、截断或大小约束发生错误ZERO_DIVIDE发生被零除4.3.1 一个系统异常的写法:declare test varchar2(20);begin select dname into test from dept where deptno=90; dbms_output.put_line(test); exception when no_data_found then dbms_output.put_line('没有找到数据');end;4.3.2 自定义

38、异常4.4 复合变量：记录复合变量（记录）是由几个相关值构成的复合变量，常用语支持select语句的返回值。使用记录可以将一行数据看成一个单元进行处理，而不必将每一列单独处理。记录的声明格式：TYPE type_name IS RECORD( Variable_name datatype, Variable_name datatype, .);示例 1：declare-定义复合变量（记录） TYPE myrecord IS RECORD( id number, -最后一个变量没有逗号 name varchar2(20) ); -声明一个新类型的变量o o myrecord;begin-注意se

39、lect into ： select deptno,dname into o from dept where deptno=10; dbms_output.put_line('部门号：'|o.id|',部门名：'|); exception when no_data_found then dbms_output.put_line('没有找到数据');end;实例 2 ： 记录中的简单变量与表的字段的定义的类型和长度一致declare-定义复合变量（记录） TYPE myrecord IS RECORD( id number, -最后一个

40、变量没有逗号 -指定name是和dept.dname一样长度的声明的变量 name dept.dname%type ); -声明一个新类型的变量o o myrecord;begin-注意select into ： select deptno,dname into o from dept where deptno=10; dbms_output.put_line('部门号：'|o.id|',部门名：'|); exception when no_data_found then dbms_output.put_line('没有找到数据');

41、end;实例 3 ：记录与表的所有字段对应declare myrec dept%ROWTYPE;begin-注意select into ： select * into myrec from dept where deptno=10; dbms_output.put_line('部门号：'|myrec.deptno|',部门名：'|myrec.dname); exception when no_data_found then dbms_output.put_line('没有找到数据');end;五 PL/SQL高级应用5.1 游标游标是oracle

42、中一种PL/SQL控制结构；可以对SQL语句的处理进行显式控制，便于对表的行数据逐条进行处理。游标不是一个数据库对象，而是以声明的方式在内存中开辟的一个区域。游标可被看着两个环节的内容，由两个方面组成的：一个是游标的结果集（如sql的select语句查询得到的表的结果集）。第二个就是这个结果中对应的数据的位置。游标分类：显式游标；隐式游标。游标的属性：6MI%FOUND%ISOPEN%NOTFOUND%ROWCOUNT重点经验：当表中的数据量很大的时候，我们不建议使用游标。为什么呢？因为它的执行效率不是很高，很耗资源。但是在有的情况下，比如逐行取数据又是很灵活的。5.1.1 显式游标示例 1：

43、- 显示游标declare - cursor mycursor is select * from dept; -游标的数据取来放入一个记录的变量中。 myrecord dept%rowtype;begin - 将显示游标 结果集打开 open mycursor; -然后，把打开的游标的结果集的一条（这里是第一条）放入我们申明的变量myrecord。 fetch mycursor into myrecord; while mycursor%found loop dbms_output.put_line(myrecord.dname|','|myrecord.deptno); -继

44、续取，指导取完 fetch mycursor into myrecord; end loop; -取到最近偶的时间我们要把游标关闭。 close mycursor; end;实例 2 ：游标参数实现对数据的控制。给游标定义一个参数（在plsql中作为参数是不需要长度的，只给出类型就可以）declare cursor cur_param(id number) is select dname from dept d where d.deptno=id; t_name dept.dname%type; begin -打开游标并赋一个值 open cur_param(1); loop fetch cu

45、r_param into t_name; exit when cur_param%notfound ; dbms_output.put_line(t_name); end loop; close cur_param;end;For循环的另外一种格式：不需要打开和关闭游标的句子。declare cursor cur_param(id number) is select dname from dept d where d.deptno=id; begin for cur in cur_param(1) loop dbms_output.put_line(cur.dname|'.名称'

46、;); end loop;end;5.1.2 %isopen属性declared_name dept.dname%type;cursor cur(id number) isselect dname from dept where dept.deptno=id;begin if cur%isopen then dbms_output.put_line('游标已经打开'); else open cur(1); end if; fetch cur into d_name; close cur; dbms_output.put_line(d_name);end;5.1.3 %rowco

47、unt属性返回至今为止，已经从游标中取回的数目。declaret_name varchar2(10);cursor mycur isselect dname from dept;beginopen mycur;loop-下面的fecth mycur into t_name语句被注释掉，将是什么样的结果？没有做fetch的时候 mycur%notfound is null将执行fetch mycur into t_name;exit when mycur%notfound or mycur%notfound is null;dbms_output.put_line('游标mycur的ro

48、wcount是：'|mycur%rowcount);end loop;close mycur;end;5.1.4 使用游标修改数据declaret_name varchar2(10);cursor mycur isselect dname from dept for update;beginopen mycur;loopfetch mycur into t_name;exit when mycur%notfound or mycur%notfound is null;-游标的当前行:current of mycurupdate dept set dname=dname|'_up

49、date' where current of mycur;end loop;close mycur;end;注意：update dept set dname=dname|'_update',loc=loc|'_u' where current of mycur;放在上面的pl/sql中，将报错“字符串缓冲区太小”！游标只指向的是列dname。5.1.5 隐式游标PL/SQL为所有SQL数据操作语句（包括返回一行的SELECT）隐式声明游标，称为隐式声明游标的原因是用户不能直接命名和控制此类游标。当用户在PL/SQL中使用数据库操作语言（DML）时，Ora

50、cle预定义一个名为SQL的隐式游标，通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。在执行DML语句后，隐式游标属性返回信息。对于隐式游标而言，属性%ISOPEN的值总是false，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。隐式游标：不需要我们declare、open、使用完了不需要我们close。begin for cur in(select dname from dept) loop dbms_output.put_line(cur.dname);end loop;end;当表中的数据量很大的时候，我们不建议使用游标。为什么呢？因为它的执行效率不是很高，很

51、耗资源。但是在有的情况下，比如逐行取数据又是很灵活的。5.2 存储过程创建语法 5.2.1 一个简单的存储过程IN：输入参数create or replace procedure pro1(id in number)-is或as都可以is-参数name varchar2(20);beginselect dname into name from dept where deptno=id;dbms_output.put_line(name);exception when TOO_MANY_ROWS then dbms_output.put_line('Select into 语句返回了多行

52、');when no_data_found then dbms_output.put_line('没有找到数据');-结束使用：end pro1也可以end;上面sql块执行成功就会创建存储过程。如何调用这个存储过程呢？如下所示declare var number;beginvar:=2;pro1(var);end;beginfor var in 1.10looppro1(var);end loop;end;beginpro1(40);end;Sql plus下还可以：execute pro1(40);5.2.2 输出参数如何使用Oracle定时作业队列当数据表中数据量很大的时候，我们进行查询速度会很慢，特别是上百万数据做报表的时候体会就更深了，为此，我们采用定时执行存储过程，提取我们需要的数据。写到这里的时候大家可能会有些疑问，这样我们的数据不就不能实时最新了吗？你的疑问是正确的，我们为什么提出这种解决方案呢，原因在于我们的数据是生产数据