FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

-- 기존 테이블을 수정하거나 새로 생성
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. 두 번째 테이블 생성: 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
    Object_Type VARCHAR2(20),             -- 'PROCEDURE' 또는 'FUNCTION'
    Table_Name VARCHAR2(100),              -- FK 참조: Tables_Info.Table_Name
    PRIMARY KEY (Procedure_Function_Name, Object_Type, Table_Name), -- 복합 PK로 관계 정의
    CONSTRAINT fk_proc_func 
        FOREIGN KEY (Procedure_Function_Name, Object_Type) 
        REFERENCES Procedures_Functions (Procedure_Function_Name, Object_Type) 
        ON DELETE CASCADE,
    CONSTRAINT fk_table 
        FOREIGN KEY (Table_Name) 
        REFERENCES Tables_Info (Table_Name) 
        ON DELETE CASCADE
);

-- 1. Procedures_Functions 테이블에 데이터 삽입
INSERT INTO Procedures_Functions (Procedure_Function_Name, Object_Type, Description)
VALUES ('sp_get_customer', 'PROCEDURE', 'Feetch customer information');
INSERT INTO Procedures_Functions (Procedure_Function_Name, Object_Type, Description)
VALUES ('fn_calculate_discount', 'FUNCTION', 'Calalculate discount percentage');
INSERT INTO Procedures_Functions (Procedure_Function_Name, Object_Type, Description)
VALUES ('sp_update_order', 'PROCEDURE', '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, Object_Type ,Table_Name)
VALUES ('sp_get_customer', 'PROCEDURE', 'Customers');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Object_Type ,Table_Name)
VALUES ('fn_calculate_discount', 'FUNCTION', 'Orders');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Object_Type ,Table_Name)
VALUES ('sp_update_order', 'PROCEDURE', 'Orders');
INSERT INTO ProcFunc_Table_Relationship (Procedure_Function_Name, Object_Type ,Table_Name)
VALUES ('sp_update_order', 'PROCEDURE',  'Products');

-- 1. 모든 프로시저와 함수에 연결된 테이블 조회
SELECT 
    p.Procedure_Function_Name AS Procedure_Function,
    p.Object_Type as Object_Type,
    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;

-- 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'
    and r.Object_Type = 'PROCEDURE';

-- 3. 특정 테이블과 연관된 프로시저/함수 조회
SELECT 
    r.Procedure_Function_Name,
    r.Object_Type,
    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	OBJECT_TYPE	PROCFUNC_DESCRIPTION	RELATED_TABLE	TABLE_DESCRIPTION
sp_get_customer	PROCEDURE	Feetch customer information	Customers	Customer details table
fn_calculate_discount	FUNCTION	Calalculate discount percentage	Orders	Order records table
sp_update_order	PROCEDURE	Update order details	Orders	Order records table
sp_update_order	PROCEDURE	Update order details	Products	Product inventory table

TABLE_NAME	DESCRIPTION
Orders	Order records table
Products	Product inventory table

PROCEDURE_FUNCTION_NAME	OBJECT_TYPE	DESCRIPTION
fn_calculate_discount	FUNCTION	Calalculate discount percentage
sp_update_order	PROCEDURE	Update order details