180327 연습문제 4장

set serveroutput on

set autoprint on

old&new 없애려면 : set verify off

<연습문제 4-1>

declare

v_annsal employees.salary%type;

v_eid employees.employee_id%type := &eid;

begin

select salary*12+nvl(commission_pct,0)*salary*12

into v_annsal

from employees

where employee_id = v_eid;

dbms_output.put_line(to_char(v_annsal,'$999,999,999,00'));

end;

<연습문제 4-2 a,b>

SQL>define b_dname=Education

SQL>define b_dname << 디파인 되었는지 확인

ed

declare

max_deptid  departments.department_id%type;

begin

select MAX(department_id)

into max_deptid

from departments;

insert into departments

values (max_deptid+10, &b_dname, null ,null);

dbms_output.put_line(max_deptid)

end;

<연습문제 4-3>

declare

  max_deptid departments.department_id%type;

  v_result varchar2(100);

 begin

  select MAX(department_id)

  into max_deptid

  from departments;

  insert into departments

  values (max_deptid+10, '&b_dname', null, null);

  v_result := sql%rowcount || 'row inserted';

  dbms_output.put_line(v_result);

 end;

  /

<연습문제 4-4>

declare

  max_deptid departments.department_id%type;

  v_result varchar2(100);

  begin

  select max(department_id)

  into max_deptid

  from departments;

  update departments

  set location_id=1700 where department_id=max_deptid;

 v_result := sql%rowcount ||'1 row updated';

  dbms_output.put_line(v_result);

end;

/