通过pl/sql中的过程嵌套表插入数据 [英] nested table insertion data through procedure in pl/sql

问题描述

如何使用pl/SQL中的过程在嵌套表中插入数据?

How to insert data in nested table using procedure in pl/SQL?

如何通过过程传递嵌套表中的参数?

how to pass parameter in nested table through procedure?

CREATE OR REPLACE TYPE  "CYBER_B" AS OBJECT
 ( REC_ID VARCHAR2(250),
  START_DATE TIMESTAMP,
  END_DATE TIMESTAMP,
  CREATION_DATE TIMESTAMP,
  ADV_DURATION TIMESTAMP,
  ADV_IMAGE BLOB,
  IMG_FILENAME VARCHAR2(250),
  IMG_MIMETYPE VARCHAR2(250)
  );
CREATE TYPE CYBERB1 AS TABLE OF CYBER_B;

CREATE TABLE  "CYBER1_TABLE" 
   (    "AD_ID" VARCHAR2(64), 
    "USERNAME" VARCHAR2(250) NOT NULL ENABLE, 
    "SELLER_DETAIL1"  "CYBERB1" , 
     PRIMARY KEY ("AD_ID") ENABLE
   ) 
 NESTED TABLE "SELLER_DETAIL1" STORE AS "ADV1_DETAIL";

cyber_b是对象 cyberb1是类型 cyber_table是我的桌子

cyber_b is object cyberb1 is type cyber_table is my table

我想创建一个在嵌套表中插入数据的过程

I want to create a procedure which inserts data in nested table

推荐答案

在更新语句中:

CREATE PROCEDURE add_cyber1_seller_detail(
  in_ad_id   CYBER1_TABLE.AD_ID%TYPE,
  in_seller  CYBER_B
)
IS
BEGIN
  UPDATE cyber1_table
  SET    seller_detail1 = COALESCE( seller1_detail, CYBERB1() )
                          MULTISET UNION ALL
                          CYBERB1( in_seller )
  WHERE  ad_id = in_ad_id;
END;
/

使用PL/SQL:

CREATE PROCEDURE add_cyber1_seller_detail(
  in_ad_id   CYBER1_TABLE.AD_ID%TYPE,
  in_seller  CYBER_B
)
IS
  sellers CYBERB1;
BEGIN
  SELECT seller_detail1
  INTO   sellers
  FROM   cyber1_table
  WHERE  ad_id = in_ad_id
  FOR UPDATE OF seller_detail1;

  IF sellers IS NULL THEN
    sellers := CYBERB1( in_seller );
  ELSE
    sellers.EXTEND(1);
    sellers( sellers.COUNT ) := in_seller;
  END IF;

  UPDATE cyber1_table
  SET    seller_detail1 = sellers
  WHERE  ad_id = in_ad_id;
END;
/

这篇关于通过pl/sql中的过程嵌套表插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！