1、概述

PL/SQL:(Procedural Language/SQL,过程化SQL语言),是对SQL(结构化查询语言)的扩展,增加了编程语言的特点,完全可以像Java一样实现逻辑判断、条件循环以及异常处理等,这是标准的SQL很难办到的事情。
PL/SQL里不能单纯的去执行select,所有的select必须用来进行insert/delete/update/赋值等操作
PL/SQL里只能运行DML(操作数据)语句,不能运行DDL语句(操作表),如果要运行DDL,需要动态执行。

2、PL/SQL块结构

2.1 三段式

declare /*声明部分:在此声明 PL/SQL 用到的变量,类型及游标,以及局部的存储过程和函数*/
	-- 一般不要把变量名声明与表中字段名完全一样
	id varchar2(20) := '001'; 

begin /*执行部分: 过程及 SQL 语句 , 即程序的主要部分,必须有。*/
	-- 下面的例子将会删除所有的纪录,而不是id=001的记录;
	delete from employees where id=id;
	-- 如果看不到输出,需要将SQL/PLUS 的环境参数 SERVEROUTPUT 设置为 ON
	-- set serveroutput on; 
	dbms_output.put_line('hello'); 

exception  /*执行异常部分: 错误处理*/
	-- when ... then
	-- when ... then
end;

2.2 变量命名规范

标识符命名规则例子
程序变量V_nameV_name
程序常量C_NameC_company_name
游标变量Name_cursorEmp_cursor
异常标识E_nameE_too_many
表类型Name_table_typeEmp_record_type
Name_tableEmp
记录类型Name_recordEmp_record
SQL*Plus 替代变量P_nameP_sal
绑定变量G_nameG_year_sal

2.3 记录类型

把逻辑相关的数据作为一个单元存储起来,称为域,作用是存放互不相同但逻辑相关的信息,可以理解为自定义数据结构。

declare
	type test_rec is record(
		l_name varchar2(30),
		d_id number(4)
	);
	v_emp test_rec;
begin  
	select last_name,department_id 
	-- into v_emp.l_name,v_emp.d_id
	into v_emp
	from employees where employee_id='200';
	dbms_output.put_line(v_emp.l_name || ', ' || v_emp.d_id);
end;

2.4 动态变量

%TYPE,将变量类型绑定到某个变量或者列上
优点:

  1. 所引用的数据库列的数据类型可以不必知道;
  2. 所引用的数据库列的数据类型可以实时改变。
declare
	v_salary employees.salary%type;
	v_email  employees.email%type;
	v_hire_date employees.hire_date%type;
begin
	select 	salary, email, hire_date 
	into v_salary, v_email,	v_hire_date 
	from employees
	where  employee_id=100;
	dbms_output.put_line(v_salary||','|| v_email ||','	|| v_hire_date); 
end;

%ROWTYPE,返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
优点:

  1. 所引用的数据库中列的个数和数据类型可以不必知道;
  2. 所引用的数据库中列的个数和数据类型可以实时改变
declare
	v_emp employees%rowtype;
begin
	select * into v_emp
	from employees 
	where employee_id = 200;
	dbms_output.put_line(v_emp.last_name || ', ' || v_emp.department_id);
end;

2.5 嵌套表类型

使用嵌套表类型创建具有多个列无限行的变量, 这很像数据库中的表。
太难不学。
例1:

declare
	type dep_table_type is table of departments%rowtype;
	-- 在使用嵌套表之前必须先使用该集合的构造器初始化它
	my_dep_table dep_table_type := dep_table_type();
begin
	-- 嵌套表可以有任意数量的行. 表的大小在必要时可动态地增加或减少: 
	-- extend(x) 方法添加 x 个空元素到集合末尾; trim(x) 方法为去掉集合末尾的 x 个元素.
	my_dep_table.extend(10);
	for i in 1 .. 5 loop
		select * into my_dep_table(i) 
		from departments 
		where department_id=200+10*i;
	end loop;
	dbms_output.put_line(my_dep_table.count());
	dbms_output.put_line(my_dep_table(5).department_id);
end;

例2:

declare 
--声明:嵌套表类型
type bookName is table of varchar2(30);

--初始化一个:嵌套表
v_book1 bookName := bookName('数据库', '高等数学', '软件工程');

--声明一个变量
temp binary_integer := 1;

begin
--v_book1嵌套表:拓展一个元素,并赋值
v_book1.extend;
v_book1(v_book1.last) := 'C++';

dbms_output.put_line('--------------------运行结果:开始线-------------------------');

--拓展了:(一个元素)输出看一下
for i in 1..v_book1.count 
loop
dbms_output.put_line(v_book1(i));
end loop;

dbms_output.put_line('--------------------运行结果:分割线-------------------------');

