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