jpa笔记_jpa query.setparameter key 从0开始?

JPA:
Java Persistence API(JAVA持久化API)
JPA是规范，Hibernate是对JPA的实现。

ORM:
ORM（Object Relation Map）对象关系(表)映射
将对象模型和关系模型进行映射（转换）
关系模型 对象模型
表 类
字段 属性
记录 对象
ORM的用途：
1）将对象转换成记录
2）将查找出来的记录转换成对象

关系模型：
create table student(
sid int,
name varchar2(20)
)
insert into student(sid, name) values(1, ‘张三’);

对象模型：
class Student{
int sid;
String sname;
}
Student stu = new Student(1, “张三”);

JPA常用的注解有哪些？
@Entity：将POJO标注为持久化类，修饰类
@Table(name = “todo”)映射表，修饰类
@Id：映射表的主键，修饰属性或方法
@Column 映射表的字段，修饰属性或方法

JPA
JPA最主要的API
EntityManagerFactory:实体管理器工厂
EntityManager:实体管理器，负责CURD和事务管理
EntityTransaction:事务管理接口

常用的CRUD的方法：
EntityManager常用方法
public void persist(Object entity);保存
public T merge(T entity);更新
public void remove(Object entity);删除
public T find(Class entityClass, Object primaryKey);根据id查询

JPA的基本操作：
private static void fun1() {
//创建持久化管理器的工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory(“201902JPA”);
EntityManager manager = factory.createEntityManager();
EntityTransaction tr = manager.getTransaction();
try{
tr.begin();
Dept dept = new Dept(66, “TR1703YF1703顺利就业部”, “思觅酒店3楼7”);
manager.persist(dept);
tr.commit();
}catch(Exception e){
tr.rollback();
}finally{
manager.close();
factory.close();
}

}
1

JPA的关系控制：
在生成pojo时JPA会规定由哪一方来负责维护关系
它的注解是：@OneToMany(mappedBy=“dept”)
/**
* 由于JPA生成实体类时规定了关系的主控方是哪一个
* 所以，凡是在处理有关系的业务操作时必须按规定做
* 比如：创建新员工并创建新部门，并将员工添加到部门
* 操作方式：
* 1：使用员工添加部门
* 2：使用部门中的员工集合来增加员工
* 按关系主控方的设计，我们选择第一种方案
* 传说中的东哥：wdw
* 2019年1月18日下午4:59:37
*/
private static void fun4() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory(“201902JPA”);
EntityManager manager = factory.createEntityManager();
//创建事务并开启
EntityTransaction transaction = manager.getTransaction();
transaction.begin();
Emp emp=new Emp();
emp.setEmpno(2);
emp.setEname(“军民鱼水深”);
emp.setJob(“门童”);
emp.setSal(new BigDecimal(3000));
emp.setComm(new BigDecimal(3000));
emp.setHiredate(new Date());
emp.setMgr(new BigDecimal(6));

	//创建新部门吧
	Dept dept=new Dept(38,"捕鱼部","韬睿大厦3");
	/*List<Emp> emps =new ArrayList<>();
	emps.add(emp);
	dept.setEmps(emps);*/
	emp.setDept(dept);
	manager.persist(dept);
	manager.persist(emp);
	System.out.println("增加新部门成功");
	transaction.commit();
	System.out.println("向部门中增加员工成功");
	manager.close();
	factory.close();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果想删除部门的同时立即删除部门之下的所有员工，则要设置级联操作
@ManyToOne总是和@JoinColumn一起使用，通过@JoinColumn注解来指定外键的名字
@OneToMany(mappedBy=“dept”,cascade=CascadeType.REMOVE)
4、级联：cascade
public enum CascadeType {
ALL, //所有级联
PERSIST, //级联添加
MERGE, //级联修改
REMOVE, //级联删除
REFRESH, //级联刷新
DETACH
}
但是这种操作是很危险的，推荐，先删除所有员工，再删除部门

