关于Oracle数据库的笔记

/*
一、表达式
    1.定义:表达式是操作数与操作符的组合
    
        操作数:可以是常量、变量、函数的返回值、另一个查询语句返回的值
        
        操作符:就是运算符，包括算术运算符、赋值运算符、比较运算符、逻辑运算符、字符匹配运算等
        
            算术运算符:+,-,*,/,mod(m,n)[计算m和n的余数]
            连接运算符:||
            赋值运算符:= (注:比较运算符也是=)
            比较运算符:=,>,<,>=,<=,<>,!=,between...and,in,like,is null
            逻辑运算符:not,and,or
            集合运算符:union,union all,minus,INTERSECT
                union:取出2个表数据合并，去除重复记录
                union all:取出2个表数据合并，保留重复记录
                minus:取出2个表中不同数据
                Intersect:取出2个表中共有数据【交集】
            字符匹配运算:
                BETWEEN...AND:如果操作数在某个范围之内，那么就为 TRUE
                IN:如果操作数等于表达式列表中的一个，那么就为 TRUE
                like:如果操作数与一种模式相匹配，那么就为 TRUE
                    %:零个或任意个字符
                    _:任意一个字符

                    
            
            
二、添加数据

    1.语法一:(常用)
        insert into 表名(字段1,...,字段n)
        values(值1,...,值n)
    
    
    2.语法二:(常用)
        insert into 表名
        values(值1,...,值n)
        
        注：必须要添加全部字段的数据(标识[identity]列除外)
    

    3.语法三:添加多条记录，数据的具体的数据
        insert into 表名(目标表)
            select 数据1,...,数据n from dual
            union
            select 数据1,...,数据n from dual
            union 
            select 数据1,...,数据n from dual
            ...
            


        create table student
        (
            id int identity(1,1) primary key ,
            name varchar(20) not null ,
            age int ,
            wieght float
        )
    
        insert into student
            select '赵六',20,180 from dual
            union
            select '田七',5,180 from dual
            union
            select '王八',2,250 from dual

    
    4.语法四:添加多条记录，数据来源于另一张数据表
        把某张表(源表)的数据，添加到另一张表(目标表,此表必须存在)中
    
        insert into 表名(目标表)
            select 字段1,...,字段n 
            from 表名(源表)
            where 条件
            
        注：目标表必须存在
        
        insert into student_two    
            select name,age,wieght from student
            where age>=18
            
            
        create table student_two
        (
            id int identity(1,1) primary key ,
            name varchar(20) not null ,
            age int ,
            wieght float
        )
        
        select * from student 
        select * from student_two 
    
    
    
    5.语法五:在创建数据表的同时，把另一张表的数据录入到表中
    
        select 源表字段名1, 源表字段名2,……, 源表字段名n 
            into 新表名
        from 源表名
        where 源表字段条件
        
        create table 表名（目标表）
        as
        select 字段1,字段2,...|* from 表名（源表）
        
        注：目标表可以不存在

三、删除数据

    1.语法一:delete from 表名
    
        select * from student_two
        delete from student_two
    
    2.语法二:delete from 表名 where 条件 
    
        注：删除一般要写条件，否则会把整张表的数据都删除了;
        一般选择唯一键、主键做为条件
    
        delete from student where id=6
    
    3.语法三:truncate table 表名
        语法三的功能等同于语法一：都可以清空表中的数据
        
        truncate table student
        
        语法一和语法三的区别:    
            1)TRUNCATE删除数据的速度快，DELETE相对更慢。
            2)TRUNCATE只能一次性删除表中全部数据，DELETE可以删除指定条件的数据行。
            3)TRUNCATE删除数据后不能回滚(不写日志)，而DELETE可以回滚。
            4)使用TRUNCATE删除表数据时，不会触发删除触发器，而DELETE则会触发相应的删除触发器。
            5)对于有FOREIGN KEY约束引用的表不能使用TRUNCATE，而DELETE则可以(除已经被引用的数据行以外)。
            
            
四、修改数据
    语法:
        update 表名 set 
            字段1='新值',
            字段2='新值',
            ...
            字段n='新值'
        where 条件
        
        select * from s69
        update s69 set
            name='张三三' ,
            age=81
        where id=3
        
        
    
    注：修改一般要写条件，否则会把整张表都修改了




五、查询数据
1.语法:
    select [distinct | 聚合函数] 字段集合 [as 别名]|*
    from  表名
    [where 查询条件语句集合]
    [group by 分组字段列表]
    [having 过滤条件语句集合]    分组查询条件
    [order by 排序字段集合 [asc | desc]]


--查询所有字段的信息
select * from 表名
select * from dept;
select deptno,dname,loc from dept; --建议


--查询数据表中局部字段的信息
select 字段名1,...,字段名n
from 表名

select dname,loc from dept ;


--按条件查询
select * from 学生信息
where 条件

select * from dept 
where deptno>20


--模糊查询like
    通配符：
    _:任意一个字符
    %：0个或多个任意字符

--查看部门名称含有字母"S"的数据
select * from dept 
where dname like '%S%'    

--查看部门名称以字母"S"结尾的数据    
select * from dept 
where dname like '%S'

--查看部门名称以"LES"结尾并前面含有两个任意字符的数据
select * from dept 
where dname like '__LES' ;
    
    
--未知值(is null , is not null)：查询某值是否为null
create table t1 
as 
select * from dept ;

insert into t1(deptno,dname) values (50,'Java开发部')

--查询部门地址为null的部门信息
select * from t1 
where loc is null

--查询部门地址不为null的部门信息
select * from t1 
where loc is not null



--列表运算符(in , not in)：查询匹配列表中的某一个值
select * from 表名
where 字段 [not] in ('值1',...,'值n')

--查询部门地址在'NEW YORK','CHICAGO','BOSTON'的部门信息
select * from t1 
where loc in ('NEW YORK','CHICAGO','BOSTON')

select * from t1 
where loc = 'NEW YORK' or loc = 'CHICAGO' or loc = 'BOSTON'


select * from t1 
where loc not in ('NEW YORK','CHICAGO','BOSTON') or loc is null

    
    


--查询前面的n条记录
注意：Oracle不支持select top 语句，所以在Oracle中经常是用order by 跟rownum
的组合来实现select top n的查询。语法如下：

select 列名1 ...列名n from
(
    select 列名1 ...列名n 
    from 表名 order by 列名1
)
where rownum <=N(抽出记录数)
order by rownum asc



eg:
select id,name from 
(
    select id,name 
    from student order by name
) 
where rownum<=10 order by rownum asc

按姓名排序取出前十条数据
其中，rownum是产生有序编号的伪列


扩展：某个范围中的数据->分页查询
方法一：利用分析函数（建议），语法为：
    row_number() over(order by 字段 desc|asc)
    
eg:
    select deptno,dname,loc from 
    (                               
        select 
            deptno,
            dname,
            loc,row_number() over ( order by deptno asc) rn 
        from t1 
    ) where rn between 2 and 4;
    


方法二：伪列（rownum）
select deptno,dname,loc from 
(                    
    select deptno,dname,loc,rownum as rn 
    from dept 
    where rownum <= 4  
) where rn >= 2;



--字符串连接:||
select 'hello' || ' world' from dual

trim(字段|数据):去除空格




--改列名（别名）用法

select 'hello' || ' world' as 你好 from dual



--可以省略as
select 'hello' || ' world' 你好 from dual

--排序(默认的是升序)
    order by 字段名 asc | desc
    1)asc:升序(默认)
    2)desc:降序
    
    select * from t1 order by deptno desc;
    select * from t1 order by dname asc ;

--聚合函数
    1)max:求最大值
    2)min:求最小值
    3)sum:求和
    4)avg:求平均值
    5)count:求记录数
    
select 
    max(sal) as 最高薪水,
    min(sal) as 最低薪水,
    sum(sal) as 薪水总和,
    avg(sal) as 平均薪水,
    count(*) as 总人数   
from emp ;    

select count(*),count(loc) from t1 ;

注：
count中如果传递具体字段时，不会统计null的字段
聚合函数一般结合分组函数使用
--统计各部门的平均薪水
select 
    deptno as 部门编号,
    avg(sal) as 平均薪水
from emp group by deptno;

--统计各经理有多少个下属员工 
select 
    mgr as 领导,
    count(*) as 下属人数 
from emp 
group by mgr
having mgr is not null
    


--集合操作符
union:取出2个表数据合并，去除重复记录
union all:取出2个表数据合并，保留重复记录
minus:取出2个表中不同数据
Intersect:取出2个表中共有数据【交集】    

SELECT 字段集合|* FROM 表1

UNION|union all|minus|intersect

SELECT 字段集合|* FROM 表2

注意: 
    两个查询的字段个数必须相同; 
    T_2 的查询字段类型要和 T_1的相同.

create table t2
as 
select * from dept ;


delete from t2 where deptno>=30

select * from t2 ;
select * from dept ;


select * from t2
union
select * from dept;

select 'aa','bb' from dual
union
select 'cc','dd' from dual
union
select 'cc','dd' from dual


select * from t2
union all
select * from dept;

select 'aa','bb' from dual
union all
select 'cc','dd' from dual
union all
select 'cc','dd' from dual




select * from dept
minus
select * from t2;


select * from dept
intersect
select * from t2;


六、连接查询--连接(合并)两张或多张表，进行查询
（多表查询一般是通过主外键关联(公共关键字)）

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。
通过连接运算符可以实现多个表查询。


关系型数据库
    表1
    表2
    表3


1、内连接    inner join ... on ...
    select * from 表1 inner join 表2 on 表1.字段=表2.字段
    
    注：一般是根据主键和外键进行连接

    select * from emp inner join dept 
    on emp.deptno = dept.deptno;


    select * from emp e inner join dept d
    on e.deptno = d.deptno;

    select 
        ename,
        job,
        e.deptno,
        dname 
    from emp e inner join dept d
    on e.deptno = d.deptno;    
    
    


2、等值连接    、不等值连接
    select * from 表1,表2 
    where 表1.字段(主键)=表2.字段(外键)
    
    select * from 表1,表2 
    where 表1.字段!=表2.字段
    
    
    select * from emp,dept 
    where emp.deptno = dept.deptno

    select * from emp e,dept d
    where e.deptno = d.deptno

    select ename,job,dname from emp e,dept d
    where e.deptno = d.deptno    
    

    
3、外连接    
3.1）左外连接
    select * from 表1 left [outer] join 表2 
    on 表1.字段=表2.字段
    
    
    select * from 表1,表2 where 表1.字段(+)=表2.字段
    
注：（+）的用法：
1>（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
2>当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
3>（+）操作符只适用于列，而不能用在表达式上。
4>（+）操作符不能与OR和IN操作符一起使用。
5>（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

3.2）右外连接
    select * from 表1 right [outer] join 表2 on 表1.字段=表2.字段
    select * from 表1,表2 on 表1.字段=表2.字段(+)


3.3）完全外连接
    select * from 表1 full [outer] join 表2 on 表1.字段=表2.字段

--主键表(主表)
create table cls
(
       name varchar2(30) primary key ,
       teacher varchar2(30) ,
       loc varchar2(30)
)

 insert into cls values('s3sj132','张老师','213') ;
 insert into cls values('s3sj133','李老师','214') ;
 insert into cls values('s3sj134','王老师','215') ;
 insert into cls values('s3sj137','赵老师','216') ;

select * from cls;
drop table stu ;
--外键表(从表)
create table stu
(
       name varchar2(30) ,
       age number(3,0) ,
       sex char(2) ,
       cls_name varchar2(30) 
)

insert into stu values ('张三',18,'男','s3sj132') ;
insert into stu values ('李四',19,'男','s3sj133') ;

insert into stu values ('王五',17,'女','s3sj134') ;
insert into stu values ('赵六',16,'男','s3sj135') ;

delete from stu where name='张三'



select * from cls;
select * from stu;

select * from cls left join stu
on cls.name=cls_name;

select * from cls inner join stu
on cls.name=cls_name;




select * from cls right outer join stu
on cls.name = cls_name;


select * from cls full outer join stu
on cls.name = cls_name;    
    
    
    

select * from cls,stu
where cls.name(+)=stu.cls_name;

select * from cls,stu
where cls.name=stu.cls_name(+);    
    
    
    
4、交叉连接
    select * from 表1 cross join 表2 
    select * from 表1 , 表2 
    
    select * from cls cross join stu;
    select * from cls,stu;

    
七、子查询
1.概念：当一个查询是另一个查询的条件时，称为子查询。
        
        
--在SELECT语句中使用子查询
select * from 学生信息
where 学号 in 
(
    select 学生编号 from 成绩信息 where 分数>96
)
    
--查询薪水最高的员工所在的部门信息
select * from dept where deptno in
(
  select deptno from emp where sal=
  (
         select max(sal) from emp 
  )
)


select emp.deptno,dname,loc,ename,sal from dept,emp 
where dept.deptno=emp.deptno 
and emp.sal =
(
       select max(sal) from emp
);




    
--子查询可以使用在SELECT、INSERT、UPDATE或DELETE语句中
insert into 学生信息
values ('2014010102','李四四',
(select 性别 from 学生信息 
where 姓名='张苗苗'),
'1999-09-09','汉族','20050101','广东珠海')


update 学生信息 set
    性别=(select 性别 from 学生信息 where 姓名='赵希坤')
where 姓名='张苗苗'


delete from 学生信息
where convert(varchar,家庭住址)=(select convert(varchar,家庭住址) from 学生信息 where 姓名='张苗苗')
    


    
八、事务处理
1、commit:提交事务
    show autocommit    : 显示是否自动事务提交
    set autocommit=on|off    : 设置是否自动事务提交

2、rollback:事务回滚
    rollback
    rollback to 保存点
    
3、设置保存点
    savepoint 保存点名称

4、设置只读事务
    set transaction read only
    
九、函数

*/

