[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;