Oracle 详细讲解

Oracle初体验
1.oracle发展（了解）
层次模型  网状模型  关系型模型  对象模型
关系型模型 ：二维表来对数据进行管理
SQL Server(中型):千万级别
Oracle(大型)：11g:grid  8i  9i  10g  12g
Mysql(小型)：百万级别
优势：1.网格计算  2.高并发 3.数据安全性  4.兼容性
2.Oracle启动使用（掌握）
1.监听（XXXXListener）:必须开启
2.打开服务（oracleserviceORCL）:必须开启
scott@orcl  没有登录
3.oracle用户（记住）
Sys:超级管理员（校长） 
System:管理员  修改密码  解锁  授权（老师）（orcl）
system@orcl  as sysdba
Alter user scott identified by 新密码
--the accout is locked
解锁：Alter user scott account unlock
Scott：普通用户 （学生）
4.操作语言（重点）
1.数据定义语言（DDL）:create drop alter
创建，修改，删除数据库对象（表），操作的是表的结构，不是表的数据
2.数据操作语言（DML）:insert delete update ,操作是表中数据
注意：oracle事务需要手动提交
添加数据
Insert into 表名（列名，列名,..）values（值，值，....）
删除数据
Delete from 表名 【where】
更新数据
Update 表名 set 列名=值，列名=值，....  【where】
Oracle体系结构(c/s)
1.    oracle体系结构（了解）
oracle服务器：oracle实例和oralce数据库
oracle实例：oracle进程和内部结构（SGA）
oralce数据库:物理结构和逻辑结构
物理结构：组成数据库系统文件（数据文件.dbf，日志文件.log，控制文件.ctl）
逻辑结构：创建后逻辑概念之间层次关系
 
 
2.    表空间（掌握）(system)
1.    system表空间
2.    non-system表空间
3.    临时（temp）表空间
1.    创建自己表空间：只有管理员有权限
Create TABLESPACE 名称
Datafile ‘路径+文件名.dbf’
Size 大小；
2.    创建一个用户
Create user 用户名
Identified by 密码
Default trablespace表空间;
注意：刚创建用户没有任何权限的，授权
3.    权限（system）
Grant：授权
Revoke：取消权限
1.    connect:登录权限
2.    resource:创建实体（表，存储过程）
3.    dba:管理员
导入：imp scott/tiger@orcl file=d:\daochu.dmp tables=(table1,table2,….)
(Fromuser=  touser=)
导出:exp  scott/tiger@orcl file=d:\daochu.dmp tables=(table1,table2,….)
4.    数据建模之三范式(理解)
1.    第一范式：表必须有主键，列具有原子性（不可再分）
例如:编号  姓名  移动电话，固定电话
2.    第二范式：满足第一范式，非主键列完全依赖主键，而不是主键的一部分（多个列来决定一个主键）
3.    第三范式：满足第二范式，非主键列直接依赖主键，不能间接产生依赖关系
5视图（view）
方便我们查询数据，将一个表中经常会被查询数据放到视图中，方便查询
    create view emp_view
as
select ename 姓名,sal 基本薪资,comm 奖金,deptno 部门 from emp;

Oracle创建表空间
创建表空间和表
Oracle存储分层概念：

ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的
oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。
数据表空间 (Tablespace)         
存放数据总是需要空间， Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式，所以一盘一个数据表空间包含一个以上的物理文件

数据表         
在仓库，我们可能有多间房子（表空间），每个房子又有多个货架（段），每架又有多层（表）。 我们在数据库中存放数据，最终是数据表的单元来存储与管理的。
数据文件         
以上几个概念都是逻辑上的， 而数据文件则是物理上的。就是说，数据文件是真正“看得着的东西”，它在磁盘上以一个真实的文件体现

1.数据库serverName（scott@127.0.0.1:1521/orcl）由一个或多个表空间组成、2.表空间（tablespace）表空间由一个或多个数据文件组成，一个表空间包含多个段、3.方案（schema）user指区分不同用户空间、4.段（segment）段是oracle数据库中的分配单位，对象如表、索引等都是以段为单位进行分配，段由一个或多个区组成、5.区（extent）区是数据文件中一个连续的分配空间，由一个或多个块组成、6.块（block）块是数据库中最小、最基本的单位，是数据库使用的最小的I/O单元，这些都是oracle数据库在数据文件中组织数据的基本单元
操作创建表空间和表：
1.创建表空间
create tablespace SIRM2 
datafile 'D:\oracle\product\10.2.0\oradata\orcl\SIRM2.dbf' size 1024M --存储地址 初始大小1G
autoextend on next 10M maxsize unlimited   --每次扩展10M，无限制扩展
EXTENT MANAGEMENT local  autoallocate
segment space management auto;
创建表空间，名称为SIRM2 ；
2. 表空间有一个数据文件*.dbf，大小为1024MB；
3. 允许表空间自动扩展(autoextends)，每次增长10MB(next 10M)，并且不限制最大大小；
4. 说明表空间本地(local)管理，并自动分配范围(autoallocate)，用户不能指定范围的大小；
5. 段空间(segment)的空间管理上使用bitmaps(auto)来管理数据块。使用AUTO会比使用MANUAL有更好的空间利用率，与效能上的提升。
在Oracle 8i以前,可以有两种选择,一种是在字典中管理(DICTIONARY),另一种是本地管理(LOCAL ),从9I开始,只能是本地管理方式.因为LOCAL 管理方式有很多优点.
在字典中管理(DICTIONARY): 将数据文件中的每一个存储单元做为一条记录,所以在做DM操作时,就会产生大量的对这个管理表的Delete和Update操作.做大量数据管理时,将会产生很多的DM操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片,这就是为什么要做磁盘整理的原因.
本地管理(LOCAL): 用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。
SEGMENT SPACE MANAGEMENT
磁盘扩展管理方法:
SEGMENT SPACE MANAGEMENT: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。

