1 PL/SQL을 사용했을 때에 유리한 점

2 블럭구조

3 변수와 상수

4 bulk collect

• SQL과의 강력한 통합
  
• 더 나은 성능(때에 따라서..)
  
• 더 나은 보안
  
• 더 나은 생산성
  
• 재사용성
  
• 다양한 패키지 제공(미리 정의됨)
  
• 객체지향 프로그래밍 지원
  
• 웹어플리케이션과 페이지들을 위한 지원
  
  

[declare
  -- declartions]
begin
  -- statements
[exception
  -- handlers]
end;

declare
  i number := 10;
  b boolean;
  str varchar(20);
  type varArrary is table of number index by pls_integer;
  comm_tab varArrary;

declare
  i number := 10;
  b boolean;
  str varchar(20);
  type varArrary is table of number index by pls_integer;
  comm_tab varArrary;
begin
  i := i + 1;
  b := (i < 3);
  str := '이재학';
  comm_tab(10) := 20*20;
end;

declare 
    bonus number(8,2);
    emp_id number(6) := 100;
begin
    select salary * 0.10
    into bonus
    from employees
    where employee_id = emp_id;

    dbms_output.put_line('bonus = ' || bonus);
end;

declare
  i number := 10;
  b boolean;
  str varchar(20);
  type varArrary is table of number index by pls_integer;
  comm_tab varArrary;
begin
  i := i + 1;
  b := (i < 3);
  str := '이재학';
  comm_tab(10) := 20*20;
  
  dbms_output.put_line(i);
  if b = true then 
      dbms_output.put_line('true');
  else
      dbms_output.put_line('false');
  end if;
  dbms_output.put_line(str);
  dbms_output.put_line(comm_tab(10));
end;

declare
  credit_limit CONSTANT NUMBER := 5000.00;

declare
  type type_job_id is table of hr.jobs.job_id%type index by pls_integer;
  type type_job_title is table of hr.jobs.job_title%type index by pls_integer;
  
  v_job_id type_job_id;
  v_job_title type_job_title;
  
  col1 char(13);
begin
  select job_id, job_title
  bulk collect into v_job_id, v_job_title
  from hr.jobs;

  for i in v_job_id.first .. v_job_id.last
  loop
      col1 := v_job_id(i);
      dbms_output.put_line('job_id = ' || col1 || '     job_title = ' || v_job_title(i));
  end loop;
end;