테이블과 프로시져 혹은 함수과의 관계를 표현하는 레이아웃

주어진 조건에 따라 Oracle Database에서 테이블을 설계하고 관계를 구성하는 SQL 스크립트를 작성해 보겠습니다.

요구사항 정리

1. 첫 번째 테이블:
   • 이름: Procedures_Functions
   • 컬럼:
     • Procedure_Function_Name (Primary Key)
     • Description (Comment)
2. 두 번째 테이블:
   • 이름: Tables_Info
   • 컬럼:
     • Table_Name (Primary Key)
     • Description (Comment)
3. 세 번째 테이블:
   • 이름: ProcFunc_Table_Relationship
   • 컬럼:
     • Procedure_Function_Name (Foreign Key, 참조: Procedures_Functions.Procedure_Function_Name)
     • Table_Name (Foreign Key, 참조: Tables_Info.Table_Name)

SQL 스크립트

-- 1. 첫 번째 테이블 생성: Procedures_Functions
CREATE TABLE Procedures_Functions (
    Procedure_Function_Name VARCHAR2(100) PRIMARY KEY, -- 프로시저/함수 이름 (PK)
    Description VARCHAR2(255)                          -- 설명
);

-- 2. 두 번째 테이블 생성: Tables_Info
CREATE TABLE Tables_Info (
    Table_Name VARCHAR2(100) PRIMARY KEY, -- 테이블 이름 (PK)
    Description VARCHAR2(255)            -- 설명
);

-- 3. 세 번째 테이블 생성: ProcFunc_Table_Relationship
CREATE TABLE ProcFunc_Table_Relationship (
    Procedure_Function_Name VARCHAR2(100), -- FK 참조: Procedures_Functions.Procedure_Function_Name
    Table_Name VARCHAR2(100),              -- FK 참조: Tables_Info.Table_Name
    PRIMARY KEY (Procedure_Function_Name, Table_Name), -- 복합 PK로 관계 정의
    CONSTRAINT fk_proc_func 
        FOREIGN KEY (Procedure_Function_Name) 
        REFERENCES Procedures_Functions (Procedure_Function_Name) 
        ON DELETE CASCADE,
    CONSTRAINT fk_table 
        FOREIGN KEY (Table_Name) 
        REFERENCES Tables_Info (Table_Name) 
        ON DELETE CASCADE
);

설명

1. 테이블 생성:
   • Procedures_Functions 테이블에는 프로시저와 함수 이름(Procedure_Function_Name)과 해당 설명(Description)이 저장됩니다.
   • Tables_Info 테이블에는 테이블 이름(Table_Name)과 해당 설명(Description)이 저장됩니다.
   • ProcFunc_Table_Relationship 테이블은 Procedures_Functions와 Tables_Info 간의 관계를 표현하며, 두 테이블을 참조하는 외래 키를 포함합니다.
2. 제약 조건:
   • ProcFunc_Table_Relationship 테이블의 Procedure_Function_Name과 Table_Name은 각각 Procedures_Functions와 Tables_Info를 참조합니다.
   • 외래 키 제약 조건에 ON DELETE CASCADE를 추가하여 참조된 데이터가 삭제되면 관계 데이터도 자동으로 삭제됩니다.
3. 확장성:
   • 이 설계는 다른 추가 정보(예: 관계의 상세 설명 등)를 확장할 수 있습니다.

728x90

다음은 작성된 테이블에 대한 샘플 데이터와 조회 쿼리를 제공하는 SQL 스크립트입니다.

---

샘플 데이터 삽입

-- 1. Procedures_Functions 테이블에 데이터 삽입
INSERT INTO Procedures_Functions (Procedure_Function_Name, Description)
VALUES ('sp_get_customer', 'Fetch customer information');
INSERT INTO Procedures_Functions (Procedure_Function_Name, Description)
VALUES ('fn_calculate_discount', 'Calculate discount percentage');
INSERT INTO Procedures_Functions (Procedure_Function_Name, Description)
VALUES ('sp_update_order', 'Update order details');

-- 2. Tables_Info 테이블에 데이터 삽입
INSERT INTO Tables_Info (Table_Name, Description)
VALUES ('Customers', 'Customer details table');
INSERT INTO Tables_Info (Table_Name, Description)
VALUES ('Orders', 'Order records table');
INSERT INTO Tables_Info (Table_Name, Description)
VALUES ('Products', 'Product inventory table');

-- 3. ProcFunc_Table_Relationship 테이블에 데이터 삽입
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Table_Name)
VALUES ('sp_get_customer', 'Customers');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Table_Name)
VALUES ('fn_calculate_discount', 'Orders');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Table_Name)
VALUES ('sp_update_order', 'Orders');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Table_Name)
VALUES ('sp_update_order', 'Products');

---

조회 쿼리

1. 모든 프로시저와 함수에 연결된 테이블 조회

SELECT 
    p.Procedure_Function_Name AS Procedure_Function,
    p.Description AS ProcFunc_Description,
    t.Table_Name AS Related_Table,
    t.Description AS Table_Description
FROM 
    ProcFunc_Table_Relationship r
JOIN 
    Procedures_Functions p ON r.Procedure_Function_Name = p.Procedure_Function_Name
JOIN 
    Tables_Info t ON r.Table_Name = t.Table_Name;

결과 예시:

Procedure_Function	ProcFunc_Description	Related_Table	Table_Description
sp_get_customer	Fetch customer information	Customers	Customer details table
fn_calculate_discount	Calculate discount percentage	Orders	Order records table
sp_update_order	Update order details	Orders	Order records table
sp_update_order	Update order details	Products	Product inventory table

