Oracle知识点总结

一、Sql操作代码

1.Oracle体系结构

• 创建表空间

  create tablespace 表空间名称
  datafile '物理文件存储位置'
  size 初始大小
  autoextend on
  next 扩容大小;
  /*
  waterboss 为表空间名称
  datafile 用于设置物理文件名称
  size 用于设置表空间的初始大小
  autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
  next 用于设置扩容的空间大小
  */
  
  例：
  create tablespace waterboss
  datafile 'c:\waterboss.dbf'
  size 100m
  autoextend on
  next 10m;
  

• 创建临时表空间

  create temporary tablespace 表空间名称
  datafile '物理文件存储位置'
  size 初始大小;
  
  例：
  create temporary tablespace temp_table
  datafile 'c:\temp_table.dbf'
  size 10m;
  

• 删除表空间

  drop tablespace 表空间名称 including contents and datafiles;
  
  例：
  drop tablespace temp_table including contents and datafiles;
  

• 添加表空间文件

  alter tablespace 表空间名称
  add datafile '物理文件存储位置' 
  size 大小;
  
  例：
  alter tablespace temp_table
  add datafile 'd:/oracle/oracleDBF/my_test.dbf' 
  size 10m;
  

• 创建用户

  create user 用户名
  identified by 密码
  default tablespace 默认表空间名称;
  /*
  wateruser 为创建的用户名
  identified by 用于设置用户的密码
  default tablesapce 用于指定默认表空间名称
  */
  
  例：
  create user wateruser
  identified by 123123
  default tablespace waterboss;
  

• 用户解锁

  alter user 用户名 account unlock;
  例：
  alter user wateruser account unlock;
  

• 用户赋权

  grant dba to 用户名;
  
  例：
  grant connect, resource to wateruser;
  

• 移除用户赋权

  revoke 权限 from 用户名;
  
  例：
  revoke resource from wateruser;
  

• 修改用户密码

  alter user 用户名 identified by 密码;
  
  例：
  alter user wateruser identified by 123456;
  

2.表操作

• 创建表

  create table 表名(
         列名 数据类型 [约束],
         列名 数据类型 [约束],
         列名 数据类型 [约束]
         ....
  );
  
  例：
  -- 创建班级表
  create table t_class(
         c_id number(10) primary key,
         c_name varchar2(30)
  );
  -- 创建学生表
  create table t_student(
         s_id number(10) primary key,
         s_name varchar2(100),
         s_age number(4) not null,
         s_sex char(2),
         c_id number(10),
         constraint fk_c_id foreign key (c_id) references t_class(c_id),
         constraint check_sex check(s_sex='男' or s_sex='女')
  );
  

• 修改表结构

  --添加一列
  alter table 表名 add 列名 数据类型 [约束];
  --修改一列
  alter table 表名 modify 列名 数据类型 [约束];
  -- 修改一列
  alter table 表名 modify 列名 数据类型 [约束];
  -- 删除一列
  alter table 表名 drop column 列名;
  -- 删除外键约束
  alter table 表名 drop constraint 约束名;
  -- 添加外键约束
  alter table 表名 add constraint 约束名 
  foreign key (列名) references 关联的表(关联表的列名);
  
  例：
  -- 添加一列
  alter table t_class add teacher_name varchar2(20);
  -- 修改一列
  alter table t_class modify c_name varchar2(100);
  -- 删除一列
  alter table t_class drop column teacher_name;
  -- 删除外键约束
  alter table t_student drop constraint fk_c_id;
  -- 添加外键约束
  alter table t_student add constraint fk_c_id 
  foreign key (c_id) references t_class(c_id);
  

• 删除表

  drop table 表名;
  
  例：
  -- 删除表
  drop table t_student;
  drop table t_class;
  

• 创建序列（用于主键自增）

  create sequence 序列名 start with 开始数 increment by 步进数;
  
  例：
  create sequence seq_cid start with 1 increment by 1;
  

###3.CRUD(增删改查)

• 添加数据

  添加主键自增语法：
  序列名.nextval
  
  例：seq_cid.nextval
  
  insert into 表名 values(值1,值2,值3...);
  
  例：
  insert into t_class values(seq_cid.nextval,'1班');
  
  

• 修改数据

  update 表名 set 列名 = 值 where 列名=值;
  
  例：
  update t_student set s_name = '张三' where s_id=1;
  

• 删除数据

  delete from 表名 where 列名 = 值;
  
  例：
  delete from t_class where c_id = 1;
  

• 查询数据

  select 列名 from 表名;
  
  例：
  select * from t_class;
  

