Oracle快速入门手册.doc

第一章 Oracle入门1.1 安装1.2 系统服务图 1-1 Oracle数据库系统服务1.3 数据库和实例1.3.1 数据库数据库是磁盘上存储的数据集合。每个数据库都有自己的名字，数据库名是用于区分数据库的一个内部标识，是以二进制方式存储在数据库控制文件中的参数。数据库创建之后不能再修改这个参数。它被写入数据库参数文件pfile或Spfile中。1.3.2 数据库实例实例是一组后台进程和共享内存。数据库实例是操作数据库的实体，用户通过实例与数据库交互。每个数据库实例都有自己的名字，实例名用来标识这个数据库实例。数据库创建后，实例名可以被修改。它也被写入数据库参数文件pfile或Spfile中。1.3.3 两者关系数据库是磁盘上存储的数据集合。数据库可以由一个或多个实例（使用RAC）装载和打开。实例是一组后台进程和共享内存。实例“一生”只能装载并打开一个数据库。数据库名与实例名可以相同。一个数据库对应一个实例的情况下设置成相同的便于标识数据库。但是在8i,9i的并行服务器中，数据库与实例不存在一一对应关系，而是一对多关系，一个数据库对应多个实例。不过一个用户只能与一个实例相连。1.3.4 数据库物理结构图 1-2 Oracle数据库的物理结构 参数文件数据库参数文件并不是数据库文件系统中的有效组成部分，因为在启动数据库的时候，参数文件并不直接参与工作。但是数据库参数文件中，记录着数据库控制文件的物理地址，所以要靠它来寻找控制文件。图 1-3 数据库参数文件 文件系统图1-4 Oracle数据库的文件系统*.CTL表示控制文件*.DBF表示数据文件*.LOG表示日志文件控制文件用来管理和控制数据文件和日志文件。在启动数据库的时候，启动实例之后，就启动对应的控制文件，接着由控制文件打开数据文件。database amount就是打开控制文件，database open是打开数据文件。在Oracle中，有两种类型的日志文件：图1-4中的REDO*.LOG称为联机日志文件，也成为重做日志文件。如果REDO01.LOG写满则写REDO02.LOG，REDO02.LOG写满了写REDO03.LOG，而REDO03.LOG写满了又会重新写入REDO01.LOG，这是联机日志文件中的非归档方式。还有一种称为归档日志文件，是在备份尤其是热备份的时候，一定要选择的一种归档方式。1.4 内存结构当一个Oracle实例启动之后，它分配了一个称为SGA（系统全局区）的内存块图1-5 Oracle数据库内存结构1.4.1 缓冲区DB buffer（数据库高速缓冲区）如果每次执行sql语句，都要对磁盘数据进行读写，则效率会非常慢。内存中被用来频繁读取数据的部分就称为数据库高速缓冲区。DB buffer是SGA中最大的部分。它又分为以下三个部分：保持缓存池再生缓存池默认缓存池共享池1.4.2 Oracle相关进程1.5 逻辑结构1.5.1 表空间1.5.2 段1.5.3 盘区1.5.4 数据块第二章 SQL/PLUS基础1.1 登录账户用sys/system账户，以sysdba的方式登录，可以解锁其他账户，如：alter user scott account unlock;alter user scott identified by aaaaa;conn scott/aaaaa;注意：密码是纯数字，如123456是不对的1.2 帮助SQLhelp indexSQL? SET1.3 替代变量和执行计划A:SQLselect * from dept where deptno=10;SQLselect * from dept where deptno=20;B:SQLselect * from dept where deptno=&tt;SQL10SQLselect * from dept where deptno=&tt;SQL20A和B得到的结果一样，但A中启动两个执行计划，B中只有一个给用户授权： Grant connet to aaa;第三章 SQL语言基础1.3.1语言的分类DDL：数据定语言 Create 实例：create table abc(a varchar2(10),b char(10);创建表alter table ChinaCity modify CitySimple null; 修改表字段为空grod table ChinaCity删除表Alter 实例：alter table abc add c number;添加表字段Drop 实例：drop table abc;删除表，alter table abc drop column c;删除表中的某一字段DCL:数据控制语言 Grant 实例：grant select on dept to tt;授权给tt用户有查询的权限 Revoke 实例：revoke select on dept from tt;收回tt用户的查询权限DML:数据操纵语言 Select 实例：select * from abc;查询abc表的值 Insert 实例：insert into abc(a,b) values(abc,xy);为abc表赋值 Update 实例：update abc set b=ttt;修改abc表中所有的b改为ttt update abc set b=yyy where a=abc;把abc表中a为abc的b改为yyy Delete 实例：delete from abc;把表里面的所有数据都情空 delete from abc where a=abc;把表里面a为abc的删除1.3.2常用的系统函数字符：length查字符，lengthb差字节，trim，ltrim和rtrim截掉空格和左右两边空格，substr（abcdefj,2,3）截取字符串，从第二个开始取三个，substr(abcdefg,length(abcdefg)-3+1,3) 答案efg可变长（varchar2(10)）的存多少字符长度就为多少，对于不可变长的(char（10）)，没有存满也是原本定的长度所以为了避免不必要的浪费，一般定义为可变长日期： sysdate当前时间，current_date查询当前时间，alter session set nls_date_format=dd-mon-yyyy hh:mi:ss;设定时间的格式next_day指定的某个星期几是几号select next_day(sysdate,星期三) from dual;转换： To_char(sysdate,yyyy-mm-dd hh24:mi:ss)把日期型传唤出字符型,24小时制 To_date(12-3月-04)把字符型转换成日期型 To_number(333)字符型转换成整型聚集函数： Sum()总数，max()最大值，min()最小值，avg()平均值，count()总记录数其他： select user from dual;查询当前的登录帐号 select sum(decode(sex,男,1,0),sum(decode(sex,女,1,0) from 表;统计男为几个，女为几个 select a1,nvl(a2,为输入) a2 from 表;如果为空值就表面为输入 select * from 表面 order by a1 asc;升序排列 select * from 表面 order by a1 desc;降序排列 select distinct a1 from 表面;去除重复数据分组语句： select pub, sum(price) from books group by pub;使用group by分组查询各出版社的图书价格 select pub,sum(price) from books group by pub having sum(price)50;group by结合having查询总金额大于50的出版社名称 select a1,count(a1) from aa group by a1 having count(a1)1;查询a1字段重复出现的次数模糊查询： select * from aa where a1 like a_;使用通配符（like）查询a1字段中以a开头，任意多个字符结尾的数据 select * from aa where a1 like %a%;查询表中a1有a字符的数据表连接： select e.eid 编号, 姓名,e.sex 性别, 所在部门 from e inner join d on e.id=d.id;内链接select e.eid 编号, 姓名,e.sex 性别, 所在部门 from e,d where e.id=d.id(+);左链接select e.eid 编号, 姓名,e.sex 性别, 所在部门 from e,d where e.id(+)=d.id;右链接子查询：（无关子查询，相关子查询） select * from e where id in (select id from d where id=e.id and id=03);select * from e where id not in (select id from d where id=e.id and id=03); select * from e where exists (select id from d);判断是否存在结果集 select * from e where not exists (select id from d);判断是否不存在结果集 select eid,name from e union select id,name from d;把两张表合并成一张，去除重复数据 select id from e intersect select id from d；返回两个sql语句中都出现的行 insert into e(eid,ename) select id,name from d;一次从别的表中插入多条记录create table ttt as (select * from e);创建表是复制别的表中的数据进入新表create table t as select eid,ename from e where eid=001;选择001的数据复制到新的表里面第四章 PL/SQL基础PL/SQL的结构：Declare.-声明变量，赋予初值，可选项Begin -具体的操作Exception -声明，实现异常处理部分end;/实例：dclarex varchar2(10);y integer:=123;z string(10):=123;begin x:=this is;dbms_output.put_line(x的值为：|x);-|表示连接字符，dbms_output是一个包，实现与外部的交互，put_line打印输出；end;/set serveroutput on size 10000;-设置显示输出信息，默认的是不输出，设置字节大小为10000；量声明：变量声明的内容：赋予变量适当的名称，适当的数据类型，定义变量（标准变量，复合变量），控制变量范围命名规则：变量由字符开头，可以包含数字，下划线，$,#等，变量长度范围130，大小写不区分，变量名不能是系统关键字存储：save c:plsql_01.text;执行： c:plsql_01.text;修改：edit c:plsql_01.text;dbms_output. new_line-表示在新行里面打印输出；和dbms_output. put一起使用，也就是dbms_output.put_line分支语句：1、 if分支 语法：if then.elsif.then.elseend if 实例：declare a number;b varchar2(10);begina :=2;If a=1 thenb :=a;elsif a=2 thenb :=B;elseb :=c;end if;dbms_output.put_line(b的值是：|b);-输出end;/2、 case分支 语法：casewhenthenelseend case 实例：declare a number;b varchar2(10);begina :=2;casewhen a=1 then b:=A;when a=2 then b:=B;when a=3 then b:=C;elseb:=abc;end case;dbms_output.put_line(b的值是：|b);-输出end;/循环语句：1、 基本循环（loop）语法：LoopEnd loop实例：DeclareX number;BeginX:=0;LoopX:=x+1;If x=3 thenExit;End if;Dbms_output.put_line(内：x=|X);End loop;Dbms_output.put_line(外：x=|X);End;/2、 while循环语法：while expression loopEnd loop; 实例： Declare X number; Begin X:=0; While x=3 loop X:=X+1;Dbms_output.put_line(内：x=|X);End loop;Dbms_output.put_line(外：x=|X);End;/3、 for循环语法：for counter in reverse start_value-起始end_value Loop-结束End loop; 实例：BeginFor i IN 1.5 loop-希望由大到小，在IN后面加上 REVERSEDBMS_output.put_line(i=|i);End loop;DBMS_OUTPUT.PUT_LINE(end of for loop);End;/4、 goto语句实现循环：实例：DeclareX number;BeginX:=0;-设置标记X:=x+1;Dbms_output.put_line(x);If x3 thenGoto repeat_loop;-如果x小于3就goto到之前的标记位置End if;End;/异常处理：异常分类 系统异常 DUP_VAL_ON_INDEX 向有唯一约束的表中插入重复行 NO_DATE_FOUND 在一个select into语句中无返回值 TOO_MANY_ROWS select into 语句返回了多行 VALUE_ERROR 一个算法，转换截断或大小约束发生错误 ZERO_DIVIDE 发生呗零除 自定义异常异常结构 Exception When . Then .复合变量：记录记录的声明： Type type_name is record( Variable_name datatype, Variable_name datatype, . ); Real_name type_name;实例： Declare Type myrecord IS record( Id varchar2(10); -id emp.eid%type;表示id变量的长度与emp表的id长度相同，也可以整张表的字段长度与某 某表相同 Name varchar2(10); Real_record myrecord; Begin Select emp_id,emp_name into real_record from emp where emo_id=001; Dbms_output.put_line(real_record.id|,|real_); End; /第五章 PL/SQL高级应用一、游标(执行效率不高，消耗资源严重) 1.1游标的概念：游标一种PL/SQL控制结构，可以对sql语句的处理进行显示控制，便于对表的行数据逐条进行处理。1.2游标的分类 显示，隐式 1.3游标的属性 %FOUND, -boolean型的判断有数据可取%ISOPEN, -查看游标是否打开，如：if cur%isopen then%NOTFOUND, -boolean型的判断没有数据可取,和found相反%ROWCOUNT-用来返回迄今为止已经从游标中取出的数据数目 显示游标实例： Declare Cursor mycur is -创建游标 Select * from books; Myrecord books%rowtype; -声明变量，与books表的类型相同 Begin Open mycur; Fetch mycur into myrecord; -把游标mycur里的数据放到变量myrecord里面，首先取的是第一条 While mycur%found loop -循环去游标里面的数据 Dbms_output.put_line(myrecord.books_id|,|myrecord.books_name); Fetch mycur into myrecord; End loop; Close mycur; End; /Oracle中游标可以带参数 实例： Declare Cursor cur_para(id varchar2) is -常见带参数的游标 Select books_name from books where books_id=id; t_name books.books_name%type; -声明变量 Begin Open cur_para(001); -传入参数 Loop -进入循环 Fetch cur_para into t_name; -把游标的内容放入到变量里面 Exit when cur_para%notfound; -游标中没有数据可取的时候退出 Dbms_output.put_line(t_name); End loop; Close cur_para; End; /以for循环的形式：不需要open和closeDeclareCursor cur_para(id varchar2) isSelect books_name from books where books_id=id;BeginDbms_output.put_line(结果集为);For cur in cur_para(0001) loop;Dbms_output.put_line(cur.books_name);End loop；End;/ROWCOUNT的使用方法： Declare t_name varchar2(10); cursor mycur is select name from deptment; begin open mycur; loop fetch mycur into t_name; exit when mycur%notfound or mycur%notfound is null; dbms_output.put_line(游标mycur的rowcount是：|mycur%rowcount); end loop; close mycur; end; /利用游标修改数据: Declare Cursor cur is -=-创建游标 Select name from deptment for update;-加上for update选项才能利用游标修改数据 text varchar2(10); -定义变量 begin open cur; fetch cur into text; -把游标中的数据放入变量里面 while cur%found loop -当有数据可取的时候进入while循环 update deptment set name=name|_t where current of cur; - current of cur判断游标的当前行 fetch cur into text; end loop; close cur; end; /隐式游标的实例：BeginFor cur in(select name from deptment) loopDbms_output.put_line();End loop;End;/4.1.1 无参过程create or replace procedure proc_test1isbegindbms_output.put_line(systimestamp);end4.1.2 带输入参数create or replace procedure proc_test2(p_temp varchar2)isbegin insert into scott.test1 values(p_temp);end4.1.3 带输出参数create or replace procedure proc_test3(p_temp varchar2, p_count out number)isbegininsert into scott.test1 values(p_temp);select count(a) into p_count from scott.test1;endvar p_count numberexec proc_test3 (c,:p_count)print p_count4.1.4 参数的几种传递方式- 位置传递- 名称传递- 组合传递4.1.4 查看过程的源代码select text from user_source where name=PROC_TEST3;【注意】过程的名字一定要大写4.2 过程实例4.2.1 过程说明1、 过程实现的功能是：接收一个关键字，根据关键字查询各个品牌中，包含该关键字的包包数量2、 在Oracle过程中查询出来的东西都要放入变量(各种类型，如果是结果集则使用游标)3、 游标类型要临时定义，且必须在过程之前，这就需要将类型定义和过程放在同一个包中4.2.2 建包- 首先，创建包规范create or replace package mypkg istype mycursor is ref cursor;procedure proc_countBrandbagByKeyword (p_temp varchar2, cur_brandbagcountinfo out mycursor);end; 4.2.3 建过程- 其次，创建包体（主要是过程）create or replace package body mypkg is procedure proc_countBrandbagByKeyword (p_temp varchar2, cur_brandbagcountinfo out mycursor ) is begin open cur_brandbagcountinfo for select a.*, (select count(bagid) from scott.T_Bag where bagbrandid=a.brandid and bagname like %|p_temp|%) bagcount from scott.T_BagBrand a order by bagcount desc; end proc_countBrandbagByKeyword;end mypkg ;4.2.4 在Oracle中调用过程- 在匿名块中调用set serveroutput ondeclare type mycurtype is ref cursor; cur_countinfo mycurtype; v1 number(18); v2 varchar2(50); v3 varchar2(20); v4 number(5);begin c_countBrandbagByKeyword (11,cur_countinfo); loop fetch cur_countinfo into v1,v2,v3,v4; exit when cur_countinfo%notfound; dbms_output.put_line(品牌名称:|v2| 包包数量:|v4); end loop;end;4.2.5 用JAVA调用oracle过程一 无输出参数的过程表：TESTTB,里面两个字段（I_ID，I_NAME）。过程：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) ASBEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);END TESTA;java代码：import java.sql.*;public class TestProcedureOne public static void main(String args ) String driver = oracle.jdbc.driver.OracleDriver; String strUrl = jdbc:oracle:thin::1521: hyq ;-hyq是指哪个数据库 Connection conn = null; CallableStatement proc = null; try Class.forName(driver); conn = DriverManager.getConnection(strUrl, hyq , hyq ); proc = conn.prepareCall( call HYQ.TESTA(?,?) ); proc.setString(1, 100); proc.setString(2, TestOne); proc.execute(); catch (SQLException ex2) ex2.printStackTrace(); catch (Exception ex2) ex2.printStackTrace(); finally / . . 二 有输出参数的过程（非列表）存储过程为：CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) ASBEGIN SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1; END TESTB;在java里调用时就用下面的代码：package com.hyq.src;public class TestProcedureTWO public TestProcedureTWO() public static void main(String args ) String driver = oracle.jdbc.driver.OracleDriver; String strUrl = jdbc:oracle:thin::1521:hyq; Statement stmt = null; ResultSet rs = null; Connection conn = null; try Class.forName(driver); conn = DriverManager.getConnection(strUrl, hyq , hyq ); CallableStatement proc = null; proc = conn.prepareCall( call HYQ.TESTB(?,?) ); proc.setString(1, 100); proc.registerOutParameter(2, Types.VARCHAR); proc.execute(); String testPrint = proc.getString(2); System.out.println(=testPrint=is=+testPrint); catch (SQLException ex2) ex2.printStackTrace(); catch (Exception ex2) ex2.printStackTrace(); finally try if(rs != null) rs.close(); if(stmt!=null) stmt.close(); if(conn!=null) conn.close(); catch (SQLException ex1) 注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。三 输出参数为游标的过程（列表）由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，1，建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;2，建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。在java里调用时就用下面的代码：package com.hyq.src;import java.sql.*;import java.io.OutputStream;import java.io.Writer;import java.sql.PreparedStatement;import java.sql.ResultSet;import oracle.jdbc.driver.*;public class TestProcedureTHREE public TestProcedureTHREE() public static void main(String args ) String driver = oracle.jdbc.driver.OracleDriver; String strUrl = jdbc:oracle:thin::1521:hyq; Statement stmt = null; ResultSet rs = null; Connection conn = null; try Class.forName(driver); conn = DriverManager.getConnection(strUrl, hyq, hyq); CallableStatement proc = null; proc = conn.prepareCall( call hyq.testc(?) ); proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR); proc.execute(); rs = (ResultSet)proc.getObject(1); while(rs.next() System.out.println( + rs.getString(1) + +rs.getString(2)+); catch (SQLException ex2) ex2.printStackTrace(); catch (Exception ex2) ex2.printStackTrace(); finally try if(rs != null) rs.close(); if(stmt!=null) stmt.close(); if(conn!=null) conn.close(); catch (SQLException ex1) 在这里要注意，在执行前一定要先把oracle的驱动包放到class路径里，否则会报错的。四：函数 基本语法： Create or replace function function_name (argument1 mode1 datatype1,-参数argument2 mode2 datatype2, -参数.) -参数 Return datatype -返回值类型 Is|as Pl/sql block; -语句块4.1创建无参函数： Create or replace function countBag Return number Is N number; Begin Select count(*) into n from T_Bag; Return n; End;4.2调用函数： 方法一（sql语句中直接调用）： Select countBag from dual; -无参调用 - Select countBagByBrand(3) from dual; -有参调用 方法二（使用变量接受函数返回值）： Var n1 number Exec :n2:=countBag Print n1 - Var n1 number Exec :n2:= countBagByBrand(3) Print n1 方法三（使用包DBMS_OUTPUT调用函数）： Set serve