2.创建用户
 create user test  identified by test 
default tablespace SIRM2
  temporary tablespace TEMP
  profile DEFAULT;
3.授权
grant dba to test;
grant connect to test;
grant resource to test;
--查看表空间
select distinct tablespace_name from dba_free_space;
--创建表空间
create tablespace XH_History_WS
datafile 'D:\app\bailongfei123\oradata\orcl\XH_History_WS.dbf'
size 120M   --存储地址 初始大小120M
autoextend on next 10M maxsize unlimited   --每次扩展10M,无限制扩展
EXTENT MANAGEMENT local  autoallocate   ---说明表空间本地(local)管理，并自动分配范围(autoallocate)，用户不能指定范围的大小
segment space management auto;     --段空间(segment)的空间管理上使用bitmaps(auto)来管理数据块。使用AUTO会比使用MANUAL有更好的空间利用率，与效能上的提升
--删除表空间，包括内容和文件
drop tablespace XH_History_WS including contents and datafiles;

--创建用户
create user XH_Bai_History_WS
identified by tiger
default tablespace XH_History_WS;

-----
alter user 用户 quota unlimited on 表空间A;
alter user 用户 quota unlimited on 表空间B;
--或者放开所有表空间
grant unlimited tablespace to 用户;
--或者索性给所有权限
grant resource,connect,dba to 用户;
--查询用户
select * from all_users;

--授权
grant connect to XH_Bai_History_WS;-- --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作
grant resource to XH_Bai_History_WS;--是授予开发人员的，能在自己的方案中创建表、序列、视图等。
grant dba to XH_Bai_History_WS;--是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限
--取消权限revoke
revoke  connect,resource from java_user;
grant create view to java_user
--创建视图权限，一般网上找都是说的这句，但是光有这句还是无法创建 
grant create  view to XH_Bai_History_WS; 

--授予查询权限 
grant select any table to B; 

--授予权限 
grant select any dictionary to B; 


--以上3项地后就能正常创建视图了。

5.表操作
1.创建表

Create table 表名(
列名 数据类型  【约束】,
列名 数据类型  【约束】,

....
列名 数据类型  【约束】
)
create table stu_b(
  stuId int primary key,
  stuName varchar2(50) not null,
  age int check(age>18)
)
insert into stu_b(stuId,stuname,age) values(1,'小明',19);
insert into stu_b values(2,'张三',20);
select * from stu_b;

create table text_b(
  txtId int primary key,
  txtName varchar2(20) not null,
  txtAge int check(txtAge>18)
)
insert into text_b values(1,'历史',20);
select * from text_b;

数据类型：
字符型：char  varchar  varchar2
Char:固定长度 char(10):abc  占10
Varchar2:可变（在存空字符时varchar存储的是空字符，varchar2存的是null）
数字型：number(m,n):数字的精度  n:小数点后几位  int  float
时间：date 
约束：非空（not null）检查（check）默认（default）唯一（unique）
主键（primary key）外键（foreign key）
注意：oracle没有主键自增，需要借助序列（sequence）例：6序列讲解
2.删除表
Drop table 表名  [purge]
Purge:表就会被彻底删除
drop table text_b purge;

3.找回删除的表
Flashback table 表名 to before drop;
flashback table text_b to before drop;

4.重命名
Rename  原来表名 to 新表名
rename stu_b to stu;

5.增加一列（修改表的结构）
Alter table 表名
Add 列名 数据类型 [约束]
alter table stu add phone varchar2(20);

6.删除一列
Alter table 表名
drop column 列名
alter table stu drop column age;

7.修改已有列的数据类型
Alter table 表名  modify 列名 修改类型 
alter table stu modify phone varchar2(50);

8.列重命名
Alter table 表名
Rename column 原来列名 to 新名字
alter table stu rename column phone to phones;

9.查询select
Emp:雇员表  dept：部门表
1.    基本查询
Select * from 表名
说明：*代表所有的列，直接写列名

2.    模糊查询：like
查询条件不精确，通过关键字进行查询
%：0或n个字符
_：1个字符
select * from emp where ename like '%A%' order by empno desc;
select * from emp where ename like '_L%';
select sum(sal) from emp group by deptno;
select * from emp;
select * from dept;

3.    分组查询：group by 
一般聚合函数（sum,avg,max,min,count），统计
注意：1.聚合函数  2.分组列名
Order by ：排序（升序，降序 desc）
4.    多表联合查询:两张以上表联合查询，主外键关系
注意：避免笛卡尔积，加上主外键约束关系
1.    Where
select empno,ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;

2.    内连接  inner join
   
select empno,ename,sal,dname,loc from emp inner join dept on emp.deptno=dept.deptno

