I want to share to you my exploration in ORACLE database:
- ROWTYPE variable
- ALIAS in ORACLE >> SELECT COLUMN_NAME “COLUMN ALIAS” FROM TABLE
- LOOP clause for CURSOR
- SUBSTR clause (LEFT : Positive Value, RIGHT : Negative Value)
Below my ORACLE script:
SET SERVEROUTPUT ON; DECLARE V_FORMCODE VARCHAR(100); V_FORMTYPE VARCHAR(1); V_TABLENAME VARCHAR(100); V_SOURCEFLAGFIELD VARCHAR(100); V_SOURCEFLAGVALUE VARCHAR(100); V_FORMMANAGEMENT TBLM_FORMMANAGEMENT%ROWTYPE; V_CURSOR SYS_REFCURSOR; V_QUERY VARCHAR(400); BEGIN V_FORMCODE := 'F0001'; SELECT TABLESOURCE, SOURCEFLAGFIELD, SOURCEFLAGVALUE INTO V_TABLENAME, V_SOURCEFLAGFIELD, V_SOURCEFLAGVALUE FROM TBLM_FORM WHERE FORMCODE = V_FORMCODE; OPEN V_CURSOR FOR SELECT * FROM TBLM_FORMMANAGEMENT WHERE FORMCODE = V_FORMCODE AND SOURCEFIELD IS NOT NULL ORDER BY SEQ; LOOP FETCH V_CURSOR INTO V_FORMMANAGEMENT; EXIT WHEN V_CURSOR%NOTFOUND; V_QUERY := V_QUERY || ',' || V_FORMMANAGEMENT.SOURCEFIELD || ' AS "' || V_FORMMANAGEMENT.REPORTITEM || '"'; END LOOP; CLOSE V_CURSOR; V_QUERY := 'SELECT ' || SUBSTR(V_QUERY, -LENGTH(V_QUERY) + 1); V_QUERY := V_QUERY || ' FROM ' || V_TABLENAME; V_QUERY := V_QUERY || ' WHERE ' || V_SOURCEFLAGFIELD || ' = ''' || V_SOURCEFLAGVALUE || ''''; DBMS_OUTPUT.PUT_LINE(V_QUERY); OPEN V_CURSOR FOR V_QUERY; END; |
CMIIW .
Leave a Reply