/*
一、函数的定义
    具有某种功能的代码段
    
    实现代码重用，模块化编程
    
二、分类
    1.系统函数,用户自定义函数
    
    2.参数,返回值
        1)无参无返
        2)无参有返
        3)有参无返
        4)有参有返
    
        函数中有两个角色:主调函数(张老师)，被调函数(袁家辉)
        参数:主调函数给被调函数传递的信息（数据）
            参数的数量：0个或多个
            
            形式参数(形参):在定义函数时的参数
            实际参数(实参):在调用函数时的参数
            
        返回值：被调函数给主调函数传递的信息（数据）
            返回值的数量：0个或１个
    
        
        int sum(int a,int b) {
            int s ;
            s = a + b ;
            return s ;
        }
    
        sum(1,2) ;
    
三、Oracle提供的系统函数 
1.数学函数

--求绝对值
select abs(-4) from dual

--power(n,m):n的m次方
select power(2,3) from dual


--返回大于或等于n最小整数值（3，4，5...）
select ceil(2.48) from dual ;    --3
select ceil(2.68) from dual ;    --3

--返回小于或等于n最大整数值（2，1，0，-1...）
select floor(2.48) from dual ;    --2
select floor(2.68) from dual ;    --2

--四舍五入
select round(2.48) from dual ;    --2
select round(2.68) from dual ;    --3

--四舍五入，设置保留位数
select round(2.48,1) from dual ;  --2.5
select round(2.163,2) from dual ;  --2.16

--随机数
--1）小数(0 ~ 1)
select dbms_random.value from dual;

--2）指定范围内的小数 ( 0 ~ 100 )
select dbms_random.value(0,100) from dual;

--3）指定范围内的整数 ( 0 ~ 100 )
select round(dbms_random.value(0,100),0) from dual;
select round(dbms_random.value(0,100)) from dual;

--4）长度为20的随机数字串
select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;

--5）随机字符串
select dbms_random.string(opt, length) from dual;
其中：opt为选项，规则如下所示：
'u','U'    :    大写字母
'l','L'    :    小写字母
'a','A'    :    大、小写字母
'x','X'    :    数字、大写字母
'p','P'    :    可打印字符
length为随机字符串的长度

select dbms_random.string('a',10) from dual;

--6）生成GUID：32位十六进制字符串
select sys_guid() from dual;
select length(sys_guid()) from dual;



2.字符串函数
--length:求字符串的长度
print len('hello,world')
select length(ename),ename from emp ;

--lower/upper:大小写
select ename,lower(ename),upper(ename) from emp ;

--concat/||：字符串连接
select concat('hello ','world') from dual ;
select 'hello ' || 'world' from dual ;


--substr("字符串",start,n):截取字符串，从start开始截取n个字符
select substr('hello,world',1,3) from dual;
select ename,substr(ename,1,3) from emp ;


--replace:替换字符串
replace('字符串','被替换子字符串','替换字符串')
select replace('hello world','world','china') from dual ;

--instr:查找字符串
instr('字符串','查找字符/字符串','起始位置'),返回下标位置（从1开始）
select instr('abcabc','c',-2) from dual;
select instr('abcabc','c',0) from dual;
select instr('abcabc','c') from dual;

注：起始位置中，正数从左向右、负数从右向左查找


--trim:去掉字符串左边、右边两边的空格
--ltrim:去掉字符串左边空格
--rtrim:去掉字符串右边空格
select 'AAA' || '   BBB    ' ||  'CCC' from dual ;
select 'AAA' || trim( '   BBB    ') ||  'CCC' from dual ;

--

3.日期函数 getdate()
--sysdate：返回当前session所在时区的默认时间
--获取当前系统时间
select sysdate from dual;

--add_months：返回指定日期月份+n之后的值，n可以为任何整数
--查询当前系统月份+2 的时间
select add_months(sysdate,2) from dual;

--查询当前系统月份-2 的时间
select add_months(sysdate,-2) from dual;

--last_day：返回指定时间所在月的最后一天
--获取当前系统月份的最后一天
select last_day(sysdate) from dual;

--months_between：返回月份差，结果可正可负，当然也有可能为 0
--获取入职日期距离当前时间多少月
select months_between(sysdate, hiredate) from emp;
select months_between(hiredate, sysdate) from emp;



--trunc（number,num_digits） 
--用法一：截取日期值
select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06

select trunc(sysdate, 'mm') from dual
 --2013-01-01 返回当月第一天.
 
select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日

select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35 
select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确


--用法二：截取数值
number:需要截尾取整的数字。 
num_digits:用于指定取整精度的数字。Num_digits 的默认值为 0。
trunc()函数截取时不进行四舍五入

select trunc(123.458) from dual --123
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.458
select trunc(123) from dual --123
select trunc(123,1) from dual --123
select trunc(123,-1) from dual --120


4.转换函数
--to_char：日期转换
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(sysdate, 'yyyy-mm-dd hh12:mi:ss') from dual

--to_char：数字转换
select to_char(-100.789999999999,'L99G999D999') from dual
select to_char(-100000.789999999999,'L99G999D99') from dual
select to_char(-100000.789999999999,'L999G999D99') from dual

9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)
0 强制显示该位，如果当前位有数字，显示数字，否则显示 0
. （句点）    小数点
, （逗号）    分组（千）分隔符
PR    尖括号内负值
S    带负号的负值（使用本地化）
$     美元符号显示
L    货币符号（使用本地化）
D    小数点（使用本地化）
G    分组分隔符（使用本地化）   10,000
MI    在指明的位置的负号（如果数字 < 0）
PL    在指明的位置的正号（如果数字 > 0）
SG    在指明的位置的正/负号
RN    罗马数字（输入在 1 和 3999 之间）
TH or th    转换成序数
V    移动 n 位（小数）（参阅注解）
EEEE    科学记数。现在不支持。



--to_date：将字符串转换成日期对象
select to_date('2011-11-11 11:11:11', 'yyyy-mm-ddhh24:mi:ss') from dual

--to_number：将字符转换成数字对象
--字符转换成数字对象
select to_number('209.976')*5 from dual
select to_number('209.976', '9G999D999')*5 from dua


5.空值判断函数
--nvl(内容,data):空值函数，类似 SQLServer中的 null()函数,如果内容为空，则值设置为data
select ename,comm,nvl(comm,0) from emp;

--nvl2(内容,data1,data2):如果内容不为空，则值设置为data1,否则设置为data2
select ename,comm,nvl2(comm,comm+200,200) from emp;

--nullif(a,b):如果 a,b 的值相等，返回 null，如果不相等，返回a
select nullif(10,10) from dual;    --空，神马都没有
select nullif(10,11) from dual;    --返回 10


6.分析函数
--row_number() over (order by 字段 asc|desc):为有序组中的每一行（划分组的行或查询行）返回一个唯一的排序值
select ename,sal,row_number() over(order by sal desc) 名次 from emp;

--rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次跳过相应次数
select ename,sal,row_number() over(order by sal desc) 名次,rank() over(order by sal desc) 名次 from emp;

--dense_rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次不跳过相应次数
select 
    ename,
    sal,row_number() over(order by sal desc) 名次1,
    rank() over(order by sal desc) 名次2, 
    dense_rank() over(order by sal desc) 名次3
from emp;







*/
/*
数据库对象

一、概述
ORACLE数据库是关系型数据库，同时也是面向对象关系型数据库，又称ORDBMS，因此，在 ORACLE 数据库中也有专属的 ORACLE 对象， 主要有如下数据库对象：
1、同义词
2、序列
3、表
4、表分区
5、视图
6、过程
7、索引


二、同义词（别名）
1、概念：
同义词是数据库方案对象的一个“别名”，经常用于简化对象访问和提高对象访问的安全性。 
同义词并不占用实际存储空间，只在数据字典中保存了同义词的定义。
Oracle同义词有两种类型，分别是公用 Oracle 同义词与私有 Oracle 同义词。

2、问题
select * from scott.emp;


    
--当前登录的session是非scott用户，访问emp表时，必须指定schema.表名

3、解决：同义词
--创建公用同义词
create public synonym syn_emp for scott.emp;
--通过访问同义词来简化对象的访问
select * from syn_emp;

4、创建同义词的语法：
    create [or replace] [public] synonym sys_name 
    for [schema.]object_name

说明：
    create:创建同义词
    create or replace:没有则创建，有则替换
    public:声明公用同义词，不写则为私有同义词
    synonym:关键字
    sys_name:用户创建同义词的名称，建议以sys_为前缀
    for：关键字
    schema:对象的集合，如包含tables, views, sequences, synonyms, indexes等；
    一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。
    
    object_name:对象名

--eg1:公用同义词
create public synonym syn_emp for scott.emp;
select * from syn_emp;

--eg2:私用同义词
create or replace synonym syn_pri_emp for scott.emp
select * from syn_pri_emp;


5、查看同义词
--查看当前用户创建的私有同义词
select * from user_synonyms;

--查看当前用户创建的所有同义词
select * from all_synonyms where table_owner='SCOTT';
select * from dba_synonyms where table_owner='SCOTT';

6、删除同义词
drop public synonym 公有同义词名称

drop synonym 私有同义词名称


7、注意事项
1）用户必须拥有 Create public synonym 的系统权限才能创建公共同义词;
  只有Create any synonym 权限才能创建私有同义词

  
2）用户必须拥有同义词所定义对象的权限才能进行访问，同义词不代表权限

3）同义词不仅可以查询，还可以添加，删除，修改，但都作用于物理表



二、序列（自动增长）
1、概念
    在oracle中sequence就是所谓的序列号，
    每次取的时候它会自动增加，
    一般用在需要按序列号排序的地方；
    Oracle的序列(SEQUENCE)类似SQLServer中的自动增长列，
    用来生成唯一，连续的整数的数据库对象，
    序列通常用来生成主键或唯一值，并且可以排序。

    
2、语法：
    CREATE SEQUENCE sequence_name
    INCREMENT BY 1                     --每次加几个 默认 1
    START WITH 1                     --从 1 开始计数 默认 1
    [MAXVALUE 值|NOMAXVALUE]         --设置最大值 默认最大 10E27
    [MINVALUE 值|NOMINVALUE]         --设置最小值 默认最小-10E26
    [CYCLE|NOCYCLE]                 --一直累加，不循环
    [CACHE 10|NoCYCLE]                 --使序列号预分配
    [Order|NoOrder 默认]


eg:
    CREATE SEQUENCE seq_test  
        INCREMENT BY 1          --每次加几个  
        START WITH 1              --从1开始计数  
        NOMAXVALUE              --不设置最大值  
        NOCYCLE                   --一直累加，不循环  
        CACHE 10                  --使序列号预分配10个数,默认NOCACHE

3、访问序列的值
    NEXTVAL:返回序列的下一个值
    CURRVAL:返回序列的当前值
    
    select 序列.nextval from dual
    select 序列.currval from dual
    
    select seq_test.nextval from dual
    select seq_test.currval from dual

4、使用
    insert into 表名（自动增长的字段） values (序列名称.nextval)

5、修改
    alter sequence 序列名称 increment by 2;    

    
    alter sequence seq_test increment by 2;    
    --每次加2
    
    1）不能修改序列的初始值
    2）序列的最小值不能大于当前值
    3）序列的最大值不能小于当前值
    
5、删除序列
  drop sequence 序列名称


三、表分区
1、概述
在ORACLE中，当表的数据不断增加后，查询数据的速度就会降低，应用程序的效率也将大大下降，每次检索数据时都得扫描整张表，浪费了极大的资源，如何处理超大表数据存储和查询带来的问题， ORACLE 提供了特有的表分区技术。

2、什么是表分区
ORACLE的表分区是一种处理超大型表，索引等对象的技术，简单可以理解为分而治之，即将一张大表分成可以管理的小块。表分区后逻辑上依然是同一张表，只是将表中的数据在物理上存储到多个(表空间)物理文件上。

3、表分区的优点
1）增强可用性【一个分区出问题，不影响其他分区】
2）维护方便【同上，只维护部分分区】
3）均衡 IO【不同分区映射到磁盘平衡 IO】
4）改善查询性能【检索自己需要的分区，提高检索速度】

4、分类

4.1）范围分区--最早,最经典,数据管理能力强，但分配不均匀

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。 
这种分区方式是最为常用的，并且分区键经常采用日期，数值。

1）语法：
CREATE TABLE 表名
(
    字段名1     类型 [约束],
    ...
    字段名n     类型 [约束]
)
--表分区的定义
PARTITION BY RANGE (COLUMN_NAME)
(
    PARTITION PART1 VALUES LESS THAN (RANG1) [TABLESPACE TBS1],
    
    PARTITION PART2 VALUES LESS THAN (RANG2) [TABLESPACE TBS2],
    
    ...
    
    PARTITION PARTN VALUES LESS THAN (MAXVALUE) [TABLESPACE TBSN],
);

其中，
    COLUMN_NAME：指定分区字段
    PART1...PARTN:是表分区的名称
    RANG1...MAXVALUE:表分区的边界值，其中MAXVALUE表示边界最大值，每个分区的边界值必须比下一个分区的边界值小。
    TABLESPACE：表空间（可选），指定表分区所在的表空间
    TBS1...TBSN：表分区所在的表空间

注意：
只能在创建表时创建表分区（指定相关的表分区类型），而不能对现有的表(未创建表分区)创建表分区。

2）例子：

eg1:根据某个值的范围来分区

CREATE TABLE part_andy1
(
    andy_ID     NUMBER NOT NULL PRIMARY KEY,
    FIRST_NAME  VARCHAR2(30) NOT NULL,
    LAST_NAME   VARCHAR2(30) NOT NULL,
    PHONE       VARCHAR2(15) NOT NULL,
    EMAIL       VARCHAR2(80),
    TATUS       CHAR(1)
)PARTITION BY RANGE (andy_ID)(
    PARTITION PART1 VALUES LESS THAN (10000) ,
    PARTITION PART2 VALUES LESS THAN (20000)
);

说明：
    andy_ID字段的数值小于10000分配在PART1分区，
    andy_ID字段的数值大于等于10000且小于20000分配在PART2分区，
    andy_ID字段的数值大于等于20000将会出错，数据无法添加


eg2:根据日期分区
注意：如果是Date类型的字段，则必须使用年份为4个字符的格式，需要使用to_date()函数指定分区边界。
CREATE TABLE part_andy2
(
    ORDER_ID          NUMBER(7) NOT NULL,
    ORDER_DATE        DATE,
    OTAL_AMOUNT     NUMBER,
    CUSTOTMER_ID     NUMBER(7),
    PAID               CHAR(1)
)PARTITION BY RANGE (ORDER_DATE)(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2014-10-1', 'yyyy-mm-dd')) ,
    PARTITION p2 VALUES LESS THAN (TO_DATE('2015-10-1', 'yyyy-mm-dd')) ,
    partition p3 values less than (maxvalue)
);

说明：
    ORDER_DATE在2014-10-1之前的，分配在p1分区，
    ORDER_DATE大于或等于2014-10-1且小于2015-10-1的，分配在p2分区，
    ORDER_DATE大于或等于2015-10-1，分配在p3分区

    
--课堂作业
创建一张表（姓名、班级、课程名称、成绩），并使用范围分区对成绩字段划分为以下四个分区：
不合格：小于60分
合格：大于等60小于80
良好：>=80且<90
优秀:>=90且<=100
    
    
3）修改分区--分区界限必须调整为高于最后一个分区界限
ALTER TABLE 表名
    ADD PARTITION 表分区名称 VALUES LESS THAN (值);
    
alter table part_andy1
    add partition PART3 values less than (30000)
    

4）截断分区--分区中数据将全部删除，但分区依然存在
alter table 表名 
    truncate partition 分区名;
    
alter table part_andy1 
    truncate partition PART2;
    
5）合并分区--将两个相邻分区合并成一个新分区，继承原分区中最高上限（可重用上界限名称，下界限不可以，也可以使用新的）
alter table 表名 
    merge partitions 分区名1,分区名2 into partition 新分区名或原上界限名称;    
    
alter table part_andy1 
    merge partitions PART2,PART3 into partition PART3;    
        
6）拆分分区--将一个分区在指定的 value 值处一分为二，变成 2 个分区，原分区将不存在，数据将分到相应新的分区
alter table 表名 
    split partition 原表分区 at (value) into (partition 拆分表分区1,partition 拆分表分区1);


--查询part_andy1中，表分区PART3的数据
select * from part_andy1 partition(PART3);

--拆分分区    
alter table part_andy1 
    split partition PART3 at (20000) into (partition PART31,partition PART32);

--查看
select * from part_andy1 partition(PART31);
select * from part_andy1 partition(PART32);

7）变更分区名--将分区名称改变
alter table 表名 
    rename partition 原分区名 to 新分区

alter table part_andy1 
    rename partition PART31 to PART31_NEW
    
8）删除分区
Alter table 表名
    drop partition 分区名    
    
alter table part_andy1 
    drop partition PART31_NEW
    
4.2）散列分区--适合静态数据，总体性能最佳，易于实施，均匀
散列分区是在列值上使用散列算法， 通过在分区键上执行 HASH 函数决定存储的分区，将数据平均地分布到不同的分区，当列的值没有合适的条件时，建议使用散列分区。
CREATE TABLE EMPLOYEE
(
    EMP_ID NUMBER(4),
    EMP_NAME VARCHAR2(14),
    EMP_ADDRESS VARCHAR2(15),
    DEPARTMENT VARCHAR2(10)
)PARTITION BY HASH (DEPARTMENT)
(
    partition p1,  
    partition p2,  
    partition p3  
)
--PARTITIONS 4;
select * from EMPLOYEE partition(p1);  
select * from EMPLOYEE partition(p2);  
select * from EMPLOYEE partition(p3);  

4.3）列表（List）分区
列表分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区，允许用户将不相关的数据组织在一起。
CREATE TABLE employee
(
    Emp_ID number(4),
    Emp_Name varchar2(14),
    Emp_Address varchar2 (15)
)PARTITION BY LIST (Emp_Address)(
    Partition north values ('北京') ,
    Partition west values ('成都','重庆') ,
    Partition south values ('广州', '深圳'),
    Partition east values ('杭州', '苏州','温州')
);

--添加数据
insert into employee values(1,'zhangsan','北京');
insert into employee values(2,'lucy','广州');
insert into employee values(3,'petter','深圳');


--查询数据
select * from employee partition (north);?    --zhangsan
select * from employee partition (south);    --lucy、petter

4.4）复合分区：
形式一：范围-散列分区
表首先按某列进行范围分区，然后再按散列算法进行散列分区，分区之中的分区被称为子分区
create table slog
(
  sno number,
  sinfo varchar(300)
)partition by range(sno)        --范围分区
subpartition by hash(sinfo)        --散列分区
subpartitions 6(
  partition p1 values less than (2000),
  partition p2 values less than (4000),
  partition p3 values less than (6000),
  partition p4 values less than (8000)
);

形式二：范围-列表分区
表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
create table slog
(
    sno number,
    sinfo varchar(300)
)
partition by range(sno)            --范围分区
subpartition by LIST(sinfo)        --列表分区
subpartition template(
    subpartition t1 values('404','NotFind'),
    subpartition t2 values('500','Error'),
    subpartition t3 values('200','Success') 
)
(
    partition p1 values less than (2000),
    partition p2 values less than (4000),
    partition p3 values less than (6000),
    partition p4 values less than (8000)
);


4.5）Interval分区
11G 版本引入的 interval 分区范围分区的一种增强功能，可实现 equi-sized 范围分区的自动化。创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引
CREATE TABLE test
(
    ID NUMBER,
    ORDER_DATE DATE
) PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTOYMINTERVAL(1,'month'))
(
    PARTITION p_first VALUES LESS THAN (to_date('2013-06-23','yyyy-mm-dd'))
);

insert into test values(1,to_date('2013-06-22','yyyy-mm-dd'));
insert into test values(1,to_date('2013-06-23','yyyy-mm-dd'));
insert into test values(1,to_date('2013-07-20','yyyy-mm-dd'));
insert into test values(1,to_date('2013-07-24','yyyy-mm-dd'));

select * from test partition (p_first);

......

5、分区原则
1）表的大小：当表的大小超过 1.5GB－2GB，或对于 OLTP 系统，表的记录超
过 1000 万，都应考虑对表进行分区。

2）数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于
这样表进行分区，可充分利用分区排除无关数据查询的特性。

3）数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样
的表需要考虑进行分区，以满足维护的需要。

4）数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建
立对应关系。

5）只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，
可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。

6）并行数据操作：对于经常执行并行操作（如 Parallel Insert,Parallel Update
等）的表应考虑进行分区。

7）表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。





*/