• 分页查询数据

  select * from (
      select rownum as rowno,列名 from 表名 t where rownum<=结束位置
  ) table_alias 
  where table_alias.rowno>=开始位置;
  
  例：
  select * from (
      select rownum as rowno,t.* from emp t where rownum<=5
  ) table_alias where table_alias.rowno>=1;
  

• 子查询

  select * from 表名 where 列名>=all(select 函数(列名) from 表名 where 列名=值)
  
  例：
  select * from emp where sal>=all(select max(sal) from emp where deptno=20)
  

二、PLSQL操作代码

###1.程序结构

declare
定义部分
begin
执行部分
exception
异常处理部分
end;

例：
declare 
 v_ename varchar(30);
begin 
  -- into指的是把ename赋给v_ename
  -- &+任意名称，指的是用来取用户输入的值
  select ename into v_ename from emp where empno=&v_empno;
  -- 输出语句
  dbms_output.put_line('查询到的名称：'||v.ename); 
exception
  when no_data_found then dbms_output.put_line('输入的编号不存在！');
end;  

2.流程控制

• PL/SQL数据类型

  1.%TYPE数据类型
  
  例：
  declare 
   t_ename emp.ename%type;
  begin
  end;
  
  2.%ROWTYPE数据类型
  
  例：
  declare 
   emp_row emp%rowtype;
  begin
  end;
  
  3.record数据类型
  
  例：
  declare 
   type record_emp is record(
   	t_ename emp.ename%type,
      t_sal emp.sal%type
   )
  begin
  end;
  
  4.table数据类型
  
  例：
  declare 
   type table_emp is table of emp%rowtype index by binary_integer;
   var_table_emp table_emp
  begin
  end;
  

• 条件控制语句 - IF

  1.
  if 条件表达式 then
  	语句段
  end if;
  
  2. 
  if 条件表达式 then
  	语句段1
  else
  	语句段2
  end if;
  
  3.
  if 条件表达式1 then
  	语句段1
  elsif 条件表达式2 then
  	语句段2
  elsif 条件表达式3 then
  	语句段3
  else
  	语句段4
  end if;
  
  例：
  declare
    t_empno emp.empno%type;
    t_comm emp.comm%type;
  begin
    t_empno := &eno;
    select comm into t_comm from emp where empno=t_empno;
    dbms_output.put_line('原来的奖金'||t_comm);
    
    if t_comm = '' or t_comm is null then
      update emp set comm = sal * 0.2 where empno=t_empno;
    elsif t_comm < 1000 then
      update emp set comm = 1000 where empno=t_empno;
    else
      update emp set comm = comm * 1.1 where empno=t_empno;
    end if;
  end;
  

• 条件控制语句 - CASE

  case 表达式
  	when 条件表达式结果1 then
  		语句段1;
  	when 条件表达式结果2 then
  		语句段2;
  	else
  		语句段n;
  end case;
  
  例：
  declare
    t_sal emp.sal%type;
  begin
    select sal into t_sal from emp where empno=&emp;
    case when t_sal<1000 then
      dbms_output.put_line('C级');
    when t_sal<2000 and t_sal>=1000 then
      dbms_output.put_line('B级');
    when t_sal>=2000 then
      dbms_output.put_line('A级');
    end case;
  end;
  

• 循环控制

  loop
  	语句段
  exit[when 条件表达式]
  end loop;
  

• 条件循环- while

  while 条件表达式 loop 语句段;
    exit when 条件表达式
  end loop;
  
  例：
  declare
  v_i number(2):=1;
  begin
    while v_i <=3 loop
      dbms_output.put_line(v_i);
      v_i:=v_i+1;
    end loop;
  end;
  

• for循环

  for 循环变量 in [reverse] 初始值表达式..终止值表达式 loop
    语句段;
  end loop;
  
  例：
  declare
  begin
    for v_i in 1..3 loop
      dbms_output.put_line(v_i);
    end loop;
  end;
  

###3.异常处理

• 异常处理

  declare
  	自定义异常名称 exception;
  begin
  exception
  	when 异常错误名称1[or 异常错误名称2] then
  	语句段1;
  	when 异常错误名称3[or 异常错误名称4] then
  	语句段2;
  	when others then
  	语句段3;
  end;
  
  例：
  declare
  begin
  insert into dept values(1,'a');
  exception
    when others then
      dbms_output.put_line('发生异常');
  end;
  

4.事务

