PL-SQL基础知识与使用

PL/SQL

PL/SQL是对SQL语句的扩展。增加了编程语言的特点，把数据操作和查询语句组织通过逻辑判断、循环等操作实现复杂的功能的程序语言。

在PL/SQL中不能直接写select，如果要写select查询必须要先通过变量接收，但可以直接写insert，update，delete

PL/SQL结构

declare
/* 声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */
begin
/* 执行部分:过程及 SQL 语句,即程序的主要部分 */
exception
/* 执行异常部分:错误处理 */
end;

变量

/*
定义一个变量:变量名 数据结构(number)
				 %type 与表中某个属性列的数据类型相一致，存储一个数据
				 %rowtype 表的行类型变量，数据类型和表的数据结构相一致,存储一行数据
:= 赋值运算符
|| 拼接符
可以用 SELECT 语句对记录变量进行赋值，, 只要保证记录字段与查询结果列表中的字段相配即可。
select into empno from emp 

set serveroutput on 打开输出
dbms_output.put_line(); 输出一行
*/

1. 根据一个员工的工资加奖金。

   declare
   	v_sal emp.sal%type;
   begin
   	select sal into v_sal from emp where empno = 7876;
   	if v_sal>3000 then
   		update emp set comm = nvl(comm,0)+500
           where empno = 7876;
       elsif v_sal>2000 then
   		update emp set comm = nvl(comm,0)+300
           where empno = 7876;
       elsif v_sal>1000 then
   		update emp set comm = nvl(comm,0)+100
           where empno = 7876;
     end if;
   commit;
   end;
   

2. 查询编号7876员工的信息。

   declare
   	row_emp emp%rowtype;
   	v_name emp.ename%type;
   begin
   	select * into row_emp from emp where empno = '7876';
   	select dname into v_name from dept where deptno = row_emp.deptno;
     dbms_output.put_line('工号'||row_emp.empno||'姓名'||row_emp.ename||'工资'||row_emp.sal||'部门名'||v_name);
   end;
   

选择

/*
if 条件 then
	代码块;
	continue;跳出本次循环
	exit;退出循环
	return;退出程序
	goto 锚; 跳到描点继续执行
elsif 条件 then
	代码块
else
	代码块
end if;
*/

循环

用于遍历游标(集合)。

/*
loop:直接开始循环，当满足指定条件后结束循环
while for:当满足指定条件后开始循环
*/

1. loop循环输出1，2，3，5，6

   declare
   	v_i number(2):=0;
   begin
   	loop
   		exit when v_i>=6;--退出条件
   		v_i:=v_i+1;
   		if v_i = 4 then
   			continue;--跳出本次循环
   			/*exit; 退出循环*/
   			/*return; 退出程序*/
   		end if;
   		dbms_output.put_line(v_i);
   	end loop;
   end;
   

2. while循环输出1，2，3，4，5

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

3. for循环输出1，2，3，4，5

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

4. 打印九九乘法表。

   declare
   begin
   	for i in 1..9 loop
   		for j in 1..9 loop
   			if (j<=i) then
   				dbms_output.put(j||'*'||i||'='||i*j||'  ');
   			end if;
   		end loop;
   	dbms_output.put_line(chr(10));/*chr(10):换行*/
   	end loop;
   end;
   

异常

程序在执行时报错，导致无法继续运行。通过异常处理，让程序继续运行。

Oracle预定义的21种异常

declare
     v_i  emp.sal%type;
begin
     dbms_output.put_line('程序开始执行');
     select sal into v_i from emp where empno=7788; 
     dbms_output.put_line('程序执行完成');
     /*异常处理模块,写在最后*/
     exception
            when TOO_MANY_ROWS then   
                dbms_output.put_line('返回多行结果');
            when others then
            	dbms_output.put_line('r');
end;

自定义异常名称

declare
     /*自定义异常类型的名称*/
     NO_ClASS_ID exception;
     /*把自定义的异常类型名称跟异常编号进行绑定*/
     pragma exception_init(NO_ClASS_ID,-02291);
begin
     update studentinfo set  sclassid=99 where stuid=11;
     exception
            when NO_ClASS_ID then   
                    dbms_output.put_line('没有该班级编号');
end;

游标

游标(cursor)：相当于Java中的集合。

处理多行记录使用游标。

不能重复打开一个游标，关闭游标后可以重新打开游标提取数据。

定义游标->打开游标->提取游标数据->关闭游标

