IT 개발,관리,연동,자동화

Maria DB customers 테이블을 위한 다양한 조건의 CRUD 프로시저 작성

_Blue_Sky_ 2024. 12. 28. 14:20
728x90
728x90


CREATE TABLE `customers` (
`del` enum('Y','N') NOT NULL DEFAULT 'N',
`customerNumber` int(11) NOT NULL,
`customerName` varchar(50) NOT NULL,
`contactLastName` varchar(50) NOT NULL,
`contactFirstName` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`salesRepEmployeeNumber` int(11) DEFAULT NULL,
`creditLimit` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`customerNumber`),
KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
)

일때

c.r.u.d 관련한 프로시저를 각각 만들어줘 다양한 조건으로

아래는 해당 테이블(customers)에 대해 CRUD(생성, 읽기, 갱신, 삭제) 작업을 수행할 수 있는 SQL 저장 프로시저들을 다양한 조건 처리로 작성한 예제입니다.

1. CREATE (INSERT)

특정 필드가 비워질 수 있으므로 기본값 처리를 추가합니다.

DELIMITER $$

CREATE PROCEDURE `createCustomer` (
    IN p_customerNumber INT,
    IN p_customerName VARCHAR(50),
    IN p_contactLastName VARCHAR(50),
    IN p_contactFirstName VARCHAR(50),
    IN p_phone VARCHAR(50),
    IN p_addressLine1 VARCHAR(50),
    IN p_addressLine2 VARCHAR(50),
    IN p_city VARCHAR(50),
    IN p_state VARCHAR(50),
    IN p_postalCode VARCHAR(15),
    IN p_country VARCHAR(50),
    IN p_salesRepEmployeeNumber INT,
    IN p_creditLimit DECIMAL(10, 2)
)
BEGIN
    INSERT INTO customers (
        customerNumber, customerName, contactLastName,
        contactFirstName, phone, addressLine1, addressLine2,
        city, state, postalCode, country, salesRepEmployeeNumber, creditLimit
    ) VALUES (
        p_customerNumber, p_customerName, p_contactLastName,
        p_contactFirstName, p_phone, p_addressLine1, p_addressLine2,
        p_city, p_state, p_postalCode, p_country, p_salesRepEmployeeNumber, p_creditLimit
    );
END $$

DELIMITER ;

2. READ (SELECT)

조건에 따라 고객 데이터를 조회하는 프로시저입니다.

  • 전체 조회
  • 특정 고객번호로 조회
  • 특정 국가별 조회
DELIMITER $$

CREATE PROCEDURE `readCustomers` (
    IN p_filterType VARCHAR(10), -- 'ALL', 'BY_ID', 'BY_COUNTRY'
    IN p_filterValue VARCHAR(50) -- 예: 고객번호 또는 국가 이름
)
BEGIN
    IF p_filterType = 'ALL' THEN
        SELECT * FROM customers WHERE del = 'N';
    ELSEIF p_filterType = 'BY_ID' THEN
        SELECT * FROM customers WHERE del = 'N' AND customerNumber = p_filterValue;
    ELSEIF p_filterType = 'BY_COUNTRY' THEN
        SELECT * FROM customers WHERE del = 'N' AND country = p_filterValue;
    END IF;
END $$

DELIMITER ;

3. UPDATE (UPDATE)

고객 데이터를 갱신하는 프로시저입니다.
갱신은 입력 필드가 NULL이 아니면 해당 필드만 업데이트하도록 설계하였습니다.

DELIMITER $$

CREATE PROCEDURE `updateCustomer` (
    IN p_customerNumber INT,
    IN p_customerName VARCHAR(50),
    IN p_contactLastName VARCHAR(50),
    IN p_contactFirstName VARCHAR(50),
    IN p_phone VARCHAR(50),
    IN p_addressLine1 VARCHAR(50),
    IN p_addressLine2 VARCHAR(50),
    IN p_city VARCHAR(50),
    IN p_state VARCHAR(50),
    IN p_postalCode VARCHAR(15),
    IN p_country VARCHAR(50),
    IN p_salesRepEmployeeNumber INT,
    IN p_creditLimit DECIMAL(10, 2)
)
BEGIN
    UPDATE customers
    SET 
        customerName = IFNULL(p_customerName, customerName),
        contactLastName = IFNULL(p_contactLastName, contactLastName),
        contactFirstName = IFNULL(p_contactFirstName, contactFirstName),
        phone = IFNULL(p_phone, phone),
        addressLine1 = IFNULL(p_addressLine1, addressLine1),
        addressLine2 = IFNULL(p_addressLine2, addressLine2),
        city = IFNULL(p_city, city),
        state = IFNULL(p_state, state),
        postalCode = IFNULL(p_postalCode, postalCode),
        country = IFNULL(p_country, country),
        salesRepEmployeeNumber = IFNULL(p_salesRepEmployeeNumber, salesRepEmployeeNumber),
        creditLimit = IFNULL(p_creditLimit, creditLimit)
    WHERE customerNumber = p_customerNumber;