视图
/*
一、概念
1、视图是一张虚拟的表，此表的结构从一个或多个表（或其它视图）查询的得到的结果一致。

2、视图一经定义，则以对象的方式存储在Oracle数据库中，视图中的数据是来源于查询的基表；对视图的CRUD操作，相应的基表也会发生变化。

3、对视图的更新或者插入限制很多,事实上,除非视图包含的是简单的select语句,
否则不能通过它来做更新.推荐的做法还是在基表上做更新或者插入操作,
一般情况下,视图只是用来方便查询的

二、优点：
1、集中用户使用的数据
2、掩盖数据库的复杂性
3、简化用户权限的管理
4、重新组织数据
5、不占物理存储空间，它只是一个逻辑对象（虚拟的表）
        
三、分类
1、关系视图：
关系视图(relational view)基本上就是经过存储的查询，可以将它的输出看作是一个表。它就是基于关系数据的存储对象。

2、内嵌视图：
又称为嵌套查询，是嵌入到父查询中的查询，能够在任何可以使用表名称的地方使用。

3、对象视图：
为了迎合数据库中对象类型而将关系表投射到特定数据类型的虚拟对象表中，视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。

4、物化视图：
就是在数据库中查询结果存储在视图中，并支持查询重写、刷新、提交等特性的视图
    
    
四、创建视图的步骤：
1、编写select语句
2、测试select语句
3、查询结果的正确性
4、创建视图
    
注意：
1）普通用户并没有创建视图的权限，如果要创建视图，需要使用 dba 角色的用户赋予 create view 的权限，如：

    grant create view to scott;

2）查看视图：User_views,All_views,Dba_views     
    
五、语法:
    CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)]
    AS 
    select_statement
    [WITH CHECK OPTION [CONSTRAINT constraint]]
    [WITH READ ONLY]
    
其中： 
OR REPLACE：若所创建的试图已经存在，ORACLE自动重建该视图； 
FORCE：不管基表是否存在ORACLE都会自动创建该视图； 
NOFORCE：只有基表都存在ORACLE才会创建该视图（默认）： 
alias：为视图产生的列定义的别名； 
select_statement：一条完整的SELECT语句，可以在该语句中定义别名； 
WITH CHECK OPTION ： 插入或修改的数据行必须满足视图定义的约束； 
WITH READ ONLY ： 该视图上不能进行任何DML操作。 

六、关系视图
1、创建普通关系视图，并对其进行DML操作
create table emp1
as
select * from emp;

create view view_emp1
as 
select * from emp1 

update view_emp1 set sal=1000 where empno=7369

2、创建只读视图
特点：只读，不能执行其他 DML 操作
create or replace view view_emp11 
as 
select * from emp1 where sal>=3000 with read only;

--执行删除操作
delete from view_emp11;    --错误


3、创建检查视图
特点：执行 DML 操作时，自动检测是否满足创建视图时所建立的 where 条件，如果不满足，直接出错
create or replace view view_emp11 
as 
select * from emp1 
where sal>=3000 with check option;

update view_emp11 set sal=4000 where empno=7902    --正确
update view_emp11 set sal=1000 where empno=7902    --错误


4、创建连接视图        
特点：连接视图是指基于多个表所创建的视图，即定义视图的查询是一个连接查询。使用连接视图的主要目的是为了简化连接查询。【 只能更新键保留表】
create or replace view view_emp_dept
as
select e.*,d.dname,d.loc
from emp e,dept d 
where e.deptno=d.deptno

update view_emp1_dept1 set sal=2450 where empno=7782    --正确
update view_emp1_dept1 set dname='aaa' where empno=7782    --错误

注意：emp的主键在视图中作为主键，则emp是键保留表，而dept是非键保留表。
在连接视图中，oracle规定可以更新键保留表。 因此，可以更新emp表中的数据，不能更新dept表中的数据。

5、创建复杂视图
特点: 复杂视图是指包含函数、表达式或分组数据的视图，主要目的是为了简化
查询
create or replace view view_emp 
as 
select count(*) 人数,avg(sal+nvl(comm,0)) 平均工资,deptno 部门编号 
from emp 
group by deptno;

6、创建强制视图
特点:正常情况下，如果基表不存在，创建视图就会失败。但是可以使用 FORCE选项强制创建视图（前提是创建视图的语句没有语法错误），但此时该视图处于失效状态,调用会出错，直到这个基表已经存在

create or replace force view view_test 
as select * from myemp;

select * from myemp;    --错误

create table myemp        --创建myemp表
as 
select * from emp ;

select * from myemp;    --正确

课堂练习
1、创建一个视图，以便于查询薪水大于2000的员工信息

2、创建一个视图，以便于查询部门为SALES的员工信息，及部门所在地

3、修改某数据测试是否成功

七、内嵌视图
内嵌视图又称为嵌套查询嵌视图。
可以出现在 SELECT 语句的 FROM 子句中，以及INSERT INTO、 UPDATE、甚至是 DELETE FROM 语句中。
内嵌视图是临时的，它只存在于父查询的运行期间。
eg:
select * from (select e.*,rownum rn from emp e) tab 
where rn>=5 and rn<=10;
其中: select e.*,rownum rn from emp e 就是一个内嵌视图，临时有效


八、物化视图
1、概述
物化视图简单理解就是一张特殊的物理表，预先计算并保存表连接或统计中需要耗时较多的操作的结果。物化视图也称为”快照”。

物化视图可以定时更新视图中的数据，对于大量数据统计查询后得出的
小量结果集这种情况比较适合。

物化视图可以查询表，视图和其它的物化视图。

我们可以通过 user_segments 查看用户创建对象所在资源情况。

2、物化视图的作用
1）实现两个数据库之间的数据同步，可以存在时间差。

2）如果是远程链接数据库的场景时，提高查询速度。（由于查询逻辑复杂，数据量比较大，导致每次查询视图的时候，查询速度慢，效率低下）

3、分类
1）包含聚集的物化视图
2）只包含连接的物化视图
3）嵌套物化视图

注意：
无论哪种视图，都需要设置物化视图的创建方式、 查询重写、 刷新方式等
几个方面的功能选项。

1)创建方式(Build Methods)
build immediate:是在创建物化视图的时候就生成数据。 默认为build immediate。

build deferred:是在创建时不生成数据，以后根据需要在生成数据

2)查询重写( Query Rewrite)
查询重写(ENABLE QUERY REWRITE):指当对物化视图的基表进行查询时，Oracle 会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

不查询重写(DISABLE QUERY REWRITE):指当对物化视图的基表进行查询时， Oracle不会判断能否通过查询物化视图来得到结果，直接对基表进行查询数据而不从物化视图中读取数据。 默认为DISABLE QUERY REWRITE。

3）刷新(Refresh)方式【自动 on commit，手动】
刷新的方法有四种: FAST、 COMPLETE、 FORCE 和 NEVER。 默认值是 FORCE。

刷新的模式有两种: ON DEMAND 和 ON COMMIT。 默认值是 ON DEMAND


4、创建视图日志文件
CREATE MATERIALIZED VIEW LOG ON
<table_name>
[TABLESPACE <tablespace_name>]             --视图日志保存位置
[WITH [PRIMARY KEY｜ROWID｜SEQUENCE ];    --标示基表每一行
[including new values]

注意：如果设置刷新方法为 fast，必须先构建一个基于基表的视图日志。

eg:
create materialized view log on emp with rowid

5、创建物化视图语法
CREATE MATERIALIZED VIEW [mv_name]
[
TABLESPACE [ts_name]                 -- 指定表空间
BUILD [IMMEDIATE|DEFERRED]             -- 创建时是否产生数据
REFRESH [FAST|COMPLETE|FORCE]         -- 快速、完全刷新
[ON COMMIT|ON DEMAND START WITH (start_time) NEXT (next_time)]     -- 刷新方式
[WITH {PRIMARY KEY |ROWID}]         --快速刷新时候唯一标示一条记录
{ENABLE|DISABLED} QUERY REWRITE     -–是否查询重写
]
AS {select_statement};

eg1:
create materialized view my_view
build immediate
refresh fast on commit
with rowid
as
select empno,ename,sal from emp where sal>=3000;

eg2:
create materialized view my_view
build immediate 
refresh 
on commit 
enable query rewrite 
as
select deptno,count(*) amount from myemp group by deptno;

eg3:
create materialized view my_view 
refresh 
start with sysdate next sysdate+1/48 
with rowid
as
select count(*),avg(sal+nvl(comm,0)) sals from myemp;

6、删除物化日志文件
drop materialized view log on empd

7、删除物化视图
drop materialized view my_view;    



九、常用系统视图
1、USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息。
1）Select * from user_users;            --查看当前用户信息
2）Select * from user_tables;        --查看当前用户创建表信息
3）Select * from user_views;            --查看当前用户创建视图信息
4）select * from user_tab_privs;        -—查看当前用户表权限
5）select * from user_sys_privs;        --查看当前用户系统
6）select * from user_role_privs;    --查看当前用户角色

2、ALL_*： 有关用户可以访问的对象的信息，即用户自己创建的对象的信息
加上其他用户创建的对象但该用户有权访问的信息。

3、DBA_*： 有关整个数据库中对象的信息。这里的 *可以为 TABLES，
INDEXES， OBJECTS， USERS 等。

4、V$*:一般是动态视图，随着客户端或参数值设定的不同而不



*/
PL/SQL
一、概述
1、概念
    是oracle在标准的 sql 语言上的扩展 。
    实现具体的业务功能。

