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过程名字