END $$

DELIMITER ;

4. DELETE (LOGICAL DELETE)

고객 데이터를 삭제하기보다는 del 플래그를 'Y'로 설정합니다(논리적 삭제). 특정 고객번호로 삭제하거나 국가별로 삭제 가능합니다.

DELIMITER $$

CREATE PROCEDURE `deleteCustomer` (
    IN p_filterType VARCHAR(10), -- 'BY_ID', 'BY_COUNTRY'
    IN p_filterValue VARCHAR(50) -- 고객번호 또는 국가 이름
)
BEGIN
    IF p_filterType = 'BY_ID' THEN
        UPDATE customers
        SET del = 'Y'
        WHERE customerNumber = p_filterValue;
    ELSEIF p_filterType = 'BY_COUNTRY' THEN
        UPDATE customers
        SET del = 'Y'
        WHERE country = p_filterValue AND del = 'N';
    END IF;
END $$

DELIMITER ;

사용 예시

1. CREATE (INSERT)

CALL createCustomer(
    103, 'Classic Cars Shop', 'Doe', 'John', '555-456-7890',
    '123 Elm St', NULL, 'Los Angeles', 'CA', '90001',
    'USA', NULL, 50000.00
);

2. READ (SELECT)

  • 모든 고객 조회: CALL readCustomers('ALL', NULL);
  • 특정 고객번호 조회: CALL readCustomers('BY_ID', 103);
  • 특정 국가별 조회: CALL readCustomers('BY_COUNTRY', 'USA');

3. UPDATE (UPDATE)

CALL updateCustomer(
    103, 'Modern Cars Inc', 'Smith', NULL, NULL,
    '456 Birch St', NULL, 'San Diego', NULL, NULL,
    NULL, NULL, 75000.00
);

4. DELETE (LOGICAL DELETE)

  • 고객 삭제(고객번호 기준): CALL deleteCustomer('BY_ID', 103);
  • 특정 국가의 고객 삭제: CALL deleteCustomer('BY_COUNTRY', 'USA');

이 같은 프로시저는 유지보수를 용이하게 하며, 클라이언트 애플리케이션에서 데이터베이스 조작을 효율적으로 관리할 수 있게 도와줍니다.


CREATE PROCEDURE `createCustomer` (
IN p_customerNumber INT,
IN p_customerName VARCHAR(50),
IN p_contactLastName VARCHAR(50),
IN p_contactFirstName VARCHAR(50),
IN p_phone VARCHAR(50),
IN p_addressLine1 VARCHAR(50),
IN p_addressLine2 VARCHAR(50),
IN p_city VARCHAR(50),
IN p_state VARCHAR(50),
IN p_postalCode VARCHAR(15),
IN p_country VARCHAR(50),
IN p_salesRepEmployeeNumber INT,
IN p_creditLimit DECIMAL(10, 2)
)
BEGIN .....

에서 파라미터 타입을 오라클처럼 테이블에서 차용하여 정의하는 방법은 ?>

MySQL에서는 Oracle처럼 테이블의 컬럼 정의를 바로 차용해 파라미터 타입을 설정하는 방법을 기본적으로 지원하지 않습니다. SQL 표준에 따라 컬럼 타입을 차용하지 않고, 저장 프로시저의 각 파라미터에 대해 명시적으로 데이터 타입을 선언해야 합니다.

하지만 동적으로 테이블 스키마를 참고해 프로시저를 생성하는 방법이나 비슷한 기능을 사용할 수 있는 방법이 존재합니다. 아래에서 설명합니다.

  1. Oracle과 같은 방식은 기본적으로 제공되지 않음
    오라클에서는 %TYPE 또는 %ROWTYPE을 사용해 테이블의 컬럼 데이터 타입을 차용할 수 있습니다.
    예: 
    p_customerNumber customers.customerNumber%TYPE;

하지만 MySQL은 이러한 기능을 지원하지 않으므로, 개발자가 직접 데이터 타입을 파라미터 정의에 명시해야 합니다.

  1. MySQL에서 동적으로 데이터 타입 차용 흉내내기
    해결 방법
    MySQL에서 흔히 사용하는 접근법은 테이블 정의를 참조해 동적으로 SQL을 생성하는 것입니다. 아래는 그 방법 중 하나를 보여줍니다.

1) 테이블 정보 조회
MySQL에서 INFORMATION_SCHEMA.COLUMNS를 사용해 특정 테이블의 컬럼 데이터 타입을 참고할 수 있습니다.