2、组成
    1)procedural language：过程语言
    2)SQL(struts Query language)：结构化查询语言

3、PL/SQL块 -> PL/SQL代码的集合
    逻辑上相关的声明和语句组合在一起
    块(block)是 pl/sql 的基本程序单元。

4、PL/SQL块分类
匿名块
非匿名块
    
注意：
    在块中不能直接使用 DDL 语句，
    但可以通过动态 SQL 来解决。

二、PL/SQL块的三大结构：声明部分、可执行部分、异常部分
语法：

declare     --声明部分（可省略）
    变量/常量的定义
    
begin        --可执行部分,由begin开始，end结束，end后必须加分号，实现具体的功能业务
    ...


exception    --异常处理（可省略）
    ...
end;



void main() {

}

public static void main(String[] args) {

}

public xxx extends HttpServlet {
    public void service() {
    
    }
}




说明：
1）各语句必须以分号结束

2）变量的定义 - declare关键字下定义
变量名称 数据类型 
变量名称 数据类型 not null default 值        --not null 必须指定默认值
变量名称 数据类型 := 值                    --定义变量时指定默认值（方式一）
变量名称 数据类型 default 值                --定义变量时指定默认值（方式二）

注：赋值运算符为   :=

eg1:
declare 
     name varchar2(30);
     name := '张三11' ; --错误