3.    外链接 ：left join  right join   full join
主表，附表：主表数据全部显示。，附表匹配主表进行显示
主表有的列附表没有，附表以空格的形式填充
主表没有附表有的列，附表的列就不在显示
5.    子查询
查询语句嵌套了查询语句
注意:子查询必须加上（）
相关子查询：子查询不可以脱离父查询而单独执行
先执行父查询，子查询利用父查询的列执行查询，父查询在利用子查询返回的结果作为查询条件
非相关子查询（独立子查询）：先执行子查询（内查询），将子查询的结果父查询（外查询）的条件,子查询都可以脱离父查询而单独执行
1.    单行子查询:子查询返回的结果一个
运算符：>,<,>=,<=,=,<>
2.    多行子查询:子查询返回的结果又多个值
运算符：in, all, any，not in
/**
 查询工资比Allen高的(非相关子查询)
*/
select sal from emp where ename='ALLEN';
select * from emp where sal>(select sal from emp where ename='ALLEN');
--和Allen同部门
select deptno from emp where ename='ALLEN';
select * from emp where deptno=(select deptno from emp where ename='ALLEN');
--比平均工资高的
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
--每部门工资最低的员工信息
select min(sal) from emp group by deptno
--多行子查询 in all any notin 
select * from emp where sal in (select min(sal) from emp group by deptno);
--查询工资高于所有部门的平均工资
select avg(sal) from emp group by deptno
select * from emp where sal>all(select avg(sal) from emp group by deptno);
--查询工资高于任意部门的平均工资
select * from emp where sal>any(select avg(sal) from emp group by deptno);
--查询工资高于本部门平均工资的所有员工（相关子查询）
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
--
select count(*),deptno from emp group by deptno

6.    分页查询 rownum(伪列)
Rownum从1开始执行
select rownum r,deptno,dname,loc from dept
select * from (select rownum r,deptno,dname,loc from dept) where r>2 and r<5;

6.序列（掌握）
在oracle中sequence就是序号，每次取的时候它会自动增加。sequence与表没有关系
Create sequence 名称(seq_表名)
注意：需要先执行一次nextval,才能执行currval

--简单创建序列
create sequence seq_stu start with 2;
select seq_stu.nextval from dual;
--借助序列实现主键自增
insert into stu values(seq_stu.nextval,'张珊珊','126743453','上海');
操作数据：
delete from stu where stuId=2;
update stu set stuName='丽水'，phones='13478564' where stuId=3;
序列字段说明
create sequence SEQ_LOG_ID
minvalue 1  --增长最小值
maxvalue 9999999999  --增长最大值,也可以设置NOMAXvalue -- 不设置最大值
start with 101  --从101开始计数
increment by 1  --自增步长为1
cache 50  --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---NOCACHE防止跳号
cycle;  --循环,当达到最大值时,不是从start with设置的值开始循环。而是从1开始循环
使用
insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入测试');CurrVal：返回 sequence的当前值 NextVal：增加sequence的值，然后返回 增加后sequence值
注意：第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。
CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。
一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。




7.创建视图
create view user_view
as 
select stuName 员工姓名,stuAge 员工年龄 from userStu;

select * from user_view
8.函数
--字符码—返回字符对应十进制
select ASCII('我爱你') from dual; 
select chr(52946) from dual;--参数为整数表示unicode码，返回对应的字符

--链接concat—
--concat链接两个字符串
select concat('0371-','4265324532') from dual;
--||连接符
select '0371-'||'3644532' from dual;
select concat('0319-','45836')||'转2465239' 电话码 from dual;
--首字母大写
--initcap返回字符串将其第一个字母大写，其余变小写
select initcap('hello') from dual;
--全大写
--upper返回字符串，并将其所有大写
select upper('hello') from dual;
--全小写
--lower返回字符串，并将其所有小写
select lower('HELLO') from dual;
--查找出现位置
--instr(备查字符串，要查找字符串，查找起始位置，第几次出现)
select instr('411421199603026845','1',1,3)  from dual;
--补充
--lpad(原字符串，补充到达个数，补充的字符串)
select lpad('gao',10,'*#') from dual; --lpad在列的左边粘贴字符
select rpad('gao',7,'#@') from dual; --rpad在列的右边粘贴字符
select ltrim('   ltrim') from dual;  --删除左边出现的字符 如空格
select rtrim('ltrimr','rm') from dual; --删除右边的字符串
--trim(type ‘字符1’ from ‘字符2’)从字符2两边开始删除字符1，如果前两个参数和from省略从字符2删除两边，type:删除方式（leading:从左边开始删除；trailing:从右边开始删除；both:默认两边删除）
select trim(leading '=' from '=trim=') from dual;  --删除字符串leading左边的字符串默认两边
select trim(trailing '=' from '=trim=') from dual;--- trailing右边删除
--截取substr
--substr(字符串，起始位置，截取个数)
select substr('123843564',3,5) from dual;
--替换
select replace('全能就是好','全能','切糕') from dual;
--日期函数
--sysdate系统当前日期
Select sysdate,to_char(sysdate,’dd-mm-yyyy day’) from dual;
select sysdate+14 from dual;
--add_months增加或减去月份
select add_months(sysdate,+1) from dual;
-- month_between(date2,date1)给出date2和date1相差月份
select month_between(sysdate,hiredate),ename from emp;
-- 
select last_day(sysdate) from dual;
--下一个
--next_day(date,’day’)当前data下个星期的日期
select next_day(sysdate,'星期三') from dual;
--舍取小数
select abs(-10) from dual; --取正
select ceil(3.006) from dual; --ceil向上取值
select floor(3.9999) from dual; --floor向下取值
select round(2.65) from dual; --四舍五入

