Inserting values into tables Oracle SQL

database-design, insert-into, oracle, sql

Solution

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;
/

Problem

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!

Original source