开发者

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

开发者 https://www.devze.com 2022-12-11 11:31 出处:网络 作者: springsnow
目录一、概述二、存储过程详解1、创建过程语法:2、创建存储过程3、调用存储过程4、C# 调用:三、存储过程返回记录集SYS_REFCURSOR1、返回单行语法2、返回多行语法四、维护存储过程1、删除过程2、显示过程代码3、查看
目录
  • 一、概述
  • 二、存储过程详解
    • 1、创建过程语法:
    • 2、创建存储过程
    • 3、调用存储过程
    • 4、C# 调用:
  • 三、存储过程返回记录集SYS_REFCURSOR
    • 1、返回单行语法
    • 2、返回多行语法
  • 四、维护存储过程
    • 1、删除过程
    • 2、显示过程代码
    • 3、查看过程状态
    • 4、重新编译过程
  • 五. 过程与函数比较
    • 1、相同点:
    • 2、不同点:
  • 六、 与过程相关数据字典

    一、概述

    过程和函数统称为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 = Param编程客栈eterDirection.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,  --www.devze.com定义租金下限
      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_empwww.devze.coms.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.Stored开发者_MysqlProcedure;
    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 PROCEDUREwww.devze.com

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

    DESC[RIBE] Procedure_name;

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

    0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    关注公众号