SELECT COLUMN_TYPE  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'customerNumber';

위 쿼리는 customers 테이블의 customerNumber 컬럼 타입 (INT(11) 등)을 반환합니다.

2) 동적으로 프로시저 생성
MySQL에서 동적 SQL을 사용해 테이블의 컬럼 타입을 분석한 후, 해당 정보를 기반으로 프로시저를 생성하는 구현 방법입니다.

예제: 스크립트로 저장 프로시저 생성

DELIMITER $$

CREATE PROCEDURE create_customer_procedure()  
BEGIN  
DECLARE sql_query TEXT;


-- 프로시저 생성 스크립트 준비
SET sql_query = CONCAT(
    'CREATE PROCEDURE `createCustomer` (',
    '    IN p_customerNumber ', 
        (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'customerNumber'), ', ',
    '    IN p_customerName ', 
        (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'customerName'), ', ',
    '    IN p_contactLastName ',
        (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'contactLastName'), ', ',

    '    -- 추가 파라미터들...',
    '    IN p_creditLimit ', 
        (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'creditLimit'), 
    ') BEGIN ',
    '    INSERT INTO customers ',
    '    (customerNumber, customerName, contactLastName, creditLimit) ',
    '    VALUES (p_customerNumber, p_customerName, p_contactLastName, p_creditLimit);',
    'END'
);

-- 동적 프로시저 실행
PREPARE statement FROM sql_query;
EXECUTE statement;
DEALLOCATE PREPARE statement;

END$$

DELIMITER ;
3) 권장 방법: 프로시저를 수동 정의하는 대신 자동화 스크립트 출력
모든 파라미터의 데이터 타입을 테이블 정의로부터 한 번 조회한 뒤, 필요한 경우 프로시저의 스크립트를 생성하도록 하여 작업 자동화를 수행할 수 있습니다.

예제: 프로시저 정의에 필요한 데이터 타입을 조회

SELECT COLUMN_NAME, COLUMN_TYPE  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'customers';

결과:

COLUMN_NAME COLUMN_TYPE
customerNumber INT(11)
customerName VARCHAR(50)
contactLastName VARCHAR(50)
creditLimit DECIMAL(10,2)

이를 바탕으로 프로시저를 자동 생성하도록 스크립트를 정의할 수 있습니다.

  1. 결론 및 권고
    현재 MySQL에서는 오라클처럼 %TYPE 또는 %ROWTYPE 같은 기능을 기본적으로 제공하지 않습니다. 그러나:

매뉴얼하게 데이터 타입을 직접 정의해야 합니다.
이는 MySQL의 기본 설계 제한이기 때문입니다.
동적 SQL을 활용해 테이블 스키마를 기반으로 프로시저를 생성하는 시스템을 구축할 수 있습니다. 이를 통해 반복 작업을 자동화할 수 있습니다.
도구나 ORM(Object-Relational Mapping) 또는 관리 도구를 사용해 프로시저 생성을 보조하는 방식도 추천됩니다.

SELECT COLUMN_NAME, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customers';

을 with 문으로 해서 동적쿼리로 할수 있을것 같은데 ...

MySQL에서 WITH 절(공통 테이블 표현식, CTE)은 동적 SQL과 직접적으로는 호환되지 않습니다. 그러나, 동적 SQL을 작성하면서 WITH 절을 활용하려면 원하는 쿼리를 조합하는 방식으로 동적 SQL을 구축해야 합니다. 아래에 이를 구현하는 방법을 설명하겠습니다.

1. WITH 절과 동적 SQL의 조합 원리

WITH 절을 사용하면 반복적으로 사용되는 테이블이나 지엽적인 데이터를 구성할 수 있습니다. 하지만 WITH 절을 동적인 방식으로 조합하려면 동적 SQL(Dynamic SQL)을 사용하여 쿼리와 함께 WITH 절을 추가하는 문자열을 생성해야 합니다. MySQL에서는 PREPARE, EXECUTE를 통해 이를 수행할 수 있습니다.

2. 목표

  • INFORMATION_SCHEMA.COLUMNS에서 특정 테이블(customers) 정보를 가져오되, 동적 SQL과 WITH 절을 활용하여 유연하게 데이터를 다룰 수 있도록 구성합니다.

3. 예제: 동적 SQL과 WITH 절 활용

예제 1: WITH 절로 테이블 스키마를 조회하는 동적 쿼리

아래 예제에서는 INFORMATION_SCHEMA.COLUMNS에서 컬럼 정보를 가져오고, 결과를 동적인 방식으로 활용하는 방법을 보여줍니다.

DELIMITER $$

