Oracle详解

数据库基础

1. 起源：人工管理、文件系统、数据库系统
2. 主要特征：结构化、数据共享、减少冗余、独立性。
3. 相关概念：DB、DBS、DBA、DBMS
4. 关系型数据库
   • 理论基础：集合论+关系代数
   • 用二维表组织数据：行、列、主键、外键、约束、索引、……
   • 支持SQL(结构化查询语言)：DDL、DML、DQL、DCL，以集合的方式处理数据，本身不区分大小写，但是对字符值进行处理时是区分大小写的
   • E-R图：1-1、1-N、N-M
   • 范式理论：1NF、2NF、3NF、BCNF、……

概述

发展史

• 1970年 IBM的Codd博士发表A Relational Model of Data for Large Shared Data Banks
• 1978年 Oracle 1 汇编语言 PDP-11
• 1983年 Oracle 3 C语言
• 1993年 Oracle 7 UNIX系统
• 1997年 Oracle 8 Java
• 1999年 Oracle 8i Internet
• 2001年 Oracle 9i 支持RAC(Real Application Clusters) / BI
• 2003年 Oracle 10g 网格计算
• 2007年 Oracle 11g
• 2013年 Oracle 12c 云计算

网格计算和云计算

网格计算：使用公用设施进行计算，不必关心数据的位置以及由哪台计算机进行计算，就像使用电网一样，不需要知道发电机的位置以及电网的连接方式。网格计算改变了企业使用资源的方式，使得数据中心等系统可以跨服务器使用资源。
云计算：一种按使用量付费的模式，这种模式提供可用的、便捷的、按需的网络访问，进入可配置的计算资源共享池(资源包括网络、服务器、存储、应用软件、服务等)，这些资源能够被快速提供，只需投入很少的管理工作或与服务供应商进行很少的交互。

安装和启动

1. Windows环境安装非常简单；Linux环境需要先检查内存、磁盘、操作系统版本、依赖包安装情况、编译器版本等，然后要配置内核参数、创建用户和用户组并添加用户的限制参数，此外还要创建安装目录、环境变量并将安装用的压缩包解压之后才能开始安装。
2. 可以使用DBCA(Database Configuration Assistant来)来创建数据库实例。
3. Windows系统可以在“服务”中启动和关闭Oracle的监听和实例，也可以通过命令的方式来启动监听(lsnrctl)和实例；Linux系统可以在SQL*Plus下使用startup和shutdown命令来启动和关闭实例。

体系结构

实例：非固定的基于内存的基本进程。
数据库：固定的、基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等。

一般情况下，Oralce数据库都是一个数据库包含一个实例。

在Oracle系统中，表空间和数据文件是Oracle数据库结构的基本要素。

表空间：存放数据库表、索引、回滚段等对象的逻辑磁盘空间。

• SYSTEM表空间：存放数据字典
• SYSAUX表空间：
• UNDO表空间：存储撤销信息
• USERS表空间：存储用户创建的对象
• TEMP表空间：
• EXAMPLE表空间

数据文件：每个表空间可以包含一个或多个数据文件，可以在创建表空间时创建一个或多个数据文件，也可以对已经存在的表空间追加新的数据文件。

select file_name, tablespace_name, bytes from dba_data_files;

控制文件：存储Oracle实例信息、数据文件信息、日志文件信息的内部二进制文件，控制文件是Oracle实例启动时必须访问的文件。

select name from v$controlfile;

日志文件：记录数据库所有发生的事务以及由Oracle内部行为引起的数据库变化的信息，在数据库恢复时，可以从日志文件中读出原来的数据。在创建表空间是可以使用nologging选项指定不产生日志。

select member from v$logfile;

常用操作

数据类型

创建表

create table tb_goods
(
gid number(8),
gname varchar2(50) not null,
gdesc varchar2(500),
gcurprice number(10, 2) not null,
) tablespace user;

删除表

drop table tb_goods;

修改表

alter table tb_goods add constraint pk_gid primary key (gid);
alter table tb_goods add column goldprice number(10, 2) not null;

约束：约束通常也称为完整性约束（实体完整性、参照完整性和域完整性），主要通过主键、外键、检查、非空、唯一、默认值等来实现。

序列：生成唯一的整数，通常用于自增主键。

create sequence seq_gid start with 10000000 increment by 1;

select seq_gid.nextval from dual;

drop sequence seq_gid;

视图：视图通常被认为是“存储的查询”或“虚拟的表“，它本身并不包含数据而是一个到表的映射，视图的作用主要是简化查询要使用的语句，同时也可以将操作限制到指定的列，起到安全和保密的作用。

create or replace view v_emp_dept as 
select ename, dname, job, sal from scott.emp t1, scott.dept t2
where t1.deptno=t2.deptno;

drop view v_emp_dept;

同义词：同义词是数据库对象的别名，可以用来保护数据库对象的隐私。

create public synonym emp for scott.emp;
drop synonym emp;

索引：索引是关系数据库中用于存放表中每条记录位置的一种对象，其主要作用是加快数据检索速度，其设计的合理性对系统性能有直接的影响。

create unique index uni_idx_goods on tb_goods (gid);
create index idx_goods_name on tb_goods (gname) cluster;

创建函数

create or replace function fn_factorial (n number) 
return number is
i number := 1;
result number := 1;
begin
for i in 1..n loop
    result := result * i;
end loop;
return result;
end fn_factorial;

创建存储过程

create or replace procedure sp_get_avg_sal_by_dept_no(
dno dept.deptno%type, avgSal out emp.sal%type) as
begin
  select avg(sal) into avgSal from emp where deptno=dno;
end sp_get_avg_sal_by_dept_no;

创建触发器

create or replace trigger tr_update_deptno
after update on dept
for each row
begin
  update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

创建用户并授权

create user luohao identified by 123123 password expire;
grant create session to luohao with admin option;

PL/SQL

declare

begin

exception

end

declare
    type t_table_of_num is table of emp.empno%type index by binary_integer;
    v_empno_array t_table_of_num;
begin
    v_empno_array(0) := 1234;
    v_empno_array(1) := 4321;
end; 

declare
    type t_record_stu is record (
        stuid number(4),
        stuname varchar2(20),
        stubirth date
    );
    v_stu t_record_stu;
begin
    v_stu.stuid := 1001;
    v_stu.stuname := 'Hao';
    v_stu.stubirth := to_date('1980-11-28', 'yyyy-mm-dd');
end;

declare 
    cursor c is select * from emp;
    v_emp c%rowtype;
begin
    open c;
    loop
        fetch c into v_emp;
        exit when (c%notfound);
        ... ...
    end loop;
    close c;

declare 
    cursor c is select * from emp;
begin
    for v_emp in c loop
        ... ...
    end loop;
end;

declare 
    cursor c is select * from emp for update;
begin
    delete ... where current of c;
    update ... where current of c;
end;

declare
    type t_cur_emp is ref cursor return emp%rowtype;
    v_emp_c t_cur_emp;
begin
    if not v_emp_c%isopen then
        open v_emp_c for select * from emp;
    end if;
    ... ...
end;

注意：

1. select语句必须且只能返回一条记录而且要使用into存入对应的变量中
2. sql%rowcount - 受影响的行数
3. execute immediate
4. too_many_rows / no_data_found / login_denied / dup_val_index / zero_divide / value_error / case_not_found
5. SQLCODE / SQLERRM