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;
?
? 运行结果:姓名张三职位工程师
?