---

2. 특정 프로시저/함수와 연관된 테이블 조회

SELECT 
    r.Table_Name,
    t.Description
FROM 
    ProcFunc_Table_Relationship r
JOIN 
    Tables_Info t ON r.Table_Name = t.Table_Name
WHERE 
    r.Procedure_Function_Name = 'sp_update_order';

결과 예시:

Table_Name	Description
Orders	Order records table
Products	Product inventory table

---

3. 특정 테이블과 연관된 프로시저/함수 조회

SELECT 
    r.Procedure_Function_Name,
    p.Description
FROM 
    ProcFunc_Table_Relationship r
JOIN 
    Procedures_Functions p ON r.Procedure_Function_Name = p.Procedure_Function_Name
WHERE 
    r.Table_Name = 'Orders';

결과 예시:

Procedure_Function	Description
fn_calculate_discount	Calculate discount percentage
sp_update_order	Update order details

---

데이터 설명

• 샘플 데이터는 고객(Customers), 주문(Orders), 상품(Products) 테이블과 이를 사용하는 프로시저 및 함수로 구성되었습니다.
• 조회 쿼리는 프로시저/함수와 테이블 간의 관계를 분석하고 특정 테이블 또는 프로시저와 관련된 데이터를 쉽게 확인할 수 있도록 설계되었습니다.

 

 

 

 

 

 새로 설계된 테이블과 쿼리는 다음과 같습니다.

---

1. 테이블 수정: Procedures_Functions

테이블 설계

Procedures_Functions 테이블에 Object_Type 필드를 추가하여, 함수인지 프로시저인지를 구분합니다.

-- 기존 테이블을 수정하거나 새로 생성
CREATE TABLE Procedures_Functions (
    Procedure_Function_Name VARCHAR2(100), -- 프로시저/함수 이름
    Object_Type VARCHAR2(20),             -- 'PROCEDURE' 또는 'FUNCTION'
    Description VARCHAR2(255),            -- 설명
    PRIMARY KEY (Procedure_Function_Name, Object_Type) -- 복합 PK
);

---

2. 프로시저/함수 리스트 조회 및 삽입

조회 쿼리

프로시저와 함수의 이름, 유형(Object_Type), 설명(Description)을 조회합니다.

SELECT 
    OBJECT_NAME AS Procedure_Function_Name,
    OBJECT_TYPE AS Object_Type,
    NVL(COMMENTS, 'No Comment') AS Description
FROM 
    USER_OBJECTS o
LEFT JOIN 
    USER_TAB_COMMENTS c ON o.OBJECT_NAME = c.TABLE_NAME
WHERE 
    o.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION');

삽입 쿼리

조회된 데이터를 Procedures_Functions 테이블에 삽입합니다.

INSERT INTO Procedures_Functions (Procedure_Function_Name, Object_Type, Description)
SELECT 
    OBJECT_NAME,
    OBJECT_TYPE,
    NVL(COMMENTS, 'No Comment')
FROM 
    USER_OBJECTS o
LEFT JOIN 
    USER_TAB_COMMENTS c ON o.OBJECT_NAME = c.TABLE_NAME
WHERE 
    o.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION');

---

3. Tables_Info 테이블 설계 및 데이터 삽입

테이블 설계

Tables_Info 테이블은 기존 설계대로 유지합니다.

CREATE TABLE Tables_Info (
    Table_Name VARCHAR2(100) PRIMARY KEY, -- 테이블 이름
    Description VARCHAR2(255)            -- 설명
);

조회 및 삽입 쿼리

테이블 이름과 설명을 조회하고 삽입합니다.

-- 조회
SELECT 
    TABLE_NAME AS Table_Name,
    NVL(COMMENTS, 'No Comment') AS Description
FROM 
    USER_TABLES t
LEFT JOIN 
    USER_TAB_COMMENTS c ON t.TABLE_NAME = c.TABLE_NAME;

-- 삽입
INSERT INTO Tables_Info (Table_Name, Description)
SELECT 
    TABLE_NAME,
    NVL(COMMENTS, 'No Comment')
FROM 
    USER_TABLES t
LEFT JOIN 
    USER_TAB_COMMENTS c ON t.TABLE_NAME = c.TABLE_NAME;

---

4. 데이터 확인

Procedures_Functions

SELECT * FROM Procedures_Functions;

예상 결과:

Procedure_Function_Name	Object_Type	Description
sp_get_customer	PROCEDURE	Fetch customer information
fn_calculate_discount	FUNCTION	Calculate discount
sp_update_order	PROCEDURE	Update order details

Tables_Info

SELECT * FROM Tables_Info;

예상 결과:

Table_Name	Description
Customers	Customer details table
Orders	Order records table
Products	Product inventory table

---

변경 요약

1. Procedures_Functions 테이블에 Object_Type 추가:
   • 프로시저와 함수를 구분하기 위해 PROCEDURE 또는 FUNCTION 값을 저장.
2. 삽입 쿼리 수정:
   • USER_OBJECTS의 OBJECT_TYPE 정보를 포함하도록 쿼리 업데이트.
3. Tables_Info는 기존 설계 유지:
   • 별도의 변경 없이 테이블 리스트와 설명을 저장. 

 

 

---

-- 실행 예 --

 

https://sqlfiddle.com/oracle/online-compiler?id=cb278c76-c533-47af-abef-9401cc4a825b

FREE AI-Enhanced Online Oracle Compiler - For learning & practice

 

 

---