PL/SQL

SQL语句是用来访问和操作关系型数据库得一种通用语言,它适用于所有得关系型数据库. 但是SQL语言只能查询出原本就有得结果. 不能做过程化开发. 因此PL/SQL就在此基础上诞生了

运行过程:

块

PL/SQL块是PL/SQL程序的最基本的单位,它由声明部分, 执行部分, 异常处理三部分组成.

语法结构:

[declare]
	/**
	 * 声明执行部分所需要的变量或常量
	 * 若没有用到变量或常量则可以省略此部分
	 */
begin
	/**
	 * 由begin开始,end结束
	 * 所有的可执行的语句和操作变量都放在这里面
	 */
[exception]
	/**
	 * 由Exception开始,在执行过程中的执行语句出现了错误时
	 * 就会进入异常部分,可以不写此部分
	 * 如果要使用异常模块的话就需要放在最后,并且后面不能有执行语句
	 */
end;

案例:

declare
	--声明一个自定义类型
	type new_type is record(
			v_course_id CLASSINFO.CLASSID%type,
			v_course_number CLASSINFO.CLASSNUMBER%type,
			v_course_teacherID CLASSINFO.CTEACHERID%type,
			v_course_grade CLASSINFO.CLASSGRADE%type
	);
	v_class new_type;
begin
	--得按照顺序查询,不然赋值时就会乱
	select CLASSID,CLASSNUMBER,CTEACHERID,CLASSGRADE 
	into v_class
	from CLASSINFO 
	where CLASSID = 1; --异常101
	dbms_output.put_line('id: '||v_class.v_course_id||'   number: '||v_class.v_course_number);
exception
	when no_data_found then
		dbms_output.put_line('没有找到该学生的信息');
end;

数据类型

数值类型

• number

  可以存储小数和整数类型数据,格式为number(p,s)

• pls_integer

  存储整数类型,存储范围为$(-2^{31})-(2^{31}-1)$,发生内存溢出的时候会直接抛异常报错

• binary_integer

  存储整数类型,存储范围为$(-2^{31})-(2^{31}-1)$,发生内存溢出的时候会给它分配一个number类型的数据

• simple_integer

  是pls_integer的一个子类型,不允许存在null值数据

字符类型

• char

  固定长度的字符串,char(n):默认长度为1,当字符串长度小于n时,会自动右补空格,所以取数据的时候需要注意空格

• varchar2

  存储可变长度的字符串,varchar2(n):最大存储长度为4000个字节.当字符串长度小于n时,不会自动补空格

• long

  存储可变长度的字符串,存储长度可达2G,但是作为PL/SQL变量,和varchar2一样,只能存储32767字节

时间类型

和sql中的时间类型一致

• date
• timestamp

布尔类型

• true
• false
• null

%type

引用数据库中表的某列类型作为某变量的数据类型,也可以直接引用PL/SQL中某个变量作为新变量的数据类型

declare
	v_age STUDENTINFO.STUAGE%TYPE;
	v_age2 v_age%type;
begin	
	select STUAGE into v_age from STUDENTINFO where STUNAME = '火云邪神';
	v_age2 := v_age;
	dbms_output.put_line('v_age = '||v_age||'  v_age2 = '||v_age2);
end;

%rowtype

引用数据库中的一行作为数据类型,即record类型(记录类型)表示一条数据类型.类似Java中的对象实例.可以使用"."来访问记录中的属性

declare
	row_student_column STUDENTINFO%rowtype;
begin
	select * into row_student_column from STUDENTINFO where stuid = 1;
	dbms_output.put_line('stuid = '||row_student_column.stuid);
end;

控制结构

顺序结构

在PL/SQL中使用goto关键字进行程序的跳转,goto需要跳转到指定标签位置,然后顺着标签继续往下执行,标签是使用"<< >>"括起来的标识符,在PL/SQL中具有唯一的id名,在标签后面必须紧跟可执行语句或者PL/SQL块.

goto不能跳转到if语句,case语句,loop语句或者子块中,因此,不到非不得已的情况下,尽量不使用goto语句

declare 
		v_sex STUDENTINFO.STUSEX%type;
begin
		select STUSEX into v_sex from STUDENTINFO where STUID = 1;
		if v_sex = '女' then
				goto woman;
		else
				goto man;
		end if;
		<<woman>>
		dbms_output.put_line('woman');
		<<man>>
		dbms_output.put_line('man');
end;

