Oracle19C入门到熟练021-函数与存储过程

视频教程

新版Oracle19C入门到熟练_哔哩哔哩_bilibili

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

熟练掌握Oracle数据库PL/SQL编程中自定义函数与存储过程实现

概述

ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过 程或函数。过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并 通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

函数

语法如下：
CREATE [OR REPLACE] FUNCTION 函数名
    [ (argment [ { IN | IN OUT }] Type,
     argment [ { IN | OUT | IN OUT } ] Type ]
    [ AUTHID DEFINER | CURRENT_USER ]
    RETURN return_type 
{ IS | AS }
	变量  变量类型
BEGIN
	FUNCTION_body
EXCEPTION
	其它语句
END;

说明：

1) OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突

2) 函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数 标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参 数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数 隐含为 IN。

3) 因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型.

无参数有返回值

需求：定义函数获取id=7839员工的工资并返回

create or replace function my_fun return number is v_sal number;
begin
	select sal into v_sal from emp where empno = 7839;
	return v_sal;
end;

调用：

sql中调用

select my_fun() from dual;

PL/SQL调用

declare
	v_sal number;
begin
	v_sal := my_fun();
	dbms_output.put_line('工资：' || v_sal);
end;

有参数有返回值

需求：定义函数查询指定id员工的工资并返回

create or replace function my_fun2(v_empno number)
	return number is v_sal number;
begin
	select sal into v_sal from emp where empno = v_empno;
	return v_sal;
end;

调用：

sql中调用

select my_fun2(7839) from dual;

PL/SQL调用

declare
	v_sal number;
begin
	v_sal := my_fun2(7839);
	dbms_output.put_line('工资：' || v_sal);
end;

存储过程

存储过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据(这里更多应该是不强调返回值)。

语法如下：
CREATE [OR REPLACE] PROCEDURE  过程名
    [ (argment [ { IN | IN OUT }] Type,
     argment [ { IN | OUT | IN OUT } ] Type ]
    [ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
	变量  变量类型
BEGIN
	PROCEDURE_body
EXCEPTION
	其它语句
END;

需求：定义存储过程获取id=7839员工工资并打印

create or replace procedure my_procd 
	is 
	v_sal number;
begin
	select sal into v_sal from emp where empno = 7839;
	dbms_output.put_line('工资：' || v_sal);
end;

调用

execute my_procd;

不管是函数还是存储过程，参数声明与传值方式都是一致的。

in : 标记表示传递给函数的值在该函数执行中不改变;（可以具体传值， 如果是变量，无法修改变量值）

注意：如果省略标记, 则参数 隐含为 IN。

create or replace procedure pro(v_no in number)
	is
begin
	v_no := 20;  -- 报错
	dbms_output.put_line('v_no：' || v_no);
end;

注意：使用show errors 方式来查看报错信息

SQL> show errors;
Errors for PROCEDURE SCOTT.PRO:
LINE/COL ERROR
-------- ------------------------------
4/2      PLS-00363: 表达式 'V_NO' 不能用作赋值目标
4/2      PL/SQL: Statement ignored

-- 修改后调用
create or replace procedure pro(v_no in number)
	is
begin
	dbms_output.put_line('v_no：' || v_no);
end;

-- 调用1
exec pro(1000);

-- 调用2
declare
	v_no number:=200;
begin
	pro(v_no);
end;

out : 标记表示传递给函数的值可以变化并传递给调用语句（不能传具体值， 传变量，可以改变变量值）

create or replace procedure pro(v_no out number)
	is
begin
	v_no := 20; 
	dbms_output.put_line('v_no：' || v_no);
end;

-- 调用1
exec pro(1000);  -- 报错， 传值需要时一个变量   :变量名

-- 调用2
declare
	v_eno number:=200;
begin
	dbms_output.put_line('前v_eno：' || v_eno);
	pro(v_eno);
	dbms_output.put_line('后v_eno：' || v_eno);
end;

变量声明方式：

var v_bb number;   -- 声明变量

exec :v_bb := 100; -- 变量赋值

print v_bb;  -- 打印变量

in out : 表示传递给函数的值可以变化并传递给调用语句.（值与变量都行）

create or replace procedure pro(v_no in out number)
	is
begin
	v_no := 20; 
	dbms_output.put_line('v_no：' || v_no);
end;

-- 调用1
exec pro(1000);  -- 报错， 传值需要时一个变量

-- 调用2
declare
	v_eno number:=200;
begin
	dbms_output.put_line('前v_eno：' || v_eno);
	pro(v_eno);
	dbms_output.put_line('后v_eno：' || v_eno);
end;

调试

需求：查询指定id的员工工资， 并加100块工资

create or replace procedure addSal(v_eno in number) 
	is
	v_sal number;
begin
	select sal into v_sal from emp where empno = v_eno;
	dbms_output.put_line('工资：' || v_sal);
	update emp set sal = sal + 100 where empno = v_eno;
	dbms_output.put_line('操作成功');
	
end;

debug需要授权

conn sys/admin as sysdba;

GRANT debug any procedure, debug connect session TO scott;

 

删除

DROP PROCEDURE [user.]存储过程名

DROP FUNCTION [user.]函数名