/*定义游标*/
cursor cursor_name sys_refcursor;--系统数据类型
type cur_emp_type is ref cursor return emp%rowtype;--自定义游标类型
/*打开游标*/
open cursor_name is select...
/*提取游标数据*/
loop
	exit when cursor_name%notfound;
	fetch cursor_name into v_i;
	dbms_output.put_line(v_i);
/*关闭游标*/
close cursor;

显式游标

显式游标是定义的游标，分为静态和动态游标。

静态游标在定义时里面的数据就固定了。

静态游标

loop循环遍历游标

declare
  row_dept dept%rowtype;
  cursor cur_dept is select * from dept;
begin
  open cur_dept;
  loop
    exit when cur_dept%notfound;
    fetch cur_dept into row_dept;
    dbms_output.put_line('部门编号：'||row_dept.deptno||',部门名：'||row_dept.dname);
  end loop;
  close cur_dept;
end;

for循环遍历游标

declare
  row_dept dept%rowtype;
  cursor cur_dept is select * from dept;
begin
  for row_dept in cur_dept loop
  	dbms_output.put_line('部门编号：'||row_dept.deptno||',部门名：'||row_dept.dname);
  end loop;
end;

动态游标

在程序运行时不能确定需要的数据，需要动态的获取。

declare
cursor cur_dept is select * from dept;
row_dept cur_dept%rowtype;
/*定义动态游标的数据类型：ref cursor*/
/*return emp%rowtype指定该类型游标只能存放emp的数据*/
type cur_emp_type is ref cursor return emp%rowtype;
/*定义游标变量，变量类型是cur_emp_type*/
cur_emp cur_emp_type;
/*cur_emp sys_refcursor 系统动态游标类型*/
row_emp cur_emp%rowtype;

begin
 open cur_dept;
 	loop
 		fetch cur_dept into row_dept;/*fetch循环将游标d数据赋给变量*/
 		exit when cur_dept%notfound;/*游标没有数据了就返回true*/
 		dbms_output.put_line('部门名称'||row_dept.dname);
 		open cur_emp for select * from emp where deptno = row_deptno;
 		loop
 			fetch cur_emp into row_emp;
 			exit when cur_emp%notfound;
 			dbms_output.put_line('员工姓名'||ename)
 		end loop;
 		close cur_emp;
 	end loop;
 close cur_dept;

隐式游标

当进行insert，update，delete操作时，系统会默认生成隐式游标。名字默认为sql

declare
begin
	update emp set sal = 666 where empno = 7788;
	/*isopen:游标是否打开，当insert，update，delete语句执行完后隐式游标自动关闭*/
	if sql%isopen then 
		dbms_output.put_line('sql游标打开');
	else 
		dbms_output.put_line('sql游标未打开');
	end if;
	/*
	found:用于隐式游标判断sql语句是否执行成功
		  用于显式游标判断是否还有数据
	not found:与found相反
	*/
	if sql%found then
		dbms_output.put_line('sql游标有数据');
	else
		dbms_output.put_line('sql游标没有数据');
	end if;
	/*rowcount:返回影响的行数*/
	dbms_output.put_line(sql%rowcount);
end;

事务

一段SQL语句的集合。

这段SQL语句要么全部执行，要么全不执行。

原子性(Atomicity)：事务中SQL语句集合作为一个整体。

一致性(Consistency)：事务执行前后数据保持相对一致。

隔离性(Isolation)：一个事务的执行不能受到另一个事务的影响。

持久性(Durability)：事务执行完成后对数据库的影响是持久的。

并发的后果

数据库是多用户使用的共享资源，若用户并发地对一个数据进行操作，就会产生意料之外的后果。

1. 脏读：一个事务查询到了另一个事务修改了但还未提交的数据。
2. 幻读：一个事务在两次查询中间被另一个事务删行，导致返回了不同的行。
3. 不可重复读：一个事务在两次查询中间数据被另一个事务修改了。

隔离级别

Oracle数据库中PLsql自带隔离机制。

/*转账*/
declare
begin
	update bank set money = money-500 where id = 1;
	savepoint a;--锚点
	update bank set money = money+500 where id = 2;
	commit;
	dbms_output.put_line('转账成功');
	exception
		when others then
			rollback to savepoint a;
			commit;
			dbms_output.put_line('转账失败，数据回滚');
end;

锁

防止事务并发产生的意料之外的后果。

DML锁：在针对表进行DML操作时，保障数据的安全。包括表级锁和行级锁。

---

TM锁(表级锁)：不能操作表。包括共享锁和排他锁。

