函数Function
创建和使用函数
1.函数基础
函数是另一种存储代码,非常类似于存储过程。两者之间的重要区别是,函数是能够返回单个值的PL/SQL语句块。函数可以接收一个或者多个参数,也可以不接收参数,但是函数的可执行部分必须有一个返回子句。必须在函数的头部声明返回值的数据类型。与过程一样,函数不是独立执行的,他必须在某些上下文中才能使用。可以把函数看做是一个语句段。函数的输出必须赋给一个变量,或者在select语句中使用。
2.函数语法
创建函数语法如下所示
CREATE [OR REPLACE] FUNCTION function_name
(parameter list)
RETURN datatype
IS
BEGIN
<body>
RETURN (return_value);
END;
函数未必使用参数,但是必须有一个return值(在函数头部声明该值的数据类型),必须在所有可能执行路径中都要返回一个值。return语句未必是主执行部分的最后一条语句,而且可能存在多个return语句(应该是每个异常都有一条return语句)。函数可能有IN、OUT或者IN OUT参数,但是一般只会看到IN参数,原因在于使用其他参数并不是一种很好的编程惯例。
create or replace function show_description
(i_course_no course.course_no%type)
return varchar2
as
v_description varchar2(50);
BEGIN
select description into v_description from course
where course_no=i_course_no;
return v_description;
exception
when no_data_found then
return('The Course is not in the database');
WHEN OTHERS THEN
RETURN('Error in running show_description');
END;
/
SQL> col description for a50
SQL> select show_description(145) as description from dual;
DESCRIPTION
--------------------------------------------------
Internet Protocols
declare
v_description varchar2(50);
BEGIN
v_description := show_description(&number);
dbms_output.put_line(v_description);
end;
/
Enter value for number: 350
Java Developer II
PL/SQL procedure successfully completed.
Boolean返回值
CREATE OR REPLACE FUNCTION id_is_good(i_student_id IN NUMBER)
RETURN BOOLEAN AS
v_id_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_id_cnt
FROM student
WHERE student_id = i_student_id;
RETURN 1 = v_id_cnt;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END id_is_good;
declare
v_id number := &id;
BEGIN
if id_is_good(v_id)
then
dbms_output.put_line('vaild');
else
dbms_output.put_line('invaild');
end if;
end;
/
BEGIN
if id_is_good(&v_id)
then
dbms_output.put_line('vaild');
else
dbms_output.put_line('invaild');
end if;
end;
/
动手实验
create or replace function new_studnet_id
return student.student_id%type
as
v_student_id student.student_id%type;
BEGIN
select student_id_seq.nextval
into v_student_id from dual;
return v_student_id;
end;
/
select new_studnet_id() from dual;
NEW_STUDNET_ID()
----------------
407
create or replace function zipcode_does_not_exist(i_zipcode in zipcode.zip%type)
return BOOLEAN
as
v_count number;
BEGIN
select count(zip) into v_count from zipcode where zip=i_zipcode;
return 0=v_count;
exception
when others then
return false;
end zipcode_does_not_exist;
/
DECLARE
cons_zip CONSTANT zipcode.zip%TYPE := '&sv_zipcode';
e_zipcode_is_not_valid EXCEPTION;
BEGIN
IF zipcode_does_not_exist(cons_zip) THEN
RAISE e_zipcode_is_not_valid;
ELSE
-- An insert of an instructor's record which
-- makes use of the checked zipcode might go here.
NULL;
END IF;
EXCEPTION
WHEN e_zipcode_is_not_valid THEN
RAISE_APPLICATION_ERROR(-20003,
'Could not find zipcode ' || cons_zip || '.');
END;
/
Enter value for sv_zipcode: 00911
DECLARE
*
ERROR at line 1:
ORA-20003: Could not find zipcode 00911.
ORA-06512: at line 14
-----------------------------------------------------
Enter value for sv_zipcode: 00914
PL/SQL procedure successfully completed.
书上的方法
CREATE OR REPLACE FUNCTION zipcode_does_not_exist(i_zipcode IN zipcode.zip%TYPE)
RETURN BOOLEAN AS
v_dummy char(1);
BEGIN
SELECT NULL INTO v_dummy FROM zipcode WHERE zip = i_zipcode;
-- Meaning the zipcode does exit
RETURN FALSE;
EXCEPTION
WHEN OTHERS THEN
-- The select statement above will cause an exception
-- to be raised if the zipcode is not in the database.
RETURN TRUE;
END zipcode_does_not_exist;
SELECT NULL INTO v_dummy FROM zipcode WHERE zip = i_zipcode;
这句
如果zipcode有值,那么
SQL> select null from zipcode where zip=00914;
N
-
如果zipcode没有值,那么
SQL> select null from zipcode where zip=00911;
no rows selected
返回的是no rows selected,而v_dummy是char(1)类型的
sys_refcursor 函数返回表
create or replace function f_get_emp return sys_refcursor
is
v_emp sys_refcursor;
BEGIN
open v_emp for 'select * from emp';
return v_emp;
end;
/
SQL> select f_get_emp from dual;
F_GET_EMP
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------------------------------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 noname
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 noname
7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 noname
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 noname
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 RESEARCH
7839 KING PRESIDENT 17-NOV-81 5000 10 ACCOUNTING
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 noname
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 RESEARCH
7900 JAMES CLERK 7698 03-DEC-81 950 30 noname
7902 FORD ANALYST 7566 03-DEC-81 3000 20 RESEARCH
7934 MILLER CLERK 7782 23-JAN-82 1300 10 ACCOUNTING
1111 YODA JEDI 17-NOV-81 5000 noname
15 rows selected.
函数返回集合
create or replace type varchar2_tt as table of varchar2(1000);
/
create or replace function f_str2list(in_str in varchar2,
in_delimiter in varchar2 default ',')
return varchar2_tt
/**
||程序说明:将按指定分隔符分隔的字符串转为嵌套表类型变量返回
||输入变量:
|| in_str 字符串,如'a,b,c'
|| in_delimiter 分隔符,默认是逗号
||输出变量:
|| varchar2_tt类型,嵌套表
**/
as
v_str varchar2(32767) default in_str||in_delimiter;
v_result varchar2_tt := varchar2_tt();
i number;
begin
loop
exit when v_str is null;
i := instr(v_str,in_delimiter);
v_result.extend;
v_result(v_result.count) := trim(substr(v_str,1,i-1));
v_str := substr(v_str,i+1);
end loop;
return v_result;
end;
/
var str varchar2(100);
exec :str := 'XY,YZ';
select count(*) from emp where ename in (
select column_value from table(cast(f_str2list(:str) as varchar2_tt))
);