Oracle的发展

Oracle的发展历程

Oracle数据库的基本结构

Oracle数据库的逻辑结构

Oracle数据库的物理结构

数据文件

日志文件

控制文件

参数文件

Oracle的内存结构

Oracle的进程结构

数据库及管理

数据库操作

相关术语

• 数据库名

• 数据库实例名

  数据库名与实例名之间的关系

• 操作系统环境变量

• 数据库域名

• 全局数据库名

• 数据库服务名

数据库配置修改

• NET服务名
• TNS文件配置

创建数据库 - create database database_name

表空间的创建和管理

SQL语言

数据定义语言 - DDL

常见数据类型

• char
• varchar
• int 或者 integer
• smallint
• real \ float \ double
• numeric
• DateTime
• Text
• Image

create

create table Student(
	sno char(20) primary key,
	sname varchar(20) not null
);

-- Oracle中
    -- 复制表
    create table table_name as select * from table_name;
    -- 只复制表结构
    create table table_name as select * from table_name
        where 1=2;

primary key 用来指示表的关键字（主码）

也可以这样用：primary key(sno,cno)

not null 用以表现字段不能为空

创建视图

create view view_name as (select * from table_name);
-- 注意 as

创建约束

alter

• add

  alter table table_name add column_name 数据类型;
  -- 例如，给Student表添加字段 phone char(16)
  alter table Student add phone char(16);
  

• alter

  -- 修改表结构
  -- alter table 表名 alter column 字段名 字段类型
  alter table table_name alter column sdept varchar(1);
  

• drop

  -- 删除字段
  alter table table_name drop column column_name;
  

drop

drop table Student;

删除表顺序应当是：关联表 -- 基本表

删除约束

数据操纵语言 - DML

select

select column_name from table_name where 

select column_name from table_name table_name_temp where
-- table_name_temp为table_name的代替，此时不使用as说明
-- where条件中table_name由table_name_temp代替

rownum

select * from (select * from table_name) 
	where rownum<=number;
	-- 找出在结果select * from table_name中的前number列
	-- 只能使用 "<" or "<="

insert

insert into table_name() values ();
-- 也可省略成
insert into table_name values();

update

update table_name set colmun=() where 

delete

delete from table_name where 

数据控制语言 - DCL

grant

-- 给用户赋予连接权
grant connect to user_name;
    -- 具有connect权限的用户 == 具有权限
    -- alter table
    -- create table/index
    -- drop table/view/index
    -- grant
    -- revoke
    -- insert/select/update/delete
    -- audit/noaudit
    
-- 给用户分配建表权
grant create table to user_name;
	-- 此时用户不能成功建表，因为没有表空间操作权
alter user user_name quota size on table_space_name;
	-- table_space_name一般为users
	
-- 将select权限赋予一个角色
grant select on table_name to (角色名);
	-- 将select权限赋予所有人
grant select on table_name to public;

在给用户赋予连接权之前，创建用户:
create user user_name identified by password

如果使用某版本的 Oracle，用户名前需要带上 C##
即写为 C##user_name

注意 create table权限和 create any table权限的区别

revoke

-- 收回用户的建表权
revoke create table from user_name;

deny

常用SQL语句

通配符

• "%" 匹配任意字符串
  

• "_" 匹配任意一个字符
  

如果需要查询的条件中包括通配符，例如：查找名字中带有 "__"的数据

-- escape 规定转义字符为'\'
select sname from Student where sname like '%\_%' escape '\';

order by

• ASC - 升序
• DESC - 降序

group by

聚合函数

• avg

• sum

• min

• max

• count

  统计去重后的数量：count (distinct column_name)

having

用在 group by子句后面，针对聚合函数的结果值进行筛选

块插入

insert into Student() 
	select column_name from table_name where 
-- select子句的column与Student（）对应

连接

内连接

外连接

查看常量

-- Oracle
	-- Oracle中常量放在dual中
	select sysdate from dual; 
	
-- SQL Server
	select getdate();

用户输入

select * from table_name where column_name=&temp;
-- &temp为用户输入

PL/SQL

匿名块 - 测试程序

不能直接使用DDL

declare
	变量
	-- 变量赋值语句为 var_name:=constant
begin
	程序
	-- "<>" "!=" 不等于
	-- "||" 连接
	-- "and" 并且
	-- "or" 或者
