Oracle：函数和存储过程知识点补充
函数
- 创建函数
create or replace function get_hello_msg
return varchar2 as --指定返回类型
--函数地定义
begin
return 'hello world';
end;
/
- 查看
get_hello_msg的基本信息
select object_name,object_type,status from user_objects where
lower(object_name)='get_hello_msg';
- 函数地定义也被存储在用户字典中，通过
user_source进行查看
select name,type,line,text from user_source where lower(name)='get_hello_msg';
-- user_source用于查询数据库中定义的函数和存储过程的代码
-- name 列标识了函数或者存储过程的名称；
-- type 列标识类型(函数/存储过程)；
-- line列标识了代码的行号，即每行源码都会存储为一条记录；
-- text 则为每一行源码的实际内容
- 查看函数的返回值
-- 开启服务端输出
set serverout on;
--声明一个变量
declare msg varchar2(20);
begin
msg:=get_hello_msg;
dbms_output.put_line(msg);
end;
/
同样，一旦函数被创建，可以像其他函数一样使用该函数；
select get_hello_msg msg from dual;
当函数有参数传入时，参数列表必须用小括号括起来，没有参数时可以省略；当函数没有小括号时，在形式上和变量相同，那么就有可能产生变量冲突。
declare
msg varchar2(20);
get_hello_msg varchar2(20);
begin
get_hello_msg:='welcome message';
msg:=get_hello_msg;
dbms_output.put_line(msg);
end;
/
oracle在遇到变量时，如果变量可用，则使用该变量；如果变量不可用，才尝试将其解释为函数名。
declare msg varchar2(20);
begin
msg:=get_hello_msg();
dbms_output.put_line(msg);
end;
/
带参数
create or replace function get_tax(p_sal number)
return number as
begin
declare tax_sal number;
begin
tax_sal:=p_sal-2000;
if tax_sal <=0 then return 0;
end if;
if tax_sal <=500 then return tax_sal*5/100;
end if;
if tax_sal<=2000 then return tax_sal*10/100-25;
end if;
if tax_sal<=5000 then return tax_sal*15/100-125;
end if;
if tax_sal<=20000 then return tax_sal*20/100-375;
end if;
if tax_sal<=40000 then return tax_sal*25/100-1375;
end if;
if tax_sal<=60000 then return tax_sal*30/100-3375;
end if;
end;
end;
/
- 调用
select get_tax(6000) from dual;
函数的确定性：传入的参数一定，无论函数被调用多少次，都会返回相同的工资额，返回值不会变。
对于确定性的函数，在定义时，可以使用deterministic选项，已告知Oracle创建确定性函数。对于频繁调用的函数，确定性将在很大程度上提高数据库性能。oracle在调用函数时，会查找以前是否使用相同的参数调用过该函数。如果查找到，那么将直接使用先前的执行结果，而不会再次执行该函数定义的操作步骤。
create or replace function get_tax2(p_sal number) return number
deterministic as
begin
declare tax_sal number;
begin
tax_sal:=p_sal-2000;
if tax_sal <=0 then return 0;
end if;
if tax_sal <=500 then return tax_sal*5/100;
end if;
if tax_sal<=2000 then return tax_sal*10/100-25;
end if;
if tax_sal<=5000 then return tax_sal*15/100-125;
end if;
if tax_sal<=20000 then return tax_sal*20/100-375;
end if;
if tax_sal<=40000 then return tax_sal*25/100-1375;
end if;
if tax_sal<=60000 then return tax_sal*30/100-3375;
end if;
end;
end;
/
案例：行转列问题
行转列问题：即将多行数据转为一列。
(回顾：Oracle中存在内置的行转列函数wm_concat)
--因为是多值操作，所以函数中使用了游标
create or replace function get_ename_string return varchar2 as
begin
declare
cursor cu_ename is
select ename from emp
order by empno;
ename varchar2(10);
rowString varchar2(500);
begin
--打开游标
open cu_ename;
fetch cu_ename into ename;
while cu_ename%found
loop
rowString:=rowString||ename||',';
fetch cu_ename into ename;
end loop;
return substr(rowString,1,length(rowString)-1);
close cu_ename;
end;
end;
/
存储过程
函数适用于复杂的统计和计算，最终将结果返回；
而存储过程则适合执行对数据库的更新，尤其时大量数据的更新。
存储过程中可以实现数据库的增删改查等操作，也可以实现复杂的运算，
但不能都直接执行数据库定义语言，即DDL操作。
术语介绍：
DML：Data manipulation language 数据操纵语言
如insert,delete,update,select;
DDL: Data Definiation language 数据库定义语言
如：create procedure等
DCL: Data control language 数据库控制语言
如：grant,deny,revoke等，只有管理员才有这样的权限。
- 存储过程的好处：
1.提高数据库执行效率；
2.提高安全性：存储过程时作为对象存在于数据库中，可以通过对存储过程分配权限来控制整个操作的安全性。同时，使用存储过程实际上实现了数据库操作从编程语言转到了数据库。只要数据库不遭到破坏，这些操作将被一直保留。
3.可复用
参数类型：IN(输入)参数；Out参数；in out参数
- 创建：
create or replace procedure update_books as
begin
update books set book_name='案例' where book_id=5;
commit;
end;
/
- 查看存储过程在数据字典中的信息
select object_name,object_type,status from user_objects where lower(object_name)='update_books';
- 在数据字典中查看
select * from user_source;
- 执行
--第一种
execute(可简写为exec) update_books;
--第二种
begin
update_books;
end;
/
IN参数
create or replace procedure update_books(in_author in varchar2) as
begin
update books set author=in_author where book_id=5;
commit;
end;
/
- 调用
begin
update_books('小明');
end;
/
OUT参数
函数可以有返回值，存储过程并没有显式的返回值，但是可以通过
out参数获得存储过程的结果。
create or replace procedure update_books(in_author in varchar2,out_author out varchar2) as
begin
update books set author=in_author where book_id=5;
select author into out_author from books where book_id=5;
commit;
end;
/
- 调用
set serverout on;
declare updated_author varchar2(10);
begin
update_books('小花',updated_author);
dbms_output.put_line(updated_author);
end;
/
IN OUT参数
in out参数既可以作为输入参数，也可以作为输出参数。一般用于对参数的值
进行处理，并处理结果输出。
典型案例：交换两个变量的值。
create or replace procedure swap(in_out_param1 in out number,in_out_param2 in out number) as
begin
declare params number;
begin
params:=in_out_param1;
in_out_param1:=in_out_param2;
in_out_param2:=params;
end;
end;
/
- 调用
declare param1 number:=25;
param2 number:=35;
begin
swap(param1,param2);
dbms_output.put_line('Param1='||param1);
dbms_output.put_line('Param2='||param2);
end;
/
参数设置注意事项
IN类型的参数，如果不希望严格遵循其顺序，那么可以用另外一种进行参数值的传入——名称表示法。
create or replace procedure update_books(in_author in varchar2,
in_book_name in varchar2) as
begin
update books set author=in_author,book_name=in_book_name where
book_id=5;
commit;
end;
/
- 一一对应调用
```sql
begin
update_books('柳青','柳青传');
end;
/
- 名称表示法
begin
update_books(in_author=>'柳青',in_book_name=>'柳青传');
end;
/
对于IN参数来说，虽然可以利用名称表示法不受参数顺序的约束。但是，一旦使用了名称表示法，那么其后的参数也必须使用名称表示法
存储过程的参数：参数的默认值
定义存储过程时，默认是仅对IN参数而言的，而OUT和IN OUT参数没有默认值；
具有默认值的参数应该置于参数列表的末尾，因为有时用户需要省略该参数；
没有默认值的参数可以遵循“IN参数”-->'OUT参数'-->'In out 参数'。
函数和存储过程的区别示什么？
函数和存储过程实际都是PL/SQL代码块。二者最明显的不同在于，
函数要求返回值，而存储过程不必返回值。