/*共享锁：一个数据可以加多个共享锁，但需要等待其他事务执行完成才能对表进行写入。*/
lock table emp in share mode;

/*排他锁：一个数据只能加一个排他锁。不允许读写。*/
lock table emp in exclusive mode;

---

TX锁(行级锁）：不能操作该行数据。

当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排他锁。

/*显式的添加行锁*/
select * from emp where empno = 7788 for update;
update emp set sal = 10000 where empno = 7788;

存储过程

类似于Java中的方法。没有返回值。

参数有输入输出类型：

​ in：把外面的数据传入到存储过程中。

​ out：把存储过程中产生的数据传到外面。

​ inout：既能传入又能传出

1. 根据员工的编号，查询员工工资

   /*创建或替代一个存储过程*/
   create or replace procedure query_sal_by_empno(
       /*定义变量，如果没有c小括号也不要写*/
   	v_empno in emp.empno%type,/*输入变量*/
       out_sal out emp.sal%type/*输出变量*/
   ) is /*或as*/
   /*定义变量*/
   begin
   	select sal into out_sal from emp where empno = v_empno;
   end;
   
   /*调用存储过程必须使用PLsql*/
   declare
   	/*调用存储过程时输入类型的参数没必要定义变量，但是输出类型的参数必须要定义参数*/
   	out_sal emp.sal%type;
   begin
   	query_sal_by_empno(7788,out_sal);
   	dbms_output.put_line(out_sal);
   end;
   

2. 根据用户名称模糊查询用户。

   create or replace procedure query_emp_by_ename(v_ename in emp.ename%type,cur_emp out sys_refcursor) 
   is
   begin
     	open cur_emp for select * from emp where ename like concat('%',concat(v_ename,'%'));
   end;
   
   /*调用*/
   declare
     	cur_emp sys_refcursor;
     	row_emp emp%rowtype;
     	v_ename emp.ename%type:='S';
   begin
     	query_emp_by_ename(v_ename,cur_emp);
   	loop
   	exit when cur_emp%notfound;
       fetch cur_emp into row_emp;
   		dbms_output.put_line(row_emp.ename);
     	end loop;
     	close cur_emp;
   end;
   

函数

函数必须有返回值。

可以嵌入到sql语句中执行。

1. 计算工资:工资加奖金,并且入职年限月多加的工资越多。

   /*创建或替代一个函数*/
   create or replace function fun_sal(v_empno emp.empno%type)
   return number/*函数必须有返回值*/
   is
   	v_year number(2);
   	v_sal emp.sal%type;
   begin
     	select (sysdate-hiredate)/365 into v_year from emp where empno = v_empno;
     	select (sal+nvl(comm,0)) into v_sal from emp where empno = v_empno;
     	if v_year>30 then
       	v_sal := v_sal+3000;
     	elsif v_year>20 then 
       	v_sal := v_sal+2000;
     	elsif v_year>10 then
       	v_sal := v_sal+1000;
     	end if;
     	return v_sal;
   end;
   
   /*嵌入到select语句中使用*/
   select ename,fun_sal(empno) from emp ;
   

2. 根据用户名称模糊查询用户。

   create or replace function fun_like_ename(v_ename emp.ename%type)
   return sys_refcursor
   is
     	cur_emp sys_refcursor;
   begin
     	open cur_emp for select * from emp where ename like concat('%',concat(v_ename,'%'));
     	return cur_emp;
   end;
   
   /*调用*/
   declare
     	cur_emp sys_refcursor;/*接收游标*/
     	row_emp emp%rowtype;/*接收行数据*/
   begin
     	cur_emp := fun_like_ename('S');
     	loop
       exit when cur_emp%notfound;
       fetch cur_emp into row_emp;
       	dbms_output.put_line(row_emp.ename);
     	end loop;
     	close cur_emp;
   end;
   

包

对函数和存储过程进行整理。

/*包的定义，相当于java中的接口*/
create or replace package pack_emp
is
	/*定义全局变量，这里不能定义游标类型变量*/
	v_test_q number(4) := 40;
	/*可以定义游标数据类型*/
	type cur_emp_type is ref cursor return emp%type;
	/*定义存储方法*/
	procedure query_emp_ename(v_ename in varchar2,v_cur_emp out sys_refcursor);
	/*定义函数*/
	function sum_sal(v_empno varchar2) return number;
end pack_emp;

/*包的主体，对定义的实现*/
create or replace package body pack_emp
is
	/*定义局部变量*/
	v_test_j number(4) := 20;
	
	/*实现存储过程*/
	procedure query_emp_ename(v_ename in varchar2,v_cur_emp out sys_refcursor)
	begin
		open v_cur_emp for select * from emp where ename = v_ename;
	end query_emp_ename;
	
	/*实现函数*/
	function sum_sal(v_empno varchar2) return number
	is
		v_sal number(4);
	begin
		select (sal+nvl(comm,0)) into v_sal from emp where empno = v_empno;
		return v_sal;
	end sum_sal;
end pack_emp;

动态SQL语句

1. 根据不同条件分页查询emp表数据。

   /*
   分页查询emp数据:
      1. 根据姓名模糊查询
      2. 工资范围查询
      3. 根据部门查询
      4. 页码
   */
   create or replace procedure fy_emp(
       v_ename varchar2,/*通过姓名查询*/
       v_min_sal number,/*最小工资查询*/
       v_max_sal number,/*最大工资查询*/
       v_deptno number,/*部门查询*/
       v_page number,/*页码查询*/
       cur_emp out sys_refcursor/*存储数据*/
   )
   is
   	/*该变量用于拼接动态sql语句的查询条件*/
   	v_where_sql varchar2(1000):=' where 1=1 ';
   	/*用于拼接存储查询语句*/
   	v_query_sql varchar2(2000);
   begin
   	/*判断是否输入条件拼接条件语句*/
   	if v_ename is not null then 
   		v_where_sql:=v_where_sql||' and ename like ''%'||v_ename||'%'' ';
   	end if;
   	
   	if v_min_sal is not null then
   		v_where_sql:=v_where_sql||' and sal>='||v_min_sal;
   	end if;
   	
   	if v_max_sal is not null then
   		v_where_sql:=v_where_sql||' and sal<='||v_max_sal;
   	end if;
   	
   	if v_deptno is not null then
   		v_where_sql:=v_where_sql||' and deptno='||v_deptno;
   	end if;
   	
   	/*拼接查询语句*/
   	/*此处查询结果必须是表结构的所有并顺序且不能写*h*/
   	/*单引号在字符串中为转义符号，''是输出一个单引号*/
   	v_query_sql:='select empno,ename,job,mgr,hiredate,sal,comm,deptno from (select e.*,rownum r from (select * from emp '||v_where_sql||' )e) where r>'||(v_page-1)*3 ||' and r<='|| v_page*3;
   	open cur_emp for v_query_sql;
   end;
   
   
   /*调用*/
   declare 
   	cur_emp sys_refcursor;/*接收数据*/
   	row_emp emp%rowtype;/*接收行数据*/
   begin
   	fy_emp(null,null,9999,30,20,cur_emp);/*调用存储过程，参数必须一一对应且不能缺省*/
       loop
       	fetch cur_emp into row_emp;
       	exit when cur_emp%notfound;
       	dbms_output.put_line(row_emp.ename); 
       end loop;
       close cur_emp;
   end;
   

触发器

触发器（Trigger）是数据库的回调函数，在指定的数据库事件发生时自动执行。

行级触发器：每执行一行都会触发该触发器。

块级触发器：不管操作了几行数据，只会触发一行。

1. 行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器

2. 语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器

3. 当省略for each row 选项时，before 和after 触发器为语句触发器，而instead of 触发器则只能为行触发器。

/*
1. 记录操作类型
2. 操作的行数据
3. 操作人
4. 操作时间
*/
/*操作记录表*/
create table bank_info(
	id number(5) primary key,
    bank_type number(1),/*1:表示修改，2:表示删除*/
    bank_name varchar2(30),
    bank_admin varchar2(30),
    bank_date date
)

/*创建主键序列*/
create sequence seq_bank_info_id
minvalue 1
maxvalue 99999
increment by 1;

/*创建主键触发器*/
create trigger tri_bank_info_id
/*在对表bank_info进行插入操作前*/
before insert on bank_info
for each row
begin
	select seq_bank_info_id.nextval into :new.id from dual;
end;

/*通过触发器实现记录操作日志*/
create or replace trigger tri_bank_to_bank_info
/*在对表bank进行删除或修改之后*/
after delete or update on bank
for each row/*h*/
/*触发的条件*/
when(old.id<60) 
declare
	v_type number(1);
begin
	/*正在进行删除操作*/
	if deleting then
		v_type = 2;
	/*正在进行修改操作*/
	elsif updating then
		v_type = 1;
	end if;
	insert into bank_info(bank_type,bank_name,bank_admin,bank_date) values(v_type,:old.name,'lee',sysdate);
end;