ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 创建基于存储过程的数据块

创建基于存储过程的数据块

Linux操作系统 作者:kawontony 时间:2013-11-04 14:17:31 0 删除 编辑
首先,创建PL/SQL包:
  
  create or replace package hek_ar_reciept_pg is
         type line_record_type is record(
          LINE_ID                          NUMBER,
          HEADER_ID                        NUMBER,
          CUSTOMER_TRX_ID                  NUMBER,
          APPLY_ACCOUNT                    NUMBER,
          APPLY_DATE                       DATE,
          GL_DATE                          DATE,
          DISCOUNT                         NUMBER,
          REVERSAL_GL_DATE                 DATE,
          AMOUNT_APPLIED_FROM              NUMBER,
          MATCH_FLAG                       VARCHAR2(30),
          APPLY_FLAG                       VARCHAR2(10),
          TRX_NUMBER                       VARCHAR2(30),
          INSTALLMENT                      NUMBER,
          AMOUNT_DUE_REMAINING             NUMBER,
          INVOICE_CURRENCY_CODE            VARCHAR2(15),
          CUSTOMER_NUMBER                  VARCHAR2(30),
          CUSTOMER_ID                      NUMBER,
          CUSTOMER_NAME                    VARCHAR2(50),
          TRX_CLASS_NAME                   VARCHAR2(4000),
          TRX_CLASS_CODE                   VARCHAR2(20),
          TRX_TYPE_NAME                    VARCHAR2(20),
          PURCHASE_ORDER                   VARCHAR2(50),
          CT_REFERENCE                     VARCHAR2(30),
          LOCATION_NAME                    VARCHAR2(40),
          BILL_TO_SITE_USE_ID              NUMBER,
          STATUS                           VARCHAR2(30),
          CASH_RECEIPT_ID                  NUMBER,
          PS_CUSTOMER_TRX_ID               NUMBER,
          TRX_BATCH_SOURCE_NAME            VARCHAR2(50),
          DUE_DATE                         DATE,
          TERM_ID                          NUMBER,
          CUST_TRX_TYPE_ID                 NUMBER,
          PROGRAM_APPLICATION_ID           NUMBER,
          PROGRAM_ID                       NUMBER,
          REQUEST_ID                       NUMBER,
          AMOUNT_DUE_ORIGINAL              NUMBER,
          AMOUNT_IN_DISPUTE                NUMBER,
          AMOUNT_LINE_ITEMS_ORIGINAL       NUMBER,
          ACCTD_AMOUNT_DUE_REMAINING       NUMBER,
          TRX_DATE                         DATE,
          APPLIED_PAYMENT_SCHEDULE_ID      NUMBER,
          CREATED_BY                       NUMBER,
          CREATION_DATE                    DATE,
          LAST_UPDATED_BY                  NUMBER,
          LAST_UPDATE_DATE                 DATE,
          LAST_UPDATE_LOGIN                NUMBER,
          ORG_ID                           NUMBER,
          ATTRIBUTE_CATEGORY               VARCHAR2(30),
          ATTRIBUTE1                       VARCHAR2(50),
          ATTRIBUTE2                       VARCHAR2(50),
          ATTRIBUTE3                       VARCHAR2(50),
          ATTRIBUTE4                       VARCHAR2(50),
          ATTRIBUTE5                       VARCHAR2(50),
          ATTRIBUTE6                       VARCHAR2(50),
          ATTRIBUTE7                       VARCHAR2(50),
          ATTRIBUTE8                       VARCHAR2(50),
          ATTRIBUTE9                       VARCHAR2(50),
          ATTRIBUTE10                      VARCHAR2(50),
          ATTRIBUTE11                      VARCHAR2(150),
          ATTRIBUTE12                      VARCHAR2(150),
          ATTRIBUTE13                      VARCHAR2(150),
          ATTRIBUTE14                      VARCHAR2(150),
          ATTRIBUTE15                      VARCHAR2(150)
         );
  
  type lines_record_type is record(
          LINE_ID                          NUMBER,
          HEADER_ID                        NUMBER,
          CUSTOMER_TRX_ID                  NUMBER,
          APPLY_ACCOUNT                    NUMBER,
          APPLY_DATE                      DATE,
          GL_DATE                          DATE,
          DISCOUNT                        NUMBER,
          REVERSAL_GL_DATE DATE,
          AMOUNT_APPLIED_FROM NUMBER,
          MATCH_FLAG VARCHAR2(30),
          APPLY_FLAG VARCHAR2(10),
          CREATED_BY NUMBER,
          CREATION_DATE DATE,
          LAST_UPDATED_BY NUMBER,
          LAST_UPDATE_DATE DATE,
          LAST_UPDATE_LOGIN NUMBER,
          ORG_ID NUMBER,
          ATTRIBUTE_CATEGORY VARCHAR2(30),
          ATTRIBUTE1 VARCHAR2(50),
          ATTRIBUTE2 VARCHAR2(50),
          ATTRIBUTE3 VARCHAR2(50),
          ATTRIBUTE4 VARCHAR2(50),
          ATTRIBUTE5 VARCHAR2(50),
          ATTRIBUTE6 VARCHAR2(50),
          ATTRIBUTE7 VARCHAR2(50),
          ATTRIBUTE8 VARCHAR2(50),
          ATTRIBUTE9 VARCHAR2(50),
          ATTRIBUTE10 VARCHAR2(50),
          ATTRIBUTE11 VARCHAR2(150),
          ATTRIBUTE12 VARCHAR2(150),
          ATTRIBUTE13 VARCHAR2(150),
          ATTRIBUTE14 VARCHAR2(150),
          ATTRIBUTE15 VARCHAR2(150)
              );
  
        
        type line_ref_cursor is ref cursor return line_record_type;
        type line_table_type is table of lines_record_type index by
  binary_integer;
  
        procedure line_query(dmlset in out line_ref_cursor);
        procedure line_lock(dmlset in out line_table_type);
        procedure line_insert(dmlset in out line_table_type);
        procedure line_update(dmlset in out line_table_type);
        procedure line_delete(dmlset in out line_table_type);
  end;


