Oracle-数据库

       Oracle

      表空间（Table Space）：Oralce中，最大的逻辑存储结构是表空间。表空间和物理上的数据文件相对应

     段（Segment）是一组盘区，这组盘区组成了被Oracle视为一个单位的数据库对象，比如表或索引。因此，段是数据库终端用户将处理的最小单位

    区（Area）：Oracle中，区是磁盘空间分配的最小单位

    块（Block）是用来管理存储空间的最基本单位，也是最小的逻辑存储单位

    模式对象（Model）：表、视图、序列、存储过程、函数等 

数据库：
Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

实例：  
一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。

用户：
用户是在实例下建立的。不同实例可以建相同名字的用户。

表空间：
表空间是一个用来管理数据存储逻辑概念，表空间只是和数据文件（ORA或者DBF文件）发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。

数据文件（dbf、ora）：

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注：

oracle是有用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

创建临时表空间   

create temporary tablespace user_temp tempfile 'C:/test/usertemp.ora' size 50M;

创建永久表空间

create tablespace user_data datafile 'C:/test/userdata.ora' size 200m autoextend on next 10m maxsize unlimited;

查看表空间名称、id，文件存储位置,初始大小

 

select tablespace_name, file_id, file_name, bytes

  from dba_data_files

 order by file_id;

 

1.4 创建用户

create user myuser identified by 123456

default tablespace user_data

temporary tablespace user_temp;

 

查看用户：

select username from dba_users;

授权用户：

grant create session,create table,unlimited tablespace to myuser;

登录：

conn myuser/123456;

删除表空间后用户下的表就无法操作

 

1.5授权

命令：grant  xxx权限 on Table to USER

grant select,insert,update,delete,all on 表名 to 用户名

例：将test表的查询权限赋予tom这个用户

grant select on test to tom  

例：将所有表的查询权限赋予tom这个用户

grant select to tom;

例：分配操作，dba，连接权限

grant resource,dba,connect to scott;

例子：

需要system用户

给scott用户授权创建视图

grant create view to scott;

撤销权限

revoke create view from scott;

 

1.3.1 操作表

查看表

下面为您介绍的语句用于实现Oracle查询用户所有表，如果您对oracle查询方面感兴趣的话，不妨一看。

select * from all_tab_comments -- 查询所有用户的表,视图等

 

select * from user_tab_comments -- 查询本用户的表,视图等

 

select * from all_col_comments  --查询所有用户的表的列名和注释.

 

select * from user_col_comments -- 查询本用户的表的列名和注释

 

select * from all_tab_columns --查询所有用户的表的列名等信息(详细但是没有备注).

 

select * from user_tab_columns --查询本用户的表的列名等信息(详细但是没有备注).

 

--一般使用1:

select t.table_name,t.comments from user_tab_comments t

 

1.3.1.1创建表

Oracle数据类型包括

字符类型

1)CHAR 数据类型        固定字节长度      2000字节

2)VARCHAR2 数据类型    可变字节长度      4000字节

3)NVARCHAR2 数据类型   可变字符长度      2000字符

数值类型

1) 可以存储整数、浮点数和实数

2) 最高精度为 38 位

3) NUMBER [( p[, s])]

4) P表示精度，S表示小数点的位数

日期类型

1)     DATE - 存储日期和时间部分，精确到整个的秒

2)     TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位

 

create table userinfo

(user_id VARCHAR2(20) not null,

user_name VARCHAR2(20) not null,

user_age NUMBER(3),

user_sal number(6,2),

creat_date date,

user_preset VARCHAR2(20),

user_del char default 1 not null

);

 -- Create/Recreate primary, unique and foreign key constraints 

alter table userinfo add constraint pk_id primary key(user_id);

 

1.3.1.2ALTER USER 命令可用于更改口令

ALTER USER <username> IDENTIFIED BY <new_password>

1.3.1.3 DROP USER 命令用于删除用户

DROP USER <username> CASCADE

1.3.1.4 dml操作表数据

添加语句

Insert into 表名 values(‘字段1’,’ 字段2’,’ 字段3’,’ 字段4’,’ 字段5’,’ 字段n’);

Insert into 表名(字段1，字段2，字段n) values(‘字段1’,’ 字段2’,’ 字段n’);

修改语句

update 表名 set字段1=参数1，字段2=参数2，字段n=参数n where 条件

查询语句

select字段1，字段2，字段n from 表名 where 条件

删除语句

delete from 表名 where 条件

Oracle-数据库

导出的三种方式:

①:传统方式——exp(导出)和(imp)导入：
②:数据泵方式——expdp导出和（impdp）导入;
③:第三方工具——PL/sql Develpoer;

1.1什么是数据库导入导出？

oracle11g数据库的导入/导出，就是我们通常所说的oracle数据的还原/备份。

数据库导入:把.dmp 格式文件从本地导入到数据库服务器中(本地oracle测试数据库中);

数据库导出:把数据库服务器中的数据(本地oracle测试数据库中的数据),导出到本地生成.dmp格式文件。

.dmp 格式文件：就是oracle数据的文件格式(比如视频是.mp4 格式,音乐是.mp3 格式);

1.2二者优缺点描述：

1.2.1.exp/imp:

优点:代码书写简单易懂,从本地即可直接导入，不用在服务器中操作，降低难度，减少服务器上的操作也就保证了服务器上数据文件的安全性。

缺点：这种导入导出的速度相对较慢，合适数据库数据较少的时候。如果文件超过几个G，大众性能的电脑，至少需要4~5个小时左右。

1.2.2.expdp/impdp:

优点:导入导出速度相对较快,几个G的数据文件一般在1~2小时左右。

缺点:代码相对不易理解,要想实现导入导出的操作，必须在服务器上创建逻辑目录(不是真正的目录)。我们都知道数据库服务器的重要性，所以在上面的操作必须慎重。所以这种方式一般由专业的程序人员来完成(不一定是DBA(数据库管理员)来干,中小公司可能没有DBA)。

注意

目标数据库:数据即将导入的数据库(一般是项目上正式数据库);

源数据库:数据导出的数据库(一般是项目上的测试数据库);

1).目标数据库要与源数据库有着名称相同的表空间。

2).目标数据在进行导入时，用户名尽量相同(这样保证用户的权限级别相同)。

3).目标数据库每次在进行数据导入前，应做好数据备份，以防数据丢失。

4).使用数据泵时,一定要现在服务器端建立可用的逻辑目录，并检查是否可用。

5).弄清是导入导出到相同版本还是不同版本(oracle10g版本与oracle11g版本)。

6).目标数据导入前,弄清楚是数据覆盖(替换),还是仅插入新数据或替换部分数据表。

7).确定目标数据库磁盘空间是否足够容纳新数据，是否需要扩充表空间。

8).导入导出时注意字符集是否相同，一般Oracle数据库的字符集只有一个，并且固定，一般不改变。

9).导出格式介绍:

 　       dmp格式：.dmp是二进制文件，可跨平台，还能包含权限，效率好； 

 　       Sql格式：.sql格式的文件，可用文本编辑器查看，通用性比较好，效率不如第一种，

适合小数据量导入导出。尤其注意的是表中不能有大字段（blob,clob,long），如果有，会报错； 

Pde格式：.pde格式的文件，.pde为PL/SQL Developer自有的文件格式，只能用PL/SQL Developer工具