CREATE PROCEDURE `getDynamicCustomerColumns` (IN p_tableName VARCHAR(50))
BEGIN
    DECLARE sql_query TEXT;

    -- 동적 쿼리 생성
    SET sql_query = CONCAT(
        'WITH column_data AS ( ',
        '    SELECT COLUMN_NAME, COLUMN_TYPE ',
        '    FROM INFORMATION_SCHEMA.COLUMNS ',
        '    WHERE TABLE_NAME = "', p_tableName, '" ',
        ') ',
        'SELECT * FROM column_data;'
    );

    -- 쿼리 준비 및 실행
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

사용 예제:

CALL getDynamicCustomerColumns('customers');

출력 예제:

COLUMN_NAME COLUMN_TYPE
customerNumber INT(11)
customerName VARCHAR(50)
contactLastName VARCHAR(50)
creditLimit DECIMAL(10,2)

 

4. 응용: 동적인 프로시저 정의

이제 조회된 컬럼 정보를 동적으로 출력하거나 프로시저를 작성할 때 사용할 수도 있습니다.

예제: 동적으로 INSERT 프로시저 생성

아래는 조회된 컬럼 정보를 바탕으로 삽입 프로시저를 자동 생성하는 예제입니다.

저장 프로시저 작성

DELIMITER $$

CREATE PROCEDURE `generateInsertProcedure` (IN p_tableName VARCHAR(50))
BEGIN
    DECLARE sql_query TEXT;

    -- SELECT 쿼리로 컬럼 이름 가져오기
    SET sql_query = (
        SELECT GROUP_CONCAT(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = p_tableName AND COLUMN_NAME <> 'del'
    );

    -- INSERT 동적 SQL 생성
    SET @procedure_body = CONCAT(
        'CREATE PROCEDURE `insert_into_', p_tableName, '` (',
        (SELECT GROUP_CONCAT(CONCAT('IN p_', COLUMN_NAME, ' ', COLUMN_TYPE) SEPARATOR ', ')
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = p_tableName AND COLUMN_NAME <> 'del'),
        ') BEGIN ',
        'INSERT INTO ', p_tableName, ' (', sql_query, ') ',
        'VALUES (', REPLACE(sql_query, ',', ', p_'), '); ',
        'END;'
    );

    -- 출력 확인용
    SELECT @procedure_body;

    -- 실행 (주석 처리 가능)
    -- PREPARE stmt FROM @procedure_body;
    -- EXECUTE stmt;
    -- DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

사용 예제:

CALL generateInsertProcedure('customers');

출력:

CREATE PROCEDURE `insert_into_customers` (
    IN p_customerNumber INT(11), 
    IN p_customerName VARCHAR(50), 
    IN p_contactLastName VARCHAR(50),
    IN p_contactFirstName VARCHAR(50),
    ...
) BEGIN 
    INSERT INTO customers (customerNumber, customerName, contactLastName, contactFirstName, ...)
    VALUES (p_customerNumber, p_customerName, p_contactLastName, p_contactFirstName, ...); 
END;

5. 제약 사항

  • MySQL의 INFORMATION_SCHEMA로부터 가져오는 데이터는 해당 시점의 테이블 구조를 기반으로 합니다. 테이블이 변경되면 동적으로 해당 정보를 다시 조회해야 합니다.
  • 동적 SQL은 복잡하며 유지보수가 어렵기 때문에 반드시 로깅이나 테스트가 필요합니다.

결론

WITH 절을 동적 SQL과 함께 사용하는 것은 가능합니다. 위 예제처럼 동적 SQL로 WITH 절을 조합하여 테이블 정보를 조회하거나 동적으로 프로시저를 생성할 수 있습니다. 이를 활용하면 테이블의 변경사항에 따라 유연하게 프로시저를 생성하거나 관리할 수 있습니다.

728x90
728x90

'IT 개발,관리,연동,자동화' 카테고리의 다른 글

AI로 뉴스를 자동 수집하여 블로그 포스팅하기  (3) 2024.12.25
Fetch API와 AJAX(XMLHttpRequest)의 주요 차이점: 현대적인 웹 개발을 위한 비동기 통신의 선택  (0) 2024.12.14
Puppeteer,Scrapy, Cheerio 등 다양한 웹 크롤링 프레임워크를 비교  (0) 2024.12.13
DBeaver: 다양한 데이터베이스를 하나의 툴로 관리하는 강력한 도구  (0) 2024.12.08
Electron: 웹 기술로 데스크톱 애플리케이션 개발하기  (1) 2024.12.07

'IT 개발,관리,연동,자동화'의 다른글

  • 현재글Maria DB customers 테이블을 위한 다양한 조건의 CRUD 프로시저 작성

관련글

티스토리툴바