Oracle在PL/SQL中使用存储过程

一、概述

过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储于数据库中。

并通过输入、输出和输入输出参数与其调用者交换信息。唯一区别是函数总向调用者返回数据。

二、存储过程详解

1、创建过程语法：

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
  [ ( parameter_declaration [, parameter_declaration ]... ) ]
  [ invoker_rights_clause ]
  { IS | AS }
  { [ declare_section ] body | call_spec | EXTERNAL} ;

说明：

• procedure_name：过程名称。
• parameter_declaration：参数声明，格式如下：

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
        | { OUT | IN OUT } [ NOCOPY ] datatype

• IN：输入参数。
• OUT：输出参数。
• IN OUT：输入输出参数。
• invoker_rights_clause：这个过程使用谁的权限运行，格式：

AUTHID { CURRENT_USER | DEFINER }

• declare_section：声明部分。
• body：过程块主体，执行部分

2、创建存储过程

带有输入、输出参数的过程

CREATE OR REPLACE PROCEDURE proc_demo
(
  dept_no NUMBER DEFAULT 10,
  sal_sum OUT NUMBER,
  emp_count OUT NUMBER
)
IS
BEGIN
  SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
FROM employees WHERE department_id = dept_no;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
 WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;

3、调用存储过程

调用方式：
1)、当在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令，而在PL/SQL块中过程可以直接引用。

-- 调用删除员工的过程
EXEC remove_emp(1);

-- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);

2)、在PL/SQL语句块中直接调用。

DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
   DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
   DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);
END;

4、C# 调用：

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char); 
cmd.Parameters["v_companycode"].Value = "aa";
cmd.Parameters.Add("v_returnvalue", OracleType.Float).Direction = ParameterDirection.Output;
cmd.ExecuteNoQuery();
string eval = cmd.Parameters["v_returnvalue"].Value.ToString();

三、存储过程返回记录集SYS_REFCURSOR

cursor与REF cursor大致有以下几点区别：

• 静态游标不能返回到客户端，只有PL/SQL才能利用它。ref游标则可以，是从Oracle的存储过站返回结果集的方式。
• PL/SQL静态游标可以是全局的，而ref游标只能在定义它的过程中使用，但ref游标可以从子例程传递到子例程，而普通游标则不能。
• 静态光标比ref游标效率要高。
• sys_refcursor在oracle9i以后系统定义的一个refcursor，主要用于在过程中返回结果集。

1、返回单行语法

create or replace procedure proc_query_rent (
param_region varchar2,  --定义区
param_room number,  --定义室
param_hall number,  --定义厅
param_rentMin number,  --定义租金上限
param_rentMax number,  --定义租金下限
param_resultSet OUT SYS_REFCURSOR --定义out参数返回结果集
) 
as 
begin 
open param_resultSet for select  * from tb_rent
where region like case when param_region IS null then '%' else param_region end
AND room like case when param_room IS null then '%' else to_char(param_room) end
AND hall like case when param_hall IS null then '%' else to_char(param_hall) end
AND rent between case when param_rentMin IS null then 0 else param_rentMin end
AND case when param_rentMax IS null then 99999999 else param_rentMax end;
end;

调用：

declare 
v_rent_rows SYS_REFCURSOR;
v_rent_row tb_rent % rowType;
begin 
 proc_query_rent('山区', null, null, 1200, null, v_rent_rows);
 Dbms_output.put_line('所在区 室 厅 租金');
 loop 
    fetch v_rent_rows into v_rent_row;//单行
    exit when v_rent_rows % NOTFOUND;
    Dbms_output.put_line(v_rent_row.region || '  ' || v_rent_row.room || '  ' || v_rent_row.hall || '  ' || v_rent_row.rent);
 end loop;
 close v_rent_rows;
end;

2、返回多行语法

存储过程：

create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,  out_curEmp out SYS_REFCURSOR) as 
begin 
open out_curEmp for 
SELECT * FROM emp WHERE deptno = in_deptNo ; 
EXCEPTION 
WHEN OTHERS THEN 
RAISE_APPLICATION_ERROR(-20101, 
'Error in getEmpByDept' || SQLCODE ); 
end getEmpByDept;

调用（执行存储过程）：

declare 
cur_emp sys_refcursor;
type emp emp_type is table of yemp%rowtype;
vemps emp_type;
begin
 sp_getEmp(line=>'A5',curemp=>cur_emp);
 fetch cur_emp bulk collect into vemps;
 for i in v_emps.first..v_emps.last loop
    dbms_output.putline(v_emps(i).empid);
 end loop;
 close cur_emp;
end;

C# 调用：

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char).Value = "aa";
cmd.Parameters.Add("curEmp", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];

四、维护存储过程

1、删除过程

可以使用DROP PROCEDURE命令对不需要的过程进行删除

DROP PROCEDURE logexecution;

2、显示过程代码

select text from user_source where name='存储过程名(大写)' and type='PROCEDURE';

3、查看过程状态

select  object_type ,object_name ,status from user_objects where  object_name  = 'procedure';

4、重新编译过程

alter procedure pro_backup compile;

五. 过程与函数比较

1、相同点：

• 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
• 输入参数都可以接受默认值，都可以传值或传引导。
• 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
• 都有声明部分、执行部分和异常处理部分。
• 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

2、不同点：

• 过程：作为PL/SQL语句执行；函数：作为表达式的一部分执行
• 过程：在规范中不包含RETURN子句；函数：必须在规范中包含RETURN子句
• 过程：不返回任何值；函数：必须返回单个值
• 过程：可以RETURN语句，但是与函数不同，它不能用于返回值；函数：必须包含至少一条RETURN语句

六、 与过程相关数据字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相关的权限:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

SQL*PLUS 中，可以用DESCRIBE 命令查看过程的名字及其参数表。

DESC[RIBE] Procedure_name;

关于Oracle在PL/SQL中使用存储过程的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持编程宝库。

 一.查看dblink--查看全部用户SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='DATAB ...