I'm trying to insert values into an 'Employee' table in Oracle SQL. I have a question regarding inputting values determined by a foreign key:
My employees have 3 attributes that are determined by foreign keys: State, Position, & Manager. I am using an INSERT INTO
statement to insert the values and manually typing in the data. Do I need to physically look up each reference to input the data or is there a command that I can use? E.g.
INSERT INTO Employee
(emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager)
VALUES
(001, "John Doe", "1 River Walk, Green Street", 3, 5, 1000)
This should populate the employee table with (John Doe, 1 River Walk, Green Street, New York, Sales Executive, Barry Green)
. New York is state_id=3
in the State
table; Sales executive is position_id=5
in the positions
table; and Barry Green is manager_id=1000
in the manager
table.
Is there a way in which I can input the text values of the referenced tables, so that Oracle will recognise the text and match it with the relevant ID? I hope this question makes sense will be happy to clarify anything.
Thanks!
You can expend the following function in order to pull out more parameters from the DB before the insert:
--
-- insert_employee (Function)
--
CREATE OR REPLACE FUNCTION insert_employee(p_emp_id in number, p_emp_name in varchar2, p_emp_address in varchar2, p_emp_state in varchar2, p_emp_position in varchar2, p_emp_manager in varchar2)
RETURN VARCHAR2 AS
p_state_id varchar2(30) := '';
BEGIN
select state_id
into p_state_id
from states where lower(emp_state) = state_name;
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_state, emp_position, emp_manager) VALUES
(p_emp_id, p_emp_name, p_emp_address, p_state_id, p_emp_position, p_emp_manager);
return 'SUCCESS';
EXCEPTION
WHEN others THEN
RETURN 'FAIL';
END;
/