begin
     name := '张三11' ;
  dbms_output.put_line('你好,' || name) ;
end;

eg2:
declare 
    name varchar2(30) not null default '匿名' ;
begin
    name := '张三'   ;
    dbms_output.put_line('你好,' || name) ;
end;

eg3:
declare //定义
    name varchar2(30) not null default '匿名' ;
    sex char(2) := '男' ;
    age number(3) default 18 ;
begin
    name := '张三'   ;
    dbms_output.put_line('姓名：' || name) ;
    dbms_output.put_line('性别：' || sex) ;
    dbms_output.put_line('年龄：' || age) ;
end;


3）常量的定义：常量在定义时必须赋初始值
常量名 constant 数据类型 ; --错误

常量名 constant 数据类型 := 常量值

常量名 constant 数据类型 default 常量值


4）给变量赋值
方式一：使用赋值运算符,把具体的数值赋值给变量/常量
    变量名称 := 值/表达式 ;

方式二:select into，把查询的数据动态的给变量赋值
select 字段 into 变量名 from 表名 [where 条件];

select 字段1,...,字段n into 变量名1,...,变量n from 表名 [where 条件];


5）&符号：代表由用户根据提示手动输入数据  -> 输入
&提示内容
declare
    变量名 varchar2 = '&提示内容' ;