--拓展两个元素,同时把(嵌套表中的第三个元素的值:赋值给这两个元素)
v_book1.extend(2, 3);

--拓展了:(两个元素)输出看一下
for i in 1..v_book1.count 
loop
dbms_output.put_line(v_book1(i));
end loop;

dbms_output.put_line('--------------------运行结果:分割线-------------------------');

--删除嵌套表(最后两个元素)
v_book1.trim(2);

--删除了:(两个元素)输出看一下
for i in 1..v_book1.count 
loop
dbms_output.put_line(v_book1(i));
end loop;

dbms_output.put_line('--------------------运行结果:分割线-------------------------');

--删除嵌套表(2到3号)元素
v_book1.delete(2,3);

--删除了:(2, 3号元素),变为(稀疏存储了),遍历方法改变
for i in 1..v_book1.count
loop
dbms_output.put_line(v_book1(temp));
--获取temp的一个位置的:索引
temp := v_book1.next(temp);
end loop;

dbms_output.put_line('--------------------运行结果:分割线-------------------------');

end;
方法描述
exists(n)第n行是否存在
count()表的大小
first()返回最小的索引值
last()返回最大的索引值=count()
prior(n)
next(n)Returns the index number that succeeds index n in a PL/SQL table;
trim(n)移除末尾n个
delete()清空表
delete(n)删除第n个
delete(n,m)删除第n-m个

3、流程控制

3.1 条件语句

DECLARE
	v_employee_id employees.employee_id%TYPE := 100;
	v_salary employees.salary%TYPE;
	v_comment VARCHAR2(35);
BEGIN
	select salary into v_salary from  employees where employee_id=v_employee_id;
	if v_salary<1500 then
		v_comment:= 'Fairly less';
	elsif v_salary <3000 then
		v_comment:= 'A little more';
	else
		v_comment:= 'Lots of salary';
	end if;
	DBMS_OUTPUT.PUT_LINE(v_comment);
END;

3.2 循环语句

while方式

DECLARE
	x number :=1;
BEGIN
	while x<=10 loop
		if x=5 then
			-- 可以使用 exit 退出循环
			exit;
		end if;
		dbms_output.put_line('x 的当前值为:'||x);
		x:= x+1;
	end loop;
END;

for方式

create table temp_table(num_col number);
DECLARE
	v_counter number := 10;
BEGIN
	for i in 1..10 loop
	dbms_output.put_line('i 的当前值为:'|| i);
	end loop;
	-- 从大到小可以用reverse修饰
	for v_counter in reverse 20 .. 25 loop
		insert into temp_table (num_col ) values ( v_counter );
	end loop;
	-- for的变量只在for范围内有效
	insert into temp_table(num_col) values (v_counter );
END ;
select * from temp_table;
drop table temp_table;

3.3 null 语句

null 语句来说明“不用做任何事情”的意思,类似占位符

BEGIN
	null;
END;

4、游标

对于非查询语句或者单行结果的查询语句会使用隐式游标,而多行结果的查询语句需要用显示游标。

4.1 基本语法

declare
	-- 定义游标
	cursor c_cursor is 
		select last_name,salary
		from employees
		where rownum < 11
		order by salary;
	v_name employees.last_name%type;
	v_sal employees.salary%type;
begin
	-- 打开游标
	open c_cursor;

	-- 提取游标数据
	fetch c_cursor into v_name,v_sal;

	-- 处理游标数据
	while c_cursor %found loop
		dbms_output.put_line(v_name || ' , ' || v_sal);
		fetch c_cursor into v_name,v_sal;
	end loop;

	-- 关闭游标
	close c_cursor;
end;

4.2 带参游标

declare
	-- 定义游标,在指定数据类型时,不能使用长度约束。
	cursor c_cursor( emp_no number default 11) is 
		select last_name,salary
		from employees
		where rownum < emp_no 
		order by salary;
	v_name employees.last_name%type;
	v_sal employees.salary%type;
begin
	-- 打开游标
	open c_cursor(emp_no => 20);

	-- 提取游标数据
	fetch c_cursor into v_name,v_sal;

	-- 处理游标数据
	while c_cursor %found loop
		dbms_output.put_line(v_name || ' , ' || v_sal);
		fetch c_cursor into v_name,v_sal;
	end loop;

	-- 关闭游标
	close c_cursor;
end;	

4.3 显式游标属性

declare
	v_eid employees.employee_id%type;
	v_sal employees.salary%type;
	cursor c_cursor is
		select employee_id,salary
		from employees;
begin
	open c_cursor;
	loop
		fetch c_cursor into v_eid,v_sal;
		exit when c_cursor %notfound;
		if v_sal <=3000 then
			update employees set salary=3000
			where employee_id=v_eid;
			dbms_output.put_line('员工:'||v_eid||' 工资已更新');
		end if;
	end loop;
	dbms_output.put_line('总记录数:'||c_cursor %rowcount);
	close c_cursor;
