[새싹] 현대IT&E 231116 기록 - PL/SQL

최정윤·2023년 11월 16일

PL/SQL sql

새싹

목록 보기

23/67

개요

PL/SQL, the Oracle procedural extension of SQL, is a portable, high-performance transaction-processing language.

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/overview.html#GUID-2FBCFBBE-6B42-4DB8-83F3-55B63B75B1EB

~ Advantages of PL/SQL
~ Main Features of PL/SQL
~ Architecture of PL/SQL
PL/SQL = SQL(manipulating power) + 3GL(processing power)
Pascal -> Ada -> PL/SQL

https://en.wikipedia.org/wiki/Generational_list_of_programming_languages
```
         C, Java     Pascal, PL/SQL     Basic, PowerScript
```
할당연산자 : a = 100 a := 100 a = 100
비교연산자 : a == 100 a = 100 a = 100
PL/SQL은 Block Structured Language임 -> Anonymous(Unnamed) Block
-> Named Block : Procedure, Function, Package, Trigger, Object, ...

declare -- optional
선언부
begin -- mandatory
실행부
exception -- optional
예외처리부
end; -- mandatory
/ <- SQL*Plus에서 실행하라는 의미임

Structured

DDL: C, A, D, R, T, C
DML: I, I, D, M, Select
TCL: C, R, S
DCL: G, R

Query란?
query 란 단어의 뜻은 문의하다, 질문하다라는 뜻이라고 한다. 그리고 프로그래밍에서 이러한 문의는 요청, 즉 '데이터베이스에 정보를 요청하는 일'을 말한다.

Language

MQL
CQL
HQL
create table books
C, C++, JAVA, Python ...

PL/SQL

SQL(Manipulating Power) + 3GL(Processing Power)
- SQL + null + Java + MyBatis + JPA
- SQL + PL/SQL + Java + MyBatis + JPA
SQL의 DML문과 Query문, 절차형 언어 등을 사용하여 절차적 프로그래밍을 가능하게 한 강력한 트랜잭션 언어이다.
SQL은 데이터를 다루는데에 아주 강력하다.

교재

PL/SQL Tutorial : https://www.oracletutorial.com/plsql-tutorial/
Oracle PL/SQL Articles : https://oracle-base.com/articles/plsql/articles-plsql
2 Day Developer's Guide : https://docs.oracle.com/en/database/oracle/oracle-database/21/tdddg/two-day-developer-intro.html
Database PL/SQL Language Reference : https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/index.html
PL/SQL Packages and Types Reference : https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/index.html
Oracle Live SQL의 Code Library : https://livesql.oracle.com/

create table books
(id number,
name varchar2(30),
price number(10, 2));

무작정 따라하기

begin
  dbms_oytput.put_line
  System.out.println()
end;
\
set serveroutput on

begin
	for i in 1..10 loop
		dbms_output.put_line('Hello World!');
	end loop;
end;
\
begin
	null;
end;
\
set serveroutput on

begin
{
select salary
from employees
where employee_id = 101;

insert into t1
values (v_salary):
}
end;
\
declare
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  select ename, sal into v_ename, v_sal
  from emp
  where empno = 7788;

  dbms_output.put_line(v_ename||' '||v_sal);
end;
\
drop table t1 purge;

create table t1 (col1 number, col2 number);

declare
	v_employee_id employees.employee_id%type;
    v_salary employees.salary%type;
begin
	select employee_id, salary into v_employee_id, v_salary
    from employees
    where employee_id = 100;
    
    if v_salary < 5000 then
    	dbms_output.put_line('Are you kidding me?');
    else
    	insert into t1(col1, col2)
        values (v_employee_id, v_salary);
    end if;
    
    commit;
end;
\
# Simple Examples : Named Block으로 수정
create or replace procedure p1
is
begin
	dbms_output.put_line('Hello World');
    
end;
select * from user_objects
where object_type = 'PROCEDURE';
\
create or replace procedure p1(amu number)
is
begin
	for i in 1..amu loop
    	dbms_output.put_line('Hello World!');
    end loop;
end;
\
execute p1(3)
execute p1(100)
\
begin
	p1(141);