JAVA持久化查询语言吧：JPQL
Java Persistence Query Language
A、是基于OOP的查询语言
B、最大的特点：兼容绝大部分数据库，
无需修改代码就能移植到不同的数据库中

Query/TypedQuery方法
List getResultList();返回一个结果集
Object getSingleResult();返回一个结果
Query setFirstResult(int startPosition);设置起始位置，在分页中使用
Query setMaxResults(int maxResult); 设置返回的对象个数，在分页中使用
Query setParameter(String name, Object value);设置命名参数
Query setParameter(int position, Object value);设置索引参数，索引从0开始
int executeUpdate();执行insert、update和delete

JPA中的懒加载
fetch=FetchType.LAZY
JPA默认就是懒加载，如果你想要设置成主动加载也可以
@OneToMany(mappedBy=“dept”,cascade=CascadeType.ALL,fetch=FetchType.EAGER)

第一：跟面试官聊数据库有几个概念一定要懂：
关系：可以有理解为数据库的一张表：user.table
实体：通俗的理解就是数据表中的一行记录
属性：表示实体当中的一个个字段：id name age sex
主键：惟一标识实体的那个字段：
外键：一个属性是另一个关系的主键
域: 字段的取值范围(例如:姓名的域为字符串集合,性别的域为男或女)

联系：多个表之间的联系：一对一，一对多，多对多
DB：Database 数据库
DBS：Database System 数据库系统
DBMS：Database Management System 数据库管理系统 ，MySql、Oracle、DB2、SqlServer
DBA：Database Assistant 数据库管理员
SQL：Structured Query Language   结构查询语言： 专门为数据库设计的操作指令集
DML  数据操作语言
	插入（新增）、查询、删除、修改等操作   增删改查
	insert             select  delete update（更新）

    DCL 数据控制语言
	控制存取许可（权限）
	grant(授权)   revoke（取消授权）

    DDL 数据库定义语言（创建数据库对象）
	常见的数据对象：表，视图（view），存储过程，触发器，函数，事件
	create 创建  drop 删除（放弃）   alter（更改）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

第二：数据库数据完整性规则分类：

例:在学生选课管理数据库中有如下4个表:
  1.学生(学号,姓名,性别,专业号,年龄)	主键为”学号”
  2.课程(课程号,课程名,学分)		主键为”课程号”
  3.选修(学号,课程号,成绩)		主键为”学号”,”课程号”
  4.专业(专业号,专业名)			主键为”专业号”

实体完整性:实现手段：
	若字段A是表R的主键,则字段A不能为空值：结论：主键不能为空
	如表1学生表中,学号不能取空值,
	在3选修(学号,课程号,成绩)中”学号”和”课程号”不能取空值

参照完整性:外键的值不允许参照不存在的相应表的主键的值,或者外键为空值
	如:  学生(学号,姓名,性别,专业号,年龄)
		专业(专业号,专业名)

用户定义完整性:通常是定义对关系中除主键与外键属性之外的其他属性取值
		的约束,即对其它字段的值域的约束（性别：只能是男或女）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

第三：数据库设计的范式：
第一范式(1NF):
最低要求的条件是实体的每个字段
必须是不可分的数据项,是最基本的规范化

	第二范式(2NF):
		若关系模式R∈1NF,且每一个非主键完全依赖于主键,则R∈2NF

	第三范式(3NF):
		若关系模式R∈2NF,且每一个非主属性都不传递于码,则R∈3NF
1
2
3
4
5

第四：什么是数据库引擎

A：是用于存储、处理和保护数据的核心服务
	利用数据库引擎可控制访问权限并快速处理事务

B：MySQL中查询数据库引擎的命令
	show engines;显示mysql所支持的所有数据库引擎
	mysql>show variables like ‘%storage_engine%’ 
	查看MySQL当前默认的存储引擎：

C：MYSQL支持三个引擎：默认的引擎是：InnoDB
	ISAM：
	MYISAM：
	HEAP：
	另外两种类型INNODB和BERKLEY（BDB）,也常常可以使用