导入导出，不能用文本编辑器查看；

10).确定操作者的账号权限。  

1.3数据导出：

打开cmd命令窗口运行以下命令

1.3.1导入导出方式

命令:exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;

数据库导出举例:

exp xinxiaoyong/123456@127.0.0.1:1521 file="e:\temp.dmp" full = y;

注意：如果需要导出完全数据库，必须具备exp_full_database权限。

exp:导出命令，导出时必写。

imp:导入命令，导入时必写,每次操作，二者只能选择一个执行。

username:导出数据的用户名，必写;

password:导出数据的密码，必写;

@:地址符号，必写;

SERVICENAME:Oracle的服务名，必写;

1521:端口号，1521是默认的可以不写,非默认要写;

file="e:\temp.dmp" : 文件存放路径地址，必写;

full=y :表示全库导出。可以不写，则默认为no,则只导出用户下的对象;

方法细分：

1.完全导入导出：

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;

2.部分用户表table导入导出：

exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" tabels= (table1,table2,table3,...);

3.表空间tablespaces导入导出：

//一个数据库实例可以有N个表空间(tablespace)，一个表空间下可以有N张表(table)。

exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" tablespaces=  (tablespace1,tablespace2,tablespace3,...);

4.用户名username对象导入导出：

exp(imp) username/password@SERVICENAME:1521 file="e:\temp.dmp" owner(username1,username2,username3);

例子：

exp scott/tiger@orcl file="c:\test\temp.dmp" full = y;

1.Oracle基础sql语句

1)SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词

2)SQL 是数据库语言，Oracle 使用该语言存储和检索信息

3)通过 SQL可以实现与 Oracle 服务器的通信

2.1运算符

1）一种符号，它是用来进行列间或者变量之间的比较和数学运算的

2）算术运算符、赋值运算符、比较运算符、逻辑运算符

2.1.1算术运算符

运算符

说    明

+

加运算，求两个数或表达式相加的和，如6+8

-

减运算，求两个数或表达式相减的差

*

乘运算，求两个数或表达式相乘的积

/

除运算，求两个数或表达式相除的商

2.1.2赋值运算符

=

把一个数或变量或表达式赋值给另一变量，如：Name='王华'

2.1.3逻辑运算符

AND

当且仅当两个布尔表达式都为true时，返回TRUE。

OR

当且仅当两个布尔表达式都为false，返回FALSE。

NOT

对布尔表达式的值取反

2.1.4比较运算符

=

等于，例如：age=23

> 

大于，例如：price>100

< 

小于

<> 

不等于

>=

大于等于

<=

小于等于

!=

不等于（非SQL-92标准）

2.2什么是查询？

1)查询产生一个虚拟表

2)看到的是表形式显示的结果，但结果并不真正存储

3)每次执行查询只是从数据表中提取数据，并按照表的形式显示出来

2.2.1仅查询员工编号、员工姓名、部门编号信息

select empno,ename,deptno from emp;

2.2.2查询员工姓名和他们的年薪

select ename,sal*12 from emp;

注：取多个字段时用逗号分隔，字段名可参与表达式运算

2.2.3简单的数学运算，但会出现冗余

select 2*3 from emp;

2.2.4简单的数学运算，可消除冗余

(dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录，可以用它来做很多事情。)

select 2*3 from dual

2.2.5获得当前日期时间

select sysdate from dual

2.2.6使用字段别名

select ename,sal*12 as  annual_sal from emp;

select ename,sal*12  annual_sal from emp;

select ename,sal*12 "annual sal" from emp;

2.2.7查询员工姓名和补贴

select ename,comm from emp

2.2.8查询员工姓名和年收入

select ename,sal*12+comm total from emp

注：任何含有空值的数学表达式返回结果依然是空值

2.2.9字符串连接

select ename||sal from emp

select ename||‘string' from emp

select ename||‘str‘’ing' from emp

2.2.10查询部门编号

select deptno from emp;出现冗余

2.2.11查询部门编号，消除冗余

select distinct deptno from emp

2.2.12同时查询部门编号与岗位，消除冗余

select distinct deptno,job from emp

注：使用关键字distinct消除冗余

查询部门编号是10的员工信息

select * from emp where deptno=10;

查询部门编号不是10的员工信息

select ename, deptno from emp where deptno <>10

查询“CLARK”员工的信息

select * from emp where ename=‘CLARK’;

查询薪水多于1500元的员工

select ename,sal from emp where sal>1500;

      注：where用于指定筛选条件

查询排在“FORD”之后的员工信息

select ename from emp where ename > 'FORD‘

查询薪水在800至1500元之间的员工信息

select ename,sal from emp where sal >= 800 and sal <= 1500;

select ename,sal from emp where sal between 800 and 1500;

      注：字符串比较按照字典排序

查询在1981年12月3日之后入职的员工信息

select ename,sal,hiredate from emp where hiredate > to_date('1981-03-11 00:00:00','yyyy-mm-dd hh24:mi:ss');

不区分大小写，mm=月，mi=分

注：比较日期时可用

               =表示当时

              <表示之前

               >表示之后

模糊查询

查询姓名中包含”ALL”字符串的员工信息

select ename from emp where ename like '%ALL%';

查询姓名中第二个字母是’A’的员工信息

select ename from emp where ename like '_A%';

注：

百分号%:0-n 

下划线_:1-1

like '%\%%' 默认转义字符\

like '%$%%' escape '$' 指定转义字符

查询没有津贴的员工信息

select ename,sal,comm from emp where comm is null

查询有津贴的员工信息

select ename,sal,comm from emp where comm is not NULL

查询薪资指定的员工信息

select ename,sal,comm from emp where sal in (800,1500,2000);

查询指定多个姓名的员工信息

select ename,sal,comm from emp where ename in ('SMITH','SCOTT','KING');

      注：

            关键字不区分大小写

            is null、is not null的用法

            in的用法

查询在10号部门工作并且薪水多于1000元的员工信息

select ename,deptno,sal from emp where deptno=10 and sal>1000

查询在10号部门工作或者薪水多于1000元的员工信息

select ename,sal from emp  where deptno=10 or sal>1000

查询在10号部门工作但薪水只有800或1500元的员工信息

select ename,sal from emp where deptno=10 and sal  in  (800,1500);

      注：not、and、or的用法

排序

按员工编号升序排列显示员工信息

select empno,ename from emp order by empno asc;

按部门编号降序排列显示员工信息

select * from dept  order by deptno desc

按部门编号升序且姓名降序的方式排列员工信息

select ename,sal,deptno from emp order by deptno asc ,ename desc;

注：使用order by排序，升序关键字asc，默认值，可省略，

        降序关键字desc，不能省略

按员工编号升序排列不在10号部门工作的员工信息

select empno,ename from emp where deptno<>10 order by empno asc;

查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal>800 order by annual_sal desc;

聚合函数

SUM()

求和    

select  sum(sal)  from emp ;

AVG()

求平均值

select  avg(sal)  from emp

COUNT()

计数

select count(ename) from emp;

MAX()

求最大值

select  max(sal)  from emp ;

MIN()

求最小值

select min(sal) from emp where id=1;

分组查询

求每个部门的平均薪水

select deptno, avg(sal) from emp group by deptno;

求各个部门的最高薪水