--trunc 截断
select trunc(3.34523,2) from dual; --截断结果3.34
select trunc(sysdate,'dd') from dual;--截断到dd天
--mod取余
select mod(9,4) from dual;
--求次方power
select power(2,2) from dual;
--开平方 sqrt
select sqrt(4) from dual;
--to_char类型转换
select to_char(sysdate,'yyyy/mm/dd') from dual;
--将数字转换字符串--点可以用D代替
select to_char(123.4,'999.9')+100 from dual; --转换成指定类型999.9
select to_char(1273494,'999,999,999')from dual;
---将字符串转换时间
select to_date('2019-03-20','yyyy-MM-dd') from dual;
--to_number(string,numeric)
--查询奖金不为空的员工数量 count不统计空值
select count(comm) from emp;
-- nvl,nvl2代替空值
--nvl(expr1,expr2)如果expr1为null返回expr2,不为null返回expr1, 注意expr1,expr2两者类型要一致
--nvl2(expr1,expr2,expr3) 如果expr1不为null返回expr2,为null返回expr3,如果expr2和expr3类型不同，expr3会转换为expr2类型。
----员工工资（基本+奖金(有奖金的返回奖金，没有0)）
select sal+nvl(comm,0) from emp;
select sal+nvl2(comm,comm,0) from emp;
--nullif  
比较 expr1和 expr2 的值，若二者相等，则返回 NULL,否则返回 expr1的值其中 expr1不能为 NULL
select nullif(3000,2000) from dual;
--plsql代码块
--定义变量v_ename varchar2(20);
--定义常量v_pi constant number(6,2):=3.14

declare--定义部分
v_ename varchar2(20);
begin—执行
  --通过编号得到用户名
 select enam into v_enam from emp where empno=&eno;-- &eno输入符号加载体
exception –异常处理部分
  when no_data_fount then
dbms_output.put_line(‘用户名不存在！’);
end;--结束

declare 
  v_ip constant number(6,2):=3.14;
  v_r number(1):=2;
  v_area number(6,2);--1.数据类型 2.精度 
begin
  v_area:=v_pi*v_r*v_r;
  dbms_output.put_line(‘面积为:’||v_area);
end;
 --自动匹配变量类型
--into字句:在plsql块中查询出的数据必须借助变量输出 into 给v_emp变量
declare 
 v_emp emp.ename%type; 
begin
  select ename into v_emp from emp where empno=&eno;
  dbms_output.put_line(v_emp);
end;
--rowtype匹配一行
declare--定义部分--rowtype匹配一行
  v_emp  emp%rowtype;
begin
  select * into v_emp from emp where empno=&eon;
  dbms_output.put_line(v_emp.ename||v_emp.sal);
end;
----table匹配整表
declare
  --定义table类型
  type v_emp_type is table of emp%rowtype
  index by binary_integer;--通过索引一行行存储
  --定义变量
  v_emp  v_emp_type;
  
begin
  select * into v_emp(0) from emp where empno=7369;
  select * into v_emp(1) from emp where empno=7499;
  dbms_output.put_line(v_emp(0).ename||v_emp(1).ename);
end;
----record:自定义
declare
  type v_emp_recordtype is record(--定义想要的数据
    ename emp.ename%type,
    total_sal number(6)  --不在表里类型也可以自定义
  );
  v_emp v_emp_recordtype;
begin
  select ename,sal+nvl(comm,0) into v_emp from emp where empno=&eno;
  dbms_output.put_line('实发工资:'||v_emp.total_sal);