D：InnDB的特点
	A:InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。
	B:InnoDNB是为处理巨大数据量时的最大性能设计，
	它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
	C:InnoDB存储引擎为在主内存中缓存数据和索引而维持他自己的缓冲池。
	D:InnoDB被用来在众多需要高性能的大型数据库站点上产生。
		一些站点在InnoDB上处理平均每秒800次插入/更新上。

总结：
Innodb引擎,Innodb引擎提供了对数据库ACID事务的支持。
并且还提供了行级锁和外键的约束。
它的设计的目标就是处理大数据容量的数据库系统。
它本身实际上是基于Mysql后台的完整的系统。
Mysql运行的时候,Innodb会在内存中建立缓冲池,
用于缓冲数据和索引。但是,该引擎是不支持全文搜索的。
同时,启动也比较的慢,它是不会保存表的行数的。
当进行Select count(*) from table指令的时候,需要进行扫描全表。
所以当需要使用数据库的事务时,该引擎就是首选。
由于锁的粒度小,写操作是不会锁定全表的。
所以在并发度较高的场景下使用会提升效率的。

Oracle没有数据库引擎的概念
oracle中不存在引擎的概念,数据处理大致可以分成两大类:
	联机事务处理OLTP(on-line transaction processing)
		OLTP是传统的关系型数据库的主要应用,主要是基本的、
		日常的事务处理,例如银行交易。
		OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,
		并且提供直观易懂的查询结果。
		OLTP 系统强调数据库内存效率,强调内存各种指标的命令率,
		强调绑定变量,强调并发操作; 
		联机分析处理OLAP(On-Line Analytical Processing)。
	
	OLAP： 
		系统则强调数据分析,强调SQL执行市场,强调磁盘I/O,强调分区等。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

第一：SQL优化：
select * from emp;这一句查询语句的效果到底如何？
以下是oracle当中按F5：执行计划所显示的结果
从结果中可以看到，这条SQL语句已经开启了全表扫描
因为它放弃的索引列

Description	对象所有者	对象名称	耗费	基数	字节
SELECT STATEMENT, GOAL = ALL_ROWS			3	12	408
 TABLE ACCESS FULL	SCOTT	EMP	3	12	408


如果想要改变：则应该修改SQL语句，因为使用了有索引的列
select *　from emp where empno>0

优化的原则：
	1：能不用子查询（嵌套查询）的坚决不用
	2：尽量使用有索引的字段
	3：有些公司有规定SQL语句的长度有限制

优化的案例：
	select id from t where num is null  
	可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：   
	select id from t where num=0  

	in 和 not in 也要慎用，否则会导致全表扫描，如： 
	select id from t where num in(1,2,3)    
	对于连续的数值，能用 between 就不要用 in 了：   
	select id from t where num between 1 and 3   

	应尽量避免在 where 子句中使用 or 来连接条件，
	否则将导致引擎放弃使用索引而进行全表扫描，如：   
	select id from t where num=10 or num=20 
	可以这样查询： 
	select id from t where num=10   
	union all       
	select id from t where num=20 

面试官问：你懂不懂SQL的优化：
	懂，因为，我们使用的MyBatis这个框架会写大量的sql
	我们写完之后，DBA或项目经理会检查SQL语句的效率
	检查的手段是使用执行计划（oracle）或解释计划（MySQL）
	通过这个检查发现一些开启了全表扫描的SQL语句
	从而修改这个SQL，优化，避免开启全表扫描

面试官问：查询海量数据的方式
	1：海量的存储本身就应该是分库分表
	2：使用分页查询
	3：框架自身也带了缓存功能
	4：多使用SQL优化

在MySQL中查看执行计划：explain命令
例：explain select * from users（此句开启了全表扫描）
以上命令执行之后可以查看此SQL执行的效率

例：explain select * from users where id>0（此句没有开启全表扫描）

