oracle存储过程简单例子

最新推荐文章于 2024-08-30 19:56:58 发布
最新推荐文章于 2024-08-30 19:56:58 发布
阅读量97 收藏
点赞数
文章标签： 数据库 python
原文链接：https://my.oschina.net/findurl/blog/829925
版权

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

过程中直接执行sql时候切记变量名不能同列名一样（下面where 条件是错误的会变成全局匹配的类似1=1）

 update ac_account a set a.now_balance=zzCur where a.account_id=account_id;

过程中可能会遇到数据查询为空的情况赋值会抛异常的，这个时候先判断下

querySql:='select count(*)
                from fc_DayAccountView
                where organ_id = '||chr(39)||88||chr(39)||
               ' and account_id = '||chr(39)||account_id||chr(39);
      execute immediate querysql   into isHis;
      
      if isHis>0 then          
      querysql:='select  nvl(dm,0), nvl(cm,0) , nvl(c_num,0)
                from fc_DayAccountView
                where organ_id = '||chr(39)||88||chr(39)||
               ' and account_id = '||chr(39)||account_id||chr(39);
     
       execute immediate querysql   into  debBalance, creBalance, c_num/*into debBalance,creBalance,c_num*/; 
      else 
        dbms_output.put_line('**没有借贷记录**');
      end if;

基本语法

create or replace procedure test1(a in integer,
                                  b in integer,
                                  c out integer)
as
begin
    c:=a+b;
    if a=1 then
    dbms_output.put_line('输入了1');
    elsif a=2 then
    dbms_output.put_line('输入了2');
    end if;
end test1;

返回个游标

create or replace procedure test2(s out sys_refcursor)
as
begin
   open s for select p.user_name from pm_user p;
end test2;

调用

declare
c integer;
s sys_refcursor;
ss pm_user.user_name%type;
begin
 test1(1,3,c);
 dbms_output.put_line(c);
 test2(s);
 loop
   fetch s into ss;
   exit when s%notfound;
   dbms_output.put_line(ss);
 end loop;
 
end;


declare
       f_date varchar2(32);--积数日期
       f_account varchar2(50);--积数账号
       f_debit_money number(15,2);--积数借方余额
       str varchar2(1000);--打印输出语句
       s sys_refcursor;--游标
       debit_dql varchar2(2000);--查询借方余额的sql
       real_debit_money number(15,2);--计算出来的借方余额
       
       errorException exception; --申明异常  
       errorCode number; --异常代号  
       errorMsg varchar2(1000); --异常信息  
       flag varchar2(10);  
       out_return varchar2(2000);--错误信息拼接