end;
---流程控制语句 （分支)(循环）
--分支if-eals(区间) switch-case
--oracle --if then end if; case when then else  end case;
--if then end if;
--plsql块
declare
---输入
 v_s number;
begin
   v_s:=&s;
  if v_s<60 then
    dbms_output.put_line('不及格');
    else
      dbms_output.put_line('及格');
  end if;
end;
---if then elseif else end if
declare
  v_s number;
begin
  v_s:=&s;
  if v_s<60 then
    dbms_output.put_line('不及格');
    elsif v_s>=60 and v_s<70 then
      dbms_output.put_line('及格');
    elsif v_s>=70 and v_s<90 then
      dbms_output.put_line('良好');
    elsif v_s>=90 and v_s<=100 then
      dbms_output.put_line('优秀');
    else
      dbms_output.put_line('输入有误！'); 
    end if;    
end;
--------------------------------
declare
  v_empno emp.empno%type;
  v_sal emp.sal%type;
  v_comm emp.comm%type;
  
begin
  v_empno:=&eno;
  select sal,comm into v_sal,v_comm from emp where empno=v_empno;
  if v_comm is null then
    update emp set comm=v_sal*0.1 where empno=v_empno;
  elsif v_comm<1000 then
    update emp set comm=1000 where empno=v_empno;
    else
      update emp set comm=v_comm+v_comm*0.1 where empno=v_empno;
       
   end if; 
end;

select * from dept;
--case when then else end case
declare
  v_deptno dept.deptno%type;
begin
  v_deptno:=&deo;
  case v_deptno
    when 10 then dbms_output.put_line('纽约');
    when 20 then dbms_output.put_line('达拉斯');
    when 30 then dbms_output.put_line('芝加哥');
    when 40 then dbms_output.put_line('波士顿');
    else dbms_output.put_line('....');
  end case;
end;
-------------------case2
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=&emo;
  case
    when v_sal<2000 then dbms_output.put_line('A级别工资');
    when v_sal>=2000 and v_sal<3000 then dbms_output.put_line('B级别工资');
    else dbms_output.put_line('C级别工资');
  end case;
end;
select * from dept;
-----------------
/***循环
   1.loop 2.while 3.for
*/
--loop exit when  end loop
--loop
   循环体
     exit  【when】;--退出出口
  end loop;
declare
  --定义table类型
 type v_dept_table is table of dept%rowtype
 index by binary_integer;
 ---定义变量
 v_dept v_dept_table;
 ---定义循环变量
 i number:=0;
begin
  loop
    select * into v_dept(i) from dept where deptno=(i+1)*10;
    dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
    i:=i+1;
    exit when i=4;
  end loop;
end;
-----while I loop end loop
--while 表达式 loop 循环体 end loop;
declare 
 type v_dept_table is table of dept%rowtype
 index by binary_integer;
 v_dept v_dept_table;
 i number:=0;
begin
  while i<4 loop
      select * into v_dept(i) from dept where deptno=(i+1)*10;
      dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
      i:=i+1;
  end loop;
end;
-------for I in 0..number loop end loop
--for 循环变量 i in 初始表达式..终止表达式 loop 循环体 end  loop;
declare
 type v_dept_table is table of dept%rowtype
 index by binary_integer;
 v_dept v_dept_table;
begin
  for i in 0..3 loop
    select * into v_dept(i) from dept where deptno=(i+1)*10;
    dbms_output.put_line('编号:'||v_dept(i).deptno||'名称'||v_dept(i).dname||'地址'||v_dept(i).loc);
  end loop;
end;
---异常处理---
begin
  insert into dept values(10,'aaa','bbb');
  exception
    when dup_val_on_index  then
  dbms_output.put_line('aaaaaa');
  
    dbms_output.put_line('其他');
end;
---自定义异常
declare
 my_exception exception;
begin
 delete from emp where empno=&eno;
 if sql%notfound then
   raise my_exception;--raise引发
   end if;
   exception
     when my_exception then
       dbms_output.put_line('编号不存在！');
end;
---事务---
create table bankjs(
 jsId int primary key,
 jsMonny varchar2(20) not null
 
);
create table bankny(
 nyId int primary key,
 nyMonny varchar2(20) not null
 
);
insert into bankjs values(1,'100');
insert into bankny values(1,'100');
select * from bankny;
select * from bankjs;
declare
  i number:=1;
begin
  update bankjs set jsMonny=jsMonny+100 where jsId=1;
  i:=i/0;
  update bankny set nyMonny=nyMonny-100 where nyId=1;
  commit;--提交
  exception
    when zero_divide  then
      rollback;--回滚
  
end;
---存储过程-----
--1,保存数据库中，针对相同的操作，下次再次使用不用重新编译
--2，预编译：sql--先编译—在执行
/*变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。 */
在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在视图（VIEW）中只能用 ，在游标(CURSOR)中只能用IS不能用AS。
--不带参
--编译的过程：并没有执行修改的操作，只是对要执行的操作进行一个语法解析等操作
create or replace procedure ifrst_procedure
is---声明
begin
  --要执行的参数
  update emp set comm=nvl(comm,0)+300;
end;
select * from emp;
--调用执行过程：这个时候才真正的执行
begin
  ifrst_procedure;
end;
--带输入参数
--默认不写是in 入参
create or replace procedure text_in(v_empno in number)
is
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=v_empno;
  dbms_output.put_line(v_sal);
end;

begin
  text_in(7369);
end;
--带输入参数输出参数
create or replace procedure text_inout(v_empno in number,v_sal out number)
is
begin
  select sal into v_sal from emp where empno=v_empno;
end;

declare
v_salout emp.sal%type;
begin
  text_inout(7369,v_salout);
  dbms_output.put_line(v_salout);
end;
--入参数输出参数 in out-----
--in out ：在执行时先作为输入参数使用，在作为输出参数
create or replace procedure tet_inout(v_inout in out number)
is
begin
  select sal into v_inout from emp where empno=v_inout;
end;
-----
 create or replace  procedure test_inout(in_out in out number)
  is
  begin
    select sal into in_out from emp where empno=in_out;
  end;
   ----执行
  declare
    a number;
  begin
    a:=&a;
    --test_inout(a);
    tet_inout(a);
    dbms_output.put_line(a);
  end;
  ---存储过程添加---
create or replace procedure insert_data(v_dempno number,v_dname varchar2,v_loc varchar2)
is
begin
  insert into dept values(v_dempno,v_dname,v_loc);
end;

--执行
begin
  insert_data(50,'部门','地址');
end;
select * from dept;
----传参关联付=>
--传参方式：位置，名称，组合。
--形参和实参关联。传递参数可以不按顺序-名称传递
begin
  insert_data(v_dname=>'部门1',v_loc=>'地址1',v_dempno => 60);
end;
--------函数---------
--函数-先编译：返回特定数据，函数肯定会给我们一个数据
--不带参function return
create or replace function my_func
return number –返回随机数
is
v_num number; --接受产生的随机数并返回
begin
  v_num:=floor(dbms_random.value(1,10));--产生随机数
  return v_num; --注意:至少有一条return语句
end;

--执行
declare
a number;
begin
  a:=my_func;
  dbms_output.put_line(a);
end;
--输入输出function in out return var
create or replace function my_funout(v_empno in number,v_dname out varchar2)
return varchar2
is
v_loc dept.loc%type;
begin
  select dname,loc into v_loc,v_dname from dept,emp where dept.deptno=emp.deptno and emp.empno=v_empno;
  return v_loc;
end;
declare
 v_loc dept.loc%type;
 v_dname dept.dname%type;
begin
  v_loc:=my_funout(7369,v_dname);
  dbms_output.put_line(v_loc);
  dbms_output.put_line(v_dname);
end;
--包规范-- package
create or replace package my_package
is
    pi constant number(10,7):=3.1415926;--定义常量 关键词constant  :=赋值符
    function getarea(ridus number) return number;--定义函数
    procedure print_area;--定义过程
end my_package;
---包体-- package body
create or replace package body my_package
is
v_area number;
--实现函数
function getarea(ridus number)
  return number
  is
  begin
    v_area:=pi*ridus*ridus;
    return v_area;
  end;
--实现过程
procedure print_area
  is
  begin
    dbms_output.put_line(v_area);
  end;
end my_package;
------调用包.----
declare
area number;
begin
  area:=my_package.getarea(4);
  dbms_output.put_line(area);
  my_package.print_area;
end;
--序列--
create sequence cc
start with 5
increment by 2;
select cc.currval from dual;
--------过程---------
create or replace procedure v_dept_output(v_empno in out number)
is
begin
  select sal into v_empno from emp where empno=v_empno;
end;
declare
 empno_sal number;
begin
  empno_sal:=&编号;
  v_dept_output(empno_sal);
  dbms_output.put_line(empno_sal);
end;
----函数 return  out—
/**
  过程和函数相同点：1，先编译，再执行
                    2，编译直接保存在数据库中
                    3，带参数，参数类型相同
   不同点：1,语法，函数:function 过程:procedure
3.    函数有返回值，return
   什么时候使用：多个值或者不返回值用过程procedure
              特定值使用函数function
**/
---通过函数输出两个数据：return 1个  利用out参数
--通过多表联合通过编号  部门 地址
create or replace function my_fun1(v_empno in number,v_loc out varchar2)
return varchar2
is
v_dname dept.dname%type;
begin
  select dname,loc into v_dname,v_loc from emp,dept where dept.deptno=emp.deptno and empno=v_empno;
  return v_dname;
end;
---
declare
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
  v_dname:=my_fun1(7369,v_loc);
  dbms_output.put_line(v_loc);
   dbms_output.put_line(v_dname);
end;
-----包规范----
----定义一些共有的组件，没有实现体
create or replace package my_packages1
is
 ip constant number:=3.1415926;
 function getarea(ridus in number) return number;--函数只用定义部分没有实现体
 procedure print_area; --过程只有定义部分
end my_packages1;
----包体-----
--包体具体执行部分，是实现包规范
create or replace package body my_packages1
is  --is里定义全局变量
v_area number;
  ---实现包规范
  --实现函数
  function getarea(ridus in number)
    return number
    is 
    begin
      v_area:=ip*ridus*ridus;
      return v_area;
    end;
   ---实现过程
   procedure print_area
    is
    begin
      dbms_output.put_line(v_area);
    end;
end my_packages1;
-----调用执行：包名.----
declare
  var_arae number;
begin
  var_arae:=my_packages1.getarea(5);
  dbms_output.put_line(var_arae);
  my_packages1.print_area;
end;
select * from emp;
--通过查询字典USER_SOURCE,可显示当前子程序及源码
Select text from user_source where name=’pack_util’;
--删除子程序
Drop procedure proc_name;
--创建索引—create index table on column
create index emp_index on emp(deptno);

select * from emp where emp.deptno='20'
--------------

--创建包规范—游标处理结果集相当java类接口
create or replace package testProduct
is
  type cursorType is ref cursor;--定义一个游标变量oracle分配内存处理结果集
end testProduct;
--过程
create or replace procedure testProcedure(userId in number,userList out testProduct.cursorType)
is
begin
  if userId=null or userId=''  then
    open userList for select * from userinfo;
  else   
    open userList for select * from userinfo u where u.userid=userId;
  end if;
end;

--申明包结构
create or replace package atii.mypackage as
type mycursor is ref cursor;
procedure queryCount(startDate in date,endDate in date,countList out mycursor);
end mypackage;

--创建包体
create or replace package body atii.mypackage as
procedure queryCount(startDate in date,endDate in date,countList out mycursor)
as
begin
open countList for
select t.createDate,count(t.createDate)
from
(select case when createtime>=(trunc(createtime)+18/24) then trunc(createtime)+1
else trunc(createtime) end createDate
from t_count ) t
where t.createDate>=to_date('2017-08-19','yyyy-MM-dd') and t.createDate<=to_date('2017-08-24','yyyy-MM-dd')
group by t.createDate
order by t.createDate;
end queryCount;
end mypackage;

----

create or replace procedure PRC_STAT_LOGIN 
IS
  v_username users.username%TYPE;
  v_password USERS.PASSWORD%TYPE;
  v_userlevel USERS.USER_LEVEL%TYPE;

begin
--声明游标  查询出每个每个用户的信息
  declare  cursor cursor_login is select * from users ;
   --cursor cursor_name is select username from users; 
  begin
    if not cursor_login%isopen then
       open cursor_login;
    end if;
     
    loop
      FETCH cursor_login INTO v_username,v_password,v_userlevel;  
      EXIT WHEN cursor_login%NOTFOUND;  
       --正常登录，返回”成功登陆”
      DBMS_OUTPUT.PUT_LINE('Login successfully!');
      IF v_username is NULL THEN--如用户名不存在，返回”用户名不存在”
        DBMS_OUTPUT.put_line('The user is not existed!');
      ELSE
        IF v_userlevel = 'U' THEN--如用户名、密码都正确，但是级别不够，管理员是A，一般用户是U，那么返回”级别不够”
          DBMS_OUTPUT.put_line('Low level!') ;   
        END IF;
      end if;
     end loop;
     
     exception
        when NO_DATA_FOUND THEN
          DBMS_OUTPUT.put_line('No data found!');
        when LOGIN_DENIED THEN  --如用户名存在，密码错误，返回”密码错误”
          DBMS_OUTPUT.PUT_LINE('PASSWORD ERROR');
      close cursor_login;   
   end;
end PRC_STAT_LOGIN;

-------游标使用---------
游标分为两种：1.隐含游标用于处理select into 和DML语句
              2.显示游标用于select语句返回多行数据
      Begin
        Delete from emp where empno=7369;--DML
        IF sql%notfound then –被隐含创建了
          dbms_output.put_line(‘sql起作用了’);
       end if;
      end;
 使用显示游标语法：
1，    定义游标CURSOR cursor_name IS select_statement;
2，    打开游标OPEN cursor_name;
3，    提取数据 FETCH cursor_name INTO variable1,variable2;
4，    关闭游标 CLOSE cursor_name;
--游标：oracle分配的一块内存，通过指针针对每一行单独处理
--游标一行一行提取数据
declare
  type v_cursor is ref cursor;--定义游标
  test_cursor v_cursor;
  test_table test_user_info%rowtype;
begin
  --打开游标
--执行查询，将结果集放在内存中，等待提取，每次提取一行。
  open test_cursor for select * from test_user_info;
  loop –循环提取
    fetch test_cursor into test_table;
    exit when  test_cursor%notfound;
     dbms_output.put_line(test_table.user_id||'-'||test_table.user_name||'-'||test_table.sex); 
  end loop;
  close test_cursor;--关闭游标，释放内存
end;
--------------table-------------一次性提取
declare
 cursor  emp_cursor  is select * from emp;
 type test_emp_table is table of emp%rowtype
 index by binary_integer;
 v_emp test_emp_table;
begin
  open emp_cursor;
  fetch emp_cursor bulk collect into v_emp; ----一次性全部提取到表结构中
 close emp_cursor; --关闭游标
for i in v_emp.first..v_emp.last loop
dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); 
  end loop;
