PL/SQL 기초

-- PL/SQL 기초

-- 행 여러번 입력 절차 ( 스크립트 )

/* 

    PL/SQL은 SQL에 없는 다음과 같은 기능이 제공

    

    - 변수 선언을 할 수 있습니다.

    - 비교 처리를 할 수 있습니다. 

    - 반복 처리를 할 수 있습니다. 

*/

/*

    스칼라

    PL/SOL에서 변수를 선언할 때 사용되는 자료형은 SQL에서 사용하던 자료형과 

    거의 유사하다. 숫자를 저장하려면 NUMBER를 사용하고 문자를 저장하려면 

    VARCHAR2를 사용해서 선언합니다. 

    레퍼런스

    이전에 선언된 다른 변수 또는 데이터베이스 컬럼에 맞추어 변수를 선언하기 위해 

    %TYPE속성을 사용할 수 있습니다. 

    그 변수나 컬럼과 같은 타입의 형을 쓰겠다.

*/

-- SCOTT 출력 절차

SET SERVEROUTPUT ON

DECLARE

    vename varchar2(10);

    VENAME VARCHAR(20);

BEGIN

    VENAME := 'SCOTT';

    DBMS_OUTPUT.PUT_LINE(VENAME);

END;

-- 7788번 사원명 출력 절차

SELECT ENAME 

FROM EMP

WHERE EMPNO=7788;

    -- 7788번 사원명 출력 절차 (출력형식 사원명 = XXX , 컬럼명 비노출)

DECLARE

    -- 컬럼 미참조 스칼라변수 선언

    VENAME VARCHAR(20);

    -- 컬럼 참조 레퍼런스(참조)변수 선언

    VENAME emp.ename%type;

BEGIN

    /* 

        INTO 절에는 반드시 컬럼이 와야함 

        (SELECT 컬럼명DMF INTO 다음에 있는 VENAME에 넣고 출력을 해야 함

    */

    SELECT ENAME INTO VENAME

    FROM EMP

    WHERE EMPNO=7788;

    DBMS_OUTPUT.PUT_LINE(' 사원명 = ' || VENAME);

END;

/*

    DECLARE - > 생략가능

    BEGIN

    END는 반드시 와야함

    

    DECLARE에 있으면 변수

    테이블에 선언되면 컬럼

*/

-- 프로그래밍에서 값, 양을 '스칼라'라고 칭함

-- 값을 가지는 것을 참조 : 레퍼런스

DECLARE

    -- 컬럼 참조 스칼라변수 선언

    VENAME EMP.ENAME%TYPE;

BEGIN

    SELECT ENAME INTO VENAME

    FROM EMP

    WHERE EMPNO=7788;

    DBMS_OUTPUT.PUT_LINE('사원명 = ' || VENAME);

END;

/*

PL/SQL의 SELECT 문으로 EMP 테이블에서 사원번호와 이름을 조회합시다. 

1. ED 다음에 파일이름을 입력하여 새로 생긴 SQL 파일에 

다음과 같이 입력하시오.(파일이름:EXAM02.sql)

*/

declare

    vempno emp.empno%type;

    vename emp.ename%type;    

begin

    dbms_output.put_line( ' 사번 / 이름' ); -- put_line : 출력 후 개행

    dbms_output.put_line( ' ------------- ' );

    

    select empno, ename into vempno, vename

    from emp

    where ename='SCOTT';

    dbms_output.put_line(vempno || ' / ' || vename);

end;

-------------- *( 모든 컬럼 )의 행 참조변수

DECLARE

    vemp EMP%ROWTYPE;

BEGIN

    dbms_output.put_line( ' 사번 / 이름' ); -- put_line : 출력 후 개행

    dbms_output.put_line( ' ------------- ' );

    

    SELECT * INTO vemp -- 모든 컬럼을 가지는 행의 입력

    FROM emp

    WHERE ename='SCOTT';

    -- 행 참조변수.컬럼

    dbms_output.put_line(vemp.empno || ' / ' || vemp.ename);

END;

SET SERVEROUTPUT ON

DECLARE

  -- 레코드 타입을 정의

  -- 하나의 레코드의 네가지의 변수타입 선언

    TYPE emp_record IS RECORD 

    (

        v_empno emp.empno%TYPE,

        v_ename emp.ename%TYPE,

        v_job emp.job%TYPE,

        v_deptno emp.deptno%TYPE 

    );

    -- 레코드로 변수 선언

    emp_rec emp_record;

