Oracle知识点总结数据库qq26020387的博客-

一、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;