end;
\
# Popup 퀴즈
# 부서 번호를 입력하면 그 부서의 급여합이 출력되도록 프로시져를 만드세요
begin
get_employees_sum_sal_by_department_id(10)
end;
\
begin
get_employees_sum_sal_by_department_id(50)
end;
\
# 해답
create or replace procedure get_employees_sum_sal_by_department_id(
    p_department_id employees.employee_id%type
)
is
  v_sum_sal number;
begin
  select sum(salary) into v_sum_sal
  from employees
  where department_id = p_department_id;

  dbms_output.put_line(v_sum_sal);
end;
\
# subprogram = procedure 및 function
- procedure 예제
- function 예제

create or replace function tax(
	p_salary employees.salary%type
) return number
is
begin
	return p_salary * 0.013;
end;
/
select	employee_id,
		last_name, 
        upper(last_name) as upper_last_name, 
        salary, 
        tax(salary) as tax
from employees;
\
# Pop 퀴즈 2
다음 조건을 만족하는 tax 함수를 만들어서 사용하는 예제를 만드세요.
단 if 문 사용금지!!!

- salary가 10000 미만이면 0.013 세율 적용
- salary가 20000 미만이면 0.015 세율 적용
- salary가 20000 이상이면 0.02 세율 적용

select	employee_id,
		last_name,
        upper(last_name) as upper_last_name,
        salary,
        tax(salary) as tax
from 	employees;
\
# 해답 1
create or replace function tax(
	p_salary employees.salary%type
) return number
is
	v_tax number;
begin
	if p_salary < 1000 then
    	v_tax := p_salary * 0.013;
    elsif p_salary < 2000 then
    	v_tax := p_salary * 0.015;
    else 
    	v_tax := p_salary * 0.02;
    end if;
    
    return p_salary * 0.013;
end;

- salary가 10000 미만이면 0.013 세율 적용
- salary가 20000 미만이면 0.015 세율 적용
- salary가 20000 이상이면 0.02  세율 적용

해답 2.

create or replace function tax2(
  p_salary number
) return number
is
  v_tax number;
begin
  v_tax := case when p_salary < 10000 then p_salary * 0.013
                when p_salary < 20000 then p_salary * 0.015
                else                       p_salary * 0.02
           end;
  return v_tax;
end;
/

# 해답 3
create or replace function tax(
	p_salary number
) return number
is
  v_tax number;
begin
  select decode(trunc(p_salary/10000),	0, p_salary*0.013,
                                          1, p_salary*,0.015,
                                          p_salary*0.02)
      into v_tax
  from dual;
  return v_tax;
end;

# 해답 4
drop table t_tax_rate
(id			number generated as idetity,
lowest_sal	number,
highest_sal	number,
tax_rate	number);

insert into t_tax_rate(lowest_sal, highest_Sal, tax_rate) values (0, 10000, 0.013);
insert into t_tax_rate(lowest_sal, highest_Sal, tax_rate) values (10000, 20000, 0.013);
insert into t_tax_rate(lowest_sal, highest_Sal, tax_rate) values (20000, null, 0.02);

commit;

select * from t_tax_rate;

create or replace function tax(
	p_salary number
) return number
is
  v_tax_rate t_tax_rate.tax_rate%type;
begin
  select tax_rate 
  into v_tax_rate
  from t_tax_rate
  where p_salary  >= lowest_sal 
  and   p_salary  < nvl(highest_sal, 100000000);
 
  return p_salary * v_tax_rate;
end;
\ 
select * from t_tax_rate
where 10000 <= lowest_sal and 10000 < highest_sal;
\

