PL/SQL insert문 사용 실습

직접 입력하여 insert

--Anonymous Procedure
--insert
set serveroutput on
set verify off

accept p_employee_id prompt '입력할 사원 번호 : '
accept p_last_name prompt '입력할 사원 이름 : '
accept p_salary prompt '입력할 사원 급여 : '

declare
v_employee_id emp.employee_id%type := &p_employee_id;
v_last_name emp.last_name%type := initcap('&p_last_name');
v_salary emp.salary%type := &p_salary;

begin
insert into emp
values(v_employee_id, v_last_name, v_salary);

dbms_output.put_line('사원번호 : ' || v_employee_id);
dbms_output.put_line('사원이름 : ' || v_last_name);
dbms_output.put_line('사원급여 : ' || v_salary);

end;
/
set verify on
set serveroutput off

 

 

 

select 하여서 insert 하기

--Anonymous Procedure
--select => insert
set serveroutput on
set verify off

accept p_employee_id prompt '입력할 사원 번호 : '

declare
    v_employee_id emp.employee_id%type := &p_employee_id;
    v_last_name emp.last_name%type := initcap('&p_last_name');
    v_salary emp.salary%type := &p_salary;

begin
   
 select last_name, salary
 into v_lasT_name, v_salary
 from employees
 where employee_id = v_employee_id;

 insert into emp
 values(v_employee_id, v_last_name, v_salary);

 commit;
   
    dbms_output.put_line('사원번호 : ' || v_employee_id);
    dbms_output.put_line('사원이름 : ' || v_last_name);
    dbms_output.put_line('사원급여 : ' || v_salary);

end;
/
set verify on
set serveroutput off