PL-SQL

2021-11-26 01:32:19

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;

PL-SQL

上一篇：Linux(Ubuntu)基础安装Jdk、tomcat、mysql


下一篇：Android 浏览器的研究（四）--- Apk的启动和主页的加载过程