PL/SQL
editplus.com 접속 해서 User Files 탭을 눌러줌.
c/c+ , java ~~~~ 써진 것을 클릭.
* SQL/PLSQL stx - SudheerReddy Sankaramaddi (2008-02-16) 선택해서 다운 받아줌.
알집 풀고 나온 파일을 C:\Program Files\EditPlus 3 폴더에 넣어서 덮어써줌.
에디트플러스켜서 configuration 해서
File - Setting & syntax 눌러서 Add 눌러서 PL/SQL 추가.
File extention : sql 해줌.
Syntax File : C:\Program Files\EditPlus 3\plsql.stx 를 넣어줌.
OK 후 나와서 새로운 파일을 켜주고 test.sql 로 저장해서 select * from emp; 했을 때 색이 변하면 잘 된것이다.
C:\app\Oracle\product\11.2.0\dbhome_1\sqlplus\admin 폴더에 glogin.sql 을 열어서 제일마지막줄에
DEFINE _EDITOR='C:\Program Files\EditPlus 3\editplus.exe
위 한줄을 추가해줌. 저장 후 끈다.
명령프롬프트에서 아무명령어를 쳐서
예를 들면 이것 - select * from dept; 쳐서
ed를 치면 에디트플러스로 실행되고
끄고 명령프롬프트에서 ed 0430을 치면 에디트플러스로 켜진다. 0430파일이 없으면 만들겠냐고 물어보고 시작한다.
SELECT empno, ename, sal
FROM emp
WHERE ename = UPPER('scott');
쳐서 저장후 반드시 닫아야한다.
그리고 명령프롬프트에서 start 0430 을 치면 결과값이 나온다.
그리고 다시 edit을 하려면 ed 0430 해서 열어서 수정해주면 된다.
------------------------------------------------------------------------------------
--REM PL/SQL 변수
--1)Scalar 변수
--VARCHAR2, NUMBER, CHAR, DATE
--2)참조변수
--ename%TYPE, sal%TYPE
--사번 7788, 사원이름 scott 의 정보를 출력하시오.
/*
SET SERVEROUTPUT ON
DECLARE
V_EMPNO NUMBER(4);
V_ENAME VARCHAR2(20);
BEGIN
V_EMPNO := 7788;
V_ENAME := UPPER('scott');
DBMS_OUTPUT.PUT_LINE('사번 | 이름');
DBMS_OUTPUT.PUT_LINE('------------------------------------');
DBMS_OUTPUT.PUT_LINE(V_EMPNO || ' | ' || V_ENAME);
END;
/
*/
--사번을 입력받은 후, 사원의 이름과 사원의 입사날짜를 출력하는 PL/SQL을 작성하시오.
/*
ACCEPT t_empno PROMPT 'Employee Number : '
DECLARE
V_EMPNO emp.empno%TYPE := &t_empno;
V_ENAME emp.ename%TYPE;
V_HIREDATE emp.hiredate%TYPE;
BEGIN
SELECT ename, hiredate
INTO V_ENAME, V_HIREDATE
FROM emp
WHERE empno = V_EMPNO;
DBMS_OUTPUT.PUT_LINE('사번 | 이름 | 입사날짜');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
DBMS_OUTPUT.PUT_LINE(V_EMPNO || ' | ' || V_ENAME || ' | '
|| TO_CHAR(V_HIREDATE, 'YYYY-MM-DD'));
END;
/
*/
--사번을 입력받은 후, 사번, 이름, 봉급, 부서이름, 근무처를 출력하는 PL/SQL을 완성하시오.
/*
ACCEPT t_empno PROMPT 'Employee Number : '
DECLARE
V_EMPNO emp.empno%TYPE := &t_empno;
V_ENAME emp.ename%TYPE;
V_SAL emp.sal%TYPE;
V_DNAME dept.dname%TYPE;
V_LOC dept.loc%TYPE;
BEGIN
SELECT ename, sal, dname, loc
INTO V_ENAME, V_SAL, V_DNAME, V_LOC
FROM emp, dept
WHERE emp.deptno = dept.deptno AND empno = V_EMPNO;
DBMS_OUTPUT.PUT_LINE('사번 | 이름 | 봉급 | 부서이름 | 근무처');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
DBMS_OUTPUT.PUT_LINE(V_EMPNO || ' | ' || V_ENAME || ' | '
|| V_SAL || ' | ' || V_DNAME || ' | ' || V_LOC);
END;
/
*/
REM %ROWTYPE
--1)테이블 또는 뷰의 여러가지 열들을 RECORD 로 선언하기 위해 사용
--2)테이블 이름 뒤에 %ROWTYPE을 붙여 선언한다.
--3)장점
--a. 알지 못하는 테이블의 칼럼들의 형식이 자동으로 부여된다.
--b. 실행시 컬럼의 갯수와 데이터 타입을 몰라도 가능
--사원테이블에서 이름을 입력받아 각 사원의 정보를 출력하는 PL/SQL 을 완성하시오.
/*
SET SERVEROUTPUT ON
ACCEPT t_ename PROMPT 'Enter a Employee Name : '
DECLARE
V_ENAME emp.ename%TYPE := '&t_ename';
emp_record emp%ROWTYPE; -> emp의 속성값이 뭐가 있는지 모르고 형식도 모르지만 그에 맞게 만들어줌..?
BEGIN
SELECT *
INTO emp_record
FROM emp
WHERE ename = UPPER(V_ENAME);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Employee No. : ' || TO_CHAR(emp_record.empno));
DBMS_OUTPUT.PUT_LINE('Employee Name : ' || emp_record.ename);
DBMS_OUTPUT.PUT_LINE('Salary : ' || emp_record.sal);
DBMS_OUTPUT.PUT_LINE('Bonus : ' || emp_record.comm);
DBMS_OUTPUT.PUT_LINE('Hiredate : ' || TO_CHAR(emp_record.hiredate, 'YYYY-MM-DD'));
END;
/
SET SERVEROUTPUT OFF
*/
REM TABLE TYPE
--여러개의 ROW를 처리하기 위해
--Syntax
-- TYPE table_type_name IS TABLE OF
-- {column_type | variable%TYPE | table_name.column%TYPE} [NOT NULL]
-- INDEX BY BINARY_INTEGER;
-- identifier table_type_name;
--1)배열 형태의 테이블 타입 선언
--TYPE ENAME_TABLE_TYPE IS TABLE OF emp.ename%TYPE
--INDEX BY BINARY_INTEGER;
--2)테이블 타입 변수 선언
--ENAME_TABLE ENAME_TABLE_TYPE;
--3)인덱스로 사용할 변수 선언 및 양의 정수값으로 초기화
--I BINARY_INTEGER :=0;
--4)반복문을 통해 SELECT 문을 통해 추출된 각 칼럼 데이터 값들을 테이블 변수에 저장
--FOR J IN (SELECT ENAME FROM EMP) LOOP
-- I := I + 1;
-- ENAME_TABLE(I) := J.ENAME;
--END LOOP;
--5)출력
--FOR K IN 1..I LOOP
-- DBMS_OUTPUT.PUT_LINE(ENAME_TABLE(K));
--END LOOP;
--테이블 변수를 이용해서 EMP 테이블에서 사원이름과 업무들을 출력하시오.
/*
SET SERVEROUTPUT ON
DECLARE
TYPE ENAME_TABLE_TYPE IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE JOB_TABLE_TYPE IS TABLE OF emp.job%TYPE
INDEX BY BINARY_INTEGER;
ENAME_TABLE ENAME_TABLE_TYPE;
JOB_TABLE JOB_TABLE_TYPE;
I BINARY_INTEGER :=0;
BEGIN
FOR J IN (SELECT ename, job FROM EMP) LOOP
I := I + 1;
ENAME_TABLE(I) := J.ename;
JOB_TABLE(I) := J.job;
END LOOP;
DBMS_OUTPUT.PUT_LINE('사원이름' || ' | ' || '업무');
DBMS_OUTPUT.PUT_LINE('--------------------------');
FOR K IN 1..I LOOP
DBMS_OUTPUT.PUT_LINE(ENAME_TABLE(K) || ' | ' || JOB_TABLE(K));
END LOOP;
END;
/
SET SERVEROUTPUT OFF
*/
REM PL/SQL 조건문
--이름, 급여, 부서번호를 입력받아 사원테이블에 등록하는 PL/SQL 을 작성하시오.
--단, 부서번호가 10번이면 입력한 급여의 20%를 추가하고, 초기값이 9000부터
--9999까지 증가하는 시퀀스를 작성해서 완성하시오.
--CREATE SEQUENCE emp_seq
-- START WITH 9000
-- INCREMENT BY 1
-- MAXVALUE 9999;
/*
ACCEPT t_name PROMPT 'Name : '
ACCEPT t_sal PROMPT 'Salary : '
ACCEPT t_deptno PROMPT 'Department No. : '
DECLARE
V_ENAME emp.ename%TYPE := UPPER('&t_name');
V_SAL emp.sal%TYPE := &t_sal;
V_DEPTNO emp.deptno%TYPE := &t_deptno;
BEGIN
IF V_DEPTNO = 10 THEN
V_SAL := V_SAL * 1.2;
END IF;
INSERT INTO emp(empno, ename, sal, deptno)
VALUES(emp_seq.NEXTVAL, V_ENAME, V_SAL, V_DEPTNO);
COMMIT;
END;
/
*/
--이름을 입력받아서 그 업무가 MANAGER 이거나 ANALYST 이면 급여가 50%가산하고,
--업무가 MANAGER와 ANALYST 가 아니면 20%를 가산하는 PL/SQL문을 작성하시오.
/*
SET SERVEROUTPUT ON
ACCEPT t_ename PROMPT '이름 : '
DECLARE
V_ENAME emp.ename%TYPE := UPPER('&t_ename');
V_JOB emp.job%TYPE;
V_SAL emp.sal%TYPE;
BEGIN
SELECT job, sal
INTO V_JOB, V_SAL
FROM emp
WHERE ename = V_ENAME;
IF V_JOB IN ('MANAGER', 'ANALYST') THEN
V_SAL := V_SAL * 1.5;
ELSE
V_SAL := V_SAL * 1.2;
END IF;
UPDATE emp
SET sal = V_SAL
WHERE ename = V_ENAME;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Update Success');
END;
/
SET SERVEROUTPUT OFF
*/
REM PL/SQL 반복문
--1)LOOP ... END LOOP --> do ~ while()
--2)FOR LOOP ... END LOOP --> for()
--3)WHILE LOOP --> while()
---LOOP ~ END LOOP 를 이용해서 1부터 5까지 출력
/*
SET SERVEROUTPUT ON
DECLARE
I NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(I);
I := I + 1;
IF I > 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
SET SERVEROUTPUT OFF
*/
--FOR LOOP 를 이용해서 부서 테이블을 출력하시오.
/*
SET SERVEROUTPUT ON
DECLARE
V_DEPT dept%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('부서번호 ' || ' | ' || '부서이름 ' || ' | ' || '근무지');
DBMS_OUTPUT.PUT_LINE('---------------------------------------');
FOR CNT IN 1..4 LOOP
SELECT *
INTO V_DEPT
FROM dept
WHERE deptno = CNT * 10;
DBMS_OUTPUT.PUT_LINE(V_DEPT.deptno || ' | ' || V_DEPT.dname || ' | '
|| V_DEPT.loc);
END LOOP;
END;
/
SET SERVEROUTPUT OFF
*/
REM SUBPROGRAM
--1)잘 정리된 논리적 코드블록 이름
--2)컴파일(pcode)된 상태에서 데이터베이스에 저장되기 때문에 성능이 향상된다.
--3)테이블 이름이나 컬럼의 이름이 명시되지 않아서 보안에 도움이 된다.
--4)종류
--Stored Procedure <-----Java에서 사용
--Stored Function
REM Stored PROCEDURE
--1)목적
--속도, 보안
--2)컴파일(pcode)된 상태에서 데이터베이스에 저장
--3)나중에 실행될 일련의 명령어들의 집합
--4)리턴할 행의 갯수가 많으면 에러발생
--5)SELECT 는 반드시 SELECT INTO 를 사용해야
--6)Syntax
-- CREATE OR REPLACE PROCEDURE procedure_name
-- (
-- 매개변수 [변수모드] 데이타타입
-- ex) v_empno IN{IN | OUT | INOUT} emp.empno%TYPE;
-- )
-- IS
-- 내부변수
-- BEGIN
-- PL/SQL 문장들;
-- END;
-- /
--DELETE FROM emp;
--CREATE OR REPLACE PROCEDURE emp_del_all
--IS
--BEGIN
-- DELETE FROM emp;
--END;
--/
--7)오류 발생시 오류 보기
--딕셔너리 USER_ERRORS
--SHOW ERROR
--8)딕셔너리에서 확인하기
/*
DESC USER_PROCEDURES;
SELECT OBJECT_NAME, PROCEDURE_NAME FROM USER_PROCEDURES;
DESC USER_SOURCE;
SQL> COLUMN NAME FORMAT A15;
SQL> COLUMN TEXT FORMAT A30;
SQL> SELECT NAME, TEXT FROM USER_SOURCE
2 WHERE NAME = UPPER('emp_del_all');
NAME TEXT
--------------- ------------------------------
EMP_DEL_ALL PROCEDURE emp_del_all
EMP_DEL_ALL IS
EMP_DEL_ALL BEGIN
EMP_DEL_ALL DELETE FROM emp;
EMP_DEL_ALL END;
*/
--9)저장 프로시저(Stored Procedure) 삭제하기
-- DROP PROCEDURE procedure_name
-- DROP PROCEDURE emp_del_all;
--10)매개변수 없는 프로시저
--CREATE OR REPLACE PROCEDURE emp_del_all
--IS
--BEGIN
-- DELETE FROM emp;
--END;
--/
--실행
--EXEC emp_del_all
--11)IN 매개변수가 있는 프로시저
--사원번호와 봉급을 입력받아 업데이트하는 저장 프로시저를 완성하시오.
/*
CREATE OR REPLACE PROCEDURE emp_sal_update
(
v_empno IN emp.empno%TYPE,
v_sal emp.sal%TYPE
)
IS
BEGIN
UPDATE emp
SET sal = v_sal
WHERE empno = v_empno;
COMMIT;
END;
/
EXEC emp_sal_update(7788, 10000);
*/
--12)OUT 매개변수가 있는 저장 프로시저
--사번을 입력받아 사원이름과 봉급 검색하는 프로시저를 완성하시오.
CREATE OR REPLACE PROCEDURE emp_select
(
v_empno IN emp.empno%TYPE,
v_ename OUT emp.ename%TYPE,
v_sal OUT emp.sal%TYPE
)
IS
BEGIN
SELECT ename, sal
INTO v_ename, v_sal
FROM emp
WHERE empno = v_empno;
END;
/
--OUT 매개변수가 있는 프로시저를 실행할 때는 반드시 변수 Binding 을 해야 한다.
SQL> VAR g_ename VARCHAR2(20)
SQL> VAR g_sal NUMBER;
SQL> EXEC emp_select(7788, :g_ename, :g_sal)
PL/SQL procedure successfully completed.
SQL> PRINT g_ename
SQL> PRINT g_sal