exception
	when then
end;

if then - elsif then - else - end if;

if then
elsif then
else
end if;

loop - end loop

-- 循环
loop
	exit when -- 结束循环
end loop;

-- while
while () loop
end loop;

-- for
for var in [] loop
end loop;

常用函数

输出一行：dbms_output.put_line();

dbms_output.put_line('muhuai');

输出不带空行：dbms_output.put()

字节填充：rpad();

dbms_output.put_line(rpad('muhuai',number,char_c));
-- number为输出行的字符长度，char_c为填充字符

游标

显式游标

声明游标

​ 游标和查询关联

cursor cursor_name is select 

使用游标

-- 打开游标
open cursor_name;
close cursor_name;

-- 遍历游标
-- 需要一个游标类型的变量
var_cursor cursor_name%rowtype;
fetch cursor_name into var_cursor;
-- 此时游标将第一条数据放入var_cursor

open cursor_name;
	fetch cursor into var_cursor;
	while cursor%found loop
		-- 循环在游标便利结束后结束
	end loop;
close cursor_name;

select for update

隐式游标

无法open/close/loop/fetch

游标在commit操作后重置

sql%found -- 说明语句修改到行
sql%notfound -- 说明语句没有修改到行
sql%rowcount -- 语句修改到的行数

动态游标 - 游标变量 - 弱游标

游标的具体查询语句不在 declare 块中定义，在 declare 块中只定义游标名称，sql语句定义在 begin 块中

declare
	type ref_cursor_name is ref cursor; -- 定义变量
	var_ref_cursor ref_cursor_name; -- 动态游标
begin
	open var_ref_cursor for select ; -- 打开动态游标
	-- 此时游标为var_ref_cursor
	-- 遍历动态游标，方法同遍历游标
	close var_ref_cursor;
end;

存储过程

需要通过主程序调用（测试程序调用）

主程序调用之前，过程程序段需要运行保存一下

create or replace procedure procedure_name 
[(var_in in var_type,var_out out var_type)] is
	-- 变量定义
begin
	-- 程序
exception
end;

-- 测试程序调用
declare
begin
	procedure_name[(var_in,var_out)];
	-- 或者
	procedure_name[(var_1=>var_in,var_2=>var_out)];
end;

函数

需要通过主程序调用（测试程序调用）

主程序调用之前，过程程序段需要运行保存一下

create or replace function function_name
[(var_in in var_type)] [return return_type] is
begin
	-- 如果有 return，则一定要 return
end;

包

需要通过主程序调用（测试程序调用）

主程序调用之前，过程程序段需要运行保存一下

创建 -- 程序块 -- 包

包头和包体不在同一程序块中，但是保证包头名和包体名相同

-- 包头
create or replace package package_name is
	-- 变量定义
	var var_type;
	cursor cursor_name is select ;
	procedure procedure_name
	(var_in in_type,var_out out out_type);
	function function_name(var_in in_type)
    return return_type;
end package_name;

-- 包体
create or replace package body package_name as
	-- 包体变量定义
begin
	-- 包头程序、函数的实现
end package_name;

-- 测试程序
declare

begin
	var_1:=package_name.var;
	package_name.procedure_name();
	var_2:=package_name.function_name();
	open package_name.cursor_name;
	close package_name.cursor_name;
end;

SQL开发

动态SQL - 针对DDL和不确定的DML

execute immediate用以执行PL/SQL中的SQL语句

execute immediate
'create table table_name ···';

触发器

类似于过程或者函数

发生在DML操作上

触发器名字不可相同

-- 创建触发器
create or replace trigger trigger_name
[before|after|instead of] [insert|delete|update]
on table_name for each row
begin
	if inserting then
	elsif updating then
	elsif deleting then
	end if;
end;

-- 删除触发器
drop trigger trigger_name;

-- 禁止|打开触发器
alter trigger trigger_name disable|enable;

伪记录

-- :new
-- delete 语句不存在相应的 :new

-- :old
-- insert 语句不存在相应的 :old

raise_application_error

-- 自定义错误类型返回
-- 因为Oracle中错误码-20000以前都被占用了，所以自定义错误码从-20000开始
-- 错误码取值范围 [-20000,-20999]
if then
	raise_application_error(-20000,'报错内容');
end if;