# ChatGPT를 활용해 PL/SQL 패키지 만들기
  
  (1) 테이블 생성

  drop table books purge;

  create table books
  (id    number generated as identity primary key,
   name  varchar2(30),
   price number(10, 2));


  (2) 위 테이블에 CRUD하는 stand-alone subprogram(stored procedure 및 stored function) 만들어줘

  create or replace procedure insert_book (
    p_name  in varchar2,
    p_price in number)
  as
  begin
    insert into books (name, price)
    values (p_name, p_price);
  end insert_book;
  /

  create or replace function read_book_by_id (
    p_id in number
  )
    return sys_refcursor 
  as
    v_cursor sys_refcursor;
  begin
    open v_cursor for
      select name, price
      from books
      where id = p_id;

    return v_cursor;
  end read_book_by_id;
  /

  create or replace procedure update_book_by_id (
    p_id    in number,
    p_name  in varchar2, 
    p_price in number)
  as
  begin
    update books
    set name = p_name,
        price = p_price
    where id = p_id;
  end update_book_by_id;
  /

  create or replace procedure delete_book_by_id (
    p_id in number
  )
  as
  begin
    delete from books
    where id = p_id;
  end delete_book_by_id;
  /

    -------

  select * from books;

  begin
    insert_book('자바 입문', 10000);
    insert_book('데이터베이스 활용', 15000);
    commit;
  end;
  / 

  select * from books;

    -------

  declare
    v_cursor sys_refcursor;
    v_name   varchar2(30);
    v_price  number(10, 2);
  begin
    v_cursor := read_book_by_id(1);

    fetch v_cursor into v_name, v_price;
    dbms_output.put_line('Name: ' || v_name || ', Price: ' || v_price);

    close v_cursor;
  end;
  /

  -------

  select * from books;

  begin
    update_book_by_id(1, '자바 입문', 12000);
    commit;
  end;
  /

  select * from books;

  -------

  select * from books;

  begin
    delete_book_by_id(1);
    commit;
  end;
  /

  select * from books;


(3) 위 테이블에 CRUD하는 패키지 만들어줘
  create or replace package pack_books
  is
    procedure insert_book (
      p_name  in varchar2,
      p_price in number);

    function read_book_by_id (
      p_id in number
    )
      return sys_refcursor;

    procedure update_book_by_id (
      p_id    in number,
      p_name  in varchar2, 
      p_price in number);

    procedure delete_book_by_id (
      p_id in number
    );
  end pack_books;
  /

  create or replace package body pack_books
  is

    function price_check(
      p_price in number
    ) 
      return varchar2
    is
    begin
      if p_price >= 0 then
        return 'True';
      else
        return 'False';
      end if; 
    end;

    procedure insert_book (
      p_name  in varchar2,
      p_price in number)
    as 
    begin
      if price_check(p_price) = 'True' then
        insert into books (name, price)
        values (p_name, p_price);
      else
        dbms_output.put_line('가격은 0원 미만일 수 없습니다.');
      end if;
    end insert_book;
  
    function read_book_by_id (
      p_id in number
    )
      return sys_refcursor 
    as
      v_cursor sys_refcursor;
    begin
      open v_cursor for
        select name, price
        from books
        where id = p_id;

      return v_cursor;
    end read_book_by_id;
 
    procedure update_book_by_id (
      p_id    in number,
      p_name  in varchar2, 
      p_price in number)
    as
    begin
      update books
      set name = p_name,
          price = p_price
      where id = p_id;
    end update_book_by_id;

    procedure delete_book_by_id (
      p_id in number
    )
    as
    begin
      delete from books
      where id = p_id;
    end delete_book_by_id;

  end pack_books;
  /

  desc pack_books

    -------

  set serveroutput on

  truncate table books;

  exec pack_books.insert_book('자바 입문', 10000);
  exec pack_books.insert_book('데이터베이스 활용', -1000);

  select * from books;

  drop procedure insert_book;
  drop procedure update_book_by_id;
  drop function read_book_by_id;
  drop procedure delete_book_by_id ;

  select * from user_objects
  where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');
  
  select * from user_source
  where name = 'PACK_BOOKS';
  
  ===============jeongyun================
  
  CREATE OR REPLACE PACKAGE book_management_pkg AS
  -- Insert a new book
  PROCEDURE insert_book(
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  );

  -- Read book details by ID
  FUNCTION read_book_by_id(
    p_id IN NUMBER
  ) RETURN SYS_REFCURSOR;

  -- Update book details by ID
  PROCEDURE update_book_by_id(
    p_id    IN NUMBER,
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  );

  -- Delete a book by ID
  PROCEDURE delete_book_by_id(
    p_id IN NUMBER
  );

END book_management_pkg;
/

