激情久久久_欧美视频区_成人av免费_不卡视频一二三区_欧美精品在欧美一区二区少妇_欧美一区二区三区的

服務器之家：專注于服務器技術及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數據庫技術|

服務器之家 - 數據庫 - Oracle - oracle通過存儲過程上傳list保存功能

oracle通過存儲過程上傳list保存功能

2021-06-21 17:53丁三壽 Oracle

這篇文章主要介紹了oracle通過存儲過程上傳list保存功能,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

一、創建oracle 需要保存的數據類型type和存儲過程produce

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
create TYPE "AL01TYPE"                                                                                                                                                                                                                                  as object
(
-- 描述 : 檔案批量轉出
-- 作者  : dt
-- 時間 : 2021-05-10
-- 版本 ：dev-1.0.1
 
  aac003       NVARCHAR2(100),
  aac002       NVARCHAR2(50),
  aat001       NVARCHAR2(50),
  aat002       NVARCHAR2(50),
  aat013       NVARCHAR2(20),
  aae011       NVARCHAR2(20),
  aae036       NVARCHAR2(20),
  aah002       NVARCHAR2(100)
);
 
create type AL01TYPELIST as table of AL01TYPE;
 
-- auto-generated definition
create PROCEDURE SP_HFSZHDA_DOUPLOADAL01(LIST   IN   AL01TYPELIST,
                                       po_message OUT VARCHAR) IS
                                       --描述：檔案轉出excel上傳
                                       --作者：dt
                                       --時間：2021-05-10
                                       --版本：dev-1.0.1
  v_object    AL01TYPE;
  le_error EXCEPTION;
  P_renum number(20);
 
  v_aah002  VARCHAR(100);
  ls_count number;
  ls_aaf025 VARCHAR(50);
 
BEGIN
  P_renum  := 0; --初始化
 
  FOR I IN 1 ..  LIST.count LOOP
    P_renum := 1 + P_renum;
 
      v_object := LIST(I);
 
 
       select   replace(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr('\0000'))  into  v_aah002 from   dual;
 
      begin
        Select count(0) into ls_count from az03 where aat001 = v_object.aat001 AND aat012 = '1';
        if ls_count=0 then
           po_message := '號：'||v_object.aat001||' 狀態異常請核對后再上傳！';
           RAISE le_error;
           end if;
 
         Select count(0) into ls_count From AL01 where aaj022='1' and aat001=v_object.aat001 and aah002=v_aah002;
         if ls_count =0 then
        -- 開始插入信息
      select  SQ_AAF025.nextval into ls_aaf025 from dual;
 
      insert into AL01(
      aaf025,
      aat012,
      aat001,
      aac003,
      aac002,
      aat002,
      aat013,
      aaj022,
      aaj026,
      aae011,
      aae036,
      aah002)values(
                 ls_aaf025,
                 '1',
                 v_object.aat001,
                 v_object.aac003 ,
                 v_object.aac002 ,
                 v_object.aat002 ,
                 v_object.aat013,
                 '1',
                 'excel上傳數據',
                 v_object.aae011,
                 v_object.aae036,
                 v_aah002
            );
            
            end if;
 
    IF P_renum >1000 THEN
            commit;
            P_renum:=0;
        END IF;
      po_message :='ok';
EXCEPTION
                   WHEN le_error THEN
                   NULL;
                   WHEN OTHERS THEN
                   ROLLBACK;
                   po_message := '上傳失敗' || SQLCODE || SQLERRM;
        end;
 
 
  END LOOP;
  COMMIT;
 
END SP_HFSZHDA_DOUPLOADAL01;

二、通過過程上傳list

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package com.cominfo.elecfile.utils;
 
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
 
import java.sql.Connection;
import java.util.List;
 
/**
 * 描述
 *
 * @Auther: dt
 * @Date: 2021/5/10 0027 09:00
 */
public class OracleUtil {
    /**
     * 根據數據庫中你的type將List組裝成Array
     * @param con
     * @param OracleObj
     * @param Oraclelist
     * @param objlist
     * @return
     * @throws Exception
     */
    public static ARRAY getArray(Connection con, String OracleObj, String Oraclelist, List<Object[]> objlist) throws Exception {
        ARRAY array=null;
        C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
        OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(con);
 
 
        if (objlist != null && objlist.size() > 0) {
            StructDescriptor structdesc = new StructDescriptor(OracleObj, connection);
            STRUCT[] structs = new STRUCT[objlist.size()];
            for (int i = 0; i < objlist.size(); i++) {
                Object[] result= (Object[]) objlist.get(i);
                structs[i] = new STRUCT(structdesc, connection, result);
            }
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,connection);
            array = new ARRAY(desc, connection, structs);
        }
 
        return array;
    }
 
}
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
List<Object[]> arrList = new ArrayList<>();
        //解析數據datamap
        for (Map<String, String> dataMap : dataMaps) {
            //創建保存對象
            Object[] objects =new Object[]{
 
                    dataMap.get("aac003"),
                    dataMap.get("aac002"),
                    dataMap.get("aat001").trim(),
                    dataMap.get("aat002"),
                    dataMap.get("aat013"),
                    'admin',
                    DateUtil.getCurrentTimeStr(),
                    'ec-20210510-wcdedgk2091',
 
            };
            arrList.add(objects);
 
        }
 
 
 
//開始調用過程
        long startTime=System.currentTimeMillis();
        Connection connection = null;
        CallableStatement sqlres = null;
        String sql = "call SP_HFSZHDA_DOUPLOADAL01(?,?)";
        String msg = "";
        try {
            connection = dataSource.getConnection();
            ARRAY paramArr = OracleUtil.getArray(connection,"AL01TYPE","AL01TYPELIST",arrList);
 
            sqlres = connection.prepareCall(sql);
            sqlres.setArray(1, paramArr);
            sqlres.registerOutParameter(2, Types.VARCHAR);
            sqlres.execute();
            msg = sqlres.getString(2);
 
            long endTime=System.currentTimeMillis()-startTime;
            System.out.println("上傳后獲取的返回參數為："+msg+"||耗時："+endTime/1000+"秒");
 
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (sqlres != null) {
                    sqlres.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (!"ok".equals(msg)){
                throw new BusinessException("上傳失??！"+msg);
            }
        }

到此這篇關于oracle通過存儲過程上傳list保存功能的文章就介紹到這了,更多相關oracle保存list內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！

原文鏈接：https://blog.csdn.net/qq_42240545/article/details/116588573

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板： 伊人999| 精品国产91久久久久久久妲己 | 国产噜噜噜噜久久久久久久久 | 久久91精品国产91久久yfo | 一区二区三区在线观看免费 | 亚洲成人激情av | 欧美激情在线播放 | 日韩黄色片免费看 | 中国7777高潮网站 | 国产九九热视频 | 久久17 | 欧美视频国产精品 | 玖草| 久草在线综合 | 免费的毛片 | 99热99精品 | 欧美国产精品一区二区 | 久草视频手机在线观看 | 亚洲网站在线 | va视频 | 91高清视频在线观看 | 高清视频91 | 国产精品久久久久久久不卡 | 91av亚洲| 日本成人午夜视频 | 在线播放av网址 | 一本在线高清码电影 | 久久久国产视频 | 91福利在线观看 | 国产porn在线 | 91福利国产在线观一区二区 | 毛片成人网| 欧美另类激情 | 9999在线视频| 看免费黄色大片 | 国产成人自拍视频在线 | 最新亚洲国产 | 国产一精品久久99无吗一高潮 | 欧美人与牲禽动交精品一区 | 亚洲人成中文字幕在线观看 | 毛片午夜|