储存过程insert into 表 select_表基本操作/事务

1.数据库类型

number(x,y)：数值型最长是x位，y位小数

varchar2(maxlength）：变长字符串，maxlength这个参数的上限是32767字节

char(max_length)：定长字符串最大2000字节

Date：日期时间只能精确到秒。

timestamp：时间戳精确到微秒

long：长字符串最大支持2GB

其他类型:

CLOB：最大长度4G -->大对象很少使用:如果存在大对象，一般的解决方案存入文件地址（地址为程序所在应用服务器的相对路径）。

BLOB：存二进制文件

注意:在数据库设计时,如果要存大文件(视频，音频等)，一定不要用BLOB/CLOB,通用的解决方案都是文件的地址。

2.创建一张表

创建学生表为例子

create table t_stuinfo(
   sid number(4),
   name varchar2(20),
   phone char(11),
   gender number(1),
   birthday date,
   address varchar2(100)
)
-- 通过其他表结构创建表
create table t_emp
as 
select * from emp;

-- 只创建表的结构(复制表结构)
create table t_emp2
as
select * from emp where 1=2;

select * from t_emp;
select * from t_emp2;
-- 修改表操作
-- [1]给表添加字段
alter table t_stuinfo add grade number(2)

-- [2]删除表的字段
alter table t_stuinfo drop column grade 

-- [3] 修改表字段
alter table t_stuinfo modify(address varchar2(150))

-- [4]重命名
rename t_stuinfo to t_stuinfo2

3.Insert/update/delete/update

-- insert
insert into t_emp2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(1010,'cai10','singer',7938,sysdate,1000,1,10)

insert into t_emp2(empno,job,ename,mgr,hiredate,sal,comm,deptno)
values(1010,'singer','cai10',7938,sysdate,1000,1,10)
-- insert是事务操作，需要提交事务。

insert into t_emp2
values(1020,'cai20','singer',7938,sysdate,2000,2,10)
--更新
update t_emp2
set ename = 'cai22',sal = 2200
where empno = 1020

4.序列

序列是oracle专有的对象，它用来产生一个自动递增的数列。

在数据库开发设计表时，如果需要一个字段的值是自增的话，优先考虑序列

-- 创建序列
create sequence seq_empno
start with 1
increment by 1

-- 序列的使用
-- 序列中的下一个值，从定义(start with)的值开始
select seq_empno.nextval from dual;
-- 获取序列的当前值
select seq_empno.currval from dual;

-- 序列的应用
select * from t_emp2;
insert into t_emp2
values(seq_empno.nextval,'cai10','singer',7938,sysdate,1000,1,10)

5.事务

事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。

事务是为了保证数据库的完整性。

事务不能嵌套

在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况：

• 用户显式执行Commit语句提交操作或Rollback语句回退。
• 当执行DDL(Create、Alter、Drop)语句事务自动提交。
• 用户正常断开连接时，Transaction自动提交。
• 系统崩溃或断电时事务自动回退。

-- beginTransaction(insert/update/delete)
insert into t_emp2
values(6,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2
values(7,'cai50','singer',7938,sysdate,5000,5,10);

-- 显示的事务结束（endTransaction）
-- commit;
rollback;
-- beginTransaction(insert/update/delete)
insert into t_emp2
values(6,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2
values(7,'cai50','singer',7938,sysdate,5000,5,10);
--隐式的事务结束
create table abc(
   sid number
)

6.保存点

-- beginTrans
insert into t_emp2 values(9,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2 values(10,'cai50','singer',7938,sysdate,5000,5,10);
select * from t_emp2;

savepoint sp1;

insert into t_emp2 values(11,'cai40','singer',7938,sysdate,4000,4,10);
insert into t_emp2 values(12,'cai50','singer',7938,sysdate,5000,5,10);
select * from t_emp2;

rollback to sp1;

commit;

当发生意外情况可以不用回滚整个事务可以回滚到指定‘点’

7.事务的特性

事务四大特征：原子性，一致性，隔离性和持久性。

1. 原子性（Atomicity）

一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。即此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的。

2. 一致性（Consistency）

一致性代表了底层数据存储的完整性。它必须由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(即，数据预期所表达的现实业务情况不相一致)。例如，在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100 你读到余额要取，有人向你转100 但是事物没提交（这时候你读到的余额应该是100，而不是200） 这种就是一致性

3. 隔离性（Isolation）

隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。

4. 持久性（Durability）

持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

8.主键约束

-- 创建表
-- 创建列级约束-显式指定名称，pk_sid
create table t_stuInfo(
     sid number(4) constraint pk_sid primary key,
     name varchar2(20)
)

-- 创建列级约束-没式显示指定名称，系统随机命名SYS_C..
create table t_stuInfo2(
     sid number(4) primary key,
     name varchar2(20)
)
-- 创建表，以表级约束
create table t_stuInfo3(
   sid number(4),
   phone char(11),
   name varchar2(20),
   constraint pk_stuinfo primary key(phone,name)
)

create table t_stuInfo4(
   sid number(4),
   phone char(11),
   name varchar2(20),
   primary key(phone,name)
)

9.唯一性约束（UNIQUE）

唯一性约束条件确保所在的字段或者字段组合不出现重复值

唯一性约束条件的字段允许出现空值

Oracle将为唯一性约束条件创建对应的唯一性索引

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) constraint uq_phone unique 
)

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) unique
)

create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11) unique not null
)

drop table t_stuInfo6;
create table t_stuInfo6(
     sid number(4) primary key,
     phone char(11),
     constraint uq_phone unique(phone)
)

insert into t_stuinfo6(sid,phone)
values(1000,'18612340000')
insert into t_stuinfo6(sid,phone)
values(1001,'18612340000')

10. 自定义约束

Check约束用于对一个属性的值加以限制

create table t_stuInfo7(
     sid number(4) primary key,
     phone char(11) unique,
     age number(3) check(age>0 and age<100)
)

insert into t_stuInfo7
values(1000,'18612341234',-10)

create table emp3
(	id number(4) primary key,
	age number(2)  check(age > 0 and age < 100),
	salary number(7,2),
	sex char(1),
	constraint salary_check check(salary > 0)
)

11.外键约束

create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid)
)

create table t_teacher1(
     tid number(4) primary key,
     name varchar2(20) not null
)

insert into t_teacher1
values(1,'alex');

insert into t_stuInfo8
values(1000,'18612341234',1)
-------------------------------------------------------------------------------------------------------
对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录（即删除一个主键值，那么对依赖的影响可采取下列3种做法：
1.RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。
2.CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
3.SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
[ON DELETE [CASCADE|SET NULL]] 如省略on短语，缺省为第一中处理方式。
---------------------------------------------------------------------------------------------------------
drop table t_stuinfo8;
create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid) 
)

create table t_stuInfo8(
     sid number(4) primary key,
     phone char(11) unique,
     tid number(4),
     constraint fk_tid foreign key(tid) references t_teacher1(tid) on DELETE CASCADE
)

create table t_teacher1(
     tid number(4) primary key,
     name varchar2(20) not null
)

insert into DELETE
values(1,'alex');

insert into t_stuInfo8
values(1000,'18612341234',1)

select * from t_stuInfo8
delete from t_teacher1 where tid = 1;