47plsql函数.doc

l 使用过程参数过程参数包括输入参数(IN)，输出参数(OUT)，输入输出参数(IN OUT)不仅要接受输入数据，而且还要输出数据到调用环境。Create or replace function procedure_namearg1 mode1 datatype1,arg2 mode2 datatype2,Return datatypeIs ASPL/SQL block；示例一 建立无参数的过程create or replace function cur2_datetime return varchar2 isbegin return to_char(sysdate,YYYY年MM月DD日hh24时mi分ss秒);end ; / SQL begin 2 dbms_output.put_line(cur2_datetime); 3 end; 4 / 2010年12月26日20时00分26秒 PL/SQL procedure successfully completed示例二 建立有参数的函数create or replace function get_sal(name varchar2) return numberas v_sal emp.sal%type;begin select sal into v_sal from emp where upper(ename)=upper(name); return v_sal;end;示例三 建立带有输出参数的过程create or replace function get_info(eno number,title out varchar2) return varchar2asname emp.empno%type;begin select ename,job into name,title from emp where empno=eno; return name;end;示例四 建立带有输入输出参数的过程create or replace function get_upd_info(eno number,sal_chg in out number) return varchar2asname emp.ename%type;begin update emp set sal=sal+sal_chg where empno=eno returning ename,sal into name,sal_chg; return name;end;示例五 建立结果缓存函数l 调用过程示例一 调用无参数的过程SQL begin 2 dbms_output.put_line(cur2_datetime); 3 end; 4 /示例二 调用带有输入参数的值SQL begin 2 dbms_output.put_line(工资： |get_sal(&name); 3 end; 4 / 工资： 3000 PL/SQL procedure successfully completed示例三 调用带有输出参数的过程SQL declare 2 v_name emp.ename%type; 3 begin 4 update_sal(&eno,&salary,v_name); 5 dbms_output.put_line(姓名 ： |v_name); 6 end; 7 / 姓名 ： cosima PL/SQL procedure successfully completed示例四 带有输入输出参数的过程SQL declare 2 v_empno emp.empno%type; 3 v_name emp.ename%type; 4 v_salchg emp.sal%type; 5 begin 6 v_empno:=&eno; 7 v_salchg:=&incre; 8 v_name:=get_upd_info(v_empno,v_salchg); 9 dbms_output.put_line(姓名 ：|v_name|,新工资 ： |v_salchg); 10 end; 11 / 姓名 ：SMITH,新工资 ： 900 PL/SQL procedure successfully completed示例五 使用位置传递为参数传递变量和数据当使用位置传递时，必须为所有参数提供变量或者输入方法SQL select get_sal(&name) 工资 from dual; 工资- 5000示例六 使用名称传递为参数传递变量和数据名称传递是指通过制定参数名为其提供变量或者数据。需要是用=为参数提供变量或数值。SQL exec :sal_chg:=200 PL/SQL procedure successfully completedsal_chg-200 SQL exec :name:=get_upd_info(&eno,:sal_chg)SQL print name sal_chgSQL print name namel 过程开发示例示例一：使用异常处理create or replace function get_sal(name varchar2) return number is v_sal emp.sal%type;begin select sal into v_sal from emp where upper(ename)=upper(name); return(v_sal);exception when no_data_found then raise_application_error(-20000,该雇员不存在);end get_sal;select get_sal(mary) from dual ORA-20000: 该雇员不存在ORA-06512: 在 SCOTT.GET_SAL, line 9示例二 使用记录类型作为返回类型create or replace function get_info(eno number) return emp%rowtypeisemp_record emp%rowtype;begin select * into emp_record from emp where empno=eno; return emp_record;exception when no_data_found then raise_application_error(-20000,该雇员不存在);end;SQL declare 2 emp_record emp%rowtype; 3 begin 4 emp_record:=get_info(&eno); 5 dbms_output.put_line(姓名 ：|emp_record.ename|,部门号： |emp_record.deptno); 6 end; 7 / 姓名 ：CLARK,部门号： 10 PL/SQL procedure successfully completed示例三 使用集合类型作为返回类型使用集合类型作为返回类型，可将多行数据返回到调用环境或应用程序。create or replace type ename_table_type is table of varchar2(10);create or replace function get_name(dno number) return ename_table_typeis ename_table ename_table_type;begin select ename bulk collect into ename_table from emp where deptno=dno; return ename_table;exception when no_data_found then raise_application_error(-20099,该部门不存在);end;declare ename_table ename_table_type;begin ename_table:=get_name(&dno); for i in 1.ename_table.count loop dbms_output.put_line(姓名： |ename_table(i); end loop;end;姓名： SMITH姓名： JONES姓名： SCOTT姓名： ADAMS姓名： FORD 示例四 使用记录变量作为输入参数l 维护过程示例一 删除函数Drop function add_department;示例二 显示编译错误Show errors示例三 确定函数状态Alter table命令改变表结构时，会将基于该表的子程序转变为invalid状态，为了确定处于invalid状态的对象，可查询user_objects.SQL select object_name from user_objects 2 where status=INVALID and object_name=function ; OBJECT_NAMEGET_EMP示例四 编译过程为了避免过程的运行错误，应该重新编译处于INVALID状态的过程，使用alter procedure命令可以编译过程。Alter function get_ename complile;示例五 查看过程代码通过查询数据字典USER_SOURCE,可以取得当前用户所拥有的子程序名称和代码。Select text from user_source where name=GET_name;SQL SELECT TEXT FROM USER_SOURCE WHERE NAME=GET_EMP; TEXT-procedure get_emp(dno number,ename_table out ename_tab