select deptno,max(sal) from emp group by deptno;

求每个部门每个岗位的最高薪水

select deptno,job,max(sal) from emp group by deptno,job;

      注：出现在select中的字段必须要么出现在group by中，

            要么出现在聚合函数中

求平均薪水大于2000的部门编号

select deptno, avg(sal) from emp    group by deptno having avg(sal)>2000

将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列

select deptno,avg(sal) from emp     where sal>1200      group by deptno

      having avg(sal)>1500    order by avg(sal) desc

2.多表链接查询

交叉连接

select ename,dname from emp,dept;

select ename,dname from emp cross join dept;

内连接

查询员工姓名及所在部门名称

select ename,dname from emp,dept where emp.deptno=dept.deptno

select ename,dname from emp [inner] join dept on emp.deptno=dept.deptno

求每个员工及他的经理姓名

select empno,ename,mgr from emp  --未实现

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

不等值连接

查询员工姓名及其薪水等级

select ename,grade from emp e join salgrade s on e.sal between s.losal and s.hisal

输出非办事员的员工姓名，所在部门名称及薪水等级

select ename,dname,grade from emp e,dept d,salgrade s     where e.deptno=d.deptno and e.sal between s.losal and s.hisal and job <> ‘CLERK’

输出第二个字母不是”A”员工姓名，所在部门名称及薪水等级

select ename,dname,grade from emp e join dept d on e.deptno=d.deptno

      join salgrade s on e.sal between s.losal and s.hisal where ename not like '_A%';

求各个部门薪水最高的员工信息

select e.ename,e.deptno,e.sal from(select deptno,max(sal) s from emp group by deptno ) t inner join emp e on t.s=e.sal;

左外连接

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

右外连接

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

全外连接

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

1.子查询

语法：在一个select语句中出现了第二个select语句

求最高薪水的员工信息

select ename,sal from emp  where sal=(select max(sal) from emp);

求多于平均薪水的员工信息

select ename,sal from emp where sal> (select avg(sal) from emp);

2.分页查询

分页关键字：rownum

输出记录的序号

select rownum r,ename from emp;

输出前五条记录

select empno,ename from emp where rownum<=5

      注：只能用<、<=,不能用= 、>、 >=

取出第十条记录之后的数据

select ename from (select rownum r,ename from emp) where r>10

按薪水由高至低排列，找出第6-10名的员工信息

select *from( select rownum r,ename,sal from ( select ename,sal

    from emp order by sal desc) ) where r>=6 and r <=10

 

 

 

3.单行函数查询

使用函数可以大大提高SELECT语句操作数据库的能力

Oracle中函数划分为单行函数和多行函数

单行函数作用于数据库表的某一行并返回一个值

字符函数

数字函数

日期函数

转换函数

其他函数。

多行函数基于数据库表多行进行运算，返回一个值

例如对多行记录的某个字段进行求和、求最大值运算等

常用字符函数

INITCAP (char)

首字母大写

initcap ('hello')

Hello

LOWER (char)

转换为小写

lower ('FUN')

fun

UPPER (char)

转换为大写

upper ('sun')

SUN

LTRIM (char, set)

左剪裁

ltrim ('xyzadams', 'xyz')

adams

RTRIM (char, set)

右剪裁

rtrim ('xyzadams', 'ams')

xyzad

TRANSLATE (char, from, to)

按字符翻译

translate ('jack', 'abcd', '1234')

j13k

REPLACE (char, search_str, replace_str)

字符串替换

replace ('jack and jue', 'j', 'bl')

black and blue

INSTR (char, substr[, pos])

查找子串位置

instr ('worldwide', 'd')

5

SUBSTR (char, pos, len)

取子字符串

substr ('abcdefg',3,2)

cd

CONCAT (char1, char2)

连接字符串

concat ('Hello', 'world')

Helloworld

常用字数字函数

ABS(n)

取绝对值

abs(-15)

15

CEIL(n )

向上取整

ceil(44.778)

45

SIN(n)

正弦

sin(1.571)

.999999979

COS(n)

余弦

cos(0)

1

SIGN(n)

取符号

sign(-32)

-1

FLOOR(n)

向下取整

floor(100.2)

100

POWER(m,n )

m的n次幂

power(4,2)

16

MOD(m,n)

取余数

mod(10,3)

1

ROUND(m,n)

四舍五入

round(100.256,2)

100.26

TRUNC(m,n)

截断

trunc(100.256,2)

100.25

SQRT(n)

平方根

sqrt(4)

2

常用的日期函数

MONTHS_BETWEEN

返回两个日期间的月份

months_between ('04-11月-05','11-1月-01')

57.7741935

ADD_MONTHS

返回把月份数加到日期上的新日期

add_months('06-2月-03',1)

add_months('06-2月-03',-1)

06-3月-03

06-1月-03

NEXT_DAY

返回指定日期后的星期对应的新日期

next_day('06-2月-03','星期一')

10-2月-03

LAST_DAY

返回指定日期所在的月的最后一天

last_day('06-2月-03')

28-2月-03

ROUND

按指定格式对日期进行四舍五入

round(to_date('13-2月-03'),'YEAR')

round(to_date('13-2月-03'),'MONTH')

round(to_date('13-2月-03'),'DAY')

01-1月-03

01-2月-03

16-2月-03

TRUNC

对日期按指定方式进行截断

trunc(to_date('06-2月-03'),'YEAR')

trunc(to_date('06-2月-03'),'MONTH')

trunc(to_date('06-2月-03'),'DAY')

01-1月-03

01-2月-03

02-2月-03

常用的转换函数

TO_CHAR

转换成字符串类型

to_char(1234.5, '$9999.9')

$1234.5

TO_DATE

转换成日期类型

to_date('1980-01-01', 'yyyy-MM-dd')

01-1月-80

TO_NUMBER

转换成数值类型

to_number('1234.5')

1234.5

常用的其他函数

NVL(EXP1, EXP2)

如果exp1的值为null，则返回exp2的值，否则返回exp1的值

NVL2(EXP1, EXP2, EXP3)

如果exp1的值为null，则返回exp3的值，否则返回exp2的值

DECODE(VALUE,IF1,THEN1,

IF2,THEN2,……,ELSE)  

如果value的值为if1，则返回then1的值，如果value的值为if2，则返回then2的值，……，否则返回else值

常用的多行函数

SUM()

求和    

select sum(sal) from emp;

AVG()

求平均值

select avg(sal) from emp;

COUNT()

计数

select count(sal) from emp;

MAX()

求最大值

select max(sal) from emp;

MIN()

求最小值

select min(sal) from emp;

其它函数

GREATEST
返回一组表达式中的最大值,即比较字符的编码大小

LEAST
返回一组表达式中的最小值 

select user from dual;

show user

NVL(null_column,default_value);

select ename sal*12 nvl(comm,0);

 

4.列转行函数

将emp表中的雇员名称按一行显示

select  wm_concat(ename) as ename from emp

 

5.分析函数查询

分析函数是什么？
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。        

  

分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

          

分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

    

分析函数例子(在scott用户下模拟)

示例目的：显示各部门员工的工资，并附带显示该部分的最高工资。

SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

示例目的：按照deptno分组，然后计算每组值的总和

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

示例目的：对各部门进行分组，并附带显示第一行至当前行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

示例目标：当前行至最后一行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