end;
属性含义
%FOUND布尔型属性,当最近一次读记录时成功返回,则值为TRUE
%NOTFOUND布尔型属性,与%FOUND 相反;
%ISOPEN布尔型属性,当游标已打开时返回 TRUE;
%ROWCOUNT数字型属性,返回已从游标中读取的记录数。

4.4 简写版游标

PL/SQL 语言提供了游标 FOR 循环语句,自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能;

declare
	cursor c_emp(dep_id number default 50) is
		select last_name,salary 
		from employees
		where department_id=dep_id;
begin
	for v_emp in c_emp(80) loop
		dbms_output.put_line(v_emp.last_name || ' , ' || v_emp.salary);
	end loop;
end;

4.5 游标子查询

begin
	for v_emp in 
		(select last_name,salary from employees) 
	loop
		dbms_output.put_line(v_emp.last_name || ' , ' || v_emp.salary);	
	end loop;
end;	

4.6 隐式游标属性

隐式游标的名字为SQL,其他与显式游标相同,比如SQL%Founds。

declare
	v_name employees.last_name%type;
	v_id employees.employee_id%type := '200';
begin
	update employees
	set last_name='xx'
	where employee_id=v_id;
	if SQL%notfound then
		dbms_output.put_line('查无此人');
	end if;
end;

关于 NO_DATA_FOUND 和 %NOTFOUND 的区别:
SELECT … INTO 语句触发 NO_DATA_FOUND;
当一个显式游标的 WHERE 子句未找到时触发%NOTFOUND;
当 UPDATE 或 DELETE 语句的 WHERE 子句未找到时触发 SQL%NOTFOUND;在提取循环中要用 %NOTFOUND
或%FOUND 来确定循环的退出条件,不要用 NO_DATA_FOUND.

4.7 游标锁

在游标定位下修改或删除表中指定的数据行时,必须使用 FOR UPDATE,以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列,直到提交或回滚时自动释放锁。

declare
	v_dep_id employees.department_id%type := '200';
	cursor emp_cursor is
		select last_name,salary
		from employees
		where department_id = v_dep_id 
		-- 当加上 NOWAIT 子句时,如果这些行真的被另一个会话锁定,
则 OPEN 立即返回并给出
		for update nowait;
begin
	for emp_rec in emp_cursor loop
		if emp_rec.salary < 3000 then
			dbms_output.put_line('hello');
			update employees set salary = 3000
			-- 当前行对应的数据库表中的数据行
			where current of emp_cursor;
		end if;
	end loop;
end;


5、函数

函数和存储过程的唯一区别是函数总向调用者返回数据,而存储过程则不返回数据。

5.1 无参函数

创建函数

create or replace function test_fun
	return date is
		v_date date;
begin
	select sysdate into v_date from dual;
	dbms_output.put_line('函数');
	return v_date;
end;

调用函数

declare
	v_date date;
begin
	v_date := test_fun();
	dbms_output.put_line(v_date);
end;
select test_fun() from dual;

5.2 带参函数

create or replace function get_salary(
	-- 默认参数,只能为输入参数设置
	dep_id employees.department_id%type default 50,
	emp_count out number)
	return number is v_sum number;
begin
	select sum(salary),count(*) into 	v_sum,emp_count 
	from employees
	where department_id = dep_id;
	return v_sum;
end;

调用函数

declare
	v_num number;
	v_sum number;
begin
	-- 按位置传入
	-- v_sum := get_salary(80,v_num);
	-- 按名称传入
	v_sum := get_salary(emp_count => v_num,dep_id => 80);
	dbms_output.put_line('80号部门的工资总和:' || v_sum || ' ,人数: '||v_num);
end;

5.3 参数的传递过程

在函数调用时,ORACLE 将实际参数数据拷贝到输入/输出参数,而当函数正常。
运行退出时,又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

6、存储过程

6.1 创建存储

create or replace procedure del_emp(
	v_empid  employees.employee_id%type) is
begin 
	delete from employees 
	where employee_id = v_empid;
	dbms_output.put_line('编号为:' || v_empid || '的员工已被除名');
end;

sql调用

call del_emp(80);

6.2 out传参

create or replace procedure query_emp(
	v_empid employees.employee_id%type,
	v_name out employees.last_name%type,
	v_sal out employees.salary%type) is
begin
	select last_name,salary into v_name,v_sal
	from employees
	where employee_id = v_empid;
end;

调用

declare
	v1 employees.last_name%type;
	v2 employees.salary%type;
begin
	query_emp(200,v1,v2);
	dbms_output.put_line('姓名:'||v1 || ',工资:' || v2);
end;

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议