Oracle中的函数与存储过程

自定义函数

函数的最大特征是必须返回值

创建自定义函数

创建函数使用create or replace function命令

create or replace function 函数名 return 返回值类型 as
begin
函数定义
end 函数名；

---

create or replace function getEmployeeCount return number as
begin
declare employee_count number;
	begin
	select count(1) into employee_count from employees;
	return employee_count;
	end;
end getEmployeeCount;

调用自定义函数

在pl/sql的环境中利用 函数名+括号 的形式直接调用函数

函数的参数

create or replace function 函数名 （数据类型1 参数1，数据类型2 参数2...）return 返回值类型 as
begin
函数定义
end 函数名； 

---

create or replace function getTableCount(table_name varchar2) return number as
begin
declare record_count number;
aquery_sql varchar(200);
	begin
	query_sql := 'select count(1) from ' || table_name;
	execute immediate_sql query_sql into record_count;
	return record_count;
	end;
end getTableCount;

execute immediate_sql query_sql into record_count;用于执行动态sql语句query_sql，并将结果置于变量record_count中

利用函数的确定性提高数据库效率

对于一个函数来说，其执行步骤和代码是固定不变的。有时，对于一个函数来说，只要传入的参数一定，那么返回值将不会发生任何改变，这样的函数称为具有确定性的函数。

---

对于函数，尤其是运算和操作步骤较为复杂的函数，每次为其传入参数，并最终返回运算结果是较为费时的。而函数的确定性，在于可以根据函数名和参数来缓存执行结果。当再次利用相同的参数调用函数时，Oracle将直接获得缓存值，而不会执行函数的实际代码，从而提高工作效率。

---

create or replace function getEaterAmout(ton number,unitPrice number) return number deterministic as
begin
declaer waterAmount number;
	begin
	if（ton<=2）then
		waterAmount := unitPrice * ton;
	end if;
	if(ton>2 and ton<=4) then 
		waterAmount := unitPrice*2 +unitPrice*2*(ton-2);
	end if;
	if(ton>4) then 
		waterAmount := unitPrice*2 +unitPrice*2*2+(ton-4)*4*unitPrice;
	end if;
	return waterAmount;
	end;
end getEaterAmout;

在return number之后添加deterministic关键字，表名所创建的函数具有确定性。

典型函数–行转列

create or replace function row2column(sqlString varchar2) return varchar2 as 
begin
declare type cu_type is ref cursor;
tmp_cursor cu_type;
tmp_row varchar2(20);
v_result varchar2(500);
	begin
	open tmp_cursor for sqlString;
	fetch tmp_cursor into tmp_row;
	while tmp_cursor%found loop
	v_result := v_result || tmp_row || ',';
	fetch tmp_cursor into tmp_row;
	end loop;
	return rtrim(v_result,',');
	end;
end row2column;

tmp_cursor cu_type;声明一个动态游标；open tmp_cursor for sqlString;在打开游标时，将传入的参数sqlString作为游标定义；while tmp_cursor%found loop则对游标tmp_cursor进行循环操作，将获得的值串联到变量v_result上

select row2column('select employee_name from employees where employee_age>30') result from dual;

获得表employee中，年龄大于30岁的员工姓名

存储过程

函数倾向于处理数据运算；返回值

存储过程倾向于数据库操作，并不返回值

创建存储过程

create or replace procedure 存储过程名称 as
begin
存储过程定义
end 存储过程名称

---

create or replace procedure updateStatus as
begin
update students set status='Act';
commit;
end updateStatus;

执行存储过程

直接利用存储过程名称调用和执行存储过程

begin
	updateStatus；
end；

如果在命令行中，使用execute命令执行存储过程

execute updateStatus；

只进不出的参数–in参数

in参数可以在存储过程内部被访问，但不能被修改

create or replace procedure insertStudent（p_name in varchar2,p_age in number） as
begin
...
end insertStudent;

只出不进的参数–out参数

create or replace peocedure insertStudent(p_name in varchar2,p_age in carchar2,original_count out number,current_count out number) as
begin
declare v_max_id number;
	begin
	if(p_name is null or legnth(p_name)=0) then
		return;
	end if;
	if(p_age<10 or p_age>30) then 
		return;
	end if;
	select count(1) into original_count from students;
	select max(student_id) into v_max_id from students;
	insert into students(student_id,student_name,student_age,status) values (v_max_id+1,p_name,p_age,'act');
	select count(1) into current_count from students;
	end;
end insertStudent;

调用

declare v_countk1 number;
v_count2 number;
begin
insertStudent('王云',17,v_countk1,v_count2);
dbms_output.put_line('原数量' || v_countk1);
dbms_output.put_line('现数量' || v_count2);
end;

可进可出的参数–in out 参数

程序包

程序包的规范

创建规范

CREATE OR REPLACE PACKAGE PKG_COVER_TEMPLATE_BASE AS
 PROCEDURE P_UPDATE_PROJ_COVER_TEMPLATE；
function getStudentsName return varchar2;
end PKG_COVER_TEMPLATE_BASE;

程序包的主体

CREATE OR REPLACE PACKAGE BODY PKG_COVER_TEMPLATE_BASE AS
function getStudentsName return varchar2 is..(具体内容)..;
PROCEDURE P_UPDATE_PROJ_COVER_TEMPLATE（） as..(具体内容)..
end PKG_COVER_TEMPLATE_BASE;

调用程序包中的函数/存储过程

select PKG_COVER_TEMPLATE_BASE.P_UPDATE_PROJ_COVER_TEMPLATE from dual;