示例目标：当前行的上一行(rownum-1)到当前行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

示例目标：   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总     

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

两个order by的执行时机
分析函数（以及与其配合的开窗函数over（））是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果：

a) 两者一致：如果sql语句中的order by满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，

那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) 两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，

那么sql语句中的排序将最后在分析函数分析结束后执行排序。

开窗函数over（）分析函数中的分组/排序/窗口
开窗函数over（）分析函数包含三个分析子句：分组子句(partition by)， 排序子句(order by)， 窗口子句(rows)
窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。

窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提。

       窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：
第一行是 unbounded preceding，
当前行是 current row，
最后一行是 unbounded following，

注释：

当开窗函数over（）出现分组(partition by)子句时，

unbounded preceding即第一行是指表中一个分组里的第一行， unbounded following即最后一行是指表中一个分组里的最后一行；

当开窗函数over（）省略了分组(partition by)子句时， 

unbounded preceding即第一行是指表中的第一行， unbounded following即最后一行是指表中的最后一行。

例如:

last_value(sal) over(partition by deptno

                     order by sal

                     rows between unbounded preceding and unbounded following)

以上示例指定窗口为整个分组。而出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！

而无论是否省略分组子句，如下结论都是成立的：

1、窗口子句不能单独出现，必须有order by子句时才能出现。

2、当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行，即在当前组中，第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

所以，

 lag(sal) over(order by sal) 解释

over（order by salary）表示意义如下：

首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，

然后，在当前组（此时为整个表的数据行）这个范围里执行排序（即order by salary），

最后，我们知道分析函数lag(sal)在当前组（此时为整个表的数据行）这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。

 

Oracle的LAG和LEAD分析函数

Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。

这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

lag(exp_str,offset,defval) over()

offset 是exp_str字段的偏移量，即 offset 为N ，指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。

 

比如说，

 

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为3时表示的是我们所要找的数据行就是表中的第一行（即4-3=1）。

 

offset的默认值为1！

 

 

 

lag（）函数的返回值为在表中从当前行位置向前数N行的那一行上exp_str字段的值。

 

当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值。

 

 

比如说，

 

在表中，假设当前我们说的当前行在表中排在第四行，则offset 为6时表示的是我们所要找的数据行就是表中的第-2行（即4-6=-2），这就表示我们所要找的数据行不在表中已经超出表的范围了，所以lag（）函数将defval这个参数值作为函数的返回值。

 

default 参数的默认值为空值null，即如果在lag（）函数中没有显式设置default 参数值时lag（）函数的返回值为空值null。

Lead函数的用法类似。

 select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;

设置了default 值之后第一行对应的值为500

select ename,job,sal ,lag(sal,1,500) over(order by sal) last_sal from emp;

指定offset的值为2时

select ename,job,sal ,lag(sal,2) over(order by sal) last_sal from emp;

 

select ename,job,sal ,lead(sal,1) over(order by sal) last_sal from emp;

select ename,job,sal ,lead(sal,2) over(order by sal) last_sal from emp;

lead 的offset N 是以记录的第N行和第一做对比注意末尾的 null 值！

Lead和Lag函数也可以使用分组，以下是使用job 分组的例子：

select ename,job,sal ,lead(sal,1) over(partition by job order by sal) last_sal from emp;

 select ename,job,sal ,lag(sal,1) over(partition by job order by sal) last_sal from emp;

                                                                                                                                                            Oracle视图序列 

 

 

                                    

什么是集合运算？
集合运算是用来把两个或多个查询的结果集做并、交、查的集合运算，包含集合运算的查询称为复合查询
联合运算（union） [去重 不排序]
1.1联合运算是从两个查询返回除去重复值后的结果
Select empno,ename from emp where deptno=10 union
Select empno,ename from emp where sal>3000
1.2完全联合运算（union all）[不去重 升序]
完全联合运算是从每个查询返回包括所有重复的结果。
注意：使用union all会比union的速度快，因为省去了去掉重复记录的时间
Select empno,ename from emp where deptno=10 union all
Select empno,ename from emp where sal>3000
1.3相交运算（intersect） [不去重 升序]
相交运算返回多个查询中所有相同的行。
Select empno,ename from emp where deptno=10 intersect
Select empno,ename from emp where sal>3000
1.4相减运算（minus）[不去重 升序]
相减运算返回在第一个查询中而不在第二个查询中的行。
(第一个 SELECT 语句减第二个 SELECT 语句)
Select empno,ename from emp where deptno=10 minus
Select empno,ename from emp where sal>3000
集合运算要看的原则
在两个select列表中的表达式必须在数目和数据类型上相匹配。
可以用圆括号改变执行的顺序。
order by 子句只能出现在语句的最后，从第一个select语句接收列名、别名或者位置记号。
Select deptno,to_char(null),ename,hiredate from emp
Union Select deptno,loc,to_char(null),to_date(null) from dept

2. 操作视图
什么是视图?
 视图以经过定制的方式显示来自一个或多个表的数据
 视图可以视为“虚拟表”或“存储的查询”
 创建视图所依据的表称为“基表”
 视图的优点有：
 提供了另外一种级别的表安全性
 隐藏的数据的复杂性
 简化的用户的SQL命令
 隔离基表结构的改变
 通过重命名列，从另一个角度提供数据

创建视图的语法:
CREATE [OR REPLACE] VIEW 视图名称
AS
(查询语句)
例子:
create or replace view myview
As select a.*,b.dname as dtn,b.loc from scott.emp a inner join scott.dept b on a.deptno=b.deptno;
查询
select * from myview
删除视图的语法：
DROP VIEW view_name
 在视图上可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
 视图上的DML语句有如下限制：
 只能修改一个底层的基表
 如果修改违反了基表的约束条件，则无法更新视图
 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图
 如果视图包含伪列或表达式，则将无法更新视图
3. 序列
序列是什么？
1序列是用于生成唯一、连续序号的对象
2序列可以是升序的，也可以是降序的
3使用CREATE SEQUENCE语句创建序列

 


create sequence mysq
start with 1 --起始值
increment by 1 --步长
maxvalue 500000
-- cycle --是否从头开始，否：nocycle
-- minvalue 和 maxvalue只能用其一
-- cache -- 缓存

通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
指定序列的下一个值
SELECT mysq.NEXTVAL FROM dual
检索序列的当前值
SELECT mysq.CURRVAL FROM dual;
插入语句调用序列获取唯一值，直接在插入值的地方调用序列名.NEXTVAL
insert into emp values(序列名.NEXTVAL,值2，值3...)
更改和删除序列
使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH参数
ALTER SEQUENCE 序列名 MAXVALUE 5000 CYCLE
使用DROP SEQUENCE语句删除序列
DROP SEQUENCE 序列名        

                                                                                                                                                                                                                   Oracle存储过程触发器

首先打开plsql工具，点击File---->new --->Test window

 

上边就是一个plsql能够执行的基本架构（并不是存储过程），其中declare 顾名思义就是描述的意思就是在这个下边可以对变量进行声明。

声明变量有大概如下两三种方式

1. v_temp     varchar(10)

2. v_temp     tablename.property%type（表明属性名%type就是该表属性的类型，这样可以灵活使用）

3. v_stu      student%rowtype;（这种类型就是定义一个变量为表数据的行类型用于接受查询的一行数据）

