存储过程.txt

oracle数据库存储过程的一些小实例

--创建存储过程
CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p
(
--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。
is_ym   IN CHAR
)
AS
--定义变量
vs_msg    VARCHAR2(4000);    --错误信息变量
vs_ym_beg   CHAR(6);       --起始月份
vs_ym_end   CHAR(6);       --终止月份
vs_ym_sn_beg CHAR(6);      --同期起始月份
vs_ym_sn_end CHAR(6);      --同期终止月份
--定义游标(简单的说就是一个可以遍历的结果集)
CURSOR cur_1 IS
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,SUM(usd_amt)/10000 usd_amt_sn
FROM BGD_AREA_CM_M_BASE_T
   WHERE ym >= vs_ym_sn_beg
   AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;

BEGIN
--用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS TO_DATE 等很常用的函数。
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm');
--先删除表中特定条件的数据。
DELETE FROM xxxxxxxxxxx_T WHERE ym = is_ym;
--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount
DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条');

INSERT INTO xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM BGD_AREA_CM_M_BASE_T
   WHERE ym >= vs_ym_beg
   AND ym <= vs_ym_end
GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条');
--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。
FOR rec IN cur_1 LOOP
   UPDATE xxxxxxxxxxx_T
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
    WHERE area_code = rec.area_code
    AND CMCODE = rec.CMCODE
    AND ym = is_ym;
END LOOP;

COMMIT;
--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。
EXCEPTION
   WHEN OTHERS THEN
      vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
    ROLLBACK;
    --把当前错误记录进日志表。
    INSERT INTO LOG_INFO(proc_name,error_info,op_date)
    VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
    COMMIT;
    RETURN;
END;









CREATE OR REPLACE PROCEDURE PROC_HR_ISNEWUSER( handphone in varchar2,user_count OUT number) AS
BEGIN
  SELECT count(1) into user_count
    FROM HR_USER
    WHERE mobile=handphone
      and status<>9;
END PROC_HR_ISNEWUSER; 
 
 
CREATE OR REPLACE PROCEDURE PROC_HR_REGISTERUSER( handphone in varchar2,user_count OUT number) AS
BEGIN
  --查询用户是否存在
  SELECT count(1) into user_count
    FROM HR_USER
    WHERE mobile=handphone;
  --如果已存在，需要更新状态为0
  if user_count > 0 then 
    update HR_USER set status=0 where mobile=handphone;
  --如果不存在，新增一条记录，状态为9
  else 
    insert into hr_user(id,province,city,mobile,status) 
      values (hr_user_id.nextval,webdev.getpname(handphone),webdev.getcityname(handphone),handphone,9);
  end if;
END PROC_HR_REGISTERUSER;






create or replace procedure P_TQA_F_NSPG(id_mth in number :=0) AS  
v_id_mth number(6);   ---一个简单的存储过程，传入加工月份，声明了个临时变量   
begin                 ----v_id_mth做判断传入加工月份用的   
      
   if id_mth = 0 then    
       select to_number(to_char(sysdate,'yyyymm')) into v_id_mth from dual;    
    else  
       v_id_mth := id_mth;   
    end if;   
    delete TQA_F_NSPG where id_mth = v_id_mth ;----先删除要加工的月份数据   
       
    insert into TQA_F_NSPG(id_mth,      -----插入到加工表中数据   
                           Nsrnbm,   
                           nsr_mc,   
                           Id_Nspgjl,   
                             
                           Sk_Je,   
                           Jj_Je,   
                           swjg_dm,   
                           mth_pg,   
                           Name_Pgry)   
                  select   v_id_mth,   
                           a.nsrnbm,   
                           a.nsr_mc,   
                           2,   
                             
                           case when b.zsxm_dm!='90' then b.je else 0 end,    
                           case when b.zsxm_dm='90' then b.je else 0 end,      
                           a.swjg_dm,   
                           to_char(a.date_pgsj,'yyyymm'),   
                           c.name_jcry   
                   from TQA_D_NSPGJYS a,TQA_D_JYBSBSKQD b,TQA_D_NSPGSJB c where  
                   a.no=b.no_jys and a.no_sj=c.no;   
                      
         
                       
                       
          insert into TQA_F_NSPG(id_mth,           
                                  Nsrnbm,   
                           nsr_mc,   
                           Id_Nspgjl,   
                           
                           Sk_Je,   
                           Jj_Je,   
                           swjg_dm,   
                           mth_pg,   
                           Name_Pgry)   
                              
                     select v_id_mth,   
                           a.nsrnbm,   
                           a.nsr_mc,   
                           case when a.id_nspgjl=3 then 3 else  
                                case when a.id_nspgjl=4 then 4 else    
                                      case when a.id_nspgjl=1 then 1 else 0 end  
                                 end    
                           end,   
                           0,   
                           0,   
                           a.swjg_dm,   
                           to_char(a.nspgrq,'yyyymm'),   
                           c.name_jcry   
                         
                    from  TQA_D_NSPGBG a,TQA_D_NSPGSJB c    
                    where  a.no_sj=c.no and (a.id_nspgjl=3 or a.id_nspgjl=4 or a.id_nspgjl=1);             
            
            
             
    commit;  ----提交   
       
