In PL/SQL, I need to create an insert_contact procedure that requires a formal p
ID: 3600049 • Letter: I
Question
In PL/SQL, I need to create an insert_contact procedure that requires a formal parameter list and inserts into the member, contact, address, and telephone tables. The code I wrote (attached below) is not valid the way I did it. I need a cursor to select from the common_lookup, then loops to get the individual column values like member type, credit card type, etc. and then does the inserts using those values. How do I do this?
CREATE OR REPLACE PROCEDURE insert_contact(PV_FIRST_NAME VARCHAR2
,PV_MIDDLE_NAME VARCHAR2
,PV_LAST_NAME VARCHAR2
,PV_CONTACT_TYPE VARCHAR2
,PV_ACCOUNT_NUMBER VARCHAR2
,PV_MEMBER_TYPE VARCHAR2
,PV_CREDIT_CARD_NUMBER VARCHAR2
,PV_CREDIT_CARD_TYPE VARCHAR2
,PV_CITY VARCHAR2
,PV_STATE_PROVINCE VARCHAR2
,PV_POSTAL_CODE VARCHAR2
,PV_ADDRESS_TYPE VARCHAR2
,PV_COUNTRY_CODE VARCHAR2
,PV_AREA_CODE VARCHAR2
,PV_TELEPHONE_NUMBER VARCHAR2
,PV_TELEPHONE_TYPE VARCHAR2
,PV_USER_NAME VARCHAR2)
IS
-- Local variables, to leverage subquery assignments in INSERT statements.
lv_address_type VARCHAR2(30);
lv_contact_type VARCHAR2(30);
lv_credit_card_type VARCHAR2(30);
lv_member_type VARCHAR2(30);
lv_telephone_type VARCHAR2(30);
BEGIN
-- Assign parameter values to local variables for nested assignments to DML subqueries.
lv_address_type := pv_address_type;
lv_contact_type := pv_contact_type;
lv_credit_card_type := pv_credit_card_type;
lv_member_type := pv_member_type;
lv_telephone_type := pv_telephone_type;
-- Create a SAVEPOINT as a starting point.
SAVEPOINT starting_point;
INSERT INTO member
( member_id
, member_type
, account_number
, credit_card_number
, credit_card_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( member_s1.NEXTVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'MEMBER_TYPE'
AND common_lookup_type = lv_member_type)
, pv_account_number
, pv_credit_card_number
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'CREDIT_CARD_TYPE'
AND common_lookup_type = lv_credit_card_type)
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO contact
( contact_id
, member_id
, contact_type
, last_name
, first_name
, middle_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( contact_s1.NEXTVAL
, member_s1.CURRVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'CONTACT'
AND common_lookup_column = 'CONTACT_TYPE'
AND common_lookup_type = lv_contact_type)
, pv_last_name
, pv_first_name
, pv_middle_name
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO address
VALUES
( address_s1.NEXTVAL
, contact_s1.CURRVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'ADDRESS'
AND common_lookup_column = 'ADDRESS_TYPE'
AND common_lookup_type = lv_address_type)
, pv_city
, pv_state_province
, pv_postal_code
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO telephone
VALUES
( telephone_s1.NEXTVAL -- TELEPHONE_ID
, contact_s1.CURRVAL -- CONTACT_ID
, address_s1.CURRVAL -- ADDRESS_ID
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'TELEPHONE'
AND common_lookup_column = 'TELEPHONE_TYPE'
AND common_lookup_type = lv_telephone_type)
, pv_country_code -- COUNTRY_CODE
, pv_area_code -- AREA_CODE
, pv_telephone_number -- TELEPHONE_NUMBER
, pv_created_by -- CREATED_BY
, pv_creation_date -- CREATION_DATE
, pv_last_updated_by -- LAST_UPDATED_BY
, pv_last_update_date); -- LAST_UPDATE_DATE
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO starting_point;
RETURN;
END insert_contact;
/
Explanation / Answer
the code i know so far
insert_contact(PV_FIRST_NAME VARCHAR2
,PV_MIDDLE_NAME VARCHAR2
,PV_LAST_NAME VARCHAR2
,PV_CONTACT_TYPE VARCHAR2
,PV_ACCOUNT_NUMBER VARCHAR2
,PV_MEMBER_TYPE VARCHAR2
,PV_CREDIT_CARD_NUMBER VARCHAR2
,PV_CREDIT_CARD_TYPE VARCHAR2
,PV_CITY VARCHAR2
,PV_STATE_PROVINCE VARCHAR2
,PV_POSTAL_CODE VARCHAR2
,PV_ADDRESS_TYPE VARCHAR2
,PV_COUNTRY_CODE VARCHAR2
,PV_AREA_CODE VARCHAR2
,PV_TELEPHONE_NUMBER VARCHAR2
,PV_TELEPHONE_TYPE VARCHAR2
,PV_USER_NAME VARCHAR2)
IS
-- Local variables, to leverage subquery assignments in INSERT statements.
lv_address_type VARCHAR2(30);
lv_contact_type VARCHAR2(30);
lv_credit_card_type VARCHAR2(30);
lv_member_type VARCHAR2(30);
lv_telephone_type VARCHAR2(30);
BEGIN
-- Assign parameter values to local variables for nested assignments to DML subqueries.
lv_address_type := pv_address_type;
lv_contact_type := pv_contact_type;
lv_credit_card_type := pv_credit_card_type;
lv_member_type := pv_member_type;
lv_telephone_type := pv_telephone_type;
-- Create a SAVEPOINT as a starting point.
SAVEPOINT starting_point;
INSERT INTO member
( member_id
, member_type
, account_number
, credit_card_number
, credit_card_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( member_s1.NEXTVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'MEMBER_TYPE'
AND common_lookup_type = lv_member_type)
, pv_account_number
, pv_credit_card_number
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'MEMBER'
AND common_lookup_column = 'CREDIT_CARD_TYPE'
AND common_lookup_type = lv_credit_card_type)
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO contact
( contact_id
, member_id
, contact_type
, last_name
, first_name
, middle_name
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES
( contact_s1.NEXTVAL
, member_s1.CURRVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'CONTACT'
AND common_lookup_column = 'CONTACT_TYPE'
AND common_lookup_type = lv_contact_type)
, pv_last_name
, pv_first_name
, pv_middle_name
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO address
VALUES
( address_s1.NEXTVAL
, contact_s1.CURRVAL
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'ADDRESS'
AND common_lookup_column = 'ADDRESS_TYPE'
AND common_lookup_type = lv_address_type)
, pv_city
, pv_state_province
, pv_postal_code
, pv_created_by
, pv_creation_date
, pv_last_updated_by
, pv_last_update_date );
INSERT INTO telephone
VALUES
( telephone_s1.NEXTVAL -- TELEPHONE_ID
, contact_s1.CURRVAL -- CONTACT_ID
, address_s1.CURRVAL -- ADDRESS_ID
,(SELECT common_lookup_id
FROM common_lookup
WHERE common_lookup_table = 'TELEPHONE'
AND common_lookup_column = 'TELEPHONE_TYPE'
AND common_lookup_type = lv_telephone_type)
, pv_country_code -- COUNTRY_CODE
, pv_area_code -- AREA_CODE
, pv_telephone_number -- TELEPHONE_NUMBER
, pv_created_by -- CREATED_BY
, pv_creation_date -- CREATION_DATE
, pv_last_updated_by -- LAST_UPDATED_BY
, pv_last_update_date); -- LAST_UPDATE_DATE
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO starting_point;
RETURN;
END insert_contact;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.