函数Function

来源：互联网发布：算法笔记 pdf 编辑：程序博客网时间：2024/06/02 22:24

函数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;

如果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))
  );

0 0