• 事务控制语句

  begin
  语句块
  savepoint 事务回滚点名称
  语句块2
  rollback to 事务回滚点名称
  语句块3
  commit
  exception
   when others then
     rollback;
  end;
  
  例：
  begin 
    insert into dept values('7','测试部门A','郑州');
    savepoint a;
    insert into dept values('8','测试部门B','郑州');
    insert into dept values('9','测试部门C','郑州');
    rollback to a;
    insert into dept values('9','测试部门D','郑州');
    commit;
  exception
    when others then
      dbms_output.put_line('请注意代码有误');
      rollback;
  end;
  

5.存储过程

-- 创建/修改存储过程
create [or replace] procedure 存储过程名称(argument1 [mode1] datatype1, argument2 [mode2]datatype2, .) is [as]
声明部分
begin
执行部分
exception
异常处理部分
end;
-- 调用存储过程
1.call 存储过程名称;
2.
declare
begin
  存储过程名称();
end;

例：
create or replace procedure pro_query_dept(v_deptno in number,v_dname out varchar2)
is
begin
  select dname into v_dname from dept where deptno=v_deptno;
  dbms_output.put_line('部门名称：'||v_dname);
exception
  when no_data_found then
    dbms_output.put_line('查无数据');
end;

调用：
-- 调用带参数的存储过程
declare
v_dname dept.dname%type;
begin
  pro_query_dept(1,v_dname);
  dbms_output.put_line('查询到的部门名称为：'||v_dname);
end;

6.函数

创建/修改：
create [or replace] function 函数名(参数1,参数2)
return 返回类型
is/as
声明部分
begin
  执行部分
exception
  异常处理部分
end;

调用：
declare
begin
函数名
end;

例：
-- 创建带参函数
create or replace function fun_query_emp(v_hiredate emp.hiredate%type, v_empno emp.empno%type)
return emp.ename%type as t_ename emp.ename%type;
begin
  select ename into t_ename from emp where hiredate=v_hiredate or empno=v_empno;
  dbms_output.put_line('员工姓名:'||t_ename);
  return t_ename;
exception
  when no_data_found then
    dbms_output.put_line('参数有误!');
end;
-- 调用函数
declare
v_ename emp.ename%type;
begin
  v_ename:=fun_query_emp(sysdate,7499);
  dbms_output.put_line(v_ename);
end;

7.包

创建包头语法:
create [or replace] package 包名称
is/as
公共的数据类型
存储过程
函数
end 包名称;

创建包体语法:
create [or replace] package body 包体名称
is/as
定义部分
end 包体名称;

调用：
declare
begin
包名称.函数/存储过程名称
end;

例:
-- 创建包头
create or replace package pag_dept
is
 -- function fun_query_dept return varchar2;
  procedure pro_query_dept;
 -- procedure pro_insert_dept(v_deptno dept.deptno%type,v_dname dept.dname%type,v_loc dept.loc%type);
end pag_dept;

-- 创建包体
create or replace package body pag_dept
is
procedure pro_query_dept
  is
  t_dname dept.dname%type;
  begin
    select dname into t_dname from dept where deptno=10;
    dbms_output.put_line('查询过程'||t_dname);
  exception
    when no_data_found then
      dbms_output.put_line('未找到数据');
  end;
end pag_dept;

-- 包的调用
declare
begin
  pag_dept.pro_query_dept;
end;

8.游标

定义游标
cursor 游标名称(参数名 参数类型...) is 查询声明;
打开游标
open 游标名称;
提取数据
fetch 游标名称 into 变量1,变量2,...;
关闭游标
close 游标名称;

例：
-- 带参数的游标
declare
cursor cursor_emp(v_deptno number) is select * from emp where deptno=v_deptno;
t_emp_row emp%rowtype;
begin
  open cursor_emp(&deptno);
  loop
    fetch cursor_emp into t_emp_row;
    exit when cursor_emp%notfound;
    dbms_output.put_line('员工姓名'||t_emp_row.ename);
  end loop;
  close cursor_emp;
end;

-- 游标for循环
declare
cursor cursor_dept is select * from dept;
begin
  for d in cursor_dept loop
    dbms_output.put_line(d.dname||'-'||d.loc);
  end loop;
end;

9.触发器

创建触发器
create [or replace] trigger 触发器名称
timing 触发事件1[or 触发事件2 or 触发事件3]
on 表名
语句块;

触发器失效
alter trigger 触发器名字 disable;
触发器生效
alter trigger 触发器名字 enable;
删除触发器
drop trigger 触发器名称;

例：
create or replace trigger tir_emp
before
insert or update or delete
on emp
begin
  if to_char(sysdate,'day') in ('星期三','星期四')
    then
      raise_application_error(-20010,'今日不可修改emp表');
  end if;
end;