Oracle数据库函数应用学习

1：建立无参数的函数

??

??? create or replace function func_datetime

?????? return varchar2

????is

??? begin

????????? return to_char(sysdate,

???????????????????? 'yyyy"年"MM"月"DD"日" HH24"时"mi"分"ss"秒"');

??? end func_datetime;

?

?测试无参数的函数:

? begin

???? dbms_output.put_line(func_datetime);? --通过输出语句来调用函数

??end;

?

2: 带输入值的函数

??? create or replace function func_isHoliday(p_date date) return integer is

????? v_weekday integer := -1;

??? begin

??????? select t_char(p_date, 'd') into v_weekday from dual;

????????if(v_weekday < 2 or v_weekday > 6) then

????????????? return 1;

??????? else

????????????? return 0;

??????? end if;

????end func_isHoliday;

?

?? 调用带输入值的函数：?

??? declare

?????? vdate date := date '2010-12-12';

??? begin

???????dbms_output.put_line(func_holiday(vdate));

????end;

?

3:带有输出输入参数的函数

?? create or replace funcition func_getinfo(empid number, position out varchar2) return varchar2 is

??????? name employees.name % type;

?? begin

?????? select t.name, p.postname into name, position from employees t, post p

?????? where t.position=p.postid and t.id = empid;

??? return name;

? end func_getinfo;

?

?? 调用带有输入和输出参数的函数：

?? declare

??? v_name employees.name % type;

??? v_post post.postname?% type;

???begin

??????? v_name := func_getinfo(2, v_post);

??????? dbms_output.put_line('姓名' || v_name || '职位' || v_post);

???end;

?

? 运行结果:姓名张三职位工程师

?