oracle存储过程示例

Plsql下存储过程的游标使用

希望能对大家有帮助吧。
create or replace package loss is
    type temp_cur is ref cursor ;
    TYPE indexByTab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER ;
    procedure loss_proc(chr_PolicyNo             in  VARCHAR2,        --** 保单号
                        num_DangerNo             in  NUMBER,          --** 危险单位序号,默认1
                        DamageDate               in  date,            --** 出险时间
                        chr_Currency             in  VARCHAR2,        --** 赔付币种
                        num_SumClaim             in  NUMBER,          --** 总估损金额
                        chr_BlnLargeLoss         out VARCHAR2,        --** 是否重大赔案:0,否;1,是
                        chr_BlnCashLoss    out VARCHAR2          --** 是否现金赔款:0,否;1,是
                     );
end loss;

create or replace package body loss is
    procedure loss_proc(chr_PolicyNo             in  VARCHAR2,        --** 保单号
                        num_DangerNo             in  NUMBER,          --** 危险单位序号,默认1
                        DamageDate               in  date,            --** 出险时间
                        chr_Currency             in  VARCHAR2,        --** 赔付币种
                        num_SumClaim             in  NUMBER,          --** 总估损金额
                        chr_BlnLargeLoss         out VARCHAR2,        --** 是否重大赔案:0,否;1,是
                        chr_BlnCashLoss    out VARCHAR2   --** 是否现金赔款:0,否;1,是
                     )  is
           cur_trail              temp_cur;
           chr_treatyno           indexByTab;
           num_ClaimShare         indexByTab;
           chr_ControlFlag        varchar2(1);
           chr_largelossflag      varchar2(1);
           chr_cashlossflag       varchar2(1);
           num_LargeLossValue     number(14,2);
           n_cnt                  number(4):=1;
begin
--根据保单号取得合约号以及其所占金额
    chr_BlnLargeLoss:='0';
    chr_BlnCashLoss:='0';
   if not cur_trail%isopen then
       open    cur_trail for
        select treatyno,
               round(sharerate*num_SumClaim,2)
        from   prpCreinsTrial
        where  policyno = chr_PolicyNo
          and  dangerno = num_DangerNo
         and  substr(reinsmode,0,1) = 2;
   end if;
--根据合约号取得其
--largelossflag(整个危险或是本公司所占份额估损标记)
--ControlFlag(共同理赔,理赔控制)
   --for i in 1..fetch_status loop
   loop
   fetch cur_trail into
         chr_treatyno(n_cnt),
         num_ClaimShare(n_cnt);
--当游标指向未尾时自动退出游标
    exit when(cur_trail%notfound);
    select controlflag,
           largelossflag,
           cashlossflag,
           LargeLossValue
    into   chr_ControlFlag,
           chr_largelossflag,
           chr_cashlossflag,
           num_LargeLossValue
     from  fhtreaty
     where treatyno = chr_treatyno(n_cnt);
--判断是否属于重大赔案
    if chr_ControlFlag = '1' and chr_BlnLargeLoss = '0' then
        if chr_largelossflag = '1'and num_ClaimShare(n_cnt) >= num_LargeLossValue then
            chr_BlnLargeLoss:= '1';
        end if;
        else if chr_largelossflag = '0' and num_SumClaim >= num_LargeLossValue then
            chr_BlnLargeLoss:= '1';
        end if;
    end if;
--现金赔款决断
    if chr_BlnCashLoss = '0' then
        if chr_cashlossflag = '1' and num_ClaimShare(n_cnt) >= num_LargeLossValue then
            chr_BlnCashLoss:= '1';
        end if;
        else if chr_cashlossflag = '0' and  num_SumClaim >= num_LargeLossValue then
            chr_BlnCashLoss:= '1';
        end if;
    end if;
    n_cnt:=n_cnt+1;
    end loop;
--关闭游标
    if cur_trail%isopen then
      close cur_trail;
    end if;
   end loss_proc;
end loss;
以上是包头部分和包体部分,另外,我写了个测试程序,出贴出来吧。
package com.sinosoft.reins.exam.action;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleDriver;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import com.sinosoft.reins.exam.Dto.OracleRe_absDto;
import com.sinosoft.reins.exam.Dto.OracleRe_paraDto;
import com.sinosoft.sysframework.web.control.BaseProcessAction;

public class Loss extends BaseProcessAction {
       