end P_TQA_F_NSPG;  










create or replace procedure P_TQA_F_NSPG(id_mth in number :=0) AS  
v_id_mth number(6);   ---一个简单的存储过程，传入加工月份，声明了个临时变量   
begin                 ----v_id_mth做判断传入加工月份用的   
      
   if id_mth = 0 then    
       select to_number(to_char(sysdate,'yyyymm')) into v_id_mth from dual;    
    else  
       v_id_mth := id_mth;   
    end if;   
    delete TQA_F_NSPG where id_mth = v_id_mth ;----先删除要加工的月份数据   
       
    insert into TQA_F_NSPG(id_mth,      -----插入到加工表中数据   
                           Nsrnbm,   
                           nsr_mc,   
                           Id_Nspgjl,   
                             
                           Sk_Je,   
                           Jj_Je,   
                           swjg_dm,   
                           mth_pg,   
                           Name_Pgry)   
                  select   v_id_mth,   
                           a.nsrnbm,   
                           a.nsr_mc,   
                           2,   
                             
                           case when b.zsxm_dm!='90' then b.je else 0 end,    
                           case when b.zsxm_dm='90' then b.je else 0 end,      
                           a.swjg_dm,   
                           to_char(a.date_pgsj,'yyyymm'),   
                           c.name_jcry   
                   from TQA_D_NSPGJYS a,TQA_D_JYBSBSKQD b,TQA_D_NSPGSJB c where  
                   a.no=b.no_jys and a.no_sj=c.no;   
                      
         
                       
                       
          insert into TQA_F_NSPG(id_mth,           
                                  Nsrnbm,   
                           nsr_mc,   
                           Id_Nspgjl,   
                           
                           Sk_Je,   
                           Jj_Je,   
                           swjg_dm,   
                           mth_pg,   
                           Name_Pgry)   
                              
                     select v_id_mth,   
                           a.nsrnbm,   
                           a.nsr_mc,   
                           case when a.id_nspgjl=3 then 3 else  
                                case when a.id_nspgjl=4 then 4 else    
                                      case when a.id_nspgjl=1 then 1 else 0 end  
                                 end    
                           end,   
                           0,   
                           0,   
                           a.swjg_dm,   
                           to_char(a.nspgrq,'yyyymm'),   
                           c.name_jcry   
                         
                    from  TQA_D_NSPGBG a,TQA_D_NSPGSJB c    
                    where  a.no_sj=c.no and (a.id_nspgjl=3 or a.id_nspgjl=4 or a.id_nspgjl=1);             
            
            
             
    commit;  ----提交   
       
end P_TQA_F_NSPG;  











Oracle方面
１．创建Oracle过程存储
create or replace procedure proce_test(paramin in varchar2,paramout out varchar2,paraminout in out varchar2)
as
  varparam varchar2(28);
begin
  varparam:=paramin;
  paramout:=varparam|| paraminout;  
end;
２．测试过程存储
declare
  param_out varchar2(28);
  param_inout varchar2(28);
begin
  param_inout:=′ff′;   
  proce_test(′dd′,param_out,param_inout);   
  dbms_output.put_line(param_out);
end;

C#方面
引用Oracle组件 
using System;
using System.Data;
using System.Data.OracleClient;    

namespace WebApplication4
{
     public class OraOprater
     {
         private OracleConnection conn=null;
         private OracleCommand cmd=null;
         public OraOprater()
         {
              string mConn="data source=ora9i.ora.com;user id=ora;password=ora";  //连接数据库
              conn=new OracleConnection(mConn);
              try
              {
                   conn.Open();
                   cmd=new OracleCommand();
                   cmd.Connection=conn;
              }
              catch(Exception e)
              {
                   throw e;
              }
         }

         public string SpExeFor(string m_A,string m_B)
         {
                //存储过程的参数声明
              OracleParameter[] parameters={
                                 new OracleParameter("paramin",OracleType.VarChar,20),
                                new OracleParameter("paramout",OracleType.VarChar,20),
                                 new OracleParameter("paraminout",OracleType.VarChar,20)
                                                };
              parameters[0].Value=m_A;
              parameters[2].Value=m_B;
              parameters[0].Direction=ParameterDirection.Input;
              parameters[1].Direction=ParameterDirection.Output;
              parameters[2].Direction=ParameterDirection.InputOutput;
              try
              {
                   RunProcedure("proce_test",parameters);
           &nbsp;      return parameters[1].Value.ToString();
              }
              catch(Exception e)
              {
                   throw e;
              }
         }

         private void RunProcedure(string storedProcName,OracleParameter[] parameters)
         {
              cmd.CommandText=storedProcName;//声明存储过程名
              cmd.CommandType=CommandType.StoredProcedure;
              foreach(OracleParameter parameter in parameters)
              {
                   cmd.Parameters.Add(parameter);
              }
              cmd.ExecuteNonQuery();//执行存储过程
         }
     }
}