[oracle] 오라클 함수 작성, procedure 작성, 수정, 삭제, 연습문제[펌]

[oracle] 오라클 함수 작성, procedure 작성, 수정, 삭제, 연습문제[펌]

----------  함수 작성 : 보통 값을 계산하고 결과를 반환     --------------
create [or replace] function 함수명
[(argument...)]
return datatype
is 변수선언
begin
  --pl/sql 블록에는 반드시 한개의 return 문 필요
end;

---tex(sawon_pay*0.1) 를 얻기 위한 함수 작성 연습--
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;    -- no : 위에 아규먼트인 func1에 값을 입력받는다.
  return pay;
end;
/

select func1(2) from dual;
select sawon_no, sawon_name, sawon_pay,func1(sawon_no) as tex 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;
/
--(1) 사용자 정의 함수(func2)를 사용 한 부서명 얻기
select sawon_no, sawon_name, buser_num, func2(buser_num) from sawon;
--(2) 서브 쿼리를 사용한 부서명 얻기
select sawon_no, sawon_name, buser_num,(select buser_name from buser where buser_no=buser_num) from sawon;
--(1), (2) 결과는 같다.

 

--procedure 작성 : 보통 처리를 위한 블록으로 이름을 갖는다.(즉 리턴값 없이 처리만 한다.)
create [or replace] procedure 프로시저명
[(argument...)]   --in:실행환경에서 program으로 값 전달,  out: program에서 실행환경으로 값 전달
is 변수선언
begin
  ~
end;

create or replace procedure up_a(no in sawon.sawon_no%type             --외부에서 사원번호를 받겠다는 뜻이다.
                                    ,name out sawon.sawon_name%type      --외부로 사원이름을 넘겨준다는 뜻
                                    ,pay out sawon.sawon_pay%type) is    --외부로 사원pay를 넘겨준다는 뜻
begin
  select sawon_name sawon_pay into name,pay from sawon where sawon_no=no;
  insert into aa(munja,su) values(name,pay);
end procedure;
/
select * from aa;

select * from sa2;

--삭제
create or replace procedure up_b(no sa2.sawon_no%type) is
begin
  delete from sa1 where sawon_no=no;
end;
/
execute up_b(3);   --sql developer에선 실행안되고 cmd->sqlplus에서 한다.

--수정
create or replace procedure up_c(no sa2.sawon_no%type,
                                    jik sa2.sawon_jik%type) is
begin
  update sa2 set sawon_jik=jik where sawon_no=no;
end;
/

exec up_c(3, '이사'); --sql developer에선 실행안되고 cmd->sqlplus에서 한다.

select * from aa;

 

create or replace procedure up_cursor is
  cursor cur is select sawon_no, sawon_name from sa2;
  p_no sa1.sawon_no%type;
  p_name sa1.sawon_name%type;
begin
  open cur;
  loop
    fetch cur into p_no, p_name;  --fetch : cur의 값을 차례대로 p_no, p_name에 넘겨준다.
    exit when cur%notfound;  -- 커서안에 내용이 없을때, exit해라
    insert into aa(bun,munja) values(p_no, p_name);
  end loop;
  close cur;
end;
/

exec up_cursor; --sql developer에선 실행안되고 cmd->sqlplus에서 한다.
select * from aa; --sqlplus에서 한다.

--------------------------
create or replace procedure up_cursor2 is
  cursor cur2 is
  select buser_name, sum(sawon_pay) tot from sa2 inner join buser on buser_num=buser_no group by buser_name;
begin
  for imsi in cur2 loop
    insert into aa(munja,su) values(imsi.buser_name, imsi.tot);
  end loop;
  exception others insert into aa(munja) values('에러 발생');  --혹시 모를 에러를 대비 할 수 있다. 즉, exception을 줄 수 있다.
end;
/

select sawon_no as 사번, sawon_name as 이름, nvl(buser_name,'임시직') as 부서명, sawon_jik as 직급, to_char(sawon_pay*10000,'999,999,999') as 연봉,
case when sawon_pay >= 4000 then  to_char(trunc((sawon_pay*10000)*0.05, -3),'999,999,999')
when sawon_pay >= 3000  then to_char(trunc((sawon_pay*10000)*0.04, -3),'999,999,999')
else to_char(trunc((sawon_pay*10000)*0.03,-3),'999,999,999') end as 세금,
to_char((sawon_pay*10000) -
case when sawon_pay >= 4000 then trunc((sawon_pay*10000)*0.05, -3)
when sawon_pay >= 3000 then trunc((sawon_pay*10000)*0.04, -3)
else trunc((sawon_pay*10000)*0.03, -3) end, '999,999,999') as 실수령액
from sawon left outer join buser on buser_num=buser_no
where sawon_pay is not null order by 실수령액 desc;

 

select to_char(max(sawon_pay*10000),'999,999,999') as 최대값,
 to_char(min(sawon_pay*10000),'999,999,999') as 최소값,
 to_char(sum(sawon_pay*10000),'999,999,999,999') as 연봉총합,
 to_char(sum(case when sawon_pay >= 4000 then trunc((sawon_pay*10000)*0.05, -3)
when sawon_pay >= 3000  then trunc((sawon_pay*10000)*0.04, -3)
else trunc((sawon_pay*10000)*0.03,-3) end),'999,999,999') as 세금총합,
to_char(sum((sawon_pay*10000) -
case when sawon_pay >= 4000 then trunc((sawon_pay*10000)*0.05, -3)
when sawon_pay >= 3000 then trunc((sawon_pay*10000)*0.04, -3)
else trunc((sawon_pay*10000)*0.03, -3) end),'999,999,999,999') as 실수령액총합
from sawon;

select buser_name as 부서명, count(*) as 인원수, to_char(round(avg(sawon_pay*10000),0),'999,999,999') as 연봉평균 from sawon
right outer join buser on buser_num = buser_no where sawon_pay is not null group by buser_name;

select to_char(max(sawon_pay)*10000,'99,999,999'),
 to_char(min(sawon_pay)*10000,'99,999,999'), to_char(sum(sawon_pay)*10000,'9,999,999,999'),
  to_char(trunc(sum(case when sawon_pay>=4000 then sawon_pay*0.05*10000 when sawon_pay>=3000 then sawon_pay*0.04*10000 else sawon_pay*0.03*10000 end),-3),'99,999,999')  as 세금총합,
  trunc(sum(sawon_pay-case when sawon_pay>=4000 then sawon_pay*0.05 when sawon_pay>=3000 then sawon_pay*0.04 else sawon_pay*0.03 end)*10000,-3) as 실수령액총합 from sawon;

 

select sawon_no, sawon_name, buser_name, sawon_jik from sawon left outer join buser on buser_num = buser_no where buser_name is not null;

select gogek_name, gogek_tel, gogek_jumin from sawon left outer join gogek on sawon_no = gogek_damsano
left outer join buser on buser_num=buser_no where buser_name = '영업부' and sawon_name=;

select * from buser;
select * from gogek;
select * from sawon;

출처: https://java7.tistory.com/22 [감성적인 개발자가 되고싶습니다...]