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;