https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html
可以从mysql的官方帮助文档中去查找各个参数的说明
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

第二：什么是事务
事务是应用程序中一系列严密的操作，
所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。
也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，
要么一个都不做。
事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。
如果其中一个步骤失败，将发生回滚操作，
撤消撤消之前到事务开始时的所以操作。

第三：什么是事务的 ACID
事务具有四个特征：
原子性（ Atomicity ）、
一致性（ Consistency ）、
隔离性（ Isolation ）和
持续性（ Durability ）。
这四个特性简称为 ACID 特性。

	1 、原子性 
	事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做 

	2 、一致性 
	事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
	因此当数据库只包含成功事务提交的结果时，
	就说数据库处于一致性状态。如果数据库系统 运行中发生故障，
	有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的
	修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，
	或者说是 不一致的状态。
	 
	3 、隔离性 
	一个事务的执行不能其它事务干扰。
	即一个事务内部的操作及使用的数据对其它并发事务是隔离的，
	并发执行的各个事务之间不能互相干扰。 

	4 、持续性 
	也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。
	接下来的其它操作或故障不应该对其执行结果有任何影响。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

第四：事务的隔离机制：是指当有多个事物操作同一个记录时产生的问题，并发事务带来的问题

	A:脏读：
		事务A读取了事务B更新的数据，
		然后B回滚操作，
		那么A读取到的数据是脏数据

	B:不可重复读：
		事务 A 多次读取同一数据，
		事务 B 在事务A多次读取的过程中，
		对数据作了更新并提交，
		导致事务A多次读取同一数据时，
		结果不一致。

	C:幻读：
		系统管理员A将数据库中所有学生的成绩
		从具体分数改为ABCDE等级，
		但是系统管理员B就在这个时候
		插入了一条具体分数的记录，
		当系统管理员A改结束后
		发现还有一条记录没有改过来，
		就好像发生了幻觉一样，这就叫幻读。
	不可重复读的和幻读很容易混淆，
	不可重复读侧重于B事务的修改，幻读侧重于B事务的新增或删除。
	解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

第五：MySQL事务隔离级别分如下几种：
数据库的事务隔离级别越严格，并发副作用越小，
但付出的代价也就越大，因为事务隔离实质上就是使事务
在一定程度上“串行化”进行，这显然与“并发”是矛盾的，
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，
比如许多应用对“不可重复读”和“幻读”并不敏感，
可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了４个事务隔离级别，
每个级别的隔离程度不同，允许出现的副作用也不同，
应用可以根据自己业务逻辑要求，通过选择不同的隔离级别来平衡＂隔离＂
与＂并发＂的矛盾
事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（默认级别repeatable-read） 否 否 是
串行化（serializable）

第六：mysql默认的事务隔离级别为repeatable-read
可以执行命令：select @@tx_isolation
查询结果是： repeatable-read

修改mysql默认的事务隔离级别
set session transaction isolation level read uncommitted

测试：1、读未提交：
（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读）
查询表account的初始值
操作：
start transaction 开启事务
select * from account;查询数据
update account set balance =balance-500 where id=1;
以上三行是在同一个事务中完成的。

（2）打开另一个客户端A，并设置当前事务模式为read uncommitted（未提交读）
	start transaction 开启事务
	select * from account;查询数据
以上两行是在另一个事务中完成的，它看到了第一个事务修改后没有提交的数据
这种结果称之为：脏读

（3）刚才修改了数据的事务立即回滚，这样数据就还原了吧
	刚才读取了数据的事务再进行修改就会发现数据不一致

以上读未提交的问题要怎么解决？
将数据库事务的隔离级别提高为：读已提交
执行步骤：

（1）将两客户端的事务的隔离级别全部设置为：read committed（读已提交）
	其中一个事务修改数据，另一个事务无法看到没有提交的数据
	如果另一个事务也进行修改同一条数据，则有如下提示
	 Lock wait timeout exceeded; try restarting transaction
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

---

