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

CREATE [ OR REPLACE ] PROCEDURE [ schema . ] procedure_name

     [ ( parameter_declaration [, parameter_declaration ]... ) ]

     [ invoker_rights_clause ]

     { IS | AS }

     { [ declare_section ] body | call_spec | EXTERNAL} ;

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

           | { OUT | IN OUT } [ NOCOPY ] datatype

AUTHID { CURRENT_USER | DEFINER }

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;

-- 调用删除员工的过程

EXEC remove_emp(1);

-- 调用插入员工的过程

EXECUTE insert_emp(1, 'tommy' , 'lin' , 2);

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 ;

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();

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 ;

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 ;

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];

DROP PROCEDURE logexecution;

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

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

alter procedure pro_backup compile;

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

DESC [RIBE] Procedure_name;