条件控制

关键字为 if-then-[elsif-else]-end if和多分枝条件case

• if

if 条件 then
	执行体;
elsif 条件1 then
	执行体;
else
	执行体;
end if;

• case

case 选择体
	when 表达式 then 执行体;
	when 表达式1 then 执行体;
	when 表达式2 then 执行体;
	......
	else 执行体;
end case;

--demo
case stuage
	when 16 then ...put(16);
	when 17 then ...put(17);
	when 18 then ...put(18);
	......
	else ...put('i don\'t know');
end case;

循环结构

• loop(常用)

  loop后面要紧跟退出循环的条件

declare
	i number := 1;
begin
	loop
		exit when i > 9;
		dbms_output.put_line(i);
		i := i+1;
	end loop;
end;

• for

  不用在declare中定义变量,for循环会自动在循环的时候定义,并且当for循环游标时,会自动打开游标,循环完成后会自动关闭游标

begin
    for v_i in 1..10 loop
        dbms_output.put_line(v_i);
    end loop;
end;

• while

declare
	v_i number := 1;
begin
	while v_i < 10 loop
		dbms_output.put_line(v_i);
		v_i := v_i + 1;
	end loop;
end;

异常处理

PL/SQL采用的是统一异常处理机制,当异常发生的时候,程序会自动跳转到异常处理部分,交给异常处理程序进行异常匹配,再调用相应的处理方法

如果程序出现异常,而没有找到对应的异常处理程序,则程序会直接中断并且抛出异常, 异常名是一一对应的

PL/SQL中的异常可以分为三类: 预定义异常, 非预定义异常, 自定义异常

declare
	--声明部分
begin
	--执行部分
exception
	--异常部分
	when exception1 then
		--异常1处理
	when exception2 then
		--异常2处理
	......
	when others then
		--其他异常处理程序
end;

示例:

declare 
	v_i studentinfo.stuage%type;
begin
	select stuage into v_i from studentinfo where stuid = 999;
	dbms_output.put_line(v_i);
exception
	when no_data_found then
		dbms_output.put_line('没有找到该学生的信息');
end;

预定义异常

是oracle数据库为我们预定义好开发过程中经常出现的异常名称, Oracle一共提供了25中预定义异常名称

可以通过数据库语句进行查看

select * from dba_source where text like '%EXCEPTION_INIT%' AND NAME = 'STANDARD';

非预定义异常

Oracle中除了预定义的25种异常外,更多的是非预定义异常

他们只存在错误编号和对应的错误信息,但是没有具体的错误名称的对应,所以当我们需要捕获这些非预定义异常时就需要为它们添加一个名称,使得它们能够被异常处理模块进行捕获并抛出处理

非预定义异常和预定义异常的处理机制是一样的

定义非预定义异常分为两步:

• 进行异常声明:声明一个异常名称
• 进行异常名称和错误编号的关联

declare
	--声明一个异常名称
	ex_pk_only exception;
	--主键唯一性错误
	pragma exception_init(ex_pk_only,-00001);
begin
	update studentInfo s set s.stuid = 1 where s.stuid = 2;
exception
	when ex_pk_only then
		dbms_output.put_line('该学生id已经存在,不允许重复');
end;

没有定义异常名称前

定义异常名称后

自定义异常

Oracle中除了预定义的异常外,还为开发者提供了一个异常处理方式, 那就是自定义异常

可以根据自己实际的业务需求和逻辑错误进行自定义异常,并且通过逻辑控制主动抛出自定义异常交给对应的异常处理模块,进行异常处理

自定义异常的步骤如下:

• 异常声明定义: 在声明部分采用Exception关键字定义异常名称
• 异常主动抛出: 在执行部分,通过逻辑控制, 主动使用raise关键字抛出异常, 交给异常处理模块进行处理

declare
	--声明一个异常名称
	ex_ck_sex exception;
	v_sex studentinfo.stusex%type;
begin
	v_sex := 'man';
	if v_sex not in ('男','女') then
		raise ex_ck_sex;
	else
		update studentinfo set stusex = v_sex
		where stuid = 1;
		dbms_output.put_line('修改成功');
	end if;
exception
	when ex_ck_sex then
		dbms_output.put_line('性别只能是男或者女');
end;

游标

是通过关键字cursor来定义的一组Oracle查询出来的数据集,类似于Java中的集合

把查询的数据集存储在内存中,然后通过游标指向其中一条记录,通过循环游标达到循环数据集的目的