1、事务隔离级别为读提交时，写数据只会锁住相应的行
1

2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）
的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，
更新数据时会锁住整张表。一个间隙被事务加了锁，
其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

3、事务隔离级别为串行化时，读写数据都会锁住整张表

4、隔离级别越高，越能保证数据的完整性和一致性，
但是对并发性能的影响也越大。

第七：MySQL的事务隔离级别案例参见：https://www.cnblogs.com/huanongying/p/7021555.html

各具体数据库并不一定完全实现了上述４个隔离级别，
例如，Oracle只提供Read committed和Serializable两个标准级别，
另外还自己定义的Read only隔离级别：
ＭySQL支持全部４个隔离级别，但在具体实现时，有一些特点，
比如在一些隔离级下是采用MVCC一致性读，但某些情况又不是。
1
2
3
4
5

---

第八：MySQL中的锁：
锁：是计算机协调多个进程或纯线程并发访问某一资源的机制
不同的存储引擎支持不同的锁机制。
MySQL大致可归纳为以下3种锁：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

InnoDB与MyISAM的最大不同有两点：
第一是支持事务（TRANSACTION）；
第二是采用了行级锁。

InnoDB行锁实现方式
    InnoDB行锁是通过索引上的索引项来实现的，
    这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。
    InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，
    InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
    在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，
    可能导致大量的锁冲突，从而影响并发性能。

什么时候使用表锁
	对于InnoDB表，在绝大部分情况下都应该使用行级锁，
	因为事务和行锁往往是我们之所以选择InnoDB表的理由。
	但在个另特殊事务中，也可以考虑使用表级锁。
	第一种情况是：事务需要更新大部分或全部数据，表又比较大，
	如果使用默认的行锁，不仅这个事务执行效率低，
	而且可能造成其他事务长时间锁等待和锁冲突，
	这种情况下可以考虑使用表锁来提高该事务的执行速度。
	第二种情况是：事务涉及多个表，比较复杂，
	很可能引起死锁，造成大量事务回滚。
	这种情况也可以考虑一次性锁定事务涉及的表，
	从而避免死锁、减少数据库因事务回滚带来的开销。

关于死锁:
    ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，
    要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，
    除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。

先说悲观锁和乐观锁吧。并发控制一般采用三种方法，
	分别是乐观锁和悲观锁以及时间戳。
	乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；
	悲观锁就刚好相反，觉得自己读数据库的时候，
	别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；
	时间戳就是不加锁，通过时间戳来控制并发出现的问题

悲观锁
	就是在读取数据的时候，为了不让别人修改自己读取的数据，
	就会先对自己读取的数据加锁，只有自己把数据读完了，
	才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，
	不允许别人读取该数据，只有等自己的整个事务提交了，
	才释放自己加上的锁，才允许其他用户访问那部分数据。
	乐观锁就比较简单了，就是不做控制，
	这只是一部分人对于并发所持有的一种态度而已。

时间戳
	就是在数据库表中单独加一列时间戳，
	比如“TimeStamp”，每次读出来的时候，把该字段也读出来，
	当写回去的时候，把该字段加1，提交之前 ，
	跟数据库的该字段比较一次，如果比数据库的值大的话，
	就允许保存，否则不允许保存，
	这种处理方法虽然不使用数据库系统提供的锁机制，
	但是这种方法可以大大提高数据库处理的并发量，
	因为这种方法可以避免了长事务中的数据库加锁开销
	（操作员A 和操作员B操作过程中，都没有对数据库数据加锁），
	大大提升了大并发量下的系 统整体性能表现。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

---

第九：Spring的事务传播机制
事务的传播特性