declare
name varchar2(4);    --name超过4个字符会出错，解决使用%type或定义足够长的字符
begin
  select ename into name from emp where empno=&员工编号;
  dbms_output.put_line('姓名为:'||name);
end;

6）%type：定义变量的数据类型和长度与数据表某列的一致
语法：变量 表名.字段%type

declare
name emp.ename%type;
begin
  select ename into name from emp where empno=&员工编号;
  dbms_output.put_line('姓名为:'||name);
end;

7）%rowtype：返回一个记录类型，其数据类型和数据表的数据结构一致
语法：变量 表名%rowtype

访问数据：
    变量.字段

declare 
  obj emp%rowtype;
begin
  select * into obj from emp where empno = 7369;
  dbms_output.put_line('姓名='|| obj.ename);
  dbms_output.put_line('岗位='|| obj.job);
  dbms_output.put_line('薪水='|| obj.sal);
end;


declare
  v_no emp.empno%type := &empno;    --用户输入员工编号
  rec emp%rowtype;                    --rec为记录
begin
  select * into rec from emp where empno=v_no;
  dbms_output.put_line('姓名:'||rec.ename||' 工资:'||rec.sal);
end;

8）异常
    zero_divide
    case_not_found
    ...
    
    when 异常种类 then
        ...


三、数据类型
1、标量数据类型
标量类型是非常常用的一种类型，没有内部组件，仅包含单个值，主要包括 number，character,date/time,boolean 类型

declare 
    变量 标量数据类型 
    ...


2、LOB 数据类型

3、组合【复合】数据类型
1）record：用来存储多个值的变量称之为组合或者复合变量，其中存储的多个值可以是 PL/SQL 记录,也可以是 PL/SQL 中的表

declare
    type 组合类型名称 is record    --创建一个组合类型
    (
        变量1 数据类型 ,
        ...
        变量n 数据类型
    );
    
    age number(3,2) ;
    
    组合类型变量 组合类型名称 ;    --定义一个组合类型的变量

    
begin
    select 字段1,...,字段n into 组合类型的变量 from 表名 where ... ;
    ...
end;



declare
  type emp_mytype is record
  (
    name emp.ename%type,
    job emp.job%type,
    sal number(10,2)
  );
  
  einfo emp_mytype;

begin
  select ename,job,sal into einfo from emp where empno=7788;
  dbms_output.put_line('姓名:'||einfo.name||' 岗位:'||einfo.job||' 待遇:'||einfo.sal);
end;


不足之处：一次只能存储一条记录的值


2）table

declare
  type 组合类型名称 is table of 数据类型 [index by binary_integer];
  
  组合类型变量 组合类型名称;
  
begin
  select 字段 into 组合类型变量(下标1) from 数据表 where ...;
  ...
  select 字段 into 组合类型变量(下标2) from 数据表 where ...;
  
end;

注：
    下标可以任意的整数（负数，无上下限）
    
    index by binary_integer : 下标自动增长，并不需要每次使用extend增加一个空间

eg1:使用by binary_integer
declare
  type my_table is table of emp.ename%type index by binary_integer;
  
  einfo my_table;        --不需要初始化
begin
  --不必须使用extend增加一个空间且下标可以任意整数
  select ename into einfo(-1) from emp where empno=7788;    
  select ename into einfo(-2) from emp where empno=7900;
  select ename into einfo(-3) from emp where empno=7902;
  
  dbms_output.put_line('姓名 1:'||einfo(-1)||'姓名 2:'||einfo(-2)||'姓名 3:'||einfo(-3));
end;





eg2:不使用by binary_integer
declare
  type my_table is table of emp.ename%type ;
  einfo my_table := my_table() ;    --必须初始化
begin
  einfo.extend;                        --必须使用extend增加一个空间且下标从1开始
  select ename into einfo(1) from emp where empno=7788;
  
  einfo.extend;
  select ename into einfo(2) from emp where empno=7900;
  
  einfo.extend;
  select ename into einfo(3) from emp where empno=7902;
  
  dbms_output.put_line('姓名 1:'||einfo(1)||'姓名 2:'||einfo(2)||'姓名 3:'||einfo(3));
end;




eg3:可以使用bulk collect一次将符合条件的数据全部写入表中
declare
  type my_table is table of emp.ename%type index by binary_integer;
  einfo my_table;
begin
  select ename bulk collect into einfo from emp ;
  for i in 1 .. einfo.count        --count返回表的记录数
    loop
      dbms_output.put_line(einfo(i));
    end loop;
end;



eg4:record与table组合类型的混合应用

