存储过程和函数

问:存储过程可以做什么？

答:存储过程可以封装比较复杂的SQL语句。或者是，我们可以通过存储过程完成一系列复杂任务的SQL写成独立的程序模块，

   存储在服务器上，供我们多次调用。

create or replace procedure findALL(o_return_int out NUMBER, o_return_string in OUT VARCHAR2) IS
--定义一个游标
CURSOR emp_cursor IS SELECT * FROM emp;
--使用PL/SQL表,类似集合操作
TYPE emp_table_type IS TABLE OF emp%ROWTYPE;
--定义数组变量
emp_table emp_table_type;
BEGIN
 --打开游标 
 IF NOT emp_cursor%ISOPEN THEN
 OPEN emp_cursor;
 END IF;
 FETCH emp_cursor BULK COLLECT INTO emp_table;
 --关闭游标
 IF emp_cursor%ISOPEN THEN
 CLOSE emp_cursor;
 END IF;
 --使用for遍历集合
 FOR v_i IN 1..emp_table.count LOOP
 dbms_output.put_line(emp_table(v_i).ename);
 END LOOP;
 o_return_int :=1;
 o_return_string := 'SUCCESS';
 EXCEPTION WHEN OTHERS THEN 
 o_return_int :=0;
 o_return_string := 'fail';
END findALL;

 调用存储过程的方式有两种:

<1>PL/SQL环境中进行调用。

DECLARE 
 o_return_int NUMBER;
 o_return_string VARCHAR2(30);
BEGIN
 findall(o_return_int,o_return_string);
 dbms_output.put_line(o_return_string || o_return_int);
END;

 

<2>在SQLPLUS环境中调用。

VARIABLE o_return_int NUMBER;
VARIABLE o_return_string VARCHAR2(40);
EXEC findall(:o_return_int,:o_return_string);

 注意：参数前的分号！！！

函数定义:

  函数用于返回特定的数据,当建立函数时，在函数头部必须包含return子句，在函数体内必须包含语句返回的数据。

--通过员工编号查询员工名称

create or replace function findEmpNameByempNo(v_empno IN NUMBER) return varchar2 is
 v_ename emp.ename%TYPE;
begin
 SELECT ename INTO v_ename FROM emp e WHERE e.empno = v_empno; 
 return(v_ename);
 EXCEPTION WHEN OTHERS THEN 
 v_ename := NULL;
 return(v_ename);
end findEmpNameByempNo;

 

--使用PL/SQL调用函数

DECLARE 
 v_result VARCHAR2(30);
BEGIN
 v_result := findEmpNameByempNo(33);
 IF v_result IS NULL THEN
    dbms_output.put_line('v_result is null');   
 ELSE 
    dbms_output.put_line('v_result is not null');
 END IF;
END;

--使用SELECT语句调用

SELECT  findEmpNameByempNo(7369) FROM dual;

--使用SQLPLUS调用

variable v_ename varchar2(50);
call findEmpNameByempNo(7369) into :v_ename;

  注意：参数前的分号！！！