end;
------------------定义参数游标---------------------
declare –部门20员工
  cursor emp_cursor(var_deptno number) is
  select * from emp where deptno=v_deptno;
v_emp emp%rowtype;
begin 
 open emp_cursor(20);
 loop
  fetch emp_cursor into v_emp;
exit when emp_cursor%notfound;
   dbms_output.put_line(‘编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
close emp_cursor;
end;
-------------部门信息---------------
--部门编号对应员工信息
declare 
 --部门游标
cursor dept_cursor is
Select * from dept;
v_dept dept%rowtype;
--emp游标
 cursor emp_cursor(v_deptno number) is
  select * from emp where deptno=v_deptno;
begin 
  open dept_cursor;
  loop
fetch dept_cursor into v_dept;
exit when dept_cursor%notfound;
dbms_output.put_line(‘部门编号’||v_dept.deptno);
open emp_cursor(v_dept.deptno)
  loop
  fetch emp_cursor into v_emp;
exit when emp_cursor%notfound;
   dbms_output.put_line(‘  员工编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
close emp_cursor;
end loop;
close dept_cursor;
end;
----简化操作-----
--游标for循环简化游标处理，当使用for循环时，oracle会隐含的打开游标，提取数据，关闭游标
declare
  cursor emp_cursor is
  select * from emp;
begin
  for emp_row in emp_cursor loop
    dbms_output.put_line('编号'||emp_row.empno||'名字'||emp_row.ename);
  end loop;
end;
---------当时使用游标for循环，可以直接使用子查询-----
begin
  for emp_row in (select * from emp) loop –使用子查询当做内容
      dbms_output.put_line('编号'||emp_row.empno||'名字'||emp_row.ename);
  end loop;
end;

----游标变量-----
Declare
  --游标类型
  type cursor_type is ref cursor;
  --定义变量
  cursor_emp  cursor_type;

  v_emp emp%rowtypw;
begin
 --打开游标
  Open  cursor_emp for select * from emp;
  loop
  fetch cursor_emp into v_emp;
exit when emp_cursor%notfound;
   dbms_output.put_line(‘  员工编号’||v_emp.empno||’名称’v_emp.ename);
end loop;
end;

--系统动态游标SYS_REFCURSOR的使用---
-- 创建表
create table test_user_info(
    user_id integer primary key,--primary key
    user_name varchar2(20),
    sex varchar2(2)
);

-- 插入测试数据
insert into test_user_info(user_id,user_name,sex) values(1,'小明','M');
insert into test_user_info(user_id,user_name,sex) values(2,'小美','F'); 
insert into test_user_info(user_id,user_name,sex) values(3,'小美','w'); 
insert into test_user_info(user_id,user_name,sex) values(4,'小美','q'); 
commit;
-- 查数 
select * from test_user_info;
---- 创建过程取数 
create or replace procedure test_SysCursor(p_cursor out sys_refcursor)
is
begin
  open p_cursor for select * from test_user_info; 
end;
---sys_refcursor入参返结果集
create or replace procedure inout_SysCursor(tname Nvarchar2,out_resultSet out sys_refcursor)
is
begin
  open out_resultSet for select * from test_user_info where user_name=tname; 
end;
--测试
declare
 v_cursor sys_refcursor;
 type type_table is table of test_user_info%rowtype
 index by binary_integer;
 v_table type_table;
 --su varchar2(20);
begin
  inout_SysCursor('小美',v_cursor);
  fetch v_cursor bulk collect into v_table;
  for i in v_table.first..v_table.last loop
   dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); 
  end loop;
end;

----测试1
declare
 v_cursor sys_refcursor;
 u test_user_info%rowtype;
begin
  test_SysCursor(v_cursor);
  --loop fetch v_cursor into u.user_id, u.user_name,u.sex;    
  loop 
    fetch v_cursor into u;          
    exit when v_cursor%notfound;     
    dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex); 
  end loop;