其中游标刚开始是总是指向数据集的列名那一行

Oracle中游标分为两类:

• 显式游标

  游标使用之前必须先声明定义,一般是对查询语句的结果集进行定义游标,然后通过打开游标循环获取结果集内的记录,或者根据业务需求跳出循环结束游标的获取

  循环完成后,通过关闭游标,结果集就不能在获取了,全部的操作都是由开发者自己编写完成,自己控制

  其中显示游标有两种:

  • 静态游标

    所查询的结果集是固定的,获取结果集是使用关键字is

  • 动态游标

    根据使用不同的条件返回不同的结果集,获取结果集使用关键字for

• 隐式游标

  指的是PL/SQL自己管理的游标,开发者不能自己控制操作,只能获得它的属性信息

显式游标

Oracle提供了一个默认的系统显式游标sys_re'f

静态游标

1. 声明游标

   声明游标是给游标命名并给游标关联一个查询结果集

declare
	cursor cursor_name is select * from xxx;

2. 打开游标

   游标声明完,可以通过打开游标命令初始化游标指针,游标一旦被打开后,它对应的结果集就是静态的不会改变

open cursor_name;

3. 读取游标中的数据

   读取游标中的数据是通过fetch into语句完成的,把当前游标指针指向的数据行读取到对应的行变量(%rowtype)中

   游标读取一般和循环loop一起使用,用于获取数据集中具体某一行的数据

fetch cursor_name into 行变量

4. 关闭游标

游标使用完后,一定要关闭游标释放资源. 关闭后,该游标关联的结果集就释放了,不能再进行操作了

close cursor_name

案例:

declare
	--定义游标
	cursor cur_stu is select * from studentinfo;
	--定义游标具体某一行的记录变量
	row_stu cur_stu%rowtype;
begin
	--打开游标
	open cur_stu;
	loop
		--游标刚开始时是指向列名的,所以需要先获取数据
		fetch cur_stu into row_stu;
		--当游标中没有数据的时候退出循环
		exit when cur_stu%notfound;
		dbms_output.put_line('id = '||row_stu.stuid||'   name = '||row_stu.stuname);
	end loop;
	--关闭游标
	close cur_stu;
end;

动态游标

declare
	cursor cur_stu is select * from studentinfo;
	row_stu cur_stu%rowtype;
	--定义动态游标的数据类型
	type cur_exam_type is ref cursor return studentexam%rowtype;
	--定义游标变量
	cur_exam cur_exam_type;
	row_exam studentexam%rowtype;
begin
	open cur_stu;
		loop
			fetch cur_stu into row_stu;
			exit when cur_stu%notfound;
			dbms_output.put_line('学生姓名'||row_stu.stuname);
			--获取动态游标
			open cur_exam for select * from studentexam where estuid = row_stu.stuid;
			loop
				fetch cur_exam into row_exam;
				exit when cur_exam%notfound;
				dbms_output.put_line('	考试科目='||row_exam.examsubject||'  成绩为:'||row_exam.examresult)
			end loop;
			close cur_exam;
		end loop;
		
	close cur_stu;
end;

系统游标

sys_refcursor

declare
	cur_stu sys_refcursor;
	row_stu studentinfo%rowtype;
begin
	open cur_stu for select * from studentinfo;
	loop
		fetch cur_stu into row_stu;
		exit when cur_stu%notfound;
		dbms_output.put_line(row_stu.stuname);
	end loop;
	close cur_stu;
end;

显式游标属性

• %notfound

  表示当前游标里面是否还有数据,没有返回true,有数据则返回false,经常用来判断游标是否全部循环完毕

• %found

  和**%notfound**刚好相反,没有数据时返回false,有数据时返回true

• %isopen

  用来判断当前游标是否打开

• %rowcount

  表示当前游标fetch into获取了多少行的记录值,用来计数的

declare
	cursor cur_stu is select * from studentinfo;
	row_stu cur_stu%rowtype;
begin
	open cur_stu;
		loop
			fetch cur_stu into row_stu;
			exit when cur_stu%notfound;
			if cur_stu%found then
				dbms_output.put_line('cur_stu%found = true');
				dbms_output.put_line('这是第'||cur_stu%rowcount||'行');
			end if;
		end loop;
		
	close cur_stu;
end;

事务

事务主要就是用于保持Oracle数据库的数据一致性,是业务上的一个逻辑单元.