接下来可以看到begin，这里的begin类似于我们的java中的main函数这里可以对变量进行赋值且程序是从这来开始来正式执行的。所以一般我们在这里对变量进行赋值

declare

  -- Local variables here

  --姓名

  v_name varchar2(50) :='张三丰';

  --薪水

  /* v_salary number(6,2);*/

  v_salary emp.ename%type;

  --地址

  v_address varchar2(100);

 

  --v_stu student%rowtype;

begin

  -- Test statements here

  --dbms_output.put_line('hello world');

  v_salary:=1500;

  --语句赋值操作。

  dbms_output.put_line('薪水---〉' || v_salary);

end;

上边这里begin下边就是对v_salary进行赋值，这里有一点需要注意：plsql中的复制是:=而不是单个=，单个=是判断是否相等意思。

输出操作语句  dbms_output.put_line()

 

例子：

declare

  vcc number;

begin

  vcc:=1;

  loop

      exit when vcc>10;

      dbms_output.put_line(vcc);

      vcc:=vcc+1;

  end loop;

end;

2 游标

在学习jdbc操作的时候返回的结果集ResultSet其实这里的结果集就可以通过游标来取的

先来说说游标的属性：

--  %rowcount  整型  获得fetch语句返回的数据行数

--%found    布尔型    最近的fetch语句返回一行数据则为真，否则为假

--%notfound  布尔型   与%found属性的返回值相反

--%isopen   布尔型    游标已经打开则为真，否则为假

--其中%notfound是游标中找不到元素时候返回true,通常用来判断推退出循环。

游标的使用需要有四个步骤

声明------》打开-----》取值-----》关闭

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

--声明游标：利用关键字 cursor  +  任意名字[参数列表（可有可无）]  +is  +你的sql语句

--打开游标：open

--提取值：fetch

--关闭游标：close

用游标提取emp表中7788雇员的名称和职务

DECLARE

  v_ename VARCHAR2(10);

  v_job   VARCHAR2(10);

  CURSOR emp_cursor IS

    SELECT ename, job FROM emp WHERE empno = 7788;

BEGIN

  OPEN emp_cursor;

  FETCH emp_cursor

    INTO v_ename, v_job;

  DBMS_OUTPUT.PUT_LINE(v_ename || ',' || v_job);

  CLOSE emp_cursor;

END;

用游标提取emp表中7788雇员的姓名、职务和工资

DECLARE

  CURSOR emp_cursor IS

    SELECT ename, job, sal FROM emp WHERE empno = 7788;

  emp_record emp_cursor%ROWTYPE;

BEGIN

  OPEN emp_cursor;

  FETCH emp_cursor

    INTO emp_record;

  DBMS_OUTPUT.PUT_LINE(emp_record.ename || ',' || emp_record.job || ',' ||

                       emp_record.sal);

  CLOSE emp_cursor;

END;

显示工资最高的前3名雇员的名称和工资

DECLARE

  V_ename VARCHAR2(10);

  V_sal   NUMBER(5);

  CURSOR emp_cursor IS

    SELECT ename, sal FROM emp ORDER BY sal DESC;

BEGIN

  OPEN emp_cursor;

  FOR I IN 1 .. 3 LOOP

    --这里的I就是隐式游标 

    FETCH emp_cursor

      INTO v_ename, v_sal;

    DBMS_OUTPUT.PUT_LINE(v_ename || ',' || v_sal);

  END LOOP;

  CLOSE emp_cursor;

END;

循环游标

DECLARE

  CURSOR emp_cursor IS

    SELECT empno, ename FROM emp;

BEGIN

  FOR Emp_record IN emp_cursor LOOP

    DBMS_OUTPUT.PUT_LINE(Emp_record.empno || ',' || Emp_record.ename);

  END LOOP;

END;

3 存储过程

存储过程概念：(Store Procedure)应用于其他编程语言中的过程.

简介：存储过程不需要有返回值，但是可以有参数。参数分三种：IN(输入)、OUT(输出)、INOUT(输入输出)。函数适用于复杂的统计和计算，最终将结果返回，而存储过程则更适合对数据的更新，尤其是大量数据的更新。

提高数据库执行效率：如果sql链接数据库，更新复杂而频繁，则需要频繁的链接数据库，连接数据库是非常耗时和耗资源的，如果将所有的工作交由存储过程来完成则大大减少数据库的链接频率，从而提高数据库的执行效率。

安全性：存储过程是作为对象存储在数据库中的，可对其分配权限来控制整个操作的安全性。

可复用：是函数的重要属性之一，由于存储过程的可复用性需求，也是提出的原因。

创建：存储过程课实现数据库的曾改删查，也可实现复杂的运算，但不能直接执行数据库定义语言，即DDL操作。

 

右键单击--->new

 

然后name写上存储过程名，parameters可以不写

create or replace procedure myproc is

begin

  dbms_output.put_line('hello world');

end myproc;

存储过程不能直接运行，需要通过pl/sql =》 test window或sqlplus（exec myproc；）

declare

 

begin

  myproc;

end;

 

包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT

IN表示输入参数，按值传递方式。

OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。

IN OUT 即可作输入参数，也可作输出参数。

参数的数据类型只需要指明类型名即可，不需要指定宽度。

参数的宽度由外部调用者决定。

过程可以有参数，也可以没有参数

变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。

变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。 异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选 结束块：由end关键字结果。

带变量的存储过程

CREATE OR REPLACE PROCEDURE proc1 (

    para1 varchar2,

    para2 OUT varchar2,

    para3 IN OUT varchar2

)

AS

    v_name varchar2(20);

BEGIN

    --para1 := 'aaa';--执行错误，不能赋值

    para2 := 'bbb';

    v_name := 'zhangsf';

    para3 := v_name;--可以赋值

    dbms_output.put_line('para1:' || para1);

    dbms_output.put_line('para2:' || para2);

    dbms_output.put_line('para3:' || para3);

    NULL;

END;

执行

declare

  p1 varchar2(10);

  p2 varchar2(3); --返回'bbb'是三个字符

  p3 varchar2(7); --返回'zhangsf'是七个字符

begin

  p1 := 'aaa';

  proc1(p1,p2,p3);

end;

 

4 存储函数

存储函数必须有且只有一个返回值和参数类型

 

右键点击---->new

 

多了返回类型；

create or replace function getName return varchar2 as

begin

  return 'wyc';

end getName;

调用

declare

  name varchar2(10);

begin

  name := getName();

  dbms_output.put_line(name);

end;

查询指定员工编的年收入(年薪+佣金)(in的用法,默认是in)

--创建

create or replace function findEmpIncome(pempno in number) return number as

  income emp.sal%type;

begin

  -- 考虑到emp表中一些员工的佣金为null

  select sal + NVL(comm, 0) into income from emp where empno = pempno;

  return income;

end;

调用

--调用

declare

  income emp.sal%type;

begin

  income := findEmpIncome(7369); --员工编号

  dbms_output.put_line('收入是' || income);

end;

前面说过存储函数只能有一个返回值,如果有多个返回值的话要结合out参数进行处理

分别使用存储过程及存储函数计算个人税收(存储函数使用到了in out参数)

--创建存储过程 (注意10%一定要写成0.1)

create or replace procedure get_rax(psal in number, rax out number) as

  money number(6);