begin 
       DBMS_OUTPUT.ENABLE (buffer_size=>null);--表示没有限制.
       str :='';--制空
       open s for 
       select to_char(f.balance_date,'yyyy-MM-dd'),f.account,f.debit_bal from FC_ACCU_BALANCE f where f.interest_sign='0' order by  f.account asc,f.balance_date asc;      
       
       loop
        fetch s into f_date,f_account,f_debit_money;
        exit when s%notfound;
        str :='日期'||f_date||'账号'||f_account||'借方余额'||f_debit_money;
        dbms_output.put_line(str);
        dbms_output.put_line('--开始计算次日期实际借方余额--');
        --重新计算借方金额
     debit_dql  := 'select badd.debit_add-asub.debit_sub
     from(
       select nvl(sum(b.trademoney),0) debit_sub
  from books_bus_allocate_order b
 where b.relationaccount ='||chr(39)||f_account||chr(39)||
   ' and b.billstatus='||chr(39)||100||chr(39)||
   ' and b.is_day_over ='||chr(39)||1||chr(39)||
   ' and b.allocatetype ='||chr(39)||1||chr(39)||
   'and b.day_over <='||chr(39)||f_date||chr(39)||')asub,
(   
select nvl(sum(b.trademoney),0) debit_add
  from books_bus_allocate_order b
 where b.relationaccount ='||chr(39)||f_account||chr(39)||
   'and b.billstatus='||chr(39)||100||chr(39)||
   ' and b.is_day_over ='||chr(39)||1||chr(39)||
   ' and b.allocatetype ='||chr(39)||0||chr(39)||
   ' and b.day_over <= '||chr(39)||f_date||chr(39)||')badd';
     execute immediate debit_dql into real_debit_money;    
      str :='实际借方余额'||real_debit_money;
      dbms_output.put_line(str);
      if real_debit_money<>f_debit_money then
      dbms_output.put_line('**金额有差别**');
      dbms_output.put_line('**开始更新余额**');
      update FC_ACCU_BALANCE f set f.debit_bal=real_debit_money where f.account=f_account and to_char(f.balance_date,'yyyy-MM-dd')=f_date;
      else
      dbms_output.put_line('**正确**');
      end if;   
      end loop;
   dbms_output.put_line('**更新结束提交事务**');   
   commit;   --提交事务
    dbms_output.put_line('**结束**'); 
    exception  
         when errorException then  
              rollback;
              errorCode := SQLCODE;  
              errorMsg := SUBSTR(SQLERRM, 1, 200);   
              flag := 'false';  
              out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 
              dbms_output.put_line(out_return);
         when others then  
               rollback;
               errorCode := SQLCODE;      
               errorMsg := SUBSTR(SQLERRM, 1, 200);   
               flag := 'false';  
               out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 
               dbms_output.put_line(out_return);            
end;


这里可以在数据库创建定时任务 job目录新建

CREATE OR REPLACE PROCEDURE JNMD_INIT_BAL
as 
        now_bank_acc varchar2(32);--当前银行账号
        now_bank_type varchar2(1);--账号类型
        now_acc_no varchar2(32);--保留
        now_bif_code varchar2(32);
        amt0 number(15,2);--初始余额
        amt1 number(15,2);--当日收入明细蓝之和
        amt2 number(15,2);--当日收入明细红之和
        amt3 number(15,2);--当日支出明细蓝之和
        amt4 number(15,2);--当日支出明细蓝之和
        bal  number(15,2);--计算后余额
        nowDate varchar2(32);--当前日期
        -------------------------------------------------------------------
        s sys_refcursor;--游标
        --------------------------------------------------------------------
        status varchar2(1);--字典状态
        over varchar2(1);--是否覆盖
        str varchar2(1000);--打印输出语句
        querysql varchar2(2000);--查询的sql
        isHis    integer;--判断是否有数据
        --------------------------------------------------------------------
        errorException exception; --申明异常  
        errorCode number; --异常代号  
        errorMsg varchar2(1000); --异常信息  
        flag varchar2(10);  
        out_return varchar2(2000);--错误信息拼接
begin
        DBMS_OUTPUT.ENABLE (buffer_size=>null);--表示没有限制.
        nowDate:=to_char(sysdate,'YYYY-MM-DD');--(当前日期)
        str :='';--制空
        status:='';
        over:='';
        --判断是否执行
        querysql:='select b.status,b.over from BT_INITBAL_DICTIONARY b';
         execute immediate querysql into status,over;
         
         if status <> '1' then
         return;
         end if;
          
        open s for 
          select b.bankacc,'1',bank_type as bank_type from bt_bankacc_app b
          union 
          select bb.bank_acc,'2',bb.bif_code from bt_bank_acc bb  where bb.bif_code='9999'; 
        loop
        
        --清楚数据
        now_bank_acc :='';
        now_bank_type:='';
        now_bif_code:='';
        amt0:=0;
        amt1:=0;
        amt2:=0;
        amt3:=0;
        amt4:=0;
        bal:=0;
        isHis:=0;
        --首先查询账号
         fetch s into now_bank_acc,now_bank_type,now_bif_code;
         exit when s%notfound;
          str:='账号:'||now_bank_acc||
               '类型'||now_bank_type||
               '日期'||nowDate||
               '余额计算开始了...';
         dbms_output.put_line(str);
         if now_bank_acc is null or now_bank_acc = '' then 
            CONTINUE;--账号为空这里跳过循环执行
         end if;  
         querysql:='select count(0) from (select bank_bal as baseAmt
  from bis_acc_his_bal a
 where a.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and a.bal_date =
       (select min(b.bal_date)
          from bis_acc_his_bal b
         where b.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
           'and to_char(b.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||')),'||
'(select sum(amt) as amt1
 from bis_acc_his_dtl
  where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
  'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
  'and cd_sign = '||chr(39)||0||chr(39)||
  'and rb_sign = '||chr(39)||2||chr(39)||
  'and status <> -2),
(select sum(amt) as amt2
  from bis_acc_his_dtl
 where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||0||chr(39)||
   'and rb_sign = '||chr(39)||1||chr(39)||
   'and status <> -2),
(select sum(amt) as amt3
  from bis_acc_his_dtl
 where bank_acc ='||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||1||chr(39)||
   'and rb_sign = '||chr(39)||2||chr(39)||
   'and status <> -2),
(select sum(amt) as amt4
  from bis_acc_his_dtl
 where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||1||chr(39)||
   'and rb_sign = '||chr(39)||1||chr(39)||
   'and status <> -2)';
      execute immediate querysql into isHis;    
    if isHis > 0  then  --存在数据  
              
         querysql:='select nvl(baseAmt,0)baseAmt,nvl(amt1,0)amt1,nvl(amt2,0)amt2,nvl(amt3,0)amt3,nvl(amt4,0)amt4 from (select bank_bal as baseAmt
  from bis_acc_his_bal a
 where a.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and a.bal_date =
       (select min(b.bal_date)
          from bis_acc_his_bal b
         where b.bank_acc = '||chr(39)||now_bank_acc||chr(39)||
           'and to_char(b.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||')),'||
'(select sum(amt) as amt1
 from bis_acc_his_dtl
  where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
  'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
  'and cd_sign = '||chr(39)||0||chr(39)||
  'and rb_sign = '||chr(39)||2||chr(39)||
  'and status <> -2),
(select sum(amt) as amt2
  from bis_acc_his_dtl
 where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||0||chr(39)||
   'and rb_sign = '||chr(39)||1||chr(39)||
   'and status <> -2),
(select sum(amt) as amt3
  from bis_acc_his_dtl
 where bank_acc ='||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||1||chr(39)||
   'and rb_sign = '||chr(39)||2||chr(39)||
   'and status <> -2),
(select sum(amt) as amt4
  from bis_acc_his_dtl
 where bank_acc = '||chr(39)||now_bank_acc||chr(39)||
   'and to_char(trans_time,'||chr(39)||'YYYY-MM-DD'||chr(39)||') <= '||chr(39)||nowDate||chr(39)||
   'and cd_sign = '||chr(39)||1||chr(39)||
   'and rb_sign = '||chr(39)||1||chr(39)||
   'and status <> -2)';
   dbms_output.put_line(querysql);
    execute immediate querysql into amt0,amt1,amt2,amt3,amt4;
    end if;
    
    bal:=amt0+amt1+amt4-amt2-amt3;
    str:='初始金额:'||amt0||
         '收入蓝之和:'||amt1||
         '收入红之和:'||amt2||
         '支出蓝之和:'||amt3||
         '支出红之和:'||amt4||
         '计算余额:'||bal;
     dbms_output.put_line(str);   
     
     isHis:=0;
     querysql:='select count(1) from bis_acc_his_bal t where t.bank_acc='||chr(39)||now_bank_acc||chr(39)||' and to_char(t.bal_date,'||chr(39)||'YYYY-MM-DD'||chr(39)||')= '||chr(39)||nowDate||chr(39); 
      dbms_output.put_line(querysql);
      execute immediate querysql into isHis;
     if isHis = 0 then
      str:='插入操作开始了...';
       dbms_output.put_line(str);
     insert into bis_acc_his_bal
       (acc_no,
        bank_acc,
        bif_code,
        bal_date,
        get_date,
        bank_bal,
        --status,
        --nextchecker,
        --corp_id,
        avail_bal)
     values
       (now_acc_no,now_bank_acc,now_bif_code, to_date(nowDate,'YYYY-MM-DD'),sysdate, bal,bal); 
     elsif over = '1' then
       str:='更新操作开始了...';
       dbms_output.put_line(str);
       update  bis_acc_his_bal set bank_bal=bal,avail_bal=bal where bank_acc=now_bank_acc and to_char( bal_date,'YYYY-MM-DD')=nowDate; 
     end if;  

    end loop; 
    
    
    dbms_output.put_line('**更新结束提交事务**');   
     commit;   --提交事务
    dbms_output.put_line('**结束**'); 
    exception  
         when errorException then  
              rollback;
              errorCode := SQLCODE;  
              errorMsg := SUBSTR(SQLERRM, 1, 200);   
              flag := 'false';  
              out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 
              dbms_output.put_line(out_return);
         when others then  
               rollback;
               errorCode := SQLCODE;      
               errorMsg := SUBSTR(SQLERRM, 1, 200);   
               flag := 'false';  
               out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 
               dbms_output.put_line(out_return);     
end;

转载于:https://my.oschina.net/findurl/blog/829925

weixin_33713350
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle存储过程实例
loganwz的博客
08-01 744
存储过程(Stored Procedure)是一组用于完成特定数据库功能的SQL语句集，SQL集包含了所要进行的各种处理，经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，得到想要的结果。 使用工具：plsql、Oracle Oracle存储过程保存在数据库procedures下，基本写法如下： 基本结构 CREATE OR...
oracle 存储过程示例
二进制天堂
05-21 760
CREATE OR REPLACE PROCEDURE PROC_CITY_DATE2T_CITY_ORDERS IS  l_maxOper_date date;  l_maxId        number;  l_errorcode   varchar2(100);BEGIN  -- author sunchengliang  SELECT max(oper_date) into l_ma
oracle存储过程简单查询,简单的oracle存储过程示例
weixin_28934081的博客
04-04 765
-- 创建无参procedure:drop procedure proc_testcreate or replace procedure proc_testistestvalue varchar2(40);beginselect user_name into testvalue from user_info where user_id = 1001;dbms_output.put_line(tes...
Oracle存储过程基本写法
热门推荐
JackLuoke猿生活
07-04 9万+
oracle 存储过程的基本语法1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有...
Oracle中的存储过程简单例子
奕奕她爹爹xueJAVA
04-02 2219
--创建表 create table TESTTABLE ( id1 VARCHAR2(12), name VARCHAR2(32) ) select t.id1,t.name from TESTTABLE t insert into TESTTABLE (ID1, NAME) values ('1', 'zhangsan'); insert into TESTTABLE (ID1,
Spring boot调用Oracle存储过程的两种方式及完整代码
08-18
Spring Boot 调用 Oracle 存储过程的两种方式及完整代码 在本文中，我们将为大家介绍 Spring Boot 调用 Oracle 存储过程的两种方式，并提供完整的代码示例。本文主要解决了在 Spring Boot 项目中调用 Oracle 存储...
oracle分页存储过程 oracle存储过程实例
12-16
代码如下:import java.sql.CallableStatement;import java.sql.Connection;...public class TestPage { public TestPage() { } public static void main(String[] args) { String driver = “oracle.jdbc.driver
Python使用cx_Oracle调用Oracle存储过程的方法示例
12-31
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考，具体如下： 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先，在数据库端创建简单的存储过程。 create or replace ...
oracle存储过程创建表分区实例
09-10
Oracle存储过程创建表分区实例 Oracle存储过程是指在Oracle数据库管理系统中的一种编程手段，它允许用户根据自己的需求创建出符合自己业务逻辑的数据库处理程序。在 Oracle中，存储过程可以用来创建表分区实例，...
ORACLE进阶（七）存储过程详解_oracle存储过程实例详解
m0_61331573的博客
04-21 1000
as //定义(游标一个可以遍历的结果集)begin //执行（常用For语句遍历游标）END LOOP;–显示cursor的处理declare—声明cursor,创建和命名一个sql工作区begin—打开cursor,执行sql语句产生的结果集–提取cursor,提取结果集中的记录–关闭cursorend;
redis能正常访问，但是springboot编译报错
生而为人我很抱歉
08-27 634
因为你在自定义的 @Configuration 类中没有明确指定配置属性的绑定，或者这个类的加载顺序影响了 Spring Boot 的属性绑定过程。你可以尝试在 MyConf 类上加上 @Order 注解来调整加载顺序，或者确保 MyConf 类不会干扰到 Redis 配置的加载。你可以在 MyConf 类上使用 @ConditionalOnProperty 注解，确保在特定条件下才加载这个配置类，以避免在 Redis 配置加载之前进行不必要的初始化。
汉服文化平台网站
heye0910032的博客
08-26 1585
本文主要探讨了使用Java语言开发汉服文化平台网站的全过程。系统采用B/S架构，严格遵循软件开发流程，从分析、设计到实现，确保了项目的系统性和科学性。系统主要面向管理员和用户两大类使用者，提供了包括首页、个人中心、汉服知识管理、服装展示管理、用户相册管理、论坛交流、系统管理、订单管理等功能。本系统的应用，旨在实现汉服文化信息管理的信息化，提高管理效率，方便用户访问和交流。通过本毕业设计，我深入学习并实践了Java语言和SSM框架在Web开发中的应用。
mysql-day02
m0_64370841的博客
08-29 1217
【代码】mysql-day02。
修复数据库中的 “Access Denied: SUPER Privilege Required” 错误
xiaochong0302的博客
08-26 1434
当您使用数据库时，您可能会看到错误消息：“Access denied; you need (at least one of) the SUPER privilege(s) for this operation”。当您的数据库用户没有足够的权限来执行某些操作时，就会发生这种情况。
python12 中，No module named‘distutils‘错误
qq_43557600的博客
08-27 9119
python12 “ No module named'distutils' ”,​ 安装 pip install setuptools ，解决 ​
数据库管理-第236期 数据库一体机的价值（20240829）
yhw1809的博客
08-29 1120
数据库一体机是硬件、操作系统和数据库之间的融合，利用更少的硬件实现小体积的高性能密度。
（1）Hilt的基本概念和使用
challenge51all的专栏
08-27 1393
Jetpack Hilt 是一个强大的依赖注入框架，它简化了 Android 应用中的依赖管理，提高了代码的可维护性和可测试性。通过使用注解和模块，开发人员可以轻松地管理应用中的依赖关系，并在不同的组件之间共享和注入依赖。在应用的 Application 类上使用这个注解，告诉 Hilt 这是一个使用 Hilt 进行依赖注入的应用。Jetpack Hilt 是一个用于 Android 的依赖注入框架，它建立在 Dagger 的基础上，旨在简化 Android 应用中的依赖注入过程。
MYSQL数据库初体验
qq_63994746的博客
08-28 1605
面向Windows操作系统简单、易用华为：欧拉阿里：龙蜥腾讯：tencentOS麒麟：（银河麒麟、中标麒麟 – >centos 优麒麟 – > Ubuntu )统信：uos红旗深度：deepin。
SpringBoot项目初始化搭建
最新发布
weixin_63405049的博客
08-30 812
本章主要讲解了SpringBoot项目初始化搭建，编程事务的使用，分页依赖的使用，以及跨域问题如何解决，解决了轻量级项目构建初期遇到的一些问题，提供于前后端交互时产生的问题进行分析与解决
Oracle存储过程详解与实战实例
Oracle存储过程实例是一份详尽的教程，它涵盖了Oracle数据库中存储过程的基础概念和实战应用。存储过程在Oracle中扮演着至关重要的角色，它们是预编译和优化的SQL代码块，具有显著的优势，如提高执行效率、增强安全...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值