declare
--第一：自定义组合类型 - recod
type myrecord is record (
     mname emp.ename%type ,
     mjob emp.job%type
) ;

--第一：自定义组合类型 - table
type myType is table of myrecord index by binary_integer ;

--第二：创建组合类型的变量
einfo myType ;

begin
  --第二：给组合类型变量赋值  
  select ename,job into einfo(1) from emp where empno=7369 ;
  select ename,job into einfo(2) from emp where empno=7499 ;
  select ename,job into einfo(3) from emp where empno=7521 ;
  
  dbms_output.put_line('第一个姓名：' || einfo(1).mname || ' 职位' || einfo(1).mjob) ;
  dbms_output.put_line('第二个姓名：' || einfo(2).mname || ' 职位' || einfo(2).mjob) ;
  dbms_output.put_line('第三个姓名：' || einfo(3).mname || ' 职位' || einfo(3).mjob) ;
end;



4、引用【参照】数据类型


四、程序控制语句
1、条件
1）if

if 条件 then
    代码块;
end if;

if(条件) {
    代码块 ;
}

注：条件一般是逻辑运算符或关系运算符或混合

--输入年龄，判断是否大于18岁，如果大于18岁，则输出可以去网吧。

2)if...else

if 条件 then
    代码块 ;
else 
    代码块 ;
end if;


--输入年龄，判断是否大于18岁，如果大于18岁，则输出可以去网吧，否则输出不可以去网吧。

3）if..else if..
if 条件1 then
    代码块1 ;
elsif 条件2 then
    代码块2 ;
elsif 条件3 then
    代码块3 ;
else 
    代码块n
end if;

--输入成绩，判断成绩的等级
0-60:不及格
60-70:及格
70-80:中等
80-90:良好
90-100:优秀
其它:输入的成绩有误


4）case
case
    when 条件1 then 
        语句块1 ;
    ...
    when 条件n then 
        语句块n ;
    else
        语句块n+1 ;
end case ;

--使用case改写以上的练习
--判断今天是星期几
    如果是周1-5，打印输出"好好学习，天天向上"
    如果是周六，打印输出"睡个懒觉,醒了去外面玩玩"
    如果是周日,打印输出"复习、做作业"


2、循环
1）loop
loop 
    要执行的语句（循环体）;
    exit when <条件语句>     --条件满足，退出循环语句
end loop;

其中： 
exit when 子句是必须的，否则循环将无法停止。


--循环打印输出1-10的数据


2)while
WHILE 条件 
LOOP 
    要执行的语句;
END LOOP;

其中：
在 WHILE 循环语句中仍然可以使用 EXIT 或 EXIT WHEN 子句

--使用while语句实现变量v_num从1到10的循环，打印输出v_num的值，如果v_num=8退出循环。

3)for
for 循环变量 in [ REVERSE ] 下限 .. 上限 
LOOP 
    要执行的语句;
END LOOP;

其中：
每循环一次，循环变量自动加 1；使用关键字 REVERSE，循环变量自动减 1
跟在 IN REVERSE 后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式
可以使用 EXIT WHEN 子句退出循环


4)特殊语句
exit    -- 相当于高级语言中的break 
continue

exit when 条件
continue when 条件


五、动态SQL语句



六、异常处理



五、动态SQL语句  
1、概述
在 PL/SQL 块中，可以执行 DML 和 TCL,但是不可以直接执行 DDL 以及 DCL，如果想在块中使用，必须使用动态 SQL。

--在PL/SQL块执行DDL操作
begin
  drop table stu ;
end;

2、动态SQL的两种实现（分类）：
本地动态 SQL
DBMS_SQL包

3、本地动态SQL
1）语法：

Execute immediate dynamic_sql_string
[into define_variable_list]
[using bind_argument_list]

说明:
dynamic_sql_string:动态执行的SQL语句
define_variable_list:用于接受 select 查询记录值的变量列表
bind_argument_list:绑定输入参数的列表

2）例子
--eg1:动态的执行DDL语句
declare 
    str_sql varchar(300) ;
begin
  str_sql := 'drop table stu' ;
  execute immediate str_sql;
end;

--eg2:接受 select 查询记录值的变量列表
declare 
    str_sql varchar(300) ;
    row_line emp%rowtype ;
begin
  str_sql := 'select * from emp where empno=' || &工号 ;
  execute immediate str_sql into row_line;
  dbms_output.put_line('姓名：' || row_line.ename);
end;



--eg3:绑定输入参数的列表
1>设置占位符-> :1、:2、...、:n
2>动态传入参数 -> using 参数值1,...,参数值n

create table stu (
    name varchar2(30) ,
    age number(3) ,
    sex char(2)
)

declare
    str_sql varchar2(100) ;
begin
    str_sql := 'insert into stu values (:1,:2,:3)' ;
    execute immediate str_sql using '张三',18,'男';
    commit;
end;

select * from stu ;


4、DBMS_SQL 包执行动态 SQL
1）步骤
第一:构建动态 SQL 语句

第二:打开游标

第三:使用 DBMS_SQL 包的 parse 过程来分析字符串

第四:使用 DBMS_SQL 包的 bind_variable 过程来绑定变量

第五:使用 DBMS_SQL 包的 execute 函数来执行语句并返回受影响的行


第六:关闭游标

2）例子
declare
  table_name varchar2(50):='&table_name';
  str_sql varchar2(500);
  v_cursor number;
  v_row int;
begin
  --第一：构建动态DDL语句
  str_sql:='delete from ' || table_name || ' where name=:1';
  --第二：为处理打开游标
  v_cursor:=dbms_sql.open_cursor;
  --第三：分析语句 -- dbms_sql.native指定语句的行为（根据版本）
  dbms_sql.parse(v_cursor,str_sql,dbms_sql.native);
   --第四：绑定变量
  dbms_sql.bind_variable(v_cursor,':1','张三');
  --第五：执行语句[DDL语句，该操作可以省略]
  v_row:=dbms_sql.execute(v_cursor);
  --第六：关闭游标
  dbms_sql.close_cursor(v_cursor);
  dbms_output.put_line('删除表中的数据，受影响行为:' || v_row);
end;



六、异常处理
1、预定义异常
declare
  v_name varchar2(30);
begin
  select ename into v_name from emp; --where empno=73691;
  dbms_output.put_line('姓名：' || v_name) ;
  
  exception
    when TOO_MANY_ROWS then
         dbms_output.put_line('返回多行');
    when no_data_found then --可以有多个 when
         dbms_output.put_line('没有查询到数据');
    when others then --可选
         dbms_output.put_line('未知异常,错误号'||sqlcode||',错误信息'||sqlerrm);
 
end;

2、非预定异常
declare
  v_i int;
  my_math exception;   --定义一个异常名
  pragma exception_init(my_math,-1476);     --将异常名与 Oracle 错误码绑定
begin
  v_i := 10/0;         --将出现异常
  exception
    --when my_math then    --除数为 0 异常将被处理
      --dbms_output.put_line('除数不能为 0！ ');
    when others then
      dbms_output.put_line('未知异常,错误号'||sqlcode||',错误信息'||sqlerrm);
end;



3、用户定义异常
eg1:
declare
  myexception exception;   --定义一个异常名
  age int;
begin
  age:=&age;
  
  if age<18 or age>36 then
    raise myexception;        --手动抛出异常
  else
    dbms_output.put_line('您输入的年龄是' || age);
  end if;
  
  exception
    when myexception then   --处理异常
      dbms_output.put_line('年龄不符合标准');
end;



eg2:抛出应用程序异常：raise_application_error
declare
  age int;
begin
  age:=&age;
  if age<18 or age>36 then
    raise_application_error(-20001,'你输入的数据'||age||'超出了合适的范围!'); --手动抛出系统异常
  else
    dbms_output.put_line('您输入的年龄是'|| age);
  end if;
end;


eg3:
declare
  age int;
  myexeption exception;--定义异常名
  pragma exception_init(myexeption,-20001);--将异常名与系统错误号绑定
begin
  age:=&age;
  
  if age<18 or age>36 then
    raise_application_error(-20001,'你输入的数据'||age||'超出了合适的范围!'); --手动抛出系统异常
  else
    dbms_output.put_line('您输入的年龄是'|| age);
  end if;
  
  exception
    when myexeption then
      --在自定义异常时，函数 sqlcode 与 sqlerrm 可以用来显示错误号与错误信息
      dbms_output.put_line('出错了,错误号:'||sqlcode||',错误信息:'||sqlerrm);
end;

