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_time
IS
BEGIN
   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,一个OUT
IS
BEGIN
  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_main
IS
BEGIN--调用上面定义的两个过程
   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 varchar2
AS
   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;


--返回record
CREATE OR REPLACE FUNCTION get_record(pno in number) RETURN emp%rowtype
IS
   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 INT
IS
 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的部门的员工数是：3
CLARK
KING
MILLER
编号是：20的部门的员工数是：5
SMITH
JONES
SCOTT
ADAMS
FORD
编号是：30的部门的员工数是：6
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
*/





--查看当前用户的过程和函数
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