end;
---测试2
declare
 v_cursor sys_refcursor;
 type test_table is table of test_user_info%rowtype
 index by binary_integer;
 v_table test_table;
begin
  test_SysCursor(v_cursor);
  fetch v_cursor bulk collect into v_table;--一次性全部提取
   for i in v_table.first..v_table.last loop
   dbms_output.put_line(v_table(i).user_id||'-'||v_table(i).user_name||'-'||v_table(i).sex); 
  end loop;
end;

---普通动态游标的创建
declare
  type rc is ref cursor; -- 定义类型
  cursor c is
    select * from dual; -- 普通静态游标

  r_cursor  rc; -- 普通动态游标
  sr_cursor sys_refcursor; -- 系统动态游标
begin
  if (to_char(sysdate, 'mi') >= 40) then
    -- ref cursor with dynamic sql 
    open r_cursor for 'select * from dim_employee';
    open sr_cursor for 'select * from dim_org_dept';
    
  elsif (to_char(sysdate, 'mi') <= 20) then
    -- ref cursor with static sql 
    open r_cursor for select * from dim_org_dept;
    open sr_cursor for select * from dim_employee;
      
  else
    -- ref cursor with static sql 
    open r_cursor for select * from dual;
    open sr_cursor for select * from dual;
      
  end if;
  
  -- the "normal" static cursor 
  open c;
  
