[Delphi] Oracle DB Column to Delphi Class

    델파이에서 클래스를 만들어서 사용할 때 데이터베이스의 테이블 컬럼들을 그대로 델파이의 클래스형태로 변환해 주는 쿼리.

    This is sort of translator to use Oracle database table columns for Delphi class object.


    --Oracle DB Column to Delphi Class

    /***********************************************************************

    * [ DB → Delphi Class 자동 생성 ]

    *

    * ex) ED_GB :arrow_lower_right:

    * FEdGb : string; { 출입국구분 }

    * procedure SetEdGb(const Value: string); { return delYn; }

    * property edGb : string read FEdGb write SetEdGb;

    * procedure THistObj.SetEdGb(const Value: string); begin FEdGb := Value; end;

    ***********************************************************************/

    DECLARE 

    P_OWNER     VARCHAR2(100); /* 사용자계정 */

    P_TABLE     VARCHAR2(100); /* 테이블명 */

    P_OBJECT    VARCHAR2(100); /* 객체명 */


    R_VAR       VARCHAR2(32000); /* 변수 */

    R_VAR_SET   VARCHAR2(32000); /* Setter 선언 */

    R_VAR_PROP  VARCHAR2(32000); /* property 선언 */

    R_SET       VARCHAR2(32000); /* set 메소드 */

    BEGIN 

    P_OWNER := 'ABCUSER';

    P_TABLE := 'H100';

    P_OBJECT := 'THistObj';


    R_VAR := '';

    R_VAR_SET := '';

    R_VAR_PROP := '';

    R_SET := '';


    FOR X IN ( 

        SELECT 

            LOWER(SUBSTR(TC.COLUMN_NAME,0,1)) || SUBSTR( REPLACE(INITCAP(TC.COLUMN_NAME), '_'), 2, LENGTH(REPLACE(INITCAP(TC.COLUMN_NAME), '_'))-1) AS COLUMN_NAME

            , TC.DATA_TYPE

            , COMMENTS

            , decode(DATA_TYPE, 'VARCHAR2', 'string', 'CHAR', 'string', 'DATE', 'TDateTime', 'NUMBER', 'Integer', 'BLOB', 'string', 'string') AS DELPHI_TYPE

        FROM ALL_TAB_COLUMNS TC, ALL_COL_COMMENTS CC

        WHERE TC.OWNER = CC.OWNER

        AND TC.TABLE_NAME = CC.TABLE_NAME

        AND TC.COLUMN_NAME = CC.COLUMN_NAME

        AND TC.OWNER = P_OWNER 

        AND TC.TABLE_NAME = P_TABLE 

        ORDER BY TC.column_id

    LOOP 

    R_VAR := R_VAR || LPAD(' ', 4) || RPAD('F' || UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2), 24, ' ') || ': ' || X.DELPHI_TYPE || '; { ' ||X.COMMENTS||' }' || CHR(13);

    R_VAR_SET := R_VAR_SET || LPAD(' ', 4) || 'procedure Set' || UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2) || '(const Value: ' || X.DELPHI_TYPE||');' || CHR(13);

    R_VAR_PROP := R_VAR_PROP || LPAD(' ', 4) || RPAD('property ' || X.COLUMN_NAME, 30, ' ') || ': ' || RPAD(X.DELPHI_TYPE, 16, ' ') || ' read ' || RPAD('F' ||UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2), 24, ' ') || ' write Set' ||UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2)||';' || CHR(13);

    R_SET := R_SET || 'procedure ' || P_OBJECT || '.Set' || UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2) || '(const Value: ' || X.DELPHI_TYPE||'); begin F' ||UPPER(SUBSTR(X.COLUMN_NAME, 1, 1))||SUBSTR(X.COLUMN_NAME, 2)||' := Value; end;'|| CHR(13);

    END LOOP;


    DBMS_OUTPUT.ENABLE(1000000);

    DBMS_OUTPUT.PUT_LINE('type');

    DBMS_OUTPUT.PUT_LINE('  ' || P_OBJECT || ' = class(TObject)');

    DBMS_OUTPUT.PUT_LINE('  private');

    DBMS_OUTPUT.PUT_LINE(R_VAR);

    DBMS_OUTPUT.PUT_LINE(R_VAR_SET);

    DBMS_OUTPUT.PUT_LINE('  public');

    DBMS_OUTPUT.PUT_LINE('  published');

    DBMS_OUTPUT.PUT_LINE(R_VAR_PROP);

    DBMS_OUTPUT.PUT_LINE('  end;'|| CHR(13));

    DBMS_OUTPUT.PUT_LINE('implementation'|| CHR(13)|| CHR(13));

    DBMS_OUTPUT.PUT_LINE('{ '|| P_OBJECT ||' }'|| CHR(13));

    DBMS_OUTPUT.PUT_LINE(R_SET);


    END;

    댓글