        /**
         * 调用存储过程的action,仅作为示范,应该放在bl层
         * @param request http请求
         * @throws Exception
         */
     //        public void referenceOraclePackage(HttpServletRequest request,
     //        HttpServletResponse response) throws Exception {
        public static void main(String[] args){
                try{
                    OracleDriver oracleDriver = new oracle.jdbc.OracleDriver();
                    DriverManager.registerDriver(oracleDriver);
                    String url="jdbc:oracle:thin^^^^^^^^;
                    String user="*";
                    String password="*";
                    Connection ocacleconn= DriverManager.getConnection(url,user,password);                    
                    String query = "{call loss.loss_proc(?,?,?,?,?,?,?)}";
                    System.out.println("==========ocacleconn的类型:============"+ocacleconn.getClass().getName()) ;
                    //必须用OracleCallableStatement类型才能提供比JDBC API更多的方法,以支持复杂数据结构的传递
                    OracleCallableStatement cstmt = (OracleCallableStatement)ocacleconn.prepareCall(query);                                    
                    //设定入参类型 OracleTypes.STRUCT对应OBJECT类型;OracleTypes.ARRAY对应TABLE类型
                    String policyno = "1002005132006000019";
                    String dangerno = "1";
                    java.sql.Date damagedate =java.sql.Date.valueOf("2006-07-26") ;
                    String currency = "CNY";
                    String sumnopaid = "1000000000000";
                    String largelossflag = "0";
                    String cashlossflag = "0";
                    cstmt.setObject(1,policyno,OracleTypes.VARCHAR);   
                    cstmt.setObject(2,dangerno,OracleTypes.VARCHAR);
                    cstmt.setObject(3,damagedate,OracleTypes.DATE);
                    cstmt.setObject(4,currency,OracleTypes.VARCHAR);
                    cstmt.setObject(5,sumnopaid,OracleTypes.VARCHAR);
                    //设定返参类型 IndexTableOutParameter对应索引表类型;OracleTypes.CURSOR对应TABLE的游标          
                        cstmt.registerOutParameter(6,OracleTypes.VARCHAR);
                        cstmt.registerOutParameter(7,OracleTypes.VARCHAR);
                        //执行存储过程
                        cstmt.execute();                       
                        String largeloss = (String)cstmt.getString(6);
                        String cashloss = (String)cstmt.getString(7);
                        System.out.println("返回参数largeloss is :"+largeloss);
                        System.out.println("cashloss is :"+cashloss);
                        //以下过程是为了控制台显示                                               
                        cstmt.close();
                }
                catch (Throwable ex){
            ex.printStackTrace();
                }
        }

oracle存储过程和函数示例

11-13

--1、创建表rn CREATE TABLE STUDENTrn (rn SNO NUMBER(4) PRIMARY KEY,rn SNAME VARCHAR2(20),rn SAGE NUMBER(4),rn SSEX VARCHAR(2)rn )rn--2、创建序列rn CREATE SEQUENCE MY_NUMrn START WITH 1rn INCREMENT BY 1rn MAXVALUE 30 NOCYCLE;rn--3、设置约束,默认值等操作rn --sage设置check约束rn ALTER TABLE STUDENT MODIFYrn (rn SAGE CHECK(SAGE>18)rn );rn --SSEX设置check约束rn ALTER TABLE STUDENT MODIFYrn (rn SSEX CHECK(SSEX='男' or ssex='女')rn );rn --sage设置默认值rn ALTER TABLE STUDENT MODIFYrn (rn SAGE DEFAULT(20)rn );rn--4、插入数据rn INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'张三',default,'男');rn INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'李斯',21,'女');rn INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'天启',default,'男');rn --附加:查看当前序列的号数rn SELECT MY_NUM.CURRVAL FROM DUAL;rn--5、查询表rn SELECT * FROM STUDENTrn--6、使用事务rn BEGIN rn INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'王林',23,'男');rn UPDATE STUDENT SET SSEX='男' WHERE SNAME='天启';rn COMMIT;rn DBMS_OUTPUT.put_line('语句执行成功');rn EXCEPTION --出异常执行该语句rn WHEN OTHERS THENrn ROLLBACK;rn DBMS_OUTPUT.put_line('语句执行失败');rn END;rn--7、创建存储过程rn --例题1:rn ----不带参数的存储过程rn CREATE OR REPLACE PROCEDURE STUDENT_PROrn ASrn BEGINrn DBMS_OUTPUT.put_line('创建存储过程'); ----此处必须加分号rn END;rn --调用存储过程rn BEGINrn STUDENT_PRO;----此处必须加分号rn END;rn --删除存储过程rn drop procedure STUDENT_PRO;rn --例题2:rn ---默认为in输入参数,OUT为输出参数,TESTOUT中带两个参数,一个输入参数,一个输出参数rn CREATE OR REPLACE PROCEDURE TESTOUTrn (VALUE1 NUMBER,VALUE2 OUT NUMBER)rn ASrn BEGINrn VALUE2:=VALUE1+100;rn END;rn --调用存储过程rn DECLARE rn RESULT NUMBER;rn BEGINrn TESTOUT(10,RESULT);rn DBMS_OUTPUT.put_line(RESULT);rn END;rn --例题3:rn SELECT * FROM SCOTT.DEPT;rn ----in out参数rn CREATE OR REPLACE PROCEDURE TEST_INOUTrn (rn DNO SCOTT.DEPT.DEPTNO%TYPE,rn NAMES OUT SCOTT.DEPT.DNAME%TYPErn )rn ASrn BEGINrn --根据dno查询names---(DNAME INTO NAMES)表示将查询到得DNAME赋值给NAMESrn SELECT DNAME INTO NAMES FROM SCOTT.DEPT WHERE DEPTNO=DNO;rn END;rn rn --调用存储过程rn --因为有out输出参数,所以调用时必须声明变量rn DECLARE rn NAMES VARCHAR(20);rn BEGINrn TEST_INOUT(10,NAMES);rn DBMS_OUTPUT.put_line(NAMES);rn END;rn--8、函数rn ----格式,特别注意每句代码的位置rn CREATE OR REPLACE FUNCTION GET_NAMErn (rn DNO SCOTT.DEPT.DEPTNO%TYPErn )rn RETURN VARCHAR2---返回数据类型rn ASrn --声明变量保存dname,并返回rn NAMES VARCHAR2(20);rn BEGINrn --根据DNO查询DNAMErn SELECT DNAME INTO NAMES FROM SCOTT.DEPT WHERE DEPTNO=DNO;rn RETURN NAMES;--返回该数据类型的值rn END;rn rn --调用函数rn BEGINrn DBMS_OUTPUT.put_line('部门名称:'||GET_NAME(20));---字符串与变量连接使用||rn END;rn rn --删除函数rn DROP FUNCTION GET_NAME;rn

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试