프로시저 procedure

블럭을 하나의 객체로 저장해서 호출해서 사용하도록 한다. - 서브프로그램

: 프로시저 procedure, 함수 function

procedure : 처리를 하고 끝난다

function : 처리 후 결과를 반환한다.

1. 프로시저 : 주로 데이터에 대한 조작(Manipulation) - insert, update, delete

프로시저 생성

create procedure or replace 프로시저명( 파라미터변수명 데이터타입, .. ) is

선언부

begin

    조건문, 대입문, 반복문, 쿼리문

end;

/

1. 선언할 파라미터가 없으면 () 생략해야 한다.

2. 파라미터의 데이터타입에는 사이즈 지정 불가

특정 사번의 사원에 대해

지정한 % 만큼 인상된 급여로 급여데이터를 변경하는 프로시저

create or replace procedure proc_update_salary(emp_id number, pct number) is

begin

    update employees set salary = round(salary*(1+pct*0.01))

    where employee_id = emp_id;

    commit;

end;

/

--실행

select employee_id, salary

from employees

where employee_id=100;

execute proc_update_salary(100, 10);

exec proc_update_salary(100, 10);

무조건 100번 사원의 급여를 24000으로 변경하는 프로시저

: 파라미터 지정하지 않는 경우

create or replace procedure proc_update100 is

begin

    update employees

    set salary=24000

    where employee_id=100;

    commit;

end;

/

execute proc_update100;

departments 테이블에 새로운 부서를 등록하는 프로시저

해당 부서코드가 이미 있다면 부서명을 변경하고

                    없다면 새로운 부서로 등록하도록 한다.

create or replace procedure proc_regist_dept(id departments.department_id%type, 

                                                name departments.department_name%type) is

                                                 --proc_regist_dept(id number, name varchar2)

        cnt number; -- 변수선언

begin

    -- 해당 부서코드 존재여부 판단하여 변수에 결과값을 담는다.

    select count(*) into cnt

    from departments

    where department_id=id;

    if cnt=0 then

            insert into departments(department_id, department_name)

            values (id, name);

    else

            update departments

            set department_name=name 

            where department_id=id;

    end if;

    commit;

end;

/

/*

select count(*) into cnt from departments where department_id=id;

select count(*) from departments where department_id=310;

*/

desc departments

select * from departments;

exec proc_regist_dept(310, 'Service');

--exec proc_regist_dept(320, 'Service');

--데이터 값이 없으면 생성, 있으면 변경됨

int datas(){

    return 10;

}

function(함수)

어떤 처리를 한 후 처리에 따른 결과를 반환하는 형태

create or replace function 함수명(파라미터명 데이터타입) 

    return 리턴할 데이터의 데이터타입 is

선언부

begin

    return 데이터; --리턴하는 데이터에 따라 데이터타입 변경됨

end;

/

1. 파라미터가 없으면 () 없어야 한다.

2. 파라미터의 사이즈는 지정할 수 없다.

3. 리턴 데이터타입 사이즈 지정할 수 없다.

사원이 소속된 부서의 부서명을 반환하는 함수

create or replace function fn_dept_name(dept_id departments.department_id%type) 

        return varchar2 is --부서명 반환

        dept_name   departments.department_name%type; --varchar2(30)

begin

    select department_name into dept_name from departments  --into dept_name 변수에 데이터 담음

    where department_id=dept_id; --부서명이 프로그램이 가져온 코드일 때

    return dept_name; --변수에 데이터 담고 리턴

end;

/

select fn_dept_name (90) from dual;

사번, 성, 부서코드, 부서명 조회

select employee_id, last_name, department_id, fn_dept_name(department_id) department_name

from employees;

/*

select department_name from departments

    where department_id=90;

*/

-----------------

사원이 소속된 부서의 부서명을 반환하는 함수

create or replace function fn_dept_name(emp_id employees.employee_id%type) 

        return varchar2 is --부서명 반환

        dept_name   departments.department_name%type; --varchar2(30)

begin

    select (select department_name from departments d where e.department_id=department_id) into dept_name from employees e --into dept_name 변수에 데이터 담음

    where employee_id=emp_id; --부서명이 프로그램이 가져온 코드일 때

    return dept_name; --변수에 데이터 담고 리턴

end;

/

select fn_dept_name(200) from dual;

--------------------

회원정보를 관리하는 member 테이블 생성

create table member (

userid      varchar2(30) constraint member_userid_pk primary key,

password    varchar2(50) not null,

name        varchar2(30) not null,

phone       varchar2(13),

email       varchar2(50)

);

desc member;

insert into member (userid, password, name)

values ('hong', 'hong', '홍길동');

insert into member (userid, password, name)

values ('jeon', 'jeon', '전우치');

insert into member (userid, password, name)

values ('sim', 'sim', '심청');

commit;

select * from member;

로그인 : 아이디, 비밀번호를 입력하고 로그인버튼 클릭시

        일치여부를 판단해서 로그인 된다.        

아이디, 비밀번호 일치여부를 판단하는 함수

일치 : 1, 불일치 : 0 반환하는 경우

create or replace function fn_login(id varchar2, pwd varchar2) 

    return number is --일치 : 1, 불일치 : 0 반환

    cnt number; --변수선언

