sql事务、索引、pl sql、分区、视图、游标、函数、触发器、存储过程

一、事务（ACID）

1. 原子性：事务是一个整体
2. 一致性：要么成功，要么失败
3. 隔离性：又分隔离等级：①读未提交——》脏读  ②读已提交  ③可重复读  ④串行化
4. 持久性：最后都要上传

 1.1 事务开启

MySQL中需要：

1. 关闭自动提交： set autocommit = 0; （oracle中无需关闭，默认手动提交）

2. 开启事务： start  transaction;

3. 提交：commit   or  回滚 ： rollback

二、索引

2.1 MySQL索引

2.1.1 主外键索引 primary key foreign key

使用方法：alter table  xxx1 add constraint  索引名  foreign key(xxx1.列名)  references  xxx2(主键)

2.1.2 一般索引  index

使用方法： create index  索引名  on  xxx(列名)

2.2 oracle

2.2.1 B_Tree

oracle默认的索引

使用方法：create index 索引名  on  表名（列名。。。）

注：可以创建多个索引，使用时必须要用到基列

2.2.2 位图

一般用于变化较小的列，例如：性别

使用方法： create  bitmap  index  索引名  on  表名（列名）

2.2.3 函数

使用方法：create index 索引名  on  函数（）；

例如：
/*函数索引*/
create index 索引名 on xxx(substr(xx,1,1));

2.3 pl sql

2.3.1  for 循环

declare
  i number := 0; --定义变量
begin            
  for i in 1..10 loop
    dbms_output.put_line(i);  --执行语句
  end loop;
end;

三、分区

目的：加快搜寻速度

3.1 range分区

例如：

/*按用户生日分区*/
create table user_part1(
       userid int primary key,
       username varchar(20) not null,
       birthday date not null
)
       partition by range(birthday)(
       partition part1 values less than(to_date('1979-12-31','yyyy-MM-dd')) tablespace course,
       partition part2 values less than(to_date('1999-12-31','yyyy-MM-dd')) tablespace course,
       partition part3 values less than(to_date('2019-12-31','yyyy-MM-dd')) tablespace course,
       partition other values less than(maxvalue)
);

/*插入数据*/
declare
  i number:=0;
begin
  for i in 1..100 loop
    insert into user_part1(userid,username,birthday)
    values(i,dbms_random.string('a',6),
    to_date('1970-1-1','yyyy-MM-dd')+round(dbms_random.value(1,20000)))
  end loop;
end;

3.2 list 分区

/*列表分区*/
create table user_part2(
       userid int primary key not null,
       username varchar(20) not null,
       gender number(2) not null
)
partition by list(gender)(
          partition part_male values(1) tablespace course,
          partition part_female values(0) tablespace course,
          partition part_unknown values(2) tablespace course
);

/*插入值*/
declare
  i number :=0;
begin
  for i in 1..100 loop
    insert into user_part2(userid,username,gender)
           values(i,dbms_random.string('a',6),
           trunc(dbms_random.value(0,3)));
  end loop;
  commit;
end;
/*查询*/
select count(*) from user_part2 partition (part_male);

3.3 hash分区

/* hash 分区 */
create table user_part3(
       userid int primary key,
       username varchar(20) not null,
       gender int not null,
       firstname varchar(20) not null
)
partition by hash(username)(
  partition p1 tablespace course,
  partition p2 tablespace course,
  partition p3 tablespace course
);
/*插入*/
declare
  i number :=0;
  uname varchar(20);
begin
  for i in 1..100 loop
    uname:=dbms_random.string('a',6);
    insert into user_part3(userid,username,gender,firstname)
           values(i,uname,
           trunc(dbms_random.value(0,3)),
           substr(uname,1,1));
  end loop;
  commit;
end;
/*查询*/
select * from user_part3 partition (p1)

四、视图

目的：经常需要的表可以封装成一个视图，需要使用时直接查看这个视图

使用方法：

1. 创建视图：create  or  replace  view 视图名  as  select语句
2. 查看视图：select  *  from  视图名
   注：oracle会存在没有权限：grant create view to 用户名;

五、游标 

5.1 静态游标

/*游标*/
declare
  cursor stus is select * from student; -- 静态游标 整张表放进去
  stur student%ROWTYPE; -- 行类型
begin
  --select * into stur from student where sno='s001';
  for stur in stus loop
  dbms_output.put_line(stur.sname||':'||stur.sage);
  end loop;
end;

5.2 动态游标

declare
  type stu_ref is ref cursor; -- 定义类型
  stus stu_ref; --用自己定义的类型定义游标变量
  str varchar(200);
 myrow student%rowtype;
begin
  str:='select * from student where sage between :X and :Y'; -- :X 占位符
  open stus for str using 20,23; -- 将using后的值传给站位值
  fetch stus into myrow ;
  while (stus%found) loop
    dbms_output.put_line(myrow.sname||':'||myrow.ssex);
    fetch stus into myrow;
  end loop;
  close stus;
end;

5.3 系统游标

/*系统游标*/
declare
  stus sys_refcursor; --oracle 已经定义好了的游标
  str varchar(200);
  myrow student%rowtype;
begin
  str:='select * from student where sage between :X and :Y'; -- :X 占位符
  open stus for str using 20,23; -- 将using后的值传给站位值
  fetch stus into myrow ;
  while (stus%found) loop
    dbms_output.put_line(myrow.sname||':'||myrow.ssex);
    fetch stus into myrow;
  end loop;
  close stus;
end;

六、自定义函数

注：函数要有返回值

/*函数 -- 有返回值*/
create or replace function gc(gender student.ssex%type) return varchar
as
  sex varchar(20);
begin
  if gender = '男' then
    sex:='male';
  else
    sex:='female';
  end if;
  return sex;
end;
/*使用函数*/
select s.sno,s.sname,s.sage,gc(s.ssex) sex from student s;

函数返回游标

/*函数返回游标*/
create or replace function findStuByAge(low int,hg int) return sys_refcursor
as
  p sys_refcursor;
  str varchar(200);
begin
  str:='select * from student where sage between :1 and :2';
  open p for str using low,hg;
  return p;
end;

七、触发器

-- 1.首先创建一个表，用于存放内容，相当于日志记录

create sequence seq_handlog; -- 2.创建序列

create or replace trigger trig_stu_log  -- 3.创建触发器
before  -- 在之前触发，还是在之后触发
update  -- delete/update/insert
on student  -- 位置
for each row --行级触发器
begin
  -- 把更新的内容放进创建的表中，原数据用 ：old.  表示  /  如果是插入，新数据 ：new.表示
  insert into stu_handler_log values(seq_handlog.nextval,:old.sno,:old.sname,:old.sage,:old.ssex,sysdate);
end;

八、存储过程

与函数的区别在于，存储过程有out值，不是return值

create or replace procedure 名字 （可以定义out值）
as
-- 这可以定义变量
begin

end；