오라클 명령어X
SQL Devloper(O) -> SqlPlus

-show user;
-desc 테이블명
-dbms_output.put_line();

-> 오라클 명령어 아님, 툴명령어임,, 툴이 바뀌면 사용할 수 없음...

2021.06.02

ex29_plsql.sql

-- 특정 직원 퇴사 -> 담당 업무 존재 확인? -> 업무 위임 -> 퇴사

-- 테이블 확인
select * from tblStaff;
select * from tblProject;


create or replace procedure procDeleteStaff(
    -- 매개변수
    pseq number,        -- 퇴사할 직원번호(tblstaff.PK)
    pstaff number,      -- 위임받을 직원번호(tblProject.staff)
    presult out number  -- 성공하면 (1) or 실패(0) -> 결과 확인
    
)
is
    vcnt number; -- 퇴사 직원의 담당 프로젝트 개수를 담는 변수
begin
    -- 1. 퇴사 직원의 담당 프로젝트가 있는지?
    select count(*) into vcnt from tblProject where staff_seq = pseq;
    dbms_output.put_line('퇴사할 직원이 ' || vcnt || '개의 프로젝트를 담당하고 있습니다.');
    
    -- 2. 1의 결과 > 있으면(위임), 없으면(아무것도 안함)
    if vcnt > 0 then
        -- 위임
        update tblProject set staff_seq = pstaff where staff_seq = pseq;
        dbms_output.put_line(pseq || '의 프로젝트를 ' || pstaff || '에게 위임합니다.');
    else
        null; -- null이라고 적으면 이 블럭에서는 정말로 아무것도 하지 않겠다는 표현(개발자 의도)★
        dbms_output.put_line('위임할 프로젝트가 없습니다.');
    end if;
    
    -- 3. 퇴사 처리
    delete from tblStaff where seq = pseq;
    dbms_output.put_line(pseq || '가 퇴사합니다.');
    
    presult := 1; -- 4. 성공
    
 exception
    when others then
        presult := 0; -- 4. 실패
    
end procDeleteStaff;


set serveroutput on;

-- 결과 확인
declare
    vresult number; -- 성공 유무
begin  
    procDeleteStaff(1,2,vresult);
    
    if vresult = 1 then
        dbms_output.put_line('퇴사성공');
    else
        dbms_output.put_line('퇴사실패');
    end if;
end;

1. 저장 프로시저 - 형식 자유로움

• 반드시 매개변수가 있어야한다.(개수는 상관없음, 0개 이상, 일반적으로는 1개 이상)
• 반환값이 0개 이상(out 파리미터 x N개 생성)

2. 저장 함수 - 형식 엄격

• 반드시 매개변수가 있어야한다.(개수는 상관없음, 0개 이상, 일반적으로는 1개 이상)
• 반드시 반환값이 있어야한다.(반드시 1개, 유일, 반환값이 단일값을 보장)

저장 함수

• 함수는 ANSI-SQL에서 사용이 가능하다.

-- public int fnSum (int pnum1, int pnum2){}
create or replace function fnSum (
    pnum1 number,
    pnum2 number
    -- presult out number -- out을 사용하는 행동 > 반환값이 여러개일 수 있다는 뜻 > 안씀
) return number -- function은 반환값이 유일
    -- return문을 사용하면 함수
    -- out 파라미터를 사용하면 프로시저
is
begin
    return pnum1 + pnum2;
end fnSum;


-- 확인
declare
    vresult number; -- 함수가 돌려주는 결과를 저장하는 변수
begin
    vresult := fnSum(100, 200);
    dbms_output.put_line(vresult);
    dbms_output.put_line(fnSum(300, 400)); -- 변수없이 바로 호출도 가능
end;

-- 반환값을 받는 방식의 차이 때문에 (프로시저는 주로 out파리미터로 받고 function은 return으로 받는다.)
-- PL/SQL(x)
-- 함수는 ANSI-SQL에서 사용이 가능하다.(함수의 특징★★★★★★)
-- 자주 쓰는 기능들은 함수로 만들어두면 편하다.

-- fnSum(basicpay, sudang) : 함수의 결과 값이 단일값이라 값이 필요한 모든 곳에 사용가능
select name, basicpay, sudang, basicpay + sudang, fnSum(basicpay, sudang) from tblInsa;

-- ANSI-SQL
-- 주민번호로 성별 나타내기
select name, buseo, jikwi,
    case
        when substr(ssn, 8, 1) = '1' then '남자'
        when substr(ssn, 8, 1) = '2' then '여자'
    end as gender
from tblInsa;


-- function
create or replace function fnGender(
    pssn varchar2 -- 매개변수로 주민등록번호 받기 
) return varchar2
is
begin
    return case
        when substr(pssn, 8, 1) = '1' then '남자'
        when substr(pssn, 8, 1) = '2' then '여자'
    end;
end fnGender;

-- 함수 사용(ANSI-SQL을 더 편하게 사용할 수 있음)
-- 함수를 한번 만들어두면 언제든 사용하기 쉬움(생산성)
select name, buseo, jikwi, fnGender(ssn) from tblInsa;


-- 저장 함수 : ANSI-SQL을 보조하는 역할, 작은 업무
-- 저장 프로시저 : 행동의 단위(메소드 개념), 큰 흐름