Oracle中实现FORM表单插入、锁定、更新行、删除行的包

此包写在oracle数据库中，供Form表单调用：

CREATE OR REPLACE PACKAGE BODY cux_main_pkg AS

  /*=====================================
  ** PROCEDURE:   insert_row()
  **=====================================*/
  PROCEDURE insert_row(x_row_id             IN OUT VARCHAR2,
                       x_insp_header_id     IN OUT NUMBER,
                       p_org_id             IN NUMBER,
                       p_doc_type_class     IN VARCHAR2,
                       p_inspection_number  IN VARCHAR2,
                       p_main_subsidiary    IN VARCHAR2 DEFAULT NULL,
                       p_status             IN VARCHAR2,
                       p_oe_header_id       IN NUMBER DEFAULT NULL,
                      
                       p_creation_date      IN DATE,
                       p_created_by         IN NUMBER,
                       p_last_updated_by    IN NUMBER,
                       p_last_update_date   IN DATE,
                       p_last_update_login  IN NUMBER DEFAULT NULL)
   
    IS
    CURSOR c IS
      SELECT ROWID
        FROM table1
       WHERE insp_header_id = x_insp_header_id;
 
  BEGIN
 
    IF x_insp_header_id IS NULL THEN
      SELECT main_s.nextval
        INTO x_insp_header_id
        FROM dual;
    END IF;
 
    INSERT INTO table1
      (insp_header_id,
       org_id,
       doc_type_class,
       inspection_number,
       main_subsidiary,
       status,
       oe_header_id,
    
       creation_date,
       created_by,
       last_updated_by,
       last_update_date,
       last_update_login
       )
    VALUES
      (x_insp_header_id,
       p_org_id,
       p_doc_type_class,
       p_inspection_number,
       p_main_subsidiary,
       p_status,
       p_oe_header_id,
    
       p_creation_date,
       p_created_by,
       p_last_updated_by,
       p_last_update_date,
       p_last_update_login
    );
 
    OPEN c;
    FETCH c
      INTO x_row_id;
    IF (c%NOTFOUND) THEN
      CLOSE c;
      RAISE no_data_found;
    END IF;
    CLOSE c;
 
  END insert_row;

  /*=====================================
  ** PROCEDURE:   lock_row()
  **=====================================*/
  PROCEDURE lock_row(p_insp_header_id     IN NUMBER,
                     p_org_id             IN NUMBER,
                     p_doc_type_class     IN VARCHAR2,
                     p_inspection_number  IN VARCHAR2,
                     p_main_subsidiary    IN VARCHAR2 DEFAULT NULL,
                     p_status             IN VARCHAR2,
                     p_oe_header_id       IN NUMBER DEFAULT NULL,
           
                     p_creation_date      IN DATE,
                     p_created_by         IN NUMBER,
                     p_last_updated_by    IN NUMBER,
                     p_last_update_date   IN DATE,
                     p_last_update_login  IN NUMBER
)
 
   IS
    CURSOR c IS
      SELECT insp_header_id,
             org_id,
             doc_type_class,
             inspection_number,
             nvl(main_subsidiary, 'MAIN') main_subsidiary,
             status,
             oe_header_id,
       
             creation_date,
             created_by,
             last_updated_by,
             last_update_date,
             last_update_login
             
        FROM table1
       WHERE insp_header_id = p_insp_header_id
         FOR UPDATE OF insp_header_id NOWAIT;
 
    rec c%ROWTYPE;
  BEGIN
 
    OPEN c;
    FETCH c
      INTO rec;
    IF (c%NOTFOUND) THEN
      CLOSE c;
      fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
      app_exception.raise_exception;
    END IF;
    CLOSE c;
 
    IF ((rec.insp_header_id = p_insp_header_id) AND
       ((rec.org_id = p_org_id) OR
       ((rec.org_id IS NULL) AND (p_org_id IS NULL))) AND
       ((rec.doc_type_class = p_doc_type_class) OR
       ((rec.doc_type_class IS NULL) AND (p_doc_type_class IS NULL))) AND
       ((rec.inspection_number = p_inspection_number) OR
       ((rec.inspection_number IS NULL) AND (p_inspection_number IS NULL))) AND
       ((rec.main_subsidiary = p_main_subsidiary) OR
       ((rec.main_subsidiary IS NULL) AND (p_main_subsidiary IS NULL))) AND
       ((rec.status = p_status) OR
       ((rec.status IS NULL) AND (p_status IS NULL))) AND
       ((rec.oe_header_id = p_oe_header_id) OR
       ((rec.oe_header_id IS NULL) AND (p_oe_header_id IS NULL))) AND
    
       ((rec.creation_date = p_creation_date) OR
       ((rec.creation_date IS NULL) AND (p_creation_date IS NULL))) AND
       ((rec.created_by = p_created_by) OR
       ((rec.created_by IS NULL) AND (p_created_by IS NULL))) AND
       ((rec.last_updated_by = p_last_updated_by) OR
       ((rec.last_updated_by IS NULL) AND (p_last_updated_by IS NULL))) AND
       ((rec.last_update_date = p_last_update_date) OR
       ((rec.last_update_date IS NULL) AND (p_last_update_date IS NULL))) AND
       ((rec.last_update_login = p_last_update_login) OR
       ((rec.last_update_login IS NULL) AND (p_last_update_login IS NULL)))) THEN
      NULL;
    ELSE
      fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
      app_exception.raise_exception;
    END IF;
 
  END lock_row;

  /*=====================================
  ** PROCEDURE:   update_row()
  **=====================================*/
  PROCEDURE update_row(p_insp_header_id     IN NUMBER,
                       p_org_id             IN NUMBER,
                       p_doc_type_class     IN VARCHAR2,
                       p_inspection_number  IN VARCHAR2,
                       p_main_subsidiary    IN VARCHAR2 DEFAULT NULL,
                       p_status             IN VARCHAR2,
                       p_oe_header_id       IN NUMBER DEFAULT NULL,
            
                       p_last_updated_by    IN NUMBER,
                       p_last_update_date   IN DATE,
                       p_last_update_login  IN NUMBER DEFAULT NULL
)
 
   IS
  BEGIN
 
    UPDATE table1
       SET insp_header_id     = p_insp_header_id,
           org_id             = p_org_id,
           doc_type_class     = p_doc_type_class,
           inspection_number  = p_inspection_number,
           main_subsidiary    = p_main_subsidiary,
           status             = p_status,
           oe_header_id       = p_oe_header_id,   

           last_updated_by    = p_last_updated_by,
           last_update_date   = p_last_update_date,
           last_update_login  = p_last_update_login
     WHERE insp_header_id = p_insp_header_id;
 
    IF (SQL%NOTFOUND) THEN
      RAISE no_data_found;
    END IF;
 
  END update_row;

  /*=====================================
  ** PROCEDURE:   delete_row()
  **=====================================*/
  PROCEDURE delete_row(p_insp_header_id IN NUMBER)
 
   IS
  BEGIN
 
    DELETE FROM table1
     WHERE insp_header_id = p_insp_header_id;
 
    IF (SQL%NOTFOUND) THEN
      RAISE no_data_found;
    END IF;
 
  END delete_row;

END cux_main_pkg;

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

转载于:http://blog.itpub.net/26189725/viewspace-1433497/