PL/SQL 기초 연습 해보기

<PL/SQL 개요>

-- 위키피디아 소개 : https://ko.wikipedia.org/wiki/PL/SQL

-- 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나이다.

-- 구조 (3단계)

-- 1) 선언부

-- 2) 수행부

-- 3) 예외부

create table aa(bun number,munja varchar2(20), su number);

set serveroutput on; -- 표준 출력장치로 출력 선언

declare no number :=0;

begin no := 100 + 200;

dbms_ouput.put_line(no);

insert into aa(bun) values(no);

end;

select * from aa;

< 배열 처리 >

declare

  type result is record(a number, b number);

  type test is varray(100) of result;

  test1 test := test(); -- 객체 초기화

begin

  test1.extend(50); -- 50개에 대한 공간 할당

  test(1).a := 10;

  test(1).b := 20;

  dbms_output.put_line(test1(1).a);

  end;

  

< exception >

  declare

    counter number(3) := 10;

    re number;

  begin

    re := counter /0;

    dbms_ouput.put_line(re);

  exception when zero_divide then

    dbms_ouput.put_line('error');

  end;

  

< 변수 선언 - 해당 테이블 형 >

  declare

    v_a sawon%rowtype;

  begin

    select * into v_a from sawon where sawon_no=1;

    dbms_output.put_line(v_a.sawon_no || v_a.sawon_name);

    insert int aa values(v_a.sawon_no, v_a.sawon_name,v_a.sawon_pay);

  end;

  

< 변수 선언 - 해당 테이블 칼럼형 >

  declare

    a sawon.sawon_no%type;

    b sawon.sawon_name%type;

    c sawon.sawon_pay%type;

  begin

    select sawon_no,sawon_name, sawon_jik into a,b,c from sawon

    where sawon_no=10;

    dbms_ouput.put_line(a || '' || b || '' || c);

end;

  

< 조건 판단문 if >

declare

  v_a sawon%rowtype;

  v_str varchar2(10);

begin

  select * into v_a from sawon where sawon_no=9;

  if(v_a.buser_num=10) then  v_str:=concat(v_a.sawon_name, ' 10');

  end if;

  if(v_a.buser_num=40) then v_str:=concat(v_a.sawon_name, '40');

  end if;

  

if(v_a.buser_num=10)

    then v_str:=concat(v_a.sawon_name,'10번');

if(v_a.buser_num=20)

    then v_str:=concat(v_a.sawon_name,'20번');

if(v_a.buser_num=30)

    then v_str:=concat(v_a.sawon_name,'30번');

if(v_a.buser_num=40)

    then v_str:=concat(v_a.sawon_name,'40번');

else

    v_str:=concat(v_a.sawon_name, '기타');

end if;

    dbms_ouput.put_line(a || '' || b || '' || c);

< 반복문 for loop >

declare

  dan number(2):= 2;

  i number(2) := 0;

  tot number(2):= 0;

begin

  for i in 1..9 loop

  dbms_output.put_line(dan || '*' || i || '=' || tot);

  end loop;

end;

< 반복문 >

declare

  v_cou number := 1;

begin

  while(v_cou <=10) loop

  dbms_output.put_line(v_cou);

  v_cou := v_cou + 1;

  end loop;

end;

-- 종료

declare

  v_cou_number := 1;

begin

  loop

  dbms_output.put_line(v_cou);

  exit when(v_cou = 5);

  v_cou := v_cou +1;

  end loop;

end;

-- 종료

< 커서 : 사용자가 실행한 sql 문의 단위를 말함 >

-- 1개의 행에 대한 수행은 별도 선언없이 암시적인 커서를 사용

-- 여러개의 행에 대해서는 커서를 이용해야 됨

-- 형식) CURSOR 커서명 OPEN 커서명 FETCH 커서명 INTO 변수 CLOSE 커서명

declare

  no number;

  name varchar2(10);

  pay number;

  cursor cs is select sawon_no, sawon_name, sawon_pay from sawon

  where sawon_jik='사원';

begin

  open cs;

  loop

  fetch cs into no, name, pay;

  exit when cs%notfound;

  dbms_output.put_line(no || '' || name || '' || pay);

end loop

  close cs;

  end;

  

 < open ~ fetch ~ close 없이 커서 처리 >