可以将它看成是一条或多条sql语句组合成的整体,只要一条sql语句错误,那么整个事务一起进行回滚

银行转账的时候必须要转帐方和收款方一起操作成功才可以将数据写入数据库中,如果其中一方操作失败,那么必须全部回滚,不然的话会造成数据库中数据出现不一致,造成数据混乱. 为了避免数据混乱,必须使用事务

基础语法:

1. set transaction

   设置事务属性,类似于servlet中的setAttribute()

2. commit

   提交事务

3. rollback

   回滚事务,把所有设置的操作都取消

4. savepoint

   设置保存点

5. rollback to savepoint

   回滚到保存点,类似于快照,如果设置了三个保存点,从保存点3回滚到保存点1时,保存点2就无效了, 必须是在没有使用commit之前才能回滚.

declare
		v_stu_name studentinfo.stuname%type;
begin
		--取出stuid为1的学生姓名
		select stuname into v_stu_name from studentinfo where stuid = 1;
		dbms_output.put_line(v_stu_name);
		--设置保存点1
		savepoint f1;
		
		--修改stuid为1的学生的姓名
		update studentinfo set stuname = 'clown' where stuid = 1;
		--设置保存点2
		savepoint f2;
		
		--查看是否设置成功
		select stuname into v_stu_name from studentinfo where stuid = 1;
		dbms_output.put_line('修改后的姓名:'||v_stu_name);
		
		--第二次修改stuid为1的学生姓名
		update studentinfo set stuname = 'losermly' where stuid = 1;
		select stuname into v_stu_name from studentinfo where stuid = 1;
		dbms_output.put_line('第二次修改后的姓名:'||v_stu_name);
		
		--回滚到保存点f2
		rollback to f2;
		select stuname into v_stu_name from studentinfo where stuid = 1;
		dbms_output.put_line('回滚到f2后的姓名:'||v_stu_name);
		
		--回滚到保存点1
		rollback to f1;
		select stuname into v_stu_name from studentinfo where stuid = 1;
		dbms_output.put_line('回滚到f1后的姓名:'||v_stu_name);
		
		commit;
end;

事务的特性(ACID)

1. 原子性(Atomicity)

   原子性是指事务是一个不可分割的工作单位,事务中的操作要么全部成功,要么全部失败

--开始一个事务
begin
	update bank set money = money-100 where name = 'loser';
	update bank set money = money+100 where name = 'clown';
--提交事务
	commit;
end;

2. 一致性(Consistency)

   数据库中的数据要么都是改变前的状态,要么都是改变后的状态

   比如:loser转100元给clown这时loser的账号减少了100元,但是clown的账号并没有增加100元

   loser的账号是更改后的状态,clown的账号还是原来的状态,这就没有达到一致性

3. 隔离性(Isolation)

   事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事物之间要相互隔离

   比如在双十一时,数据库中同时有许多人进行操作,每个人的操作都是在一个自己的事务中

   所以在数据库中,有很多事务会同时存在,但是它们之间不会相互干扰

4. 持久性(Durability)

   持久性是指一个事务一旦被提交,那么它对数据库中的数据改变就是永久性的,即使数据库发生故障,也不应该对其有任何影响

   比如在提交事务后,程序执行完成看到提示时就可以认定事务已经正确提交,即使此时数据库发生错误,也必须要将我们提交的事务处理完成,不然就会造成我们看到事务处理完成,但是数据库因为故障而没有执行事务的重大错误

   如果是回滚,则数据完全没有被修改,就相当于没有发生这件事.事务结束后必须要重新开启一个事务才能修改数据

JDBC中ACID的体现

public class App{
    public static void main(String[] args){
        /**
         * 1.注册JDBC驱动
         * 2.获取连接
         * 3.开启事务
         * 4.创建SQL语句
         * 5.执行SQL语句
         * 6.提交事务
         * 7.关闭连接
         */
        String url = "jdbc:oracle:thin:@192.168.79.135:1521:orcl.localdomain";
        String user = "SCOTT";
        String passwd = "toor";
        try{
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //获取连接
            Connection conn = DriverManager.getConnection(url,user,passwd);
            //开启事务
            conn.setAutoCommit(false);
            //创建sql语句
            String sql = "update bank set money = money - 100 where name = '张三'";
            //执行sql
            PrepareStatement ps = conn.prepareStatement(sql);
            int rs = ps.executeUpdate();
            //给李四添加存款
            sql = "update bank set money = money+100 where name = '李四'";
            ps = conn.prepareStatement(sql);
            rs = ps.executeUpdate();
            //提交事务
            conn.commit();            
        } catch (Exception e){
            e.printStackTrace();
        } finally{
            //关闭连接
            ps.close();
            conn.close();
        }       
    }
}