事务传播行为就是多个事务方法调用时，如何定义方法间事务的传播。Spring定义了7中传播行为：
（1）propagation_requierd：如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中，这是Spring默认的选择。
（2）propagation_supports：支持当前事务，如果没有当前事务，就以非事务方法执行。
（3）propagation_mandatory：使用当前事务，如果没有当前事务，就抛出异常。
（4）propagation_required_new：新建事务，如果当前存在事务，把当前事务挂起。
（5）propagation_not_supported：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
（6）propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。
（7）propagation_nested：如果当前存在事务，则在嵌套事务内执行。
如果当前没有事务，则执行与propagation_required类似的操作。

在Spring中如何进行事务的设置
@Override
@Transactional(rollbackFor={Exception.class})
@Transactional(rollbackFor={Exception.class},propagation=Propagation.SUPPORTS)
public void batchUpdate(List<Object[]> batchArgs) {
System.out.println(“批量修改数据”);
String sql=“insert into tr09student (id,name,age,sex) values(?,?,?,?)”;
int [] num=jdbctemplate.batchUpdate(sql, batchArgs);
System.out.println(num.length);
System.out.println(10/0);

}
1

可以看出，
使用@Transactional注解的方式配置文件要简单的多，
将事务交给事务注解驱动。它有个缺陷是他会把所有的连接点
都作为切点将事务织入进去，显然只需要在buyStock()方法织入事务即可。

第十：存储过程
例：带参数的存储过程
create procedure wdw_pro3(v_id number,v_name varchar2) is
begin
update x set name=v_name where id=v_id;
end;

第十一：exec和call的区别
在sqlplus中这两种方法都可以使用：
exec pro_name(参数1…); call pro_name(参数1…);
区别：
1. 但是exec是sqlplus命令，只能在sqlplus中使用；call为SQL命令，没有限制.
2. 存储过程没有参数时,exec可以直接跟过程名（可以省略()），但call则必须带上().

Sql代码
SQL> --创建过程 插入数据
SQL> create or replace procedure pro1 is
2 begin --执行部分
3 insert into mytest values(‘张三’, ‘mm’);
4 end;
SQL> exec pro1;

SQL> call pro1;

带输入输出参数的存储过程
create or replace procedure wdw_pro4(v_id in number,v_name out varchar2)
as
begin
select name into v_name from x where id=v_id;
end;
三种执行方式：
A:在存储过程编译窗口进行测试

B:在命令窗口调用
SQL> var v_name varchar2;
SQL> exec zengli.wdw_pro4(7,:v_name);
PL/SQL procedure successfully completed
v_name
---------
aa

C:在PL/SQL块中调用
declare
v_name varchar2(50);
begin
wdw_pro4(9,v_name);
dbms_output.put_line(v_name);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第十三：
JDBC操作存储过程1
A:创建表
create table test(
tid number(5),
tname varchar2(20)
)

B:创建存储过程
1

CREATE OR REPLACE PROCEDURE test_pro_1
IS
Begin
insert into test values (11,‘aa’);
End;

C:测试存储过程
1

SQL> exec test_pro_1;
PL/SQL procedure successfully completed

SQL> select * from test;
TID TNAME

---

11 aa

D:JDBC操作
1
2
3

public int updateAll2(){
con=BaseDao.getConnection();
try {
ct=con.prepareCall("{call test_pro_1}");
return ct.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
BaseDao.closeAll(con, pt, null);
}
return 0;
}

第十三：JDBC操作存储过程2
A:创建存储过程
CREATE OR REPLACE PROCEDURE test_pro_2(id number,name varchar2)
IS
Begin
insert into test values (id,name);
commit;
End;

B:测试存储过程
1

SQL> exec test_pro_2(2,‘a’);
PL/SQL procedure successfully completed

C:JDBC操作
1

public int updateAll3(){
con=BaseDao.getConnection();
try {
ct=con.prepareCall("{call test_pro_2(?,?)}");
ct.setInt(1, 4);
ct.setString(2, “dd”);
return ct.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
BaseDao.closeAll(con, pt, null);
}
return 0;
}

第十四：JDBC调用存储过程3
A:创建带有out类型参数的存储过程
CREATE OR REPLACE PROCEDURE test_pro_4(num out number)
IS
Begin
select count(*) into num from test;
End;