begin

    select count(*) into cnt from member where userid=id and password=pwd;

    return cnt;

end;

/

select count(*) from member where userid='hong' and password='hong';

select * from member;

-- dual로 데이터 판단

select fn_login('hong', 'hong') from dual;

select fn_login('hong', 'hsf') from dual;

아이디, 비번 일치여부를 판단해 일치시 이름을 반환

create or replace function fn_user_name(id member.userid%type, pwd member.password%type) 

    return varchar2 is

    name member.name%type;

begin

    select name into name from member

    where userid=id and password=pwd;

    return name;

end;

/

select name from member where userid='hong' and password='hong'; --홍길동

select fn_user_name('hong', 'asfd') from dual; --null

-----------------------------------------------------------------------------------------

desc notice

select * from notice order by id;

id 컬럼에는 sequence 를 적용.

insert into notice(id, title, content, writer)

values (seq_notice.nextval, '테스트', '테스트 공지글', '나그네');

commit;

id 컬럼에 시퀀스가 자동으로 적용되게 처리

: trigger 를 사용한다.   - DML(insert, update, delete) 실행시 각 데이터행의 컬럼값을 제어하는 처리를 자동실행

trigger 생성

create or replace trigger 트리거명

    실행시점(before, after) DML형태(insert, update, delete) on 테이블명

    for each row

begin

    처리로직

end;

/

seq_notice 의 시퀀스값은 notice 테이블의 id 컬럼에 반영

create or replace trigger trg_notice

    before insert on notice

    for each row

begin

    select seq_notice.nextval into :new.id from dual;

end;

/

insert into notice(title, content, writer)

values ('트리거 테스트', '트리거 테스트 공지글', '나그네');

commit;

select * from notice

order by id desc;

member 테이블에 userpwd 컬럼 추가 : raw

alter table member

add (userpwd raw(100));

select * from member;

비밀번호의 암호화가 필요하다

암호화 처리를 할 수 있는 권한 : dbms_crypto 패키지의 사용권한

-- cmd창에서 SQL> sqlplus system/0000 as sysdba

암호화처리와 관련된 스크립트를 실행

-- SQL> @D:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\dbmsobtk.sql => @ + admin 경로 + dbmsobtk.sql

권한부여 : 

SQL> grant execute on dbms_crypto to public;

암호화 함수

create or replace function fn_encrypt(input varchar2, key_data varchar2 := 'sw20170925')

    return raw is

    input_raw raw(100);

    key_raw raw(100);

    encryption_type pls_integer; --sql에서는 사용불가하고, pls_블럭에서만 사용가능한 데이터 타입

begin

    -- varchar2 -> raw 타입 변경

    input_raw := utl_i18n.string_to_raw(input, 'AL32UTF8');

    key_raw := utl_i18n.string_to_raw(key_data, 'AL32UTF8');

    -- 암호화에는 암호화슈트 지정이 필요함: dbms_crypto.des_cbc_pkcs5

    encryption_type := dbms_crypto.des_cbc_pkcs5;

    -- 입력한 데이터를 암호화처리

    return dbms_crypto.encrypt( src=>input_raw, key=>key_raw, typ=>encryption_type );

    

end;

/

select fn_encrypt('hong') from dual;

select * from member;

update member set userpwd=fn_encrypt (password);

commit;

member 테이블의 password 컬럼 삭제

alter table member

drop column password; --alter는 자동 commit

아이디, 비번 일치여부를 판단해 일치시 이름을 반환

create or replace function fn_user_name(id member.userid%type, pwd varchar2) 

    return varchar2 is

    name member.name%type;

begin

    select name into name from member

    where userid=id and userpwd=fn_encrypt(pwd); --userpwd=fn_encrypt(pwd) 암호화된 데이터. 복호화.

    return name;

end;

/

새로운 회원가입을 한다.

insert into member

values('park', '박문수', '010-1234-5678', 'park@naver.com', fn_encrypt('park'));   -- park이라고 저장되어진다.

select * from member;

select fn_user_name('hong','hong') from member dual; --db에 입력된건 암호화된데이터, 내가 입력한 건 hong데이터. 그래tj null로 나온다. 복호화시켜야함.

암호화되어 관리된 데이터를 원래 데이터로 바꾸는 복호화처리

복호화함수

create or replace function fn_decrypt(input_raw varchar2, key_data varchar2:= 'sw20170925') 

    return varchar2 is

    key_raw raw(100);

    decryption_type pls_integer;

    decrypted_raw raw(100);

begin

    -- key_data 를 raw 타입으로 변환

    key_raw := utl_i18n.string_to_raw(key_data, 'AL32UTF8');

    -- 암호화에 사용한 슈트를 복호화에 사용

    decryption_type := dbms_crypto.des_cbc_pkcs5;

    -- 복호화처리를 통해 복호화된 raw타입의 데이터를 반환받음

    decrypted_raw := dbms_crypto.decrypt( src=>input_raw, key=>key_raw, typ=>decryption_type);

    -- 복호화된 raw타입의 데이터를 문자타입으로 변환

    return utl_i18n.raw_to_char(decrypted_raw, 'AL32UTF8');

end;

/

select userid, fn_decrypt(userpwd) from member;