begin

  money := psal - 3500;

  if money <= 1500 then

    rax := money * 0.03 - 0;

  elsif money <= 4500 then

    rax := money * 0.1 - 105;

  elsif money <= 9000 then

    rax := money * 0.2 - 555;

  elsif money <= 35000 then

    rax := money * 0.25 - 1005;

  elsif money <= 55000 then

    rax := money * 0.3 - 2755;

  elsif money <= 80000 then

    rax := money * 0.35 - 5505;

  else

    rax := money * 0.45 - 13505;

  end if;

end;

调用

declare

  rax number(20);

begin

  get_rax(5000, rax);

  dbms_output.put_line('您要交的税为 : ' || rax);

end;

 

存储函数

pname要回传给调用者,从而输出姓名

create or replace function get_rax2(pname in out varchar2, psal in number)

  return number as

  money number(6);

  rax   number(20);

begin

  money := psal - 3500;

  if money <= 1500 then

    rax := money * 0.03 - 0;

  elsif money <= 4500 then

    rax := money * 0.1 - 105;

  elsif money <= 9000 then

    rax := money * 0.2 - 555;

  elsif money <= 35000 then

    rax := money * 0.25 - 1005;

  elsif money <= 55000 then

    rax := money * 0.3 - 2755;

  elsif money <= 80000 then

    rax := money * 0.35 - 5505;

  else

    rax := money * 0.45 - 13505;

  end if;

  return rax;

end;

调用

declare

  rax  number(20);

  name varchar2(10);

begin

  name := 'xiaoming';

  rax  := get_rax2(name, 5000);

  dbms_output.put_line('尊敬的' || name || ',您应交的税为 :' || rax);

end;

 

5 触发器

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行，触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用

 

触发器类型:

1 DML触发器

对数据表进行DML语句操作（如insert、update、delete）时所触发的触发器，可以分为：

语句级触发器或行级触发器：行级触发器会对数据库表中的受影响的每一行触发一次触发器代码，语句级触发器则只触发一次，与语句所影响到的行数无关

before触发器或after触发器：before触发器在触发事件发生之前执行触发器代码，after触发器则在触发事件发生之后执行

语法：

create [or replace] trigger trigger_name

{before | after} trigger_event

on table_name

[for each row]

[when trigger_condition]

trigger_body

语法解释：

trigger_name：触发器名称

before | after : 指定触发器是在触发事件发生之前触发还暗示发生之后触发

trigger_event：触发事件，在DML触发器中主要为insert、update、delete等

table_name：表名，表示发生触发器作用的对象

for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器

when trigger_condition：添加的触发条件

trigger_body：触发体，是标准的PL/SQL语句块

 

语句级(STATEMENT)触发器和行级(ROW)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次）

 

2 替代触发器

 对视图进行操作时定义的触发器，替代触发器只能定义在视图上

语法：

create [or replace] trigger trigger_name --触发器名称

instead of trigger_event --触发事件

on view_name --视图名称

for each row  --替代触发器必须指定为行级的触发器

[when trigger_condition] --触发条件

trigger_body --触发体，PL/SQL块

 

3 系统触发器

对数据库实例或某个用户模式进行操作时定义的触发器，可以分为：

数据库系统触发器和用户触发器

案例

DML触发器的案例都是基于student表和stu_log表来进行的，所以先创建student表和stu_log表

create table STUDENT   ---创建student表

(

  id        NUMBER(19), --id

  stu_no    VARCHAR2(20), --学号

  stu_name  VARCHAR2(32), --姓名

  stu_age   NUMBER,  --年龄

  stu_major VARCHAR2(32) --专业

)

create table STU_LOG   ---创建stu_log表，用于记录对student表的操作日志

(

  log_id     NUMBER,  --日志id

  log_action VARCHAR2(100),  --操作名称

  log_date   DATE,  --操作时间

  log_message   VARCHAR2(32) --

)

 

a行级触发器（before触发器）

create or replace trigger modify_stu

before insert on student

for each row

declare

next_id number;

begin

  select seq_test.nextval into next_id from dual;

  :new.id :=next_id;

end;

需要有“seq_test”序列

执行新增语句后发现id有值

insert into student(stu_no,stu_name,stu_age,stu_major) values('NO1','张三',20,'中文系');

 

如果删除触发器，新增的记录id无值

 

b行级触发器（before触发器）

create or replace trigger modify_stu

  after insert or delete or update of stu_name on student

  for each row

begin

  if inserting then

    insert into stu_log values (1, 'insert', sysdate, :new.stu_name);

  elsif deleting then

    insert into stu_log values (2, 'delete', sysdate, :old.stu_name);

  elsif updating then

    insert into stu_log values (3, 'update_old', sysdate, :old.stu_name);

    insert into stu_log values (4, 'update_new', sysdate, :new.stu_name);

  end if;

end;

执行

insert into student values(1,'NO2','李四',21,'数学系');  --插入一条数据

delete student where stu_name='张三';   --删除一条数据

update student set stu_age=19 where stu_name='李四';  --修改李四的年龄

update student set stu_name='王二' where stu_name='李四';  --修改李四的名称

 

c语句级触发器（before触发器）：用来控制对表的修改

create or replace trigger modify_stu

  before insert or update or delete on student

begin

  if deleting then

    raise_application_error(-20001, '该表不允许删除数据');

  elsif updating then

    raise_application_error(-20002, '该表不允许修改数据');

  elsif inserting then

    raise_application_error(-20003, '该表不允许插入数据');

  end if;

end;

 

说明：

其中，new是新插入的数据，old是原来的数据

 

insert只会有new，代表着要插入的新记录

delete只会有old，代表着要删除的记录

update由于执行的是先删除旧的记录，再插入新的记录，因此new和old都会有，且含义与上面的相同，update触发器，可根据具体需求选择记录旧记录还是新记录

 

                                                                                plsql

1.索引

概念:索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引.

   从物理上说，索引通常可以分为：分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。其中，B树索引属于最常见的索引。

1) 类似书的目录结构

2) Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

3) 索引直接指向包含所查询值的行的位置，减少磁盘I/O

4) 与所索引的表是相互独立的物理结构

5) Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

6) 语法：CREATE INDEX index ON table (column[, column]...);

7) B-tree结构(非bitmap)：

1.1唯一索引

1)何时创建：当某列任意两行的值都不相同

2)当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

3)语法：CREATE UNIQUE INDEX index ON table (column);

1.2组合索引

1)何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

2) 组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

1.3位图索引

1)何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。Where 条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap

2) 结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

3)语法：CREATE BITMAP INDEX index ON table (column[, column]...);

1.4 基于函数的索引

1)何时创建：在WHERE条件语句中包含函数或者表达式时

2)函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

3)语法：CREATE INDEX index ON table (FUNCTION(column));

1.5反向键索引

目的：比如索引值是一个自动增长的列

 

多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索引。

性能问题

1.6键压缩索引

比如表landscp的数据如下：

site feature job

Britten Park, Rose Bed 1, Prune

Britten Park, Rose Bed 1, Mulch

Britten Park, Rose Bed 1,Spray

Britten Park, Shrub Bed 1, Mulch

Britten Park, Shrub Bed 1, Weed

Britten Park, Shrub Bed 1, Hoe

……

查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。

Create index zip_idx

on landscp(site, feature, job)

