샘플 테이블 만들기

  drop table products purge;

  create table products
  (id    number generated as identity primary key,
   prod_name  varchar2(30),
   prod_price number(10, 2));

CRUD procedure/function 만들기

INSERT

CREATE OR REPLACE PROCEDURE insert_product(p_prod_name VARCHAR2, p_prod_price NUMBER) IS
BEGIN
    INSERT INTO products (prod_name, prod_price) VALUES (p_prod_name, p_prod_price);
    COMMIT;
END insert_product;
/

UPDATE

CREATE OR REPLACE PROCEDURE update_product(p_id NUMBER, p_prod_name VARCHAR2, p_prod_price NUMBER) IS
BEGIN
    UPDATE products SET prod_name = p_prod_name, prod_price = p_prod_price WHERE id = p_id;
    COMMIT;
END update_product;
/

DELETE

CREATE OR REPLACE PROCEDURE delete_product(p_id NUMBER) IS
BEGIN
    DELETE FROM products WHERE id = p_id;
    COMMIT;
END delete_product;
/

price 조회

CREATE OR REPLACE FUNCTION get_product_price(p_id NUMBER) RETURN NUMBER IS
    v_price NUMBER;
BEGIN
    SELECT prod_price INTO v_price FROM products WHERE id = p_id;
    RETURN v_price;
END get_product_price;
/

Products - SYS_REFCURSOR 타입으로 리턴받기

CREATE OR REPLACE FUNCTION get_all_products RETURN SYS_REFCURSOR IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR SELECT * FROM products;
    RETURN v_cursor;
END get_all_products;
/

CRUD Package/PackageBody

Package body

• 패키지 바디에 위에서 생성한 프로시져와 함수를 넣어주고 위 프로시져/함수 등은 삭제한다.

CREATE OR REPLACE PACKAGE BODY products_pkg AS
  -- 가격이 음수가 되지 않도록 검증하는 프라이빗 함수
  FUNCTION validate_price(p_price NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_price >= 0;
  END validate_price;

  -- 신규 제품 추가
  PROCEDURE insert_product(p_prod_name VARCHAR2, p_prod_price NUMBER) IS
  BEGIN
    IF validate_price(p_prod_price) THEN
      INSERT INTO products (prod_name, prod_price) VALUES (p_prod_name, p_prod_price);
      COMMIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('가격은 음수가 될 수 없습니다.');
    END IF;
  END insert_product;

  -- 제품 정보 업데이트
  PROCEDURE update_product(p_id NUMBER, p_prod_name VARCHAR2, p_prod_price NUMBER) IS
  BEGIN
    IF validate_price(p_prod_price) THEN
      UPDATE products SET prod_name = p_prod_name, prod_price = p_prod_price WHERE id = p_id;
      COMMIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('가격은 음수가 될 수 없습니다.');
    END IF;
  END update_product;

  PROCEDURE delete_product(p_id NUMBER) IS
  BEGIN
    DELETE FROM products WHERE id = p_id;
    COMMIT;
  END delete_product;

  FUNCTION get_product_price(p_id NUMBER) RETURN NUMBER IS
    v_price NUMBER;
  BEGIN
    SELECT prod_price INTO v_price FROM products WHERE id = p_id;
    RETURN v_price;
  END get_product_price;

  FUNCTION get_all_products RETURN SYS_REFCURSOR IS
    v_cursor SYS_REFCURSOR;
  BEGIN
    OPEN v_cursor FOR SELECT * FROM products;
    RETURN v_cursor;
  END get_all_products;
END products_pkg;
/

Package

• 패키지 바디에 있는 것들은 private 으로 선언되어 있어 패키지 내부에서만 참조할 수 있다.
• 패키지를 생성하고 외부에서 사용할 함수들을 넣어 public으로 만들어준다.

CREATE OR REPLACE PACKAGE products_pkg AS
  PROCEDURE insert_product(p_prod_name VARCHAR2, p_prod_price NUMBER);
  PROCEDURE update_product(p_id NUMBER, p_prod_name VARCHAR2, p_prod_price NUMBER);
  PROCEDURE delete_product(p_id NUMBER);
  FUNCTION get_product_price(p_id NUMBER) RETURN NUMBER;
  FUNCTION get_all_products RETURN SYS_REFCURSOR;
END products_pkg;
/

Test

begin
    products_pkg.insert_product('안멋쟁이필통', 4000);
    products_pkg.insert_product('멋쟁이필통', 3000);
    products_pkg.insert_product('노트북', 2000000);
end;
select * from products;

• 결과