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;