compress 2;

将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。

Prefix 0: Britten Park, Rose Bed 1

Prefix 1: Britten Park, Shrub Bed 1

实际所以的结构为：

0 Prune

0 Mulch

0 Spray

1 Mulch

1 Weed

1 Hoe

特点：组合索引的前缀部分具 有非选择性时，考虑使用压缩。减少I/O,增加性能。

九、 索引组织表(IOT)

将表中的数据按照索 引的结构存储在索引中，提高查询速度。

牺牲插入更新的性能，换取查询 性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。

必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。

十、 分区索引

A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

 

 

 

2.PL/SQL查询

位置:登陆plsql空白处右键选择Test-window

PL/SQL是一种块结构的语言，这意味着PL/SQL程序被划分和编写代码的逻辑块。每块由三个子部分组成：

S.N.

段和说明

1

声明 
此部分开头使用关键字DECLARE。它是一个可选的部分，并限定在该程序中使用的所有变量，游标，子程序，和其他元素。

2

可执行命令
此部分是封闭关键字BEGIN和END，这是一个强制性的部分。它由程序的可执行文件的PL/SQL语句。它应具有至少一个可执行的代码行，这可能仅仅是一个空命令，以指示什么都不执行。

3

异常处理
此部分开头使用关键字EXCEPTION。此部分又是可选的，含有异常，在程序处理错误中。

每一个PL/SQL语句都以分号（;）结束。可嵌套在使用BEGIN和END等PL/SQL块。这里是一个PL/SQL块的基本结构：

DECLARE

   <declarations section>

BEGIN

   <executable command(s)>

EXCEPTION

   <exception handling>

END;

'Hello World' 示例:

DECLARE

   message  varchar2(20):= 'Hello, World!';

BEGIN

   dbms_output.put_line(message);

END;

PL/SQL标识符是常量，变量，异常，过程，游标和保留字。标识符是由一个字母后面可以跟更多的字母，数字，美元符号，下划线和数字符号，并且不得超过30个字符。

默认情况下，标识符不区分大小写。所以，可以使用 integer 或 INTEGER 表示数值。不能使用保留关键字作为标识符。

PL/SQL分隔符

分隔符是一个具有特殊意义的符号。以下是在PL/SQL分隔符的列表：

分隔符

描述

+, -, *, /

加法，减法/否定，乘法，除法

%

属性索引

'

字符串分隔符

.

组件选择

(,)

表达式或列表分隔符

:

主机变量指示符

,

项目分离符

"

带引号的标识符分隔符

=

关系运算符

@

远程访问指示符

;

声明终止符

:=

赋值运算符

=>

操作符关联

||

连接运算符

**

乘方运算符

<<, >>

标签分隔符（开始和结束）

/*, */

多行注释分隔符（开始和结束）

--

单行注释指示符

..

范围操作

<, >, <=, >=

关系运算符

<>, '=, ~=, ^=

不同版本的不相等的

PL/SQL注释

-    单行注释
/*
 * 多行注释
 */

PL/SQL程序单元

下列任何一个

l  PL/SQL 块

l  函数

l  包

l  包体

l  过程

l  触发器

l  类型

l  类型体

PL/SQL数据类型

分类

描述

标量

单值没有内部组件，如NUMBER, DATE 或 BOOLEAN

大对象(LOB)

指向其他数据项，如文本，图形图像，视频剪辑和声音等存储大对象

复合

具有可单独访问的内部组件的数据项。例如，集合和记录

引用

指向其他数据项

PL/SQL标量数据类型和子类型

数据类型

描述

数字

在其上执行的算术运算的数值

字符

代表单个字符或字符的字母数字字符串值

布尔

在其上执行的逻辑运算的逻辑值

日期时间

日期和时间

PL/SQL数值数据类型和子类型

数据类型

描述

PLS_INTEGER

通过2,147,483,647到-2147483648范围内有符号整数，以32位表示

BINARY_INTEGER

通过2,147,483,647到-2147483648范围内的有符号整数，以32位表示

BINARY_FLOAT

单精度IEEE 754格式的浮点数

BINARY_DOUBLE

双精度IEEE 754格式的浮点数

NUMBER(prec, scale)

定点或浮点数在范围1E-130至（但不包括）绝对值1.0E126。 NUMBER变量也可以表示0

DEC(prec, scale)

ANSI具体的定点类型使用38位小数最大精度

DECIMAL(prec, scale)

IBM特定的固定点型具有38位小数最大精度

NUMERIC(pre, secale)

浮点类型具有38位小数最大精度。

DOUBLE PRECISION

具有126个二进制数字最大精度ANSI特定浮点型（约38位十进制数）

FLOAT

具有126个二进制数字（约38位十进制数）最大精度ANSI和IBM特定的浮点型

INT

具有38位小数最大精度ANSI具体的整数类型

INTEGER

ANSI和IBM的38位小数最大精度具体的整数类型

SMALLINT

ANSI和IBM的38位小数最大精度具体的整数类型

REAL

具有63位二进制数字最大精度浮点型（大约18位小数）

下面是一个有效的声明：

DECLARE

   num1 INTEGER;

   num2 REAL;

   num3 DOUBLE PRECISION;

BEGIN

   null;

END;

PL/SQL布尔数据类型

在逻辑操作中使用布尔数据类型存储的逻辑值。逻辑值为：true 和 false 的布尔值以及 NULL 值。

但是，SQL没有数据类型等同于布尔。因此，布尔值不能用于：

l  SQL语句

l  内置SQL函数（如TO_CHAR）

l  从SQL语句调用PL/ SQL函数

PL/SQL日期时间和间隔类型

DATE数据类型存储固定长度的日期时间，其中包括每天在几秒钟内从午夜开始的时间。有效的日期范围从公元前4712年1月1日至999912月31日。

默认的日期格式由Oracle初始化参数NLS_DATE_FORMAT设置。例如，默认的可以是“DD-MON-YY'，它包括一个两位数字的月份中的日期，月份名称的缩写，以及年的最后两位数字，例如，01- OCT-12。

每个日期，包括世纪，年，月，日，时，分，秒。下表显示了每个字段的有效值：

字段名称

有效日期时间值

有效的间隔值

YEAR

-4712 to 9999 (年除外 0)

任何非零整数

MONTH

01 to 12

0 to 11

DAY

01 to 31 (受制于年份和月份的数值，按日历的区域设置的规则)

任何非零整数

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 to 59.9(n), 其中，9（n）是时间小数秒精度

0 to 59.9(n), 其中，9（n）是区间小数秒精度

TIMEZONE_HOUR

-12 to 14 （范围可容纳夏令时更改）

不适用

TIMEZONE_MINUTE

00 to 59

不适用

TIMEZONE_REGION

在动态性能视图找到V$TIMEZONE_NAMES

不适用

TIMEZONE_ABBR

在动态性能视图找到V$TIMEZONE_NAMES

不适用

PL/SQL大对象（LOB）数据类型

大对象（LOB）数据类型是指大到数据项，例如文本，图形图像，视频剪辑和声音波形。 LOB数据类型允许高效的，随机的，分段访问这些数据。以下是预定义的PL/SQL LOB数据类型：

数据类型

描述

大小

BFILE

用于存储大型二进制对象在操作数据库之外的系统文件

依赖于系统，不能超过4千兆字节（GB）

