https://www.udemy.com/course/plsql-by-example/learn/lecture/3169122#overview

Procedures

• MODE

  • IN

    읽기 전용 값

  • OUT

    쓰기 전용 값?

  • IN OUT

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_mname IN VARCHAR2,
c_add1 IN VARCHAR2,
c_add2 IN VARCHAR2,
c_city IN VARCHAR2,
c_country IN VARCHAR2,
c_date_added IN DATE,
c_region IN VARCHAR2
)
AS
BEGIN
    INSERT INTO CUSTOMER(customer_id, first_name, last_name, middle_name, address_line1, address_line2, city, country, date_added, region)
    VALUES (c_id, c_fname, c_lname, c_mname, c_add1, c_add2, c_city, c_country, c_date_added, c_region);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('머시기');
END ADD_CUSTOMER;

-- 프로시져 호출 방법1
BEGIN
    ADD_CUSTOMER(17,'woo','bun','tu','도로명주소',NULL,'서울','한국',SYSDATE,'머시기'); 
END;

-- 프로시져 호출 방법2
BEGIN
    ADD_CUSTOMER
    (
    c_id => 17,
    c_fname => 'woo',
    c_lname => 'bun',
    c_mname => 'tu',
    c_add1 => '도로명주소',
    c_add2 => NULL,
    c_city => '서울',
    c_country => '한국',
    c_date_added => SYSDATE,
    c_region => '머시기'
    );
END;
-- 이렇게 인자값을 명시해주면 인자의 순서가 바뀌는 것은 문제되지 않는다

• OUT MODE 예시

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_mname IN VARCHAR2,
c_add1 IN VARCHAR2,
c_add2 IN VARCHAR2,
c_city IN VARCHAR2,
c_country IN VARCHAR2,
c_date_added IN DATE,
c_region IN VARCHAR2,
total_count OUT NUMBER
)
AS
BEGIN
    INSERT INTO CUSTOMER(customer_id, first_name, last_name, middle_name, address_line1, address_line2, city, country, date_added, region)
    VALUES (c_id, c_fname, c_lname, c_mname, c_add1, c_add2, c_city, c_country, c_date_added, c_region);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('머시기');
    
    SELECT COUNT(1) INTO total_count FROM CUSTOMER;
END ADD_CUSTOMER;

프로시져를 호출할 때 OUT MODE의 인자에 값을 전달하더라도 조용히 무시된다.

• IN OUT MODE 예시

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN OUT NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_mname IN VARCHAR2,
c_add1 IN VARCHAR2,
c_add2 IN VARCHAR2,
c_city IN VARCHAR2,
c_country IN VARCHAR2,
c_date_added IN DATE,
c_region IN VARCHAR2
)
AS
BEGIN
    INSERT INTO CUSTOMER(customer_id, first_name, last_name, middle_name, address_line1, address_line2, city, country, date_added, region)
    VALUES (c_id, c_fname, c_lname, c_mname, c_add1, c_add2, c_city, c_country, c_date_added, c_region);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('머시기');
    
    SELECT COUNT(1) INTO c_id FROM CUSTOMER;
END ADD_CUSTOMER;

DECLARE
    count NUMBER(10):=45;
BEGIN
    ADD_CUSTOMER
    (
    c_id => count,
    c_fname => 'woo',
    c_lname => 'bun',
    c_mname => 'tu',
    c_add1 => '도로명주소',
    c_add2 => NULL,
    c_city => '서울',
    c_country => '한국',
    c_date_added => SYSDATE,
    c_region => '머시기'
    );
    
    DBMS_OUTPUT.PUT_LINE('Total records: ' || tcount);
END;
-- customer_id는 45가 저장되고, DBMS_OUTPUT의 결과로는 CUSTOMER의 총 row 개수가 반환된다.

Function

프로시져와 달리 사용자 정의 함수는 return 값이 있다.

CREATE OR REPLACE FUNCTION find_salescount 
(
    p_sales_date IN DATE
) RETURN NUMBER
AS 
    num_of_sales number:=0;
BEGIN
    SELECT COUNT(*) INTO num_of_sales 
    FROM sales WHERE sales_date=p_sales_date;
    
    RETURN num_of_sales;
END find_salescount;

-- 호출 예시
SELECT find_salescount(to_date('01-jan-2015','dd-mon-yyyy')) FROM DUAL;