15PLSQL--函数，存储过程
4.子程序
定义：ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。
过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并
通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调
用者返回数据，而过程则不返回数据。
4.1 创建函数
语法：
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type --RETURN 子句
{ IS | AS } <类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;
--create or replace function function_demo 声明函数名
--[ (argment [ { IN | IN OUT }] Type,创建形参，参数只带类型，不带宽度，有IN/OUT/IN OUT/ 声明OUT 参数，在调用过程中需要设置变量；
--RETURN return_type 返回结果类型 ，通过PLSQL程序块需要通过声明变量，将函数执行值赋值给变量
--IS/AS 声明变量过程
--BEGIN 函数执行主体
--EXCEPTION
--END;
--因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型.
--OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突
--IN 参数标记表示传递给函数的值在该函数执行中不改变;调用程序向过程内传值，过程内部不能给IN参数赋值
--OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句;由过程内向过程外传递，由外向内传递无效，调用时用变量传递。
-- IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数隐含为 IN。
参数类型
A、无参过程，不加括号
B、参数数据类型不能跟宽度
C、可以是带缺省值的参数
D、参数的三种类型：IN、OUT、IN OUT
a、IN参数由过程外向过程内传递，过程内部不能给IN参数赋值
b、OUT参数由过程内向过程外传递，传入的值无效
c、IN OUT内外都有效
d、过程带有OUT参数的，实参必须是一个变量
--不带参数
CREATE OR REPLACE FUNCTION FUN_TEST01
RETURN date
is v_date date;
BEGIN
SELECT SYSDATE INTO v_date FROM DUAL;
DBMS_OUTPUT.PUT_LINE('今天，你好啊世界');
RETURN v_date;
END;
--查看报错信息：show errors;
--调用函数方法
方法一：
SELECT FUN_TEST01 FROM DUAL; --类似与系统函数
方法二：
DECLARE
v_date date;
BEGIN
v_date:=FUN_TEST01();
DBMS_OUTPUT.PUT_LINE(v_date);
END;
------------------获取某部门工资之和-----------
CREATE OR REPLACE FUNCTION fun_sal(dep_id emp.deptno%TYPE,count_emp out number)
RETURN NUMBER
IS sum_sal NUMBER;
BEGIN
SELECT SUM(sal),COUNT(empno) INTO sum_sal,count_emp FROM emp WHERE deptno=dep_id;
RETURN sum_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查询数据不存在');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
--调用函数方法
DECLARE
count_emp number:=0;
BEGIN
DBMS_OUTPUT.PUT_LINE(fun_sal(20,count_emp));
DBMS_OUTPUT.PUT_LINE(count_emp);
END;
---------------------获取每个部门的工资之和-----------------
CREATE OR REPLACE FUNCTION fun_sal01(dep_id NUMBER,count_emp out NUMBER)
RETURN NUMBER
IS
CURSOR sal_cur IS SELECT * FROM emp WHERE deptno=dep_id;
sum_sal number;
BEGIN
sum_sal :=0;
count_emp :=0;
FOR C IN sal_cur LOOP
count_emp :=count_emp+1;
sum_sal :=sum_sal+c.sal;
END LOOP;
RETURN sum_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查询部门数据不存在');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
--调用函数方法
DECLARE
count_emp number:=0;
BEGIN
DBMS_OUTPUT.PUT_LINE(fun_sal01(&deptno,count_emp));
DBMS_OUTPUT.PUT_LINE(count_emp);
END;
4.2创建存储过程
授权：GRANT EXECUTE ON 过程名 TO 用户;
--------------编辑一个过程函数，调用a,b的值-----------------------
CREATE OR REPLACE PROCEDURE swap_demo(a in out number,b in out number)
IS
c number:=0;
BEGIN
c:=a;
a:=b;
b:=c;
--DBMS_OUTPUT.PUT_LINE(a ||' '||b);
END;
--
DECLARE
a number :=&a;
b number :=&b;
BEGIN
dbms_output.put_line(a||' '||b);
swap_demo(a,b);
dbms_output.put_line(a||' '||b);
END;
------------------编写一个计算器的存储过程-----------------------------
CREATE OR REPLACE PROCEDURE calculate(a number,b number ,op char)
AS
result VARCHAR2(10);
BEGIN
result:=
CASE op
WHEN '+' THEN TO_CHAR(a+b)
WHEN '-' THEN TO_CHAR(a-b)
WHEN '*' THEN TO_CHAR(a*b)
WHEN '/' THEN TO_CHAR(a/b,'0.99')
ELSE '?'
END;
IF result = '?' THEN
DBMS_OUTPUT.PUT_LINE('计算器功能不符合');
ELSE
DBMS_OUTPUT.PUT_LINE(a||' '||op||' '||b||' = '||result);
END IF;
/*IF result='?' THEN
DBMS_OUTPUT.PUT_LINE('运算符无效！');
ELSE
DBMS_OUTPUT.PUT_LINE(a|| ' ' || op || ' ' || b ||' = ' || result);
END IF;*/
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('除数不能为0');
END;
----------------体验 IN ,OUT,IN OUT 参数区别--------------------------------------
CREATe OR REPLACE PROCEDURE test(a in NUMBER,b OUT NUMBER, c IN OUT NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('初始值：'||a||','||b||','||c);
b:=10;
c:=20;
DBMS_OUTPUT.PUT_LINE('改变后的值：'||a||','||b||','||c);
END;
--调用存储
DECLARE
A NUMBER:=50;
B NUMBER:=100;
C NUMBER:=200;
BEGIN
--调用过程前：
DBMS_OUTPUT.PUT_LINE('调用前:'||A||','||B||','||C);
test(A,B,C);
DBMS_OUTPUT.PUT_LINE('调用后:'||A||','||B||','||C);
END;