BLOB

用于存储大型二进制对象在数据库中

8到128兆兆字节（TB）的

CLOB

用于存储字符大块数据在数据库中

8 - 128 TB

NCLOB

用于在数据库中存储大块NCHAR数据

8 - 128 TB

PL/SQL用户定义子类型

 

子类型是另一种数据类型，这是所谓的基本类型的子集。 子类型具有其基本类型相同的操作，但其只是有效值的一个子集。

PL/SQL预定义了一些子类型的封装标准。例如，PL/SQL预定义的子类型CHARACTER和INTEGER如下：

SUBTYPE CHARACTER IS CHAR;

SUBTYPE INTEGER IS NUMBER(38,0);

可以定义和使用自己的子类型。下面的程序说明定义和使用一个用户定义的子类型：

DECLARE

   SUBTYPE name IS char(20);

   SUBTYPE message IS varchar2(100);

   salutation name;

   greetings message;

BEGIN

   salutation := 'Reader ';

   greetings := 'Welcome to the World of PL/SQL';

   dbms_output.put_line('Hello ' || salutation || greetings);

END;

/

当上述代码在SQL提示符执行时，它产生了以下结果：

Hello Reader Welcome to the World of PL/SQL

PL/SQL NULL

PL/SQL NULL值表示缺少或未知的数据，它不是一个整数，字符，或任何其他特定的数据类型。需要注意的是NULL不是一样的空数据串或空字符值'\0'。NULL可以被分配，但它不能与任何东西等同，包括其本身。

 

PL/SQL 循环

declare

vcc number;

begin

vcc:=1;

loop

  exit when vcc>10;

  dbms_output.put_line(vcc);

  vcc:=vcc+1;

  end loop;

end;

 

 

3.游标

Oracle本质上作为一个数据库的存在，必须提供访问数据库的方法，允许用户针对某个结果集进行逐行访问—这是游标的本质

游标的使用需要有四个步骤，记住这四个步骤即可。

声明------》打开-----》取值-----》关闭

--声明游标：利用关键字 cursor  +  任意名字[参数列表（可有可无）]  +is  +你的sql语句

 

--声明一个游标  在游标内声明变量

declare cursor cu_stu_name is

select stuid,stuname from students;

 

stuid varchar2(50);

stuname varchar2(50);

--直接使用列类型声明变量

 

declare cursor cu_stu_name is

select stuid,stuname from students;

stuid students.stuid%type;   --声明变量类型与表中的字段类型相同

stuname students.stuname%type;

 

--使用行类型的变量临时存储

 

declare cursor cu_stu_name is

select * from students;

student students.stuid%rowtype;   --rowtype 声明一个行类型的变量，实际类型与students的行保持一致

--参数

--使用游标有三个步骤

--1.打开游标，2.通过游标获取数据，3.关闭游标

declare cursor cu_stu is

select stuid,stuname from students;

stuid students.stuid%type;

stuname students.stuname%type;

begin

  open cu_stu;

  fetch cu_stu into stuid,stuname;

 

  while cu_stu%found loop

    dbms_output.put_line(stuid||':'||stuname);

    fetch cu_stu into stuid,stuname;

    end loop;

    close cu_stu;

end;

--使用行类来避免过多定义局部变量

declare cursor cu_stu is select * from students; --声明一个游标

student students%rowtype;

begin

  open cu_stu;

  fetch cu_stu into student;

 

  while cu_stu%found loop

     dbms_output.put_line(student.stuid||':'||student.stuname);

     fetch cu_stu into student;

     end loop;

 

4.存储函数

函数概念：oracle数据库中不仅可以使用单条语句对数据进行曾改删查操作，而且可以多条语句组成一个语句块，并一起执行，这些语句块可以进行显示命名，并被其他应用调用。者写明明的语句块被称为存储过程。

 

目标：通过学习可以做到应用程序包来封装函数和存储过程。

主要内容：oracle自定义函数、oracle存储过程，包装函数与存储过程—程序包。

函数：与其他函数一样，oracle函数也是对象之一，并且必须返回一个值，区别于存过程。

函数分为两类：系统函数、自定义函数。

功能划分：每个独立的功能使用单独的函数实现，防止代码复杂化，减少维护成本，更容易最大化代码复用，针对功能实现函数。

参数：函数的传入参数可以没有，如果有，一定要明确其数据类型，类型不能在函数内部进行修改，所以不用担心使用者环境。

返回值：函数必须有返回值，并且必须在函数的结尾处用return命令返回。

创建函数：

--创建一个简单的函数

create or replace function get_hello_msg

return varchar2 as  --返回值类型

begin               --函数的定义

  return 'hello word'; --返回值每条语句以'；'结尾

end get_hello_msg;

--调用测试

select get_hello_msg() from dual;

--查询函数信息

select * from user_objects where lower(object_name)='get_hello_msg'

--存在数据字典中，所以也可以通过视图user_source查询

select * from user_source where lower(name)='get_hello_msg'

在pl/sql下查看返回值

开启服务端输出：sql>set serverout on;

declare msg varchar2(20);

 get_hello_msg varchar2(20);

begin

  get_hello_msg := 'aaaaaaaaaa';

  msg := get_hello_msg;--尽量不要省略，如果有同名的变量可能进行错误的引导

  dbms_output.put_line(msg);

end;

正确方法：

declare msg varchar2(20);

begin

  msg := get_hello_msg();

  dbms_output.put_line(msg);

end;

--函数被创建  可以用下面的方式查看函数的值

select get_hello_msg msg from dual

 

例子：

--计算员工应缴个人所得税的函数

create or replace function get_tax(p_salary number)

return number as

begin

  declare tax_salary number;

  begin

    tax_salary := p_salary-2000;

    if tax_salary<=0 then

      return 0;

      end if;

      --对于频繁使用的函数，确定性将很大程度上提高数据库性能 deterministic

      if tax_salary<=500 then

        return tax_salary*5/100;

        end if;

       

            if tax_salary<=2000 then

              return tax_salary*10/100-25;

               end if;

              

               if tax_salary<=5000 then

               return tax_salary*15/100-125;

               end if;

              

               if tax_salary<=20000 then

               return tax_salary*20/100-375;

               end if;

              

               if tax_salary<=40000 then

               return tax_salary*25/100-1375;

               end if;

              

               if tax_salary<=60000 then

               return tax_salary*30/100-3375;

               end if;

              

            end;

        end get_tax;

   

select get_tax(3000) tax from dual

--取出emp表中的名称

create or replace function get_emp_name

return varchar2 as

begin

  declare cursor cu_emp is

  select ename from emp; --声明一个游标，可循环获得表中的记录

  ename varchar2(20);

  rowString varchar2(500); --字符串缓冲区最好设置大一点

  begin

    open cu_emp;

    fetch cu_emp into ename;--游标指向的当前记录的数据赋值给 ename

   

    while cu_emp%found loop --循环处理游标所指向的记录

      rowString := rowString || ename || ','; --将ename添加到rowString的末尾

     fetch cu_emp into ename;

      end loop;

     

      return substr(rowString,1,length(rowString)-1);

      end;

      end get_emp_name;

select get_emp_name() from dual

 

 

 

posted @ 2019-08-09 11:17  韩老板  阅读(405)  评论(0)  编辑  收藏  举报