declare

  saw sawon%rowtype;

  cursor cc is

    select sawon_no, sawon_name, sawon_pay from sawon

    where sawon_jik='과장';

begin

  for saw in cc loop

    exit when cc%notfound;

    dbms_output.put_line(saw.sawon_no || '' || saw.sawon_name ||

end loop;

end;

< 함수 작성하기 >

-- create or replace function 함수명[(인자)...]

-- return datatype

-- is

-- begin

-- end;

create or replace function func1 (no number)

  return number is pay number(9);

begin

  pay := 0;

  select sawon_pay * 0.1 into pay from sawon where sawon_no=no;

  return pay;

end;

select func1(100) from dual;

select sawon_no, sawon_name, sawon_pay;

sawon_pay * 0.1 func1(sawon_no) from sawon;

< 부서명 얻기 >

create or replace function func2(bno number) return varchar2 is

bname varchar2(10);

begin

select buser_name into bname from buser where buser_no=bno;

return bname;

end;

select sawon_no, sawon_name, buser_num from sawon;

< procedure : 이름을 가진 PL/SQL 블럭 >

-- 형식

-- create or replace procedure 프로시저명 [(인자)...] is

-- 변수선언

-- begin

-- end;

create table sawon1 as select * from sawon;

select * from sawon1;

< 레코드 삭제 프로시저 >

create or replace produre pr_a is

begin

delete from sawon1 where sawon_no=1;

end;

/

execute pr_a; -- procedure의 실행

select * from sawon1;

< 레코드 수정 프로시저 >

create or replace procedure pr_b is

bun number :=2;

begin

update sawon1 set sawon_pay = 7777 where sawon_no=bun;

end;

/

exec pr_b;

select * from sawon1;

-- 매개변수가 있는 레코드 삭제 프로시저

create or replace procedure pr_c(no sawon1.sawon_no%type) is

begin

delte from sawon1 where sawon_no = no;

end;

/

exec pr_c(2);

exec pr_c(3);

select * from sawon1;

< 매개변수가 있는 레코드 수정 프로시저 >

create or replace procedure pr_d(no sawon1.sawon_no%type, is

jik sawon1.sawon_pay%type) is

bun number := 2;

begin

update from sawon1 set sawon_pay = pay, sawon_jik = jik

where sawon_no=no;

end;

/

exec pr_d(5, 8888, '이사');

exec pr_d(6, 5678, '과장대우');

select * from sawon1;

create or replace procedure pr_sell(no in sawon1.sawon_no%type) is

name varchar2(10);

begin

  select sawon_name into name from sawon

  where sawon_no=no;

  dbms_output.put_line('이름은' || name);

end;

exec pr_sell(5);

-- 종료

create or replace procedure pr_sell2(no in sawon1.sawon_no%type,

name out sawon1.sawon_name%type,

jik out sawon1. sawon_jik%type) is

begin

  select sawon_name, sawon_jik into name, jik from sawon

  where sawon_no=no;

  dbms_output.put_line('이름은' || name || ',직급은' || jik);

end;

/

< 정리 : person 테이블 >

create table person(id number(3) primary key,

name varchar2(20), wieght number(3));

desc person;

insert into person values(1, '홍길동', 66);

insert into person values(2, '고길동', 76);

insert into person values(3, '신길동', 68);

select * from person;

< procedure로 insert >

create or replace procedure p_1(no_number(3), name varchar2, weight number) is

name varchar2, weight number) is

begin

  insert into person values(no, name, weight);

  commit;

  exception when others then

  dbms_output.put_line('insert error');

  rollback;

end;

/

exec p_1(5,'김밥', 77);

select * from person;

< select 복수 개 변환 : 커서 사용 >

create or replace procedure p_5(s_id person.id%type) is

  cursor cur is select id,name from person;

  pid person.id%type;

  pname person.name%type;

begin

  open cur;

  loop

  fetch cur into pid, pname;

  exit when cur%notfound;

  dbms_output.put(pid);

  dbms_output.put(pname);

  end loop;

  close cur;

end;

/

< select 복수 개 반환 : 커서 사용 >

create or replace procedure p_6 is

  cursor cur is select id, name,weight from person;

begin

  for per_list in cur loop

  dbms.output.put(per_list.id);

  dbms_output.put(per_list.name);

  dbms_output.put_line(per_list.weight);

  end loop;

end;

exec p_6;