end;


----触发器---
1.DML触发器—在对数据库DML操作触发，并且可以对每一行或者语句操作上进行触发
2.替代触发器—专门为试图操作的一种触发器
3,系统触发器—对数据库系统事件进行触发，如启动关闭
--触发器组成
 1.触发事件—DML或者DDL语句
 2.触发时间，是在触发之前(before)还是之后（aftre）
 3.触发操作—使用PL/sql
 4.触发对象—表，视图，模式，数据库
 5.触发频率，定义执行次数
--触发器调用过程，只能包含DML

DML触发器
----事件触发器
-----星期天时不能对emp进行修改操作（insert,delete,update）
Create or replace trigger tri_no_sun
Before insert or update or delete  --在修改，添加，删除之前触发
On emp –在哪个表触发
Begin –被触发执行的操作
  If to_char(sysdate,’day’) in (‘星期日’) then
   --raise_application_error(-20000,‘今天不能修改emp表’);
case 
  when inserting then –条件谓词使用
   raise_application_error(-20000,‘今天不能添加emp表’);
  when updateing then
   raise_application_error(-20001,‘今天不能修改emp表’);
when deleteing then
   raise_application_error(-20002,‘今天不能删除emp表’);
end caes;
  End if;
End;
测试
Delete from emp where empno=7369;
-----失效触发器
Alter trigger tri_on_sun disable;
--启动
Aletr tigger tri_on_sun enable;
--删除
Drop trigger tri_on_sun;
-----行级触发器-fo  reach row-----
--当降低部门30工资触发
Create trigger no_sql
Before update of sal,comm or delete
On emp
for each row –行级触发器
 when(old.deptno=30)
begin
  caes 
   when updateing(‘sal’) then
if :new.sal<:old.sal then
  raise_application_error(-20002,‘这个部门工资不能降低’);
end if;
   when updateing(‘comm) then
if :new.comm<:old.comm then
  raise_application_error(-20000,‘这个部门奖金不能降低’);
end if;
 when deleteing then
  raise_application_error(-20001,‘这个部门不能删除’);
end;
-----after触发器执行DML之后触发----
---级联更新after触发---
Create trigger case_update
After update of deptno –修改emp表之后触发
On dept
For each row
Begin
  Update emp set deptno=:new.deptno where deptno=:lod.deptno;
End;

Update dept set deptno=50 where deptno=20;

------数据的备份-----
--删除一个表时，将被删除的数据通过过程添加到一个回收表，回收表在删除时被触发调用
--1.创建一个回收表，和被删除的表一样
Create table deldept(
  Deptno number(7),
  Dname varchar2(30),
  Loc varchar2(40)
);
--2,创建过程
Create or replace procedore add_deldept(v_deptno number,v_dname varchar2,v_loc varchar2)
Is
Begin
  Insert into deldept values(v_deptno,v_dname,v_loc);
End;
--触发器调用过程
create or replace trigger beifen_dept
after delete –在删除dept行之后触发
on dept
for each row –行级触发
begin
  add_deldept(:old,deptno,:old,bname,:old.loc);--存储旧参数
endl
delete from dept where deptno=40;

替代触发器
--不能在复杂视图上执行DML操作，必须基于视图创建instead of 触发器
 注意：只能适用于视图，不能指定before和after,必须指定for each row,只能视图上创建
Create or replace view emp_view
As
Selet deptno,count(*) total_employeer,sun(sql) total_sal from emp group by deptnol

--删除不成功
Delete from emp_view where  deptno=20;

Create or replace trigger view_tri
Instead of delete
On emp_view
For each row
Begin
  dbms_output.put_line(‘替代触发器创建成功’);
End;