oracle 存储过程和函数

--子程序/*  子程序是被命名的PL/SQL块，可以带参数，多次被调用，模块化  过程&&函数  过程：执行特定操作  函数：返回特定数据    定义：  CREATE [OR REPLACE] PROCEDURE procedure_name(argument [mode] datatype...)  IS|AS  --这里可以声明一些变量，相当于declare块，不过没有declare关键字  BEGIN     statement...  END;  -------******-------      procedure_name 过程名      argument 参数名      mode 参数类型， IN 输入参数 || OUT 输出参数 ||IN OUT . 默认是输入参数      datatype 参数类型 ，不需要指定长度    */--不带参数CREATE OR REPLACE PROCEDURE p_timeISBEGIN   dbms_output.put_line(sysdate);END;--调用call p_time();--默认IN型参数CREATE OR REPLACE PROCEDURE emp_1(num int)AS   v_name emp.ename%type;   v_deptno emp.deptno%type;BEGIN   IF num<10000 THEN--根据传入的参数，是否执行查询      select ename,deptno into v_name,v_deptno from emp where emp.empno=num;      dbms_output.put_line(v_name||'--'||v_deptno);   ELSE       dbms_output.put_line('too big');   END IF;END;--调用BEGIN   emp_1(77880);END;--明确定义参数模式/*   IN      read-only ：pass values into 相当于一个常量   OUT     write-only ：pass values back 相当于一个变量   IN OUT  read/write*/CREATE OR REPLACE PROCEDURE emp_count_1(v_deptid IN int,v_count OUT int)--一个IN,一个OUTISBEGIN  select count(emp.empno) into v_count from emp where emp.deptno=v_deptid;  dbms_output.put_line(v_count);END;--调用DECLARE    v_count int;BEGIN   --传递OUT类型的参数是传递一个类型相符的变量，因为out的类型的是要作为输出的，所以需要具体的变量来存放   emp_count_1(10,v_count);   --传递参数默认按定义顺序，但也可以如下按照参数名称   emp_count_1(v_count=>v_count,v_deptid => 10);   --当然也可以两种方式混合使用   emp_count_1(10,v_count => v_count);END;--感觉out的参数很麻烦的，还不如在过程内直接定义一个变量CREATE OR REPLACE PROCEDURE emp_count_2(v_deptid IN INT)IS   v_count int;BEGIN   select count(emp.empno) into v_count from emp where emp.deptno=v_deptid;   dbms_output.put_line(v_count);END;--调用BEGIN   emp_count_2(10);END;--过程调用过程CREATE OR REPLACE PROCEDURE p_mainISBEGIN--调用上面定义的两个过程   p_time();   emp_count_2(10);END;  --调用BEGIN   p_main();END;--函数/* 有返回值，其它的和过程差不多  CREATE [OR REPLACE] FUNCTION function_name(argument [mode] datatype...) RETURN datatype --返回类型必须 IS|AS [variable datatype...] BEGIN     statement...     Pl/SQL块中必须要有一条return语句 END;*/--CREATE OR REPLACE FUNCTION get_name(u_id IN int) RETURN varchar2AS   v_name emp.ename%type;BEGIN   select ename into v_name from emp where emp.empno=u_id;   RETURN v_name;END;--调用DECLARE   v_name emp.ename%type;   v_job emp.job%type;BEGIN   v_name :=get_name(7788);--直接给变量赋值   select job into v_job from emp where ename=get_name(7788) and rownum<2;--在查询语句中调用   dbms_output.put_line(v_name);END;--返回recordCREATE OR REPLACE FUNCTION get_record(pno in number) RETURN emp%rowtypeIS   v_record emp%rowtype;BEGIN  select *  into v_record from emp where empno=pno;  return v_record;END;--DECLARE    v_record emp%rowtype;BEGIN   v_record := get_record(7788);   dbms_output.put_line(v_record.ename||'--'||v_record.job);END;--如果一个函数没有参数，那么调用他的时候可以不用加()--exp CREATE OR REPLACE FUNCTION get_num(v_id in int) RETURN INTIS CURSOR v_cs is select count(empno) tt,max(empno) mx from emp where emp.deptno=v_id group by deptno; v_num v_cs%rowtype;BEGIN OPEN v_cs; FETCH v_cs into v_num; dbms_output.put_line(v_num.tt||'--'||v_num.mx); close v_cs; RETURN v_num.tt;END;--select get_num(10) from dual;--综合例子--统计emp表中每个部门的人员DECLARE  --声明一个嵌套表用来存放要查询的部门编号  TYPE v_array IS VARRAY(5) OF INT;  v_arr v_array;  --声明一个嵌套表用来存放每一个部门的员工名字  TYPE v_list_type IS TABLE OF VARCHAR2(30);  v_list v_list_type := v_list_type();  --声明一个函数 根据部门查询统计该部门的员工总数和员工名字  FUNCTION get_num(v_dpno IN INT) RETURN v_list_type  IS    v_num INT;    v_i int :=1;    v_name varchar2(30);    v_names v_list_type := v_list_type();    TYPE v_cs_type IS REF CURSOR;    v_cs v_cs_type;      BEGIN    --统计员工总数    select count(empno) into v_num from emp where emp.deptno= v_dpno;    dbms_output.put_line('编号是：'||v_dpno||'的部门的员工数是：'||v_num);    --查询该部门员工名字    OPEN v_cs FOR select ename from emp where emp.deptno =v_dpno;    LOOP       FETCH v_cs into v_name;       EXIT WHEN v_cs%NOTFOUND;       v_names.EXTEND;       v_names(v_i) :=v_name;       v_i :=v_i+1;    END LOOP;    RETURN v_names;--返回查询的员工的结果集  END;  BEGIN  v_arr :=v_array(10,20,30);  FOR i in 1..v_arr.count LOOP    v_list := get_num(v_arr(i));    FOR j in 1..v_list.COUNT LOOP       dbms_output.put_line(v_list(j));    END LOOP;  END LOOP;END;/*--输出结果编号是：10的部门的员工数是：3CLARKKINGMILLER编号是：20的部门的员工数是：5SMITHJONESSCOTTADAMSFORD编号是：30的部门的员工数是：6ALLENWARDMARTINBLAKETURNERJAMES*/--查看当前用户的过程和函数select object_name,created,status from user_objects      where object_type in('PROCEDURE','FUNCTION');--查看过程源代码/* 创建过程后，ORACLE将过程名，源代码及执行代码放到数据字典中。 调用时，程序按照其执行代码直接执行，而不需要从新解析代码。 所以使用子程序性能要优于执行SQL语句。数据字典表 USER_SOURCE*/select text from user_source where name ='EMP_1'; --字符串要大写--删除过程--DROP PROCEDURE procedure_name; --procedure_name过程名字

reference
http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_5009.htm
http://psoug.org/reference/functions.html
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html