-- 19-1:建立无参数的函数create or replace function cur_datetimereturn varchar2isbeginreturn to_char(sysdate,'yyyy年mm月dd日hh24″时mi分ss秒');end;/-- 19-2:建立带有输入参数的函数create or replace function get_sal(name varchar2)return numberasv_sal emp.sal%type;beginselect sal into v_sal from emp where upper(ename)=upper(name);return v_sal;end;/-- 19-3:建立带有输出参数的函数create or replace function get_info(eno number,title out varchar2) return varchar2asname emp.ename%type;beginselect ename,job into name,title from empwhere empno=eno;return name;end;/-- 19-4:建立带有输入输出参数的函数create or replace function get_upd_info(eno number,sal_chg in out number) return varchar2asname emp.ename%type;beginupdate emp set sal=sal+sal_chg where empno=enoreturning ename,sal into name,sal_chg;return name;end;/-- 19-5:建立结果缓存函数create or replace function get_name(no varchar2)return number result_cache relies_on(emp)asv_name emp.ename%type;beginselect ename into v_name from emp where empno=no;return v_name;end;/-- 19-6:调用无参数的函数begindbms_output.put_line(cur_datetime);end;/-- 19-7:调用带有输入参数的函数begindbms_output.put_line('工资:'||get_sal('&name'));end;/-- 19-8:调用带有输出参数的函数declarev_name emp.ename%type;v_job emp.job%type;beginv_name:=get_info(&eno,v_job);dbms_output.put_line('姓名:'||v_name||',岗位:'||v_job);end;/-- 19-9:调用带有输入输出参数的函数declarev_empno emp.empno%type;v_name emp.ename%type;v_salchg emp.sal%type;beginv_empno:=&eno;v_salchg:=&incre;v_name:=get_upd_info(v_empno,v_salchg);dbms_output.put_line('姓名:'||v_name||',新工资:'||v_salchg);end;/-- 19-10:使用位置传递为参数传递变量和数据select get_sal('&name') 工资 from dual;-- 19-11:使用名称传递为参数传递变量和数据var salary numberexec :salary:=get_sal(name=>'&name')-- 19-12:使用组合传递为参数传递变量和数据var name varchar2(10)var sal_chg numberexec :sal_chg:=200exec :name:=get_upd_info(&eno,:sal_chg)print name sal_chg-- 19-13:在sql语句中调用pl/sql函数select get_sal(name=>'scott') salary from dual;-- 19-14:使用异常处理create or replace function get_sal(name varchar2)return numberasv_sal emp.sal%type;beginselect sal into v_sal from empwhere upper(ename)=upper(name);return v_sal;exceptionwhen no_data_found thenraise_application_error(-20000,'该雇员不存在');end;/-- 19-15:使用纪录类型作为返回类型create or replace function get_info(eno number) return emp%rowtypeisemp_record emp%rowtype;beginselect * into emp_record from emp where empno=eno;return emp_record;exceptionwhen no_data_found thenraise_application_error(-20000,'该雇员不存在');end;/declareemp_record emp%rowtype;beginemp_record:=get_info(&eno);dbms_output.put_line('姓名:'||emp_record.ename||',部门号:'||emp_record.deptno);end;/-- 19-16:使用集合类型作为返回类型create or replace type ename_table_type is table of varchar2(10);/create or replace function get_name(dno number) return ename_table_type isename_table ename_table_type;beginselect ename bulk collect into ename_table from emp where deptno=dno;return ename_table;exceptionwhen no_data_found thenraise_application_error(-20099,'该部门不存在');end;/declareename_table ename_table_type;beginename_table:=get_name(&dno);for i in 1..ename_table.count loopdbms_output.put_line('姓名：'||ename_table(i));end loop;end;/-- 19-17:删除函数drop function get_name;-- 19-18:显示编译错误show errors-- 19-19:确定函数状态select object_name from user_objects where status='invalid' and object_type='function';-- 19-20:编译函数alter function get_info compile;-- 19-21:查看函数代码select text from user_source where;
，