Oracle的PL/SQL编程

什么是PL/SQL

PL/SQL（Procedure Language/SQL）
PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分
支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数
据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

PL/SQL的基本语法

DECLARE
	声明部分(变量声明,光标声明,例外声明)
BEGIN
	语句序列(DML语句...)
EXCEPTION
	例外处理语句
END;

声明部分

再把数据类型贴一下

变量的声明方式为先变量名,再变量类型
普通数据类型

--类型后面要跟上长度
var		char(15);

-- :=就是赋值的意思,后面可以直接跟要赋的值
married		boolean :=true;

-- number类型第一个参数为精度,包括小数点后面的位数,第二个参数为小数点后面的位数
psal		number(7,2)

除了普通数据类型,还有引用数据类型

-- 引用型变量，即 my_name 的类型与 emp 表中 ename 列的类型一样
emprec 		emp.ename%type;

--使用into对它进行赋值
declare
	v_ename		emp.ename%type;
begin
	select e.ename into v_ename from emp e where e.empno = 7369;
	dbms_output.put_line(v_ename);
end;



-- 记录型变量,为可以接受emp表一样所有数据的对象
v_obj_emp	emp%rowtype
    
-- 也是使用into进行赋值,注意,它只能接收到一行的数据,如果查询结果为多行或空,就会报错
declare
	v_obj_emp 	emp%rowtype;
begin
	select * into v_obj_emp from emp e where e.empno = 7369;
	dbms_output.put_line(v_obj_emp.ename || ' ' || v_obj_emp.sal);
end;

逻辑控制语句

if语句

-- if语句
declare
	pnum number := # --&的功能为从控制台输入 后面的num显示在控制台
begin
	if pnum = 1 then
		dbms_output.put_line('我是 1');
	end if;
end;

-- if else 语句
declare
	mynum number := #  
begin
	if mynum = 1 then
		dbms_output.put_line('我是 1');
	else
		dbms_output.put_line('我不是 1');
	end if;
end;


-- if  elseif 语句
declare
	mynum number := #
begin
	if mynum < 18 then
		dbms_output.put_line('未成年人');
	elsif mynum >= 18 and mynum < 40 then
		dbms_output.put_line('中年人');
	elsif mynum >= 40 then
		dbms_output.put_line('老年人');
	end if;
end;

循环

-- while loop循环
declare
step number := 1;
begin
	while step <= 10 
	loop
		dbms_output.put_line(step);
		step := step + 1;
	end loop;
end;


-- loop exit when 循环
declare
	step number := 1;
begin
	loop
		exit when step > 10;
		dbms_output.put_line(step);
		step := step + 1;
	end loop;
end;


-- for循环
declare
	step number := 1;
begin
	for step in 1 .. 10 
	loop
		dbms_output.put_line(step);
	end loop;
end;

游标

游标可以理解为java中的集合,用来储存查询返回的多条数据
语法

CURSOR 游标名 [(参数名 数据类型,参数名 数据类型,...)] IS SELECT语句;
--中括号内为可选,一般不用

游标的使用步骤
0. 定义游标和变量 cursor c1 is select ename from emp; pjob emp.empjob%type;

1. 打开游标 open c1;
2. 取一行游标的值 fetch c1 into pjob;
3. 当讲所有值取出后关闭循环exit when c1%notfound
4. 关闭游标,释放资源 close c1;

例子,用游标的方式输出emp表中的员工编号和姓名

declare
	cursor pc is select * from emp; --声明游标
	pemp emp%rowtype; --声明记录型变量
begin
	open pc; --开启游标
	loop
		fetch pc into pemp; --取出游标每一行的数据
		exit when pc%notfound; --当游标所有数据被取出后,跳出循环
		dbms_output.put_line(pemp.empno || ' ' || pemp.ename); -- 执行逻辑操作
	end loop;
	close pc;
end;

另一个例子,为部门号为10的员工涨工资

declare
	cursor pc(dno myemp.deptno%type) is 
	select empno from myemp where deptno = dno; --声明游标
	pno myemp.empno%type; --声明记录型变量
