oracle 中增加行,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;