procedure
1. PL/SQL을 미리 등록해서 일괄적으로 처리할 수 있는 프로그램 집합체.
2. 형식
CREATE OR REPLACE PROCEDURE
(
매개변수 리스트 -- 자료형 지정시 크기지정 불가
)
IS
내부변수 리스트;
BEGIN
실행문;
END;
-- 두개 이상의 테이블에 자료를 분산 저장하는 프로시저
-- ID, PW만 저장하는 전용 테이블. 로그인할 때 주로 사용.
CREATE TABLE test18 (
id varchar2(10)
,pw varchar2(10)
);
ALTER TABLE test18
ADD CONSTRAINT test18_id_pk PRIMARY KEY (id);
INSERT INTO test18(id, pw) VALUES ('test', '1234');
INSERT INTO test18(id, pw) VALUES ('hong', '1234');
COMMIT;
-- 개인정보가 저장된 테이블. 회원 정보 출력시에만 사용.
CREATE TABLE test19 (
id varchar2(10)
,name varchar2(10)
,tel varchar2(10)
,email varchar2(10)
);
ALTER TABLE test19
ADD CONSTRAINT test19_id_fk FOREIGN KEY (id) REFERENCES test18(id);
ALTER TABLE test19
ADD CONSTRAINT test19_id_pk PRIMARY KEY (id)
INSERT INTO test19(id, name, tel, email) VALUES ('test', '테스트', '111-1111' , 'test@nate');
INSERT INTO test19(id, name, tel, email) VALUES ('hong', '홍길동', '222-2222' , 'hong@nate' );
commit;
-- 회원 가입하는 경우(id, pw, name, tel, email을 동시에 입력 받는다.)
INSERT INTO test18(id, pw) VALUES ('kim', '1234');
INSERT INTO test19(id, name, tel, email) VALUES ('kim', '김길동', '333-3333' , 'kim@nate');
COMMIT;
--> INSERT 쿼리를 두개 이상 동시 실행하려면 프로시저로 처리한다.
--프로시저 생성
CREATE OR REPLACE PROCEDURE proInsertMember
(
vid VARCHAR2
,vpw VARCHAR2
,vname VARCHAR2
,vtel VARCHAR2
,vemail VARCHAR2
)
IS
BEGIN
INSERT INTO test18(id, pw) VALUES (vid, vpw);
INSERT INTO test19(id, name, tel, email) VALUES (vid, vname, vtel, vemail);
COMMIT;
END;
-- 프로시저 실행
EXECUTE proInsertMember('park', '1234', '박길동', '444-4444', 'park@nate');
select t18.id, t18.pw, t19.name, t19.tel, t19.email
from test18 t18, test19 t19
where t18.id = t19.id;
-- 전화번호 수정 프로시저 작성. ID, PW가 일치하는 경우만 수정 가능.
UPDATE test19
SET TEL='새로운전화번호'
WHERE id='hong' and pw='1234'; --X
CREATE OR REPLACE PROCEDURE proUpdateTel
(
vid VARCHAR2
,vpw VARCHAR2
,vtel VARCHAR2
)
IS
BEGIN
UPDATE (select t1.id, t1.pw, t2.tel
from test18 t1, test19 t2
where t1.id = t2.id) tt
SET tt.tel=vtel
WHERE tt.id=vid and tt.pw=vpw;
COMMIT;
END;
EXECUTE proUpdateTel('hong', '1234', '새로운전화번호'); -- O
계정(sqluser, 1234) 새로 만들고, emp.sql 실행
-> city, buseo, jikwi, emp 테이블 생성, 제약조건 등록, 시퀀스 등록, 자료 입력됨.
-----------------------------------------------------
emp.sql 내용 실행
-----------------------------------------------------
문제) emp 테이블에서 신규 자료 입력하는 프로시저 작성.
EXECUTE proEmpInsert('김신애', '810809-2111111', '2001-10-10', '서울', '011-4151-4444', '개발부', '사원', 900000 , 102000);
INSERT INTO emp (emp_id, name, ssn, ibsadate, city_id, tel, buseo_id, jikwi_id, basicpay, sudang)
VALUES (empSeq.nextval
, '김신애'
, '810809-2111111'
, '2001-10-10'
, '서울' --'서울'이 아니라 city_id가 입력되어야 함
, '011-4151-4444'
, '개발부' --'개발부'가 아니라 buseo_id가 입력되어야 함
, '사원' --'사원'이 아니라 jikwi_id가 입력되어야 함
, 900000
, 102000); --X
INSERT INTO emp (emp_id
, name
, ssn
, ibsadate
, city_id
, tel
, buseo_id
, jikwi_id
, basicpay
, sudang)
VALUES (empSeq.nextval
, '김신애'
, '810809-2111111'
, '2001-10-10'
, (select city_id from city where city='서울')
, '011-4151-4444'
, (select buseo_id from buseo where buseo='개발부')
, (select jikwi_id from jikwi where jikwi='사원')
, 900000
, 102000); --O
COMMIT;
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE proEmpInsert
(
vname VARCHAR2
, vssn VARCHAR2
, vibsadate DATE
, vcity VARCHAR2 --city명
, vtel VARCHAR2
, vbuseo VARCHAR2 --buseo명
, vjikwi VARCHAR2 --jikwi명
, vbasicpay NUMBER
, vsudang NUMBER
)
IS
BEGIN
INSERT INTO emp (emp_id
, name
, ssn
, ibsadate
, city_id
, tel
, buseo_id
, jikwi_id
, basicpay
, sudang)
VALUES (empSeq.nextval
, vname
, vssn
, vibsadate
, (SELECT city_id FROM city WHERE city=vcity)
, vtel
, (SELECT buseo_id FROM buseo WHERE buseo=vbuseo)
, (SELECT jikwi_id FROM jikwi WHERE jikwi=vjikwi)
, vbasicpay
, vsudang);
COMMIT;
END;
문제) 상품, 입고, 출고 테이블에서 입고 테이블에 데이터 입력시 상품 테이블의 재고 수량이 변동되는 프로시저 작성.
CREATE TABLE 상품 (
상품코드 VARCHAR2(4)
,상품명 VARCHAR2(30)
,소비자가격 NUMBER
,재고수량 NUMBER DEFAULT 0 -- 자동 입력
);
CREATE TABLE 입고 (
입고번호 NUMBER
,상품코드 VARCHAR2(4)
,입고일자 DATE
,입고수량 NUMBER
,입고단가 NUMBER
);
-- 상품 테이블의 상품코드를 PK로 지정
ALTER TABLE 상품
ADD CONSTRAINT 상품_상품코드_PK PRIMARY KEY(상품코드);
-- 입고 테이블의 입고번호를 PK로 지정
ALTER TABLE 입고
ADD CONSTRAINT 입고_입고번호_PK PRIMARY KEY(입고번호);
-- 입고 테이블의 상품코드는 상품 테이블의 상품코드를 참조(FK)하는 키 지정
ALTER TABLE 입고
ADD CONSTRAINT 입고_상품코드_FK FOREIGN KEY(상품코드)
REFERENCES 상품(상품코드);
-- 상품 테이블의 상품 정보 입력
INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T001', '스마트TV40인치', 1000000);
INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T002', '스마트TV50인치', 1500000);
INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T003', '스마트TV60인치', 2000000);
INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('R001', '냉장고200리터', 1500000);
INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('R002', '냉장고250리터', 2000000);
COMMIT;
SELECT * FROM 상품;
-- 입고 테이블에서 입고 절차 진행시 상품 테이블의 재고수량 변동하는 프로시저 작성. 입고 번호는 자동 증가 처리(시퀀스가 아닌 프로시저 내에서 해결).
insert into 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) values ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), 'R002', SYSDATE, 10, 1800000);
UPDATE 상품
SET 재고수량 = 재고수량 + 10
WHERE 상품코드 = 'R002';
COMMIT;
insert into 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) values ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), 'R002', SYSDATE, 10, 1800000);
UPDATE 상품
SET 재고수량 = 재고수량 + 10
WHERE 상품코드 = 'R002';
COMMIT;
SELECT NVL(MAX(입고번호), 0)+1 FROM 입고; -- 시퀀스를 대신할 수 있는 쿼리를 통한 번호 얻기
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE ibgoInsert
(
v상품코드 VARCHAR2
,v입고수량 NUMBER
,v입고단가 NUMBER
)
IS
BEGIN
INSERT INTO 입고(입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), v상품코드, SYSDATE, v입고수량, v입고단가);
UPDATE 상품
SET 재고수량 = 재고수량 + v입고수량
WHERE 상품코드 = v상품코드;
COMMIT;
END;
******** 정답
CREATE OR REPLACE PROCEDURE ibgoInsert
(
v상품코드 VARCHAR2
,v입고수량 NUMBER
,v입고단가 NUMBER
)
IS
BEGIN
INSERT INTO 입고(입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), v상품코드, SYSDATE, v입고수량, v입고단가);
UPDATE 상품
SET 재고수량 = 재고수량 + v입고수량
WHERE 상품코드 = v상품코드;
COMMIT;
END;
문제2) 입고 테이블에서 입고수량을 수정하는 프로시저 작성
--입고번호, 상품코드, 이전수량, 이후수량
--> 입고 기록과 상품 재고가 변동됨.
EXECUTE ibgoUpdate(2, 'R002', 10, 20);
--입고 기록은 10에서 20으로 수정되어야 하고,
--재고 수량은 현재 재고수량에서 +10(이후수량-이전수량)으로 수정되어야 함.
CREATE OR REPLACE PROCEDURE ibdoUpdate
(
v입고번호 NUMBER
,v상품코드 VARCHAR2
,v이전수량 NUMBER
,v이후수량 NUMBER
)
IS
BEGIN
UPDATE 입고
SET 입고수량 = v이후수량
WHERE 입고번호 = v입고번호;
IF v이전수량 < v이후수량 THEN
UPDATE 상품
SET 재고수량 = 재고수량 + (v이후수량-v이전수량)
WHERE 상품코드 = v상품코드;
else
UPDATE 상품
SET 재고수량 = 재고수량 - (v이전수량-v이후수량)
WHERE 상품코드 = v상품코드;
END IF;
END;
문제3) 입고 테이블에서 입고수량을 삭제하는 프로시저 작성
--입고번호, 상품코드
--> 입고 기록과 상품 재고가 변동됨.
EXECUTE ibgoDelete(2, 'R002');
--입고 기록은 삭제되어야 하고,
--재고 수량은 현재 재고수량에서 삭제된 입고수량만큼 줄어야 함.
CREATE OR REPLACE PROCEDURE ibgoDelete
(
v입고번호 NUMBER
,v상품코드 VARCHAR2
)
IS
BEGIN
UPDATE 상품
SET 재고수량 = 재고수량 - (select 입고수량 from 입고 where 입고번호 = v입고번호)
WHERE 상품코드 = v상품코드;
DELETE 입고
WHERE 입고번호 = v입고번호;
END;
권한 관리
1. 권한은 시스템 권한과 객체권한이 있다.
2. 시스템 권한
- 오라클 관리자 암호 변경 (O/S Admin 권한 사용자)
명령프롬프트>sqlpuls / as sysdba
sql> alter user sys identified by 새로운암호;
- 사용자 암호 변경 (by 오라클 관리자)
sql> alter user 사용자계정이름 identified by 새로운암호;
- 사용자 암호 변경 (by 사용자)
SQL> ALTER USER 자신의계정이름 IDENTIFIED BY 새로운암호;
--사용자 암호 변경 (타사용자)
SQL> ALTER USER 타사용자계정이름 IDENTIFIED BY 새로운암호; -- 관리자가 아니므로 불가
--사용자 암호 사용 기간 만료 (관리자)
-->로그인할때는 예전 암호, 로그인하면 새로운 암호 입력하도록 함.
SQL> ALTER USER 사용자계정명 IDENTIFIED BY 임시암호;
SQL> ALTER USER 사용자계정명 PASSWORD EXPIRE; -- 암호를 만료시켜 로그인한 후 암호를 강제적으로 변경하도록 함 (by 관리자)
--사용자 계정 잠금 (관리자)
SQL>ALTER USER 잠글계정명 ACCOUNT LOCK;
--사용자 계정 잠금 해제 (관리자)
SQL>SQL>ALTER USER 해제할계정명 ACCOUNT UNLOCK;
--사용자 객체(테이블) 생성 시도
SQL>CREATE TABLE 테이블명(con1 number, col2 varchar2(10)); --생성 권한 부족
--사용자 객체 생성 권한 부여 (관리자)
SQL>GRANT CREATE TABLE TO 권한을줄계정명;
--사용자 삭제 (소유한 객체 없는 경우, 관리자)
SQL>DROP USER 계정명;
--사용자 삭제 (소유한 객체 있는 경우, 관리자)
SQL>DROP USER 계정명 CASCADE;
--사용자 삭제 (타사용자)
SQL>DROP USER 타계정명; -- 관리자가 아니므로 불가
--사용자 계정에 관리자 권한 부여 (관리자)
SQL>GRANT DBA TO 권한을줄계정명; -- DBA: 롤명
--계정 연습용 Cuser 사용자 생성 (사용자-관리자 권한 부여)
SQL> 가능
--사용자 암호 변경 (사용자-관리자 권한 부여)
SQL> 가능
--사용자 암호 사용 기간 만료 (사용자-관리자 권한 부여)
SQL> 가능
--사용자 계정 잠금 (사용자-관리자 권한 부여)
SQL> 가능
--사용자 삭제 (사용자-관리자 권한 부여)
SQL> 가능
--사용자 계정에 부여된 관리자 권한 취소 (관리자)
SQL> REVOKE DBA FROM 권한을회수할계정명 -- DBA: 롤명 // DBA 대신 다른 권한을 기재해도 됨.
--계정 연습용 Cuser 사용자 생성 (사용자-관리자 권한 취소)
SQL> 불가
--사용자 암호 변경 (사용자-관리자 권한 취소)
SQL> 불가
--사용자 암호 사용 기간 만료 (사용자-관리자 권한 취소)
SQL> 불가
--사용자 계정 잠금 (사용자-관리자 권한 취소)
SQL> 불가
--사용자 삭제 (사용자-관리자 권한 취소)
SQL> 불가
3. 객체 권한(SQLDeveloper)
scott 계정에서 hr 계정내 테이블에 접근(select 문) 시도
--객체 접근 시도 (관리자)
select * from 테이블을소유한계정명.테이블명; -- 가능
--객체 접근 시도 (타사용자)
select * from 테이블을소유한계정명.테이블명; -- 불가
--객체 접근 권한 부여 (소유자) -- 예를 기준으로 HR 계정만 실행가능 (같은 테이블 스페이스 내에 있을 경우)
grant select on employees to scott; -- SELECT: 객체명
--객체 접근 시도 (타사용자-권한 부여)
select * from 테이블을소유한계정명.테이블명; -- 가능
--특정 정보만 제한하는 뷰 생성 (소유자)
--SALARY와 커미션퍼센트만 보여주기 싫을때 뷰생성해서 뷰 권한을 부여해줌.
CREATE OR REPLACE VIEW employeesView
AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, MANAGER_ID, DEPARTMENT_ID
FROM employees;
--제한된 뷰 접근 권한 부여 (소유자)
grant select on employeesView to scott;
--객체 접근 시도 (제한된 뷰 사용, 타사용자-권한 부여)
select * from hr.employeesView;
--객체 접근 권한 취소 (소유자)
revoke select on employees from scott;
--부여한 객체 권한 확인(소유자) -- HR입장
select * from user_tab_privs_made;
--부여된 객체 권한 확인(타사용자) --scott 입장
select * from user_tab_privs_recd;
--객체 수정, 삭제 권한 부여 (소유자)
GRANT SELECT, INSERT, UPDATE, DELETE ON 객체 TO 사용자명;
--객체 수정, 삭제 시도 (타사용자-권한 부여)
--가능
--객체 수정, 삭제 권한 취소 (소유자)
REVOKE SELECT, INSERT, UPDATE, DELETE ON 객체 FROM 사용자명;
'Oracle > 기본문법' 카테고리의 다른 글
WRITTEN BY