CREATE OR REPLACE PACKAGE BODY book_management_pkg AS
  -- Insert a new book
  PROCEDURE insert_book(
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  ) AS
  BEGIN
    INSERT INTO books (name, price)
    VALUES (p_name, p_price);
    COMMIT;
  END insert_book;

  -- Read book details by ID
  FUNCTION read_book_by_id(
    p_id IN NUMBER
  ) RETURN SYS_REFCURSOR AS
    v_cursor SYS_REFCURSOR;
  BEGIN
    OPEN v_cursor FOR
      SELECT name, price
      FROM books
      WHERE id = p_id;

    RETURN v_cursor;
  END read_book_by_id;

  -- Update book details by ID
  PROCEDURE update_book_by_id(
    p_id    IN NUMBER,
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  ) AS
  BEGIN
    UPDATE books
    SET name = p_name,
        price = p_price
    WHERE id = p_id;
    COMMIT;
  END update_book_by_id;

  -- Delete a book by ID
  PROCEDURE delete_book_by_id(
    p_id IN NUMBER
  ) AS
  BEGIN
    DELETE FROM books
    WHERE id = p_id;
    COMMIT;
  END delete_book_by_id;

END book_management_pkg;
/

-- Insert a new book
BEGIN
  book_management_pkg.insert_book('새로운 책', 20000);
END;


-- Read book details by ID
DECLARE
  v_cursor SYS_REFCURSOR;
  v_name   VARCHAR2(30);
  v_price  NUMBER(10, 2);
BEGIN
  v_cursor := book_management_pkg.read_book_by_id(1);

  FETCH v_cursor INTO v_name, v_price;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Price: ' || v_price);

  CLOSE v_cursor;
END;

-- Update book details by ID
BEGIN
  book_management_pkg.update_book_by_id(1, '업데이트된 책', 25000);
END;

-- Delete a book by ID
BEGIN
  book_management_pkg.delete_book_by_id(1);
END;

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

-- 패키지 삭제
DROP PACKAGE book_management_pkg;

-- 테이블 삭제
DROP TABLE books PURGE;

-- 테이블 생성
CREATE TABLE books (
  id    NUMBER GENERATED AS IDENTITY PRIMARY KEY,
  name  VARCHAR2(30),
  price NUMBER(10, 2)
);

-- 패키지 생성
CREATE OR REPLACE PACKAGE book_management_pkg AS
  -- Insert a new book
  PROCEDURE insert_book(
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  );

  -- Read book details by ID
  FUNCTION read_book_by_id(
    p_id IN NUMBER
  ) RETURN SYS_REFCURSOR;

  -- Update book details by ID
  PROCEDURE update_book_by_id(
    p_id    IN NUMBER,
    p_name  IN VARCHAR2,
    p_price IN NUMBER
  );

  -- Delete a book by ID
  PROCEDURE delete_book_by_id(
    p_id IN NUMBER
  );

END book_management_pkg;
/

-- 테이블 초기 데이터 삽입
BEGIN
  INSERT INTO books (name, price) VALUES ('자바 입문', 10000);
  INSERT INTO books (name, price) VALUES ('데이터베이스 활용', 15000);
  COMMIT;
END;
/

수업 내용

PLSQL 개요.txt
교재.txt
데이터가 움직이느냐 로직이 움직이느냐.txt
Anonymous Block 만들기.txt
Anonynous Block Named Block으로 수정.txt
Popup 퀴즈 1.txt
Popup 퀴즈 2.txt
Identity Columns : https://oracle-base.com/article
CRUD 예제.txt
CRUD 예제 만들기. txt
방형욱, gseducation@naver.com, 17:50까지 보내주세요
- 파일 첨부만 해서 보낼것
- 인사나 기타등이 있으면 0점임!

Data가 움직이느냐, Logic이 움직이느냐
Data가 움직이는 것은 어렵기 때문에 Logic이 갈 수 있을 때까지 가는 것이 좋다.

하둡 (Hadoop)

하나의 성능 좋은 컴퓨터를 이용하여 데이터를 처리하는 대신 적당한 성능의 범용 컴퓨터 여러 대를 클러스터화하고 큰 크기의 데이터를 클러스터에서 병렬로 동시에 처리하여 처리 속도를 높이는 것을 목적으로 하는 분산처리를 위한 오픈소스 프레임워크이다.
대용량의 데이터를 적은 비용으로 더 빠르게 분석할 수 있는 플랫폼이다.
하둡은 로직이 갈 수 있을때까지는 가는 프로그램이다.

https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1