什么是子程序
命名的 PL/SQL 块,编译并存储在数据库中。
子程序的各个部分:
声明部分
可执行部分
异常处理部分(可选)
子程序的分类:
过程---执行某些操作(PL/SQL)
函数---执行操作并返回值
过程
理解:过程类似java类中的无返回值的方法,声明方法名和参数,写方法体;
创建过程的语法:
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
/*例子*/
CREATE OR REPLACE PROCEDURE find_emp(emp_no NUMBER) --类似与java方法名和参数
AS
empname VARCHAR2(20);--类似与java方法中的局部变量
BEGIN --类似于java中的方法体
SELECT ename INTO empname FROM emp WHERE empno = emp_no;
DBMS_OUTPUT.put_line('员工的名字是:'||empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('员工没有找到');
END;
/*去执行*/
BEGIN
find_emp(456);
END;
过程参数的三种模式
IN:用于接受调用程序的值,默认的参数模式
OUT:用于向调用程序返回值
IN OUT:用于接受调用程序的值,并向调用程序返回更新的值
CREATE OR REPLACE PROCEDURE find_empName(findEmpNo emp.empno%TYPE,
empName OUT emp.ename%TYPE)--声明过程名和参数,其中第一个参数等价于findEmpNo IN emp.empno%TYPE,
AS
BEGIN
SELECT ename INTO empName FROM emp WHERE empno=findEmpNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Dbms_Output.put_line('不存在');
END;
DECLARE
empName emp.ename%TYPE;
BEGIN
find_empName(7499,empName);
Dbms_Output.put_line(empName);
END;
函数
什么是函数:
在oracle中存在很多函数,比如to_char,max等等,这些都是函数。下面我们要自己定义自己的函数。
定义函数:
必须要有return子句
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式:
使用 PL/SQL 块
使用 SQL 语句
自定义比较大小的函数
CREATE OR REPLACE FUNCTION queryMax(a NUMBER,b NUMBER) --声明方法名和参数
RETURN NUMBER --返回值
AS
maxNumber NUMBER; --局部变量
BEGIN --方法体
IF a > b THEN
maxNumber := a;
ELSE
maxNumber := b;
END IF;
RETURN maxNumber;
END;
SELECT queryMax(1,2) FROM dual;--返回2
过程就像是java中返回值是void的函数,函数就像是java中有返回值的函数
自主事务处理
自主事务处理的过程:
1、主事务处理启动独立事务处理
2、然后主事务处理被暂停
3、自主事务处理子程序内的 SQL 操作
4、然后终止自主事务处理
5、恢复主事务处理
定义形式:
PRAGMA AUTONOMOUS_TRANSACTION
用于标记子程序为自主事务处理
不加自主事务处理的情况
CREATE OR REPLACE PROCEDURE t1
AS
BEGIN
rollback;
END;
CREATE OR REPLACE PROCEDURE t2
AS
BEGIN
UPDATE emp SET comm=100 WHERE empno = 7369;
t1();
END;
此时过程t2不会成功更新员工表;
加上自出事务处理之后
CREATE OR REPLACE PROCEDURE t1
AS
PRAGMA AUTONOMOUS_TRANSACTION;--这里加上自主事务处理
BEGIN
rollback;
END;
CREATE OR REPLACE PROCEDURE t2
AS
BEGIN
UPDATE emp SET comm=100 WHERE empno = 7369;
t1();
END;
此时此刻过程t2成功更新数据。
自主事务处理就是在每一个过程中自己处理自己的事务,不会影响调用该过程的其他过程的事务;