create or replace package body hek_ar_reciept_pg is
        procedure line_query(dmlset in out line_ref_cursor) is
        begin
            open dmlset for
            select line_id,
                   header_id,
                   customer_trx_id,
                   apply_account,
                   apply_date,
                   gl_date,
                   discount,
                   reversal_gl_date,
                   amount_applied_from,
                   match_flag,
                   apply_flag,
                   trx_number,
                   installment,
                   amount_due_remaining,
                   invoice_currency_code,
                   customer_number,
                   customer_id,
                   customer_name,
                   trx_class_name,
                   trx_class_code,
                   trx_type_name,
                   purchase_order,
                   ct_reference,
                   location_name,
                   bill_to_site_use_id,
                   status,
                   cash_receipt_id,
                   ps_customer_trx_id,
                   trx_batch_source_name,
                   due_date,
                   term_id,
                   cust_trx_type_id,
                   program_application_id,
                   program_id,
                   request_id,
                   amount_due_original,
                   amount_in_dispute,
                   amount_line_items_original,
                   acctd_amount_due_remaining,
                   trx_date,
                   applied_payment_schedule_id,
                   created_by,
                   creation_date,
                   last_updated_by,
                   last_update_date,
                   last_update_login,
                   org_id,
                   attribute_category,
                   attribute1,
                   attribute2,
                   attribute3,
                   attribute4,
                   attribute5,
                   attribute6,
                   attribute7,
                   attribute8,
                   attribute9,
                   attribute10,
                   attribute11,
                   attribute12,
                   attribute13,
                   attribute14,
                   attribute15
              from hek_ar_reciept_lines_v;
        end;
  
        procedure line_lock(dmlset in out line_table_type) is
             l_line_id number;
        begin
             select line_id
               into l_line_id
               from hek_ar_reciept_lines_all
              where line_id = dmlset(1).line_id
                for update;
        end;
  
        procedure line_insert(dmlset in out line_table_type) is
        begin
             insert into hek_ar_reciept_lines_all(LINE_ID,
                                                  HEADER_ID,
                                                  CUSTOMER_TRX_ID,
                                                  APPLY_ACCOUNT,
                                                  APPLY_DATE,
                                                  GL_DATE,
                                                  DISCOUNT,
                                                  REVERSAL_GL_DATE,
                                                  AMOUNT_APPLIED_FROM,
                                                  MATCH_FLAG,
                                                  APPLY_FLAG,
                                                  CREATED_BY,
                                                  CREATION_DATE,
                                                  LAST_UPDATED_BY,
                                                  LAST_UPDATE_DATE,
                                                  LAST_UPDATE_LOGIN,
                                                  ORG_ID,
                                                  ATTRIBUTE_CATEGORY,
                                                  ATTRIBUTE1,
                                                  ATTRIBUTE2,
                                                  ATTRIBUTE3,
                                                  ATTRIBUTE4,
                                                  ATTRIBUTE5,
                                                  ATTRIBUTE6,
                                                  ATTRIBUTE7,
                                                  ATTRIBUTE8,
                                                  ATTRIBUTE9,
                                                  ATTRIBUTE10,
                                                  ATTRIBUTE11,
                                                  ATTRIBUTE12,
                                                  ATTRIBUTE13,
                                                  ATTRIBUTE14,
                                                  ATTRIBUTE15)
                                           values(dmlset(1).LINE_ID,
                                                  dmlset(1).HEADER_ID,
                                                  dmlset(1).CUSTOMER_TRX_ID,
                                                  dmlset(1).APPLY_ACCOUNT,
                                                  dmlset(1).APPLY_DATE,
                                                  dmlset(1).GL_DATE,
                                                  dmlset(1).DISCOUNT,
                                                  dmlset(1).REVERSAL_GL_DATE,
                                                  dmlset(1).AMOUNT_APPLIED_FROM,
                                                  dmlset(1).MATCH_FLAG,
                                                  dmlset(1).APPLY_FLAG,
                                                  dmlset(1).CREATED_BY,
                                                  dmlset(1).CREATION_DATE,
                                                  dmlset(1).LAST_UPDATED_BY,
                                                  dmlset(1).LAST_UPDATE_DATE,
                                                  dmlset(1).LAST_UPDATE_LOGIN,
                                                  dmlset(1).ORG_ID,
                                                  dmlset(1).ATTRIBUTE_CATEGORY,
                                                  dmlset(1).ATTRIBUTE1,
                                                  dmlset(1).ATTRIBUTE2,
                                                  dmlset(1).ATTRIBUTE3,
                                                  dmlset(1).ATTRIBUTE4,
                                                  dmlset(1).ATTRIBUTE5,
                                                  dmlset(1).ATTRIBUTE6,
                                                  dmlset(1).ATTRIBUTE7,
                                                  dmlset(1).ATTRIBUTE8,
                                                  dmlset(1).ATTRIBUTE9,
                                                  dmlset(1).ATTRIBUTE10,
                                                  dmlset(1).ATTRIBUTE11,
                                                  dmlset(1).ATTRIBUTE12,
                                                  dmlset(1).ATTRIBUTE13,
                                                  dmlset(1).ATTRIBUTE14,
                                                  dmlset(1).ATTRIBUTE15);
        end;
  
        procedure line_update(dmlset in out line_table_type) is
        begin
            update hek_ar_reciept_lines_all
               set HEADER_ID = dmlset(1).HEADER_ID,--LINE_ID =