B:测试带有out参数的存储过程
	SQL> var count number;
	SQL> exec test_pro_4(:count);
	PL/SQL procedure successfully completed
	count
	---------
	7

C:JDBC测试带输出参数的存储过程

public int updateAll4(){
	con=BaseDao.getConnection();
	try {
		ct=con.prepareCall("{call test_pro_4(?)}");
		ct.registerOutParameter(1, Types.NUMERIC);
		ct.executeUpdate();
		return ct.getInt(1);
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		BaseDao.closeAll(con, pt, null);
	}
	return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

---

第十五：什么是函数
函数是用于处理一个数据项并返回结果的运算，
是一种有零个或多个参数并且有一个返回值的程序。
函数可以接受零个或多个参数，带不同参数的函数完成不同的运算，
并产生一个或多个结果，在SQL中Oracle内建了一系列函数，
这些函数都可被称为SQL或PL/SQL语句。

函数的格式：
函数的一般格式是：select sysdate from dual;如果没有参数括号可以省略
函数名（参数）：select sum(COMM) as 所有员工工资总和 from emp;
函数名（参数，参数，参数）:
select to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss') from dual;
select replace('abcabdabcabd', 'abc', 'AB') from dual;

函数的返回结果：	
单行函数：日期函数 数字函数 字符函数 转换函数 空值函数 
		to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
		select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual; 
		select months_between(sysdate, to_date('2016-12-14 13:23:44','yyyy-mm-dd hh24:mi:ss')) from dual;
		select extract(year from sysdate) from dual;//提取时间中的年
		select abs(-1) from dual;返回一个负数的绝对值
		select round(3.15,1) from dual;//舍入
		select mod(10,3) from dual;//求余数
		select ascii('a') from dual;//显示一个字母代表的数字
		select ltrim('   A') from dual;//删除字符的空格
		select replace('abcabdabcabd', 'abc', 'AB') from dual;//字符串替换
		select translate('abcabdabcabd', 'abc', 'AB') from dual;//打散替换
		select substr('ABCDEFG', 2, 5) from dual;//字符串截取
		select ename, decode(job, 'CLERK', '清洁工', 'SALESMAN', '业务员', 'MANAGER
		', '经理', 'ANALYST', '分析师', 'PRESIDENT', '总裁', '未知职业') from emp;

多行函数（组函数）:row_number()over(partition by col1 order by col2)
	partition by：按职位分组再
	order by sal desc：按薪水排序，降序
	row_number()：从1开始，为每一条分组记录返回一个数字
	select ename, job, sal, row_number() over(partition by job order by sal desc) as 排名 from emp;
	
	先按部门分组，再统计每一个部门的工资总和
	select distinct(deptno),sum(sal) over(partition by deptno ) 部门工资汇总 from emp;

oracle提供给每一个表的隐藏字段：rowid rownum
	select t.*,rowid,rownum from stu t;

聚合函数最常用：
	求和：sum
	求平均：avg
	计数：count
	最大值：max
	最小值：min
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

第十六：函数与存储过程的区别？
　　相同点： 1.创建语法结构相似，都可以携带多个传入参数和传出参数。
　　　　 2.都是一次编译，多次执行。
　　不同点： 1.存储过程定义关键字用procedure，函数定义用function。
　　　　　　 2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
　　　　　　 3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。
第六：存储过程的好处
优点

1. 运行速度：对于很简单的sql，存储过程没有什么优势。
对于复杂的业务逻辑，因为在存储过程创建的时候，
数据库已经对其进行了一次解析和优化。存储过程一旦执行，
在内存中就会保留一份这个存储过程，
这样下次再执行同样的存储过程时，可以从内存中直接调用，
所以执行速度会比普通sql快。

2. 减少网络传输：存储过程直接就在数据库服务器上跑，
所有的数据访问都在数据库服务器内部进行，
不需要传输数据到其它服务器，所以会减少一定的网络传输。
但是在存储过程中没有多次数据交互，
那么实际上网络传输量和直接sql是一样的。
而且我们的应用服务器通常与数据库是在同一内网，
大数据的访问的瓶颈会是硬盘的速度，而不是网速。

3. 可维护性：的存储过程有些时候比程序更容易维护，
这是因为可以实时更新DB端的存储过程。
有些bug，直接改存储过程里的业务逻辑，就搞定了。

4. 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

5. 可扩展性：应用程序和数据库操作分开，独立进行，
而不是相互在一起。方便以后的扩展和DBA维护优化。

缺点  
1

1. SQL本身是一种结构化查询语言，但不是面向对象的的，
本质上还是过程化的语言，面对复杂的业务逻辑，
过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，
如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

2. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，
则您仍需要更新程序集中的代码以添加参数、更新调用，等等，
这时候估计会比较繁琐了。

3. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，
但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，
那效率实在堪忧。

5. 不支持群集，数据库服务器无法水平扩展，
或者数据库的切割（水平或垂直切割）。数据库切割之后，
存储过程并不清楚数据存储在哪个数据库中。

总结

1. 适当的使用存储过程，能够提高我们SQL查询的性能，
　　2. 存储过程不应该大规模使用，滥用。
　　 3. 随着众多ORM 的出现，存储过程很多优势已经不明显。
　　4. SQL最大的缺点还是SQL语言本身的局限性——SQL本身是
一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑
——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑，
还是交给代码去处理吧。

第十七：视图是一个虚拟表，可以存储多个表的查询结果
CREATE OR REPLACE VIEW TR22VIEW1 AS
SELECT empno, ename, job, dname FROM emp
LEFT OUTER JOIN dept
ON emp.deptno = dept.deptno
with read only;

第十八：数据库对象有哪些？
表
存储过程
函数
视图
索引
游标

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

分析函数和聚合函数的不同之处是什么？
	普通的聚合函数用group by分组，
	每个分组返回一个统计值，而分析函数采用partition by分组，
	并且每组每行都可以返回一个统计值。
分析函数的形式
	分析函数带有一个开窗函数over()，包含三个分析子句:
	分组(partition by), 
	排序(order by), 
	窗口(rows) ，
	他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)		。
	注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提


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

第二十：Oracle分析函数与聚合函数的区别：
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，
而聚合函数对于每个组只返回一行。
问题1：求出每个部门工资最高的前3名。
利用我们传统的聚合函数max可以方便地取出工资最高的一个员工，
但是取出多个就无能为力了，同样，如果不分组我们可以通过排序取出工资最高的前3名，
但无法实现对多个部门的分组。而采用rank聚合函数，可以方便地实现我们的要求。

完整的语句如下:排名函数(Rank)
 select * from (
 select deptno,sal,ename, rank()over(partition by deptno order by sal desc) pm from emp)
 where pm<=3
1
2
3
4

第二十一：游标（cursor）
A、为什么需要游标？
在pl/sql中，不能直接执行select * from emp等这样的语句，
如果需要读取表中的数据，必须使用游标。

B、游标的四个步骤
1）声明游标
     cursor 游标名(参数列表) is select查询语句;
     理解：定义了一个游标，该游标指向查询结果的第一条记录的上一行。

2）打开游标
     open 游标名;

3）提取游标数据

4）关闭游标     
     close 游标名;

案例：将游标写在存储过程当中，方便重复调用
create or replace procedure TR1702 is
dname_ dept.dname%TYPE;
  --1、声明游标
  CURSOR c IS SELECT dname FROM dept;
begin
  --2、打开游标
  OPEN c;
  --3、提取游标数据
  FETCH c INTO dname_; --读取第一行
  WHILE c%FOUND LOOP
    dbms_output.put_line(dname_);
    FETCH c INTO dname_;
  END LOOP;
  --4、关闭游标
  CLOSE c;
end TR1702;
调用方法：exec TR1702
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31