begin
	open pc(20); --开启游标
	loop
		fetch pc into pno; --取出游标的每一行数据赋值给变量
		exit when pc%notfound; --当游标所有值被取出,跳出循环
		update myemp t set t.sal = t.sal + 1000 where t.empno =
		pno; --执行逻辑命令
	end loop;
	close pc; --关闭游标,释放资源
end;

例外

异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。Oracle PL/SQL中的异常就是例外
Oracle中内置了20000个例外,绝大多数需求都可以被满足
比较常用的有

no_data_found (没有找到数据)
too_many_rows (select …into 语句匹配多个行)
zero_divide ( 被零除)
value_error (算术或转换错误)
timeout_on_resource (在等待资源时发生超时)

举个栗子

declare
	pnum number;
begin
	pnum := 1 / 0;
exception
	when zero_divide then
		dbms_output.put_line('被0除');
	when value_error then
		dbms_output.put_line('数值转换错误');
	when others then
		dbms_output.put_line('其他错误');
end;

此外,我们可以自定义例外,处理业务异常
看下面这个例子

declare
	no_emp_found exception; --声明一个例外
	cursor pemp is
	select t.ename from emp t where t.deptno = 50; --声明了一个游标
	pename emp.ename%type; --声明了一个记录型变量
begin
	open pemp;
	fetch pemp into pename;
	if pemp%notfound then
		raise no_emp_found; --触发在下面定义的 no_emp_found
	end if;
	close pemp;
exception
	when no_emp_found then
		dbms_output.put_line('没有找到员工');
	when others then
		dbms_output.put_line('其他错误');
end;

需要注意的是,没事使用自定义例外都要重新编写,声明

储存过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
储存过程就相当于java中封装的方法;

创建格式如下

create [or replace] PROCEDURE  过程名[(名 参数名 in/out  数据类型)] AS
begin
	PLSQL 子程序体； 子程序体；
End;

--或者
create [or replace] PROCEDURE  过程名[(名 参数名 in/out  数据类型)] is
	定义储存过程需要用到的变量
begin
	PLSQL 子程序体； 子程序体；
End 过程名 过程名;

看这个例子 给员工变更工资 并打印 前后工资的情况

--参数要注意下,in为输入的参数,out为输出的参数,这个场景下,我们只需要进行数据输入
--而且参数不需要写数据类型的参数
--一般尽量使用CREATE OR REPLACE这样就算改储存过程已经存在,也可以进行更改
CREATE OR REPLACE PROCEDURE CHANGEEMPSALBYENPNO(ENO IN NUMBER,MON IN NUMBER)
IS
       V_OLD_SAL EMP.SAL%TYPE;
       V_NEW_SAL EMP.SAL%TYPE;
BEGIN
       --查询涨工资之前的工资情况,赋值给变量V_OLD_SAL;
       SELECT E.SAL INTO V_OLD_SAL FROM EMP E 
       WHERE E.EMPNO=ENO;
       --对该员工工资数据进行操作,并提交
       UPDATE EMP SET SAL=SAL+MON where empno=eno;
       COMMIT;
       --再查询一下变更之后的该员工工资
       SELECT E.SAL INTO V_NEW_SAL FROM EMP E
       WHERE E.EMPNO=ENO;
       DBMS_OUTPUT.put_line('操作完成!');
       dbms_output.put_line('涨工资之前:'||V_OLD_SAL);
       dbms_output.put_line('涨工资之后'||V_NEW_SAL);
exception
       WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.put_line('没有查询到数据!');
       WHEN TOO_MANY_ROWS THEN 
           DBMS_OUTPUT.put_line('查询返回多条结果!');
       WHEN OTHERS THEN
           dbms_output.put_line('系统未知错误!');
end;     



--调用这个储存过程非常简单
begin
           CHANGEEMPSALBYENPNO(7369,100);
end;

===========输出结果==============
操作完成!
涨工资之前:800
涨工资之后900

储存函数

储存函数和储存过程本质上没区别。只是函数有限制只能返回一个标量，而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程不行。执行的本质都一样。

create or replace function  函数名(Name in type, Name out type, ...) return 型 数据类型 is
	 结果变量 数据类型;
begin
	return( 结果变量);
end[ 函数名];