dmlset(1).LINE_ID,
                   CUSTOMER_TRX_ID = dmlset(1).CUSTOMER_TRX_ID,
                   APPLY_ACCOUNT = dmlset(1).APPLY_ACCOUNT,
                   APPLY_DATE = dmlset(1).APPLY_DATE,
                   GL_DATE = dmlset(1).GL_DATE,
                   DISCOUNT = dmlset(1).DISCOUNT,
                   REVERSAL_GL_DATE = dmlset(1).REVERSAL_GL_DATE,
                   AMOUNT_APPLIED_FROM = dmlset(1).AMOUNT_APPLIED_FROM,
                   MATCH_FLAG = dmlset(1).MATCH_FLAG,
                   APPLY_FLAG = dmlset(1).APPLY_FLAG,
                   CREATED_BY = dmlset(1).CREATED_BY,
                   CREATION_DATE = dmlset(1).CREATION_DATE,
                   LAST_UPDATED_BY = dmlset(1).LAST_UPDATED_BY,
                   LAST_UPDATE_DATE = dmlset(1).LAST_UPDATE_DATE,
                   LAST_UPDATE_LOGIN = dmlset(1).LAST_UPDATE_LOGIN,
                   ORG_ID = dmlset(1).ORG_ID,
                   ATTRIBUTE_CATEGORY = dmlset(1).ATTRIBUTE_CATEGORY,
                   ATTRIBUTE1 = dmlset(1).ATTRIBUTE1,
                   ATTRIBUTE2 = dmlset(1).ATTRIBUTE2,
                   ATTRIBUTE3 = dmlset(1).ATTRIBUTE3,
                   ATTRIBUTE4 = dmlset(1).ATTRIBUTE4,
                   ATTRIBUTE5 = dmlset(1).ATTRIBUTE5,
                   ATTRIBUTE6 = dmlset(1).ATTRIBUTE6,
                   ATTRIBUTE7 = dmlset(1).ATTRIBUTE7,
                   ATTRIBUTE8 = dmlset(1).ATTRIBUTE8,
                   ATTRIBUTE9 = dmlset(1).ATTRIBUTE9,
                   ATTRIBUTE10 = dmlset(1).ATTRIBUTE10,
                   ATTRIBUTE11 = dmlset(1).ATTRIBUTE11,
                   ATTRIBUTE12 = dmlset(1).ATTRIBUTE12,
                   ATTRIBUTE13 = dmlset(1).ATTRIBUTE13,
                   ATTRIBUTE14 = dmlset(1).ATTRIBUTE14,
                   ATTRIBUTE15 = dmlset(1).ATTRIBUTE15
              where line_id = dmlset(1).line_id;
        end;
  
        procedure line_delete(dmlset in out line_table_type) is
        begin
            delete from hek_ar_reciept_lines_all
                  where line_id = dmlset(1).line_id;
        end;
  end;

2.启动数据块向导建立数据块。

3.选择“下一步”,此时显示查询过程对话框。输入过程名“hek_ar_reciept_pg.line_query”单击刷新按钮,移动所有列到数据库项。 


4.插入。

5.更新。

6.删除。

7.锁定。7.锁定。

7.锁定。

8.最终显示效果。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24627116/viewspace-775623/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-17

  • 博文量
    164
  • 访问量
    362847