PL/SQL 기초 연습

728x90
<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;


본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.



728x90