BEGIN

  -- SCOTT 사원의 정보를 레코드 변수에 저장

    SELECT empno,ename, job, deptno

    INTO emp_rec

    FROM emp

    WHERE ename = UPPER('SCOTT');

  -- 레코드 변수에 저장된 사원 정보를 출력

  DBMS_OUTPUT.PUT_LINE('사원번호 : '    ||  TO_CHAR(emp_rec.v_empno));

  DBMS_OUTPUT.PUT_LINE('이      름 : '    ||  emp_rec.v_ename);

  DBMS_OUTPUT.PUT_LINE('담당업무 : '    ||  emp_rec.v_job);

  DBMS_OUTPUT.PUT_LINE('부서번호 : '    ||  TO_CHAR(emp_rec.v_deptno));

END;

/*

    1. 상품 테이블 생성 

    (

        상품번호 문자(4) 기본키,

        상품명 문자(20),

        판매수량 숫자,

        가격 숫자

    )

*/

desc product;

create table product

(

    p_idx char (4) primary key ,

    p_name varchar2 (20) ,

    p_sum number ,

    p_price number 

);

COMMENT ON column product.p_idx is '상품번호 형식 : p001 .. p999 ( 999개 )';   

-- 행 전체 삭제

delete from product;

drop table product;

/*

    조건

    상품번호 형식 : P001 .. P999 (999개)

*/

/*

    2. 상품번호는 자동증가컬럼으로 시퀀스가  입력

    조건 :1..999(999개)

*/

drop sequence product_p_idx_seq;

create sequence product_p_idx_seq

start with 1

maxvalue 999

increment by 1;

commit;

/*

    3. 임의의 4 개행  입력(상품번호 입력시 lpad() 적용)

    진라면, 신라면, 너구리, 비빔면

    (예) 

    insert into 상품

    values(concat('P',lpad(????),3,'0')),'진라면',5,100)

    p, 번호 1..999 -> 번호 왼쪽 자리는 0으로 채움

*/

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' )), '진라면', 5, 100);

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' ), '신라면', 10, 200);

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' )), '너구리', 15, 300);

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' )), '비빔면', 20, 400);

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' )), '짜파게티', 25, 500);

insert into product values(concat('P', LPAD(to_char( product_p_idx_seq.nextval ), 3, '0' )), '짬뽕', 30, 600);

commit;

select * from PRODUCT;

-- 4. 현재 총매출액(각 상품의 판매수량 * 가격의 합)을 구하여 p_view 뷰로 정의

create or replace view p_view

as

select sum(p_sum * p_price) as "총매출액"

from product;

-- 여기서 별명을 지정해야함 안그러면 오류

룹 함수 SUM과 AVG를 사용해서 각 부서별 급여 총액과 평균을 구하는 뷰를 작성해 봅시다. 뷰를 작성하기 위해서 SELECT 절 다음에 SUM이란 그룹 함수를 사용하면 결과를 뷰의 특정 컬럼처럼 사용하는 것입니다. 따라서 물리적인 칼럼이 존재하지 않는 가상 칼럼이기에 칼럼 명도 상속 받을 수 없습니다. 뷰를 생성할 때 가상 칼럼을 사용하려면 사용자가 반드시 이름을 따로 설정해야 한다는 것을 명심하기 바랍니다. 

emample

CREATE VIEW VIEW_SAL

AS

SELECT DEPTNO, SUM(SAL) AS "SalSum", AVG(SAL) AS "SalAvg"

FROM EMP_COPY

GROUP BY DEPTNO;

-- 정의한 뷰를 축약시켜서 쿼리문 실행

select * from product_view;

select * from product_view2;

drop view product_view;

/*

5. 가장많이 팔린(판매수량 높은) 2개 상품의 상품명,판매수량을

뷰를 사용하여 출력(TOP 2)

*/

--인라인 뷰 : 인라인 뷰는 FROM 절에 사용되는 서브 쿼리를 의미한다. 

--[출처] [오라클/Oracle] 인라인뷰(inline view) / ROWNUM|작성자 JOKER

select ROWNUM, p_name, p_sum

from (select p_name, p_sum

from product

order by p_sum desc)

where rownum<=2;

-- 인라인뷰 재사용 : 수정될수도 있으므로 or replace 삽입

create or replace view view_qty

as

select p_name, p_sum

from product

order by p_sum desc;

--------------분리

select ROWNUM, p_name, p_sum

from view_qty

where rownum<=2;