存储过程
一、子程序
    一个命名的 PL/SQL 块，编译并存储在数据库中。
    
    PL/SQL块可以实现复杂的业务逻辑，但只是临时的，只能用一次;
    而子程序把实现业务逻辑的PL/SQL块进行命名，存储在数据库中，便于重复使用。

二、子程序的结构
    子程序的结构和普通的PL/SQL块是一致的，也包括如下部分:
     
    声明部分
     可执行部分
     异常处理部分

    
三、子程序的分类
    存储过程
    函数
    
四、优点
     模块化【将程序分解为逻辑模块】
     可重用性【可以被任意数目的程序调用】
     可维护性【简化维护操作】
     安全性【通过设置权限，使数据更安全】

五、存储过程
1、概念
    过程是用于完成特定任务的子程序，通过使用过程不仅可以简化客户端应用程序的开发和维护，而且还可以提高应用程序的运行性能。

2、语法
--创建过程，可指定运行过程需传递的参数
create [or replace] procedure 存储过程名 [(参数列表)]
is|as 
   [变量/常量的定义]              
begin
   可执行的语句部分              --具体的业务逻辑功能的实现
[exception
   异常的具体处理
]              
end;

注意事项:
1）过程体内不能使用查询语句，只能用于赋值（SQL语句块都如此）
2）如果过程体语句有错误也能创建成功
3）没有参数就不写，不用()
4）参数列表，语法如下
  参数名 in/out 数据类型,...
5）在调用存储过程时，也可以通过"=>"给参数赋值。
    输入/输出参数名=>值

3、hello,world
--创建
create or replace procedure sayhello
as             
begin
   dbms_output.put_line('hello,world') ;    
end;

--调用
begin
    sayhello();
end;

4、调用
exec 存储过程名[(实参列表)] ;

或

begin
    存储过程名称[(实参列表)] ;
end;


5、输入/输出参数
in : 输入参数（默认）
out: 输出参数


6、例子
1）无输入参数、无输出参数
--创建
create or replace procedure sum1
as
  a number := 1 ;
  b constant number := 1 ;      
begin
   dbms_output.put_line(a+b) ;  
end;

--调用
begin 
    sum1;
end;


2）有输入参数、无输出参数
--创建(参数默认是输入参数)
create or replace procedure sum2(a in number,b number)
as
  c number := 3 ;     
begin
   dbms_output.put_line(a+b+c) ;  
end;

--调用
begin 
    sum2(1,2);
end;    

3、无参有返
--创建
create or replace procedure sum3(s out number)
as
  a number := 1 ;
  b number := 2 ;
  c number := 3 ;     
begin
   s := a + b + c ;  
end;

--调用
declare 
    s number ;
begin 
    sum3(s);
    dbms_output.put_line(s) ;
end;



4、有参有返
--创建
create or replace procedure sum4(a number,b in number,s out number)
as   
begin
   s := a + b  ;  
end;

--调用
declare 
    aa number := 11 ;
    bb number := 22 ;
    ss number ;
begin 
    sum4(aa,bb,ss);
    dbms_output.put_line(ss) ;
end;

5、多输出参数
--创建
create or replace procedure sum5(a number,b in number,s1 out number,s2 out number)
as   
begin
   s1 := a + b ;  
   s2 := a * b ;
end;

--调用
declare 
    aa number := 2 ;
    bb number := 3 ;
    ss1 number ;
    ss2 number ;
begin 
    sum5(aa,bb,ss1,ss2);
    dbms_output.put_line(ss1) ;
    dbms_output.put_line(ss2) ;
end;


课堂作业
1、输入员工编号，打印输出员工的姓名、职位、薪水，如果没有此员工，则进行异常处理，输出“对不起，不存在此用户”
2、输入部门编号，返回该部门的员工人数
3、输入部门编号，返回该部门最高薪水和最低薪水的员工姓名


六、为用户授予执行存储过程的权限
    grant execute on 存储过程名 to 用户名;


七、查看存储过程
select * from user_objects;
select name,line,text from user_source where name='HELLO';

八、删除存储过程
drop procedure 存储过程名 ;


---------------------------------------------------------------
函数
一、概念


二、语法
create [or replace] function 函数名[(参数列表)]
return 数据类型  
is|as 
  变量/常量的定义
begin
  函数具体实现的功能;
  return 结果;
[exception
  异常处理;]
end;

三、例子
1、无参
create or replace function fun_one return int
as
v_num number;
begin
  select max(sal)-min(sal) into v_num from emp;
  return v_num;
end;

--调用
--SQL语句调用函数
select fun_one from dual;

--使用PL/SQL块调用函数
declare
    n number:=0;
begin
    n:=fun_one();
    dbms_output.put_line('n:' || n);
end;

2、有参
create or replace function fun_two(eno number) return varchar2
as
    v_ename emp.ename%type;
begin
    select ename into v_ename from emp where empno=eno;
    return v_ename;
exception
    when no_data_found then
        dbms_output.put_line('没有找到数据');
        return '';
end;


四、函数授权
    grant execute on 函数名 to 用户名

    
五、查看
    user_objects,user_source

六、删除函数
    drop function 函数名 
    
七、存储过程与函数的区别
1、存储过程：
1）作为 PL/SQL 语句执行
2）在规格说明中不包含  RETURN 子句
3）不返回任何值
4）可以包含 RETURN 语句，但是与函数不同，它不能用于返回值

2、函数
1）作为表达式的一部分供SQL调用
2）必须在规格说明中包含 RETURN 子句
3）必须返回单个值
4）必须包含至少一条 RETURN 语句





*/

create or replace procedure sayhello(b in number)
as 
    a number := 10 ;
    c number ;
begin 
    c := a-b ;
    dbms_output.put_line(c) ;
end ;

begin
    sayhello(20);
end ;




--1、输入员工编号，打印输出员工的姓名、职位、薪水，如果没有此员工，则进行异常处理，输出“对不起，不存在此用户”

--第一
create or replace procedure hao(b in number)
as 
 
   v_name  varchar2(30) ;
   v_job  varchar2(30) ;
   v_sal number ;
begin 
    select ename,job,sal into v_name,v_job,v_sal from emp where empno=b ;
    dbms_output.put_line('姓名：'|| v_name || '  工作：' || v_job || '  薪水：' || v_sal) ;
    
     exception
     when no_data_found then 
         dbms_output.put_line('对不起，不存在此用户');
end ;


declare 
    bianhao number ;
begin 
    bianhao :=&编号 ;
    hao(bianhao) ;
end ;


--第二

create or replace procedure haohao(b in number)
as 
 
  obj emp%rowtype;
begin 
    select * into obj from emp where empno=b ;
    dbms_output.put_line('姓名：'|| obj.ename || '  工作：' || obj.job || '  薪水：' || obj.sal) ;
    
     exception
     when no_data_found then 
         dbms_output.put_line('对不起，不存在此用户');
end ;


declare 
    bianhao number ;
begin 
    bianhao :=&编号 ;
    haohao(bianhao) ;
end ;





--2、输入部门编号，返回该部门的员工人数

create or replace procedure bumen(b in number,s out number)
as 
  
   v_people number ;
begin 
     select count(ename) into v_people  from emp e,dept d where e.deptno=d.deptno and d.deptno=b ;
     
     s:=v_people ;
    
end ;

declare 
    bianhao number ;
    s number ;
begin 
    bianhao :=&编号 ;
    bumen(bianhao,s) ;
    
    dbms_output.put_line('部门编号为'||bianhao||'的部门人数为:'||s)  ;
end ;





--3、输入部门编号，返回该部门最高薪水和最低薪水的员工姓名


create or replace procedure bumen2(b in number)
as 
   max_name varchar2(30) ;
   min_name varchar2(30) ;
   max_sal number ;
   min_sal number ;
begin 
    select max(sal) into max_sal from emp e,dept d where e.deptno=d.deptno and d.deptno=b  ;
    select ename into max_name from emp where sal= max_sal and deptno=b ;
     
    select min(sal) into min_sal from emp e,dept d where e.deptno=d.deptno and d.deptno=b  ;
      select ename into min_name from emp where sal=min_sal and deptno=b ;
  
    
     dbms_output.put_line('部门编号为'||b||'的最高薪水的姓名为:'||max_name||'薪水为：'||max_sal)  ;
      dbms_output.put_line('部门编号为'||b||'的最低薪水的姓名为:'||min_name||'薪水为：'||min_sal)  ;
end ;

declare 
    bianhao number ;
begin 
    bianhao :=&编号 ;
    bumen2(bianhao) ;
end ;

  select min(sal)  from emp e,dept d where e.deptno=d.deptno and deptno=20  ;

select min(sal),ename into min_sal,min_name from emp where deptno=20 ;

select * from emp