事务类型

显示事务

开发者主动控制事务的提交和回滚,可以利用命令来控制事务的提交

常见的PL/SQL语句块就是这种类型的事务,必须利用commit提交

隐式事务

指Oracle数据库自己控制事务的提交和回滚

Oracle中的DDL语句(create, drop, alter等),DCL语句(grant, revoke)等都是一经执行直接持久化到数据库,不需要开发者手动提交.或者DML语句(update, delete, update)在数据库设置了自动提交:set autocommit on,也可以不经过主动提交就可以直接持久化到数据库中.

事务的隔离级别

Read uncommitted(读未提交)

事务A要读取一个数据之前,事务B刚对该数据进行了更改,但是还没有进行提交时事务A就将该条数据进行了读取.而后事务B需要对该条数据进行其他的约束操作和修改,最后才能提交.

这样可能出现==脏读==. 也就是说事务B读取到了事务A还未提交的数据.事务A读取了一个不存在的数据或者是一个错误的数据

可能会出现脏读

Read committed(读已提交)

事务A读取数据时,事务B正在对该数据进行操作,此时事务A不能读取到事务B操作后的值,只有等事务B提交以后事务A才能读取到事务B操作的数据

该隔离级别避免了脏读,但是可能出现==不可重复读==. 事务A事先读取了数据, 事务B紧接着对数据做出了更新,并且提交了事务.此时,当事务A再次读取数据时,数据已经发生了改变

解决了脏读的问题

Repeatable read(可重复读)

事务A在读取数据时,不论其他事务如何更改该数据,只要事务A没有结束,事务A所读的数据就一直都是一样的.

因此叫做可重复读隔离级别,这样避免了脏读和不可重复读,但是还是会出现==幻读==, 事务A在读取数据时,事务B将该数据删除并commit了,但在事务A里面还是原来的数据. 这样事务A就读到了不存在的数据

类似于事务A将需要的数据做了一份缓存,然后只从该缓存中读取数据

解决了脏读,不可重复读的问题

serializable(序列化/串行化)

事务A读取数据时,不允许其他事务对该数据进行任何操作,只能一个事务完成后另一个事务在进行操作

解决了脏读, 不可重复读和幻读的问题, 但是效率太低了

其中Oracle只支持read commit和serializable两种级别,默认的是==read commit==级别. mysql支持以上四种,默认的是==repeatable read==级别

锁🔒

数据库是一个多用户使用的共享资源,当多个用户并发存取数据时,在数据库中就会产生多个事务同时操作统一数据的情况

若对并发不做控制,那么就可能造成用户读取或存储不正确的数据,破坏数据的一致性

锁是一种Oracle的访问机制,在访问同一资源时,防止不同事务操作同一个数据时出现数据问题

Oracle锁就是事务的隔离性,当前的事务不能影响其他事务.它的最小范围是数据行,锁定时其他事务蹦年操作该数据记录,但是可以读取锁定时的记录值.因此,Oracle锁的作用就是保证数据的完整性

Oracle锁的类型

排他锁

可以理解为写锁,简称X锁,这种锁是防止资源的共享,用于修改数据.

如果事务A对数据加了X锁,那么其他事务就不能在对该数据添加任何锁,直到事务A完结,排他锁释放

共享锁

可以理解为读锁,简称S锁,加了共享锁的数据, 只能共享读,不能再给它加排他锁进行写的操作

Oracle正式使用锁的机制来实现系统高并发,利用不同类型的排它锁或共享锁来管理并发会话对数据的操作,其中具体的类型有:

DML锁

数据锁,控制DML数据操作.可以细分为行级锁和表级锁

• 行级锁

  行级锁是范围最小的DML锁,主要用来控制数据行的修改, 删除操作. 当对表中的某行数据进行修改时, 需要对其加上行级排他锁,防止其他事务也对其进行修改,等数据修改完成,事务提交时自动释放排他锁.

• 表级锁

  主要作用是在修改表数据时, 会对表结构加一个表级锁, 这个时候不应许其它事物对表进行修改或者删除(DDL操作)

DDL锁

用来保护数据库对象结构的

Oracle创建函数

Oracle创建函数是通过PL/SQL自定义编写的, 通过关键字function按照自己的需求把复杂的业务封装到函数中,函数提供一个返回值,返回给使用者. 这样使用者就不需要了解业务逻辑是如何弄得,将函数中的业务逻辑交给专门的开发人员进行编写.

创建函数的语法

• 输入部分:PL/SQL函数可以有输入参数,在调用函数时,必须给输入参数赋值
• 逻辑计算部分:逻辑计算部分是由PL/SQL块组成业务逻辑计算部分. 这部分主要是通过输入参数, 表数据, sql计算函数等进行逻辑计算得到想要的结果.也就是程序的执行部分
• 输出部分: 通过逻辑计算部分, 我们可以得到一个函数唯一的返回值进行返回(函数必须要有返回值)

create [or replace] function fn_name(
	[参数名1 in 类型,参数名2 in 类型]
) return datatype
is | as --没有区别,通常使用is
	--声明部分,类似declare
begin
	--执行体
end

demo:

返回字符串

--使用create or replace是为了避免数据库中已经存在相同名字的函数
create or replace function fn_select_stu_by_id(
	v_stuid in number
) return varchar2
is 
	re_name varchar2(50);
begin
	select stuname into re_name from studentinfo where stuid=v_stuid;
	return re_name;
end;

--使用自定义函数进行查询
select fn_select_stu_by_id(stuid) from studentinfo;

demo1:

返回游标

create or replace function fn_select_stu 
return sys_refcursor
is
	cur_stu sys_refcursor;
begin
	open cur_stu for select * from studentinfo;
	return cur_stu;	
end;

declare
	cur_stu1 sys_refcursor;
	row_stu studentinfo%rowtype;
begin
	cur_stu1 := fn_select_stu();
	loop
		fetch cur_stu1 into row_stu;
		exit cur_stu1%notfound;
		dbms_output.put_line(row_stu.stuname);
	end loop;
	close cur_stu1;
end;

查看函数的业务逻辑

--函数名得大写
select * from user_source where user_source.name = 'FN_SELECT_STU_BY_ID';

删除函数

drop function 函数名

Oracle存储过程

创建存储过程的语法

如果存储过程没有参数的时候就不能写括号

create [or replace] procedure 过程名
(
	p1 in|out datatype,
    p2 in|out datatype,
    ...
    pn in|out datatype
)
is
	--声明部分
begin
	--执行体
end;

• procedure 是创建存储过程的关键字
• create [or replace] 如果存储过程已经存在则覆盖原有的过程
• in|out 存储过程具有入参和出参两种参数选择,in表示的是从存储过程的外部传入参数进去,out表示的是将存储过程里面的结果返回到存储过程外面
• is 后面跟随存储过程中用到的声明变量,和declare类似

demo:

create or replace procedure pro_stu_sort_by_id(
	cur_stu out sys_refcursor;
)
is

begin
	open cur_stu for select * from studentinfo order by stuid;
end;

declare
	cur_stu sys_refcursor;
	row_stu studentinfo%rowtype;
begin
	pro_stu_sort_by_id(cur_stu);
	loop
		fetch cur_stu into row_stu;
		exit when cur_stu%notfound;
		dbms_output.put_line('stuid = '||row_stu.stuid||'   stuname = '||row_stu.stuname);
	end loop;
	close cur_stu;
exception
	when others then
		dbms_output.put_line('出现异常');
end;

包

和Java中的包类似, 可以通过使用包来分类管理过程和函数

包分为两部分:包规范和包体

create or replace package pack_stu
is
	--定义结构体
-- 	type type_stu is record(
-- 		v_stuname studentinfo.stuname%type;
-- 		v_stuid studentinfo.stuid%type;
-- 	);
	--定义游标(不能使用系统游标)
	type cur_stu is ref cursor return studentinfo%rowtype;
	--定义存储过程
	procedure pro_stu_sort_by_id(
        cur_stu out sys_refcursor
    );
    --定义方法
    function fn_select_stu return sys_refcursor;
end pack_stu;

--包的主体方法
create or replace package body pack_stu
is
	--给包规范中的参数进行具体操作
	procedure pro_stu_sort_by_id(
        cur_stu out sys_refcursor
    ) is
    begin
    	open cur_stu for select * from studentinfo order by stuid;
    end pro_stu_sort_by_id;
    
    --函数
    function fn_select_stu return sys_refcursor 
    is
    	cur_stu sys_refcursor;
    begin
    	open cur_stu for select * from studentinfo;
    	return cur_stu;
    end;
end pack_stu;

--测试
declare
	cur_stu sys_refcursor;
	row_stu studentinfo%rowtype;
begin
	pack_stu.pro_stu_sort_by_id(cur_stu);
	loop
		fetch cur_stu into row_stu;
		exit when cur_stu%notfound;
		dbms_output.put_line(row_stu.stuid);
	end loop;
	close cur_stu;
	
	--函数
	cur_stu := pack_stu.fn_select_stu();
	loop
		fetch cur_stu into row_stu;
		exit when cur_stu%notfound;
		dbms_output.put_line('fn: '||row_stu.stuid);
	end loop;
	close cur_stu;
end;

触发器

是使用者对数据库的对象做特定的操作是,触发的一段PL/SQL程序代码, 叫做触发器. 触发的时间包括对表的DML操作, 用户的DDL操作以及数据库事件等

触发器的作用

触发器可以根据不同的数据库事件进行特定的调用触发器程序块.因此,它可以帮助开发者完成一些在PL/SQL存储过程完成不了的问题,比如操作日志记录, 校验数据的正确性等

触发器的类型

按照用户具体的操作事件的类型,可以分为5种触发器:

数据操作(DML)触发器

定义在表上的,当对表执行insert, update, delete操作时可以触发该触发器

如果按照对表中行级数据进行触发或语句级进行触发又可以分为

• 行级触发器(row)

  每执行一行都会触发

• 块级触发器

  不管业务逻辑中操作了几行数据,都只会触发一次

如果按照修改数据的前后又可以分为after触发器和before触发器

demo1

create table demo_sex(
		stusex varchar2(20)
)

--创建一个触发器
create or replace trigger tri_demo_sex
--在数据插入之前执行
before update or insert on demo_sex
--行级触发,每一行修改都要进行触发
for each row
begin 
	--具体的处理过程
	-- :new代表的是即将更新的数据集合 :old代表的是原来的数据
	if :new.stusex not in('男','女') then
		raise_application_error(-20001,'性别错误,请正确选择');
	end if;
end;

insert into demo_sex(stusex) values('man');

设置触发器记录日志

create table demo_sex(
	stusex varchar2(20),
    sno number(3)
)

--日志表
create table demo_log(
	v_time date,
    v_type varchar2(20),
    old_sno number(3),
    new_sno number(3)
)

--创建一个触发器监听demo_sex表
create or replace trigger tri_demo_to_log
after delete or update or insert on demo_sex
for each row
declare
	v_type varchar2(20);
	old_sno demo_sex.sno%type;
	new_sno demo_sex.sno%type;
begin
	if deleting then
		v_type := 'delete';
		old_sno := :old.sno;
		new_sno := null;
	elsif inserting then
		v_type := 'insert';
		old_sno := null;
		new_sno := :new.sno;
	elsif updating then
		v_type := 'update';
		old_sno := :old.sno;
		new_sno := :new.sno;
	end if;
	
	insert into demo_log (v_time, v_type, old_sno, new_sno)
	values(sysdate, v_type, old_sno, new_sno);
end;

insert into demo_sex(stusex, sno) values('男',10);
insert into demo_sex(stusex, sno) values('男',9);
insert into demo_sex(stusex, sno) values('女',8);
commit;
update demo_sex set sno = 11 where sno = 10;
commit;
delete demo_sex where sno = 8;
commit;

数据定义操作(DDL)触发器

当对数据库对象进行create, alter, drop操作时,触发触发器进行一些操作记录的保存, 或者限定操作

用户和系统事件触发器

作用在数据库系统上, 当进行数据库事件时,触发触发器,一般用来记录登录的相关信息

instead of触发器

作用在视图上,当用户对视图进行操作时,触发该触发器把相关的操作转换为对表进行操作

复合触发器

指的是对数据操作(DML)触发器当中的多种类型触发器进行复合, 比如:一个触发器当中包含有after(before)的行级触发器和after(before)的块级触发器

索引

创建索引

1. 自动创建

   oracle会自动为主键和唯一键创建约束,无法将自动创建的索引删除,当删除唯一键约束时对应的索引会被自动删除

2. 手动创建

   在查询的时候,经常被用来做为查询添加的字段,应该添加索引

create index index_stu_name on studentinfo (stuname);