“ITPUB论坛代码语法显亮工具 v1.0”发布

Masterzhh2008 · 发表于 2008-11-19 19:28

学习

newkid · 发表于 2008-11-20 02:58

addm真是生猛啊！我给你提个建议，就是增加所谓的Q-quote的处理:
SELECT q'(-- '/*_%')' FROM DUAL;
另外我看到你用了一处goto end_find;
这里为什么不用EXIT呢？

addm · 发表于 2008-11-20 08:57

原帖由 newkid 于 2008-11-20 02:58 发表
addm真是生猛啊！我给你提个建议，就是增加所谓的Q-quote的处理:
SELECT q'(-- '/*_%')' FROM DUAL;
另外我看到你用了一处goto end_find;
这里为什么不用EXIT呢？

多谢newkid的宝贵建议。

这个Q-quote真不错。我以前总是用字符串中的两个单引号，表示一个字符串里的单引号。如果字符串里内嵌单引号，就要仔细看，有没有多一个，少一个。有Q-quote就方便多了。

应该用那个goto语句，应该换成EXIT，提高代码的结构化程度。当时太晚了，人又困，把代码调试通过，就没再仔细查看了。还是newkid细心啊。

lfree · 发表于 2008-11-21 08:31

vim 加转换html 就OK了。

lfree · 发表于 2008-11-21 08:32

/* Formatted on 2008/11/21 08:30 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE itpub
IS
PROCEDURE format_file_text (FILE VARCHAR2);
END itpub;
/

CREATE OR REPLACE PACKAGE BODY itpub
IS
TYPE t_word IS TABLE OF VARCHAR2 (30);

-- 判断word是否在集合word_set中存在
FUNCTION in_set (word VARCHAR2, word_set t_word)
   RETURN BOOLEAN
IS
   l_word VARCHAR2 (30);
BEGIN
   l_word := UPPER (word);

   FOR i IN word_set.FIRST .. word_set.LAST
   LOOP
      IF word_set (i) = l_word
      THEN
         RETURN TRUE;
      END IF;
   END LOOP;

   RETURN FALSE;
END;

FUNCTION is_keyword (word VARCHAR2)
   RETURN INTEGER
IS
   keywords t_word
      := t_word ('ABORT',
                  'ACCEPT',
                  'ACCESS',
                  'ADD',
                  'ALL',
                  'ALTER',
                  'AND',
                  'ANY',
                  'ARRAY',
                  'ARRAYLEN',
                  'AS',
                  'ASC',
                  'ASSERT',
                  'ASSIGN',
                  'AT',
                  'AUDIT',
                  'AUTHID',
                  'AUTHORIZATION',
                  'BASE_TABLE',
                  'BEGIN',
                  'BETWEEN',
                  'BODY',
                  'BULK',
                  'BY',
                  'CASE',
                  'CHAR_BASE',
                  'CHECK',
                  'CLOSE',
                  'CLUSTER',
                  'CLUSTERS',
                  'COLAUTH',
                  'COLLECT',
                  'COLUMN',
                  'COLUMNS',
                  'COMMENT',
                  'COMMIT',
                  'COMPRESS',
                  'CONNECT',
                  'CONSTANT',
                  'CRASH',
                  'CREATE',
                  'CURRENT',
                  'CURRVAL',
                  'CURSOR',
                  'DATABASE',
                  'DATA_BASE',
                  'DAY',
                  'DBA',
                  'DEBUGOFF',
                  'DEBUGON',
                  'DECIMAL',
                  'DECLARE',
                  'DEFAULT',
                  'DEFINITION',
                  'DELAY',
                  'DELETE',
                  'DELTA',
                  'DESC',
                  'DIGITS',
                  'DISPOSE',
                  'DISTINCT',
                  'DO',
                  'DROP',
                  'ELSIF',
                  'ELSE',
                  'END',
                  'ENTRY',
                  'EXCEPTION',
                  'EXCEPTION_INIT',
                  'EXCLUSIVE',
                  'EXISTS',
                  'EXIT',
                  'EXTENDS',
                  'FALSE',
                  'FETCH',
                  'FILE',
                  'FOR',
                  'FORALL',
                  'FORM',
                  'FROM',
                  'FUNCTION',
                  'GENERIC',
                  'GOTO',
                  'GRANT',
                  'GROUP',
                  'HAVING',
                  'HEAP',
                  'IDENTIFIED',
                  'IF',
                  'IMMEDIATE',
                  'IN',
                  'INCREMENT',
                  'INDEX',
                  'INDEXES',
                  'INDICATOR',
                  'INITIAL',
                  'INSERT',
                  'INTERFACE',
                  'INTERSECT',
                  'INTO',
                  'IS',
                  'ISOLATION',
                  'JAVA',
                  'LEVEL',
                  'LIKE',
                  'LIMITED',
                  'LOCK',
                  'LOOP',
                  'MAXEXTENTS',
                  'MINUS',
                  'MLSLABEL',
                  'MODE',
                  'MODIFY',
                  'NATURAL',
                  'NATURALN',
                  'NEW',
                  'NEXTVAL',
                  'NOAUDIT',
                  'NOCOMPRESS',
                  'NOCOPY',
                  'NOT',
                  'NOTFOUND',
                  'NOWAIT',
                  'NULL',
                  'NUMBER_BASE',
                  'OF',
                  'OFFLINE',
                  'OLD',
                  'ON',
                  'ONLINE',
                  'OPAQUE',
                  'OPEN',
                  'OPERATOR',
                  'OPTION',
                  'OR',
                  'ORDER',
                  'OUT',
                  'PACKAGE',
                  'PARTITION',
                  'PCTFREE',
                  'POSITIVE',
                  'POSITIVEN',
                  'PRAGMA',
                  'PRIOR',
                  'PRIVATE',
                  'PRIVILEGES',
                  'PROCEDURE',
                  'RAISE',
                  'PUBLIC',
                  'RANGE',
                  'RECORD',
                  'REF',
                  'RELEASE',
                  'RENAME',
                  'REPLACE',
                  'RESOURCE',
                  'RESTRICT_REFERENCES',
                  'RETURN',
                  'REVERSE',
                  'REVOKE',
                  'ROLLBACK',
                  'ROW',
                  'ROWLABEL',
                  'ROWNUM',
                  'ROWS',
                  'ROWTYPE',
                  'RUN',
                  'SAVEPOINT',
                  'SCHEMA',
                  'SEPARATE',
                  'SESSION',
                  'SET',
                  'SELECT',
                  'SHARE',
                  'SIZE',
                  'SPACE',
                  'SQL',
                  'SQLBUF',
                  'SQLCODE',
                  'SQLERRM',
                  'START',
                  'STATEMENT',
                  'SUBTYPE',
                  'TABAUTH',
                  'TABLES',
                  'TASK',
                  'TERMINATE',
                  'THEN',
                  'TO',
                  'SUCCESSFUL',
                  'SYNONYM',
                  'SYSDATE',
                  'TABLE',
                  'TRUE',
                  'TRIGGER',
                  'TYPE',
                  'UNION',
                  'UNIQUE',
                  'UPDATE',
                  'USE',
                  'VALIDATE',
                  'VALUES',
                  'VIEWS',
                  'VIEW',
                  'WHEN',
                  'WHENEVER',
                  'WHERE',
                  'WHILE',
                  'WITH',
                  'WORK',
                  'WRITE',
                  'XOR'
               );
   datatypes t_word
      := t_word ('BFILE',
                  'BINARY_INTEGER',
                  'BLOB',
                  'BOOLEAN',
                  'CHAR',
                  'CLOB',
                  'DATE',
                  'FLOAT',
                  'HOUR',
                  'INTEGER',
                  'INTERVAL',
                  'LONG',
                  'MINUTE',
                  'MONTH',
                  'NCLOB',
                  'NUMBER',
                  'OCIROWID',
                  'PLS_INTEGER',
                  'RAW',
                  'REAL',
                  'ROWID',
                  'SECOND',
                  'SMALLINT',
                  'TIMESTAMP',
                  'UROWID',
                  'VARCHAR',
                  'VARCHAR2',
                  'YEAR',
                  'ZONE'
               );
   FUNCTIONS t_word
      := t_word ('ABS',
                  'ACOS',
                  'ADD_MONTHS',
                  'APPENDCHILDXML',
                  'ASCII',
                  'ASCIISTR',

                  /* 因论坛字数限制，此处删去一些行
                  ... ...
                  ... ...
                  */
                  'XMLROOT',
                  'XMLSEQUENCE',
                  'XMLSERIALIZE',
                  'XMLTABLE',
                  'XMLTRANSFORM'
               );
BEGIN
   IF in_set (word, keywords)
   THEN
      RETURN 1;
   END IF;

   IF in_set (word, datatypes)
   THEN
      RETURN 2;
   END IF;

   IF in_set (word, FUNCTIONS)
   THEN
      RETURN 3;
   END IF;

   RETURN 0;
   NULL;
END;

FUNCTION process_line (str VARCHAR2, state IN OUT INTEGER)
   RETURN VARCHAR2
IS
   l_pos                         INTEGER       := 1;
   l_ocur                      INTEGER;
   l_ocur2                      INTEGER;
   l_str                         VARCHAR2 (30);
   l_len                         INTEGER;                -- length(str)
   l_start                      INTEGER;
   l_ret                         VARCHAR (32767);
   l_multi_comm_begin CONSTANT VARCHAR2 (30) := '';
   l_multi_comm_end    CONSTANT VARCHAR2 (30) := '';
   l_single_comm_begin CONSTANT VARCHAR2 (30) := '';
   l_single_comm_end    CONSTANT VARCHAR2 (30) := '';
   l_string_begin       CONSTANT VARCHAR2 (30) := '';
   l_string_end       CONSTANT VARCHAR2 (30) := '';
   l_keyword_begin    CONSTANT VARCHAR2 (30) := '';
   l_keywork_end       CONSTANT VARCHAR2 (30) := '';
   l_datatype_begin    CONSTANT VARCHAR2 (30) := '';
   l_datatype_end       CONSTANT VARCHAR2 (30) := '';
   l_function_begin    CONSTANT VARCHAR2 (30) := '';
   l_function_end       CONSTANT VARCHAR2 (30) := '';
BEGIN
   l_len := LENGTH (str);                                     -- 行的长度

   IF str IS NULL
   THEN
      RETURN NULL;
   END IF;

   IF state = 1
   THEN
      -- 接上次多行
      l_ocur := INSTR (str, '*/');

      IF l_ocur = 0
      THEN
         --dbms_output.put_line(str);
         RETURN str;
      --return 1; -- 仍为多行注释
      END IF;

      --dbms_output.put_line(substr(str, 1, l_ocur + 1)); -- 多行注释结束
      state := 0;
      l_ret := l_ret || SUBSTR (str, 1, l_ocur + 1) || l_multi_comm_end;
      l_pos := l_ocur + 2;
   END IF;

   WHILE l_pos <= l_len
   LOOP
      l_ocur := REGEXP_INSTR (str, '(/\*|--|''|[A-Za-z_1-9]+)', l_pos);

      IF l_ocur > 0
      THEN
         l_str := REGEXP_SUBSTR (str, '(/\*|--|''|[A-Za-z_1-9]+)', l_pos);

         IF l_ocur > l_pos
         THEN
            l_ret := l_ret || SUBSTR (str, l_pos, l_ocur - l_pos);
         END IF;

         l_pos := l_ocur + LENGTH (l_str);

         CASE l_str
            WHEN '/*'
            THEN
               -- 多行注释起始
               l_ocur := INSTR (str, '*/', l_pos);

               IF l_ocur = 0
               THEN
                  l_ret :=
                     l_ret || l_multi_comm_begin
                     || SUBSTR (str, l_pos - 2);
                  state := 1;                               -- 多行注释
                  RETURN l_ret;
               END IF;

               -- 多行注释，结束在一行内
               l_ret :=
                     l_ret
                  || l_multi_comm_begin
                  || SUBSTR (str, l_pos - 2, l_ocur - l_pos + 4)
                  || l_multi_comm_end;
               l_pos := l_ocur + 2;
            WHEN '--'
            THEN
               -- 单行注释起始

               -- 单行注释，输出到行尾
               l_ret :=
                     l_ret
                  || l_single_comm_begin
                  || SUBSTR (str, l_ocur)
                  || l_single_comm_end;
               --state := 0; -- 正常
               RETURN l_ret;
            WHEN ''''
            THEN
               -- 字符串起始
               l_start := l_ocur;
               l_ocur := INSTR (str, '''', l_start + 1);
               l_ocur2 := INSTR (str, '''''', l_start + 1);

               WHILE l_ocur > 0
               LOOP
                  IF l_ocur2 <> l_ocur
                  THEN
                     l_ret :=
                           l_ret
                        || l_string_begin
                        || SUBSTR (str, l_start, l_ocur - l_start + 1)
                        || l_string_end;
                     l_pos := l_ocur + 1;
                     GOTO end_find;
                  END IF;

                  l_ocur := INSTR (str, '''', l_ocur2 + 2);
                  l_ocur2 := INSTR (str, '''''', l_ocur2 + 2);
               END LOOP;

               <<end_find>>
               NULL;
            ELSE
               l_ocur := is_keyword (l_str);

               CASE l_ocur
                  WHEN 1
                  THEN
                     -- 关键字
                     l_ret :=
                        l_ret || l_keyword_begin || l_str || l_keywork_end;
                  WHEN 2
                  THEN
                     -- 数据类型
                     l_ret :=
                        l_ret || l_datatype_begin || l_str
                        || l_datatype_end;
                  WHEN 3
                  THEN
                     -- 函数
                     l_ret :=
                        l_ret || l_function_begin || l_str
                        || l_function_end;
                  ELSE
                     -- 非查找字符
                     l_ret := l_ret || l_str;
               END CASE;
         END CASE;
      ELSE
         --l_pos := l_len + 1;
         -- 未找到任何可能的关键字
         l_ret := l_ret || SUBSTR (str, l_pos);
         RETURN l_ret;
      END IF;
   END LOOP;

   -- state := 0; -- 正常
   RETURN l_ret;
END;

PROCEDURE format_file_text (FILE VARCHAR2)
IS
   v1                   VARCHAR2 (32767);
   v2                   VARCHAR2 (32767);
   f1                   UTL_FILE.file_type;
   f2                   UTL_FILE.file_type;
   l_state                INTEGER          := 0;
                                             -- 行状态：0 正常， 1 多行注释
   l_font_begin CONSTANT VARCHAR2 (30)    := '';
   l_font_end    CONSTANT VARCHAR2 (30)    := '';
BEGIN
   BEGIN
      f1 := UTL_FILE.fopen ('ITPUB', FILE, 'R', 1024);
      f2 :=
         UTL_FILE.fopen ('ITPUB',
                           FILE
                        || TO_CHAR (SYSDATE, '.yyyy-mm-dd_hh24_mi_ss')
                        || '.txt',
                        'W',
                        1024
                        );
      UTL_FILE.put_line (f2, l_font_begin);

      LOOP
         UTL_FILE.get_line (f1, v1);
         --c := c + 1;
         --dbms_output.put_line('line: ' || c || ', state: ' || l_state || v1);
         v2 := process_line (v1, l_state);             -- 依次处理每一行
         DBMS_OUTPUT.put_line (v2);
         UTL_FILE.put_line (f2, v2);
      END LOOP;
   EXCEPTION
      WHEN UTL_FILE.invalid_path
      THEN
         -- 异常，文件不存在
         BEGIN
            DBMS_OUTPUT.put_line ('EXCEPTION: INVALID_PATH');
         END;
      WHEN NO_DATA_FOUND
      THEN
         -- 异常，文件结束
         UTL_FILE.fclose (f1);
         UTL_FILE.put_line (f2, l_font_end);
         UTL_FILE.fclose (f2);
   END;
END;
BEGIN
NULL;
END;
/

[ 本帖最后由 lfree 于 2008-11-21 08:38 编辑 ]

〇〇 · 发表于 2008-11-21 09:13

用其他用户
SQL> ho mkdir c:\itpub

SQL> CREATE DIRECTORY itpub AS 'D:\itpub';

Directory created.

SQL> conn / as sysdba
Connected.
SQL> GRANT read,WRITE ON DIRECTORY itpub TO tt;

Grant succeeded.

编译出错
Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY ITPUB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
624/13 PL/SQL: Item ignored
624/13 PLS-00201: identifier 'UTL_FILE' must be declared
625/13 PL/SQL: Item ignored
625/13 PLS-00201: identifier 'UTL_FILE' must be declared
630/5 PL/SQL: Statement ignored
631/7 PL/SQL: Statement ignored
631/7 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
用sys用户编译通过但执行不成功
664 /

Package body created.

SQL> exec itpub.format_file_text('test.sql');
BEGIN itpub.format_file_text('test.sql'); END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "SYS.ITPUB", line 631
ORA-06512: at line 1

〇〇 · 发表于 2008-11-21 09:15

sys用户没有问题了
CREATE DIRECTORY itpub AS 'D:\itpub';
应该是
CREATE DIRECTORY itpub AS 'c:\itpub';

〇〇 · 发表于 2008-11-21 09:16

WITH t AS (
SELECT c.*,lvl
   ,SUBSTR(node_path,1,(lvl-1)*2) node_path2  ---- 这里假设所有数据是两位字符，如果不是则在构造 node_path 的时候用RPAD对齐
   ,SUBSTR(node_path,1,lvl*2)    node_path3
FROM (SELECT level AS node_level,node,node_path,ROWNUM as rn2
      FROM (SELECT MIN(rn) as RN,node,node_path
            FROM ( SELECT rn
                           ,DECODE(lvl,1,b.class1,2,b.class2,3,b.class3,name) as node
                           ,DECODE(lvl,1,b.class1
                                    ,2,b.class1||b.class2
                                    ,3,b.class1||b.class2||b.class3
                                    ,b.class1||b.class2||b.class3||name
                                 ) AS node_path
                     FROM (SELECT ROWNUM RN, a.*
                           FROM (SELECT *
                                    FROM treetest
                                 ORDER BY class1,class2 NULLS FIRST,class3 NULLS FIRST,name NULLS FIRST) a
                           ) b
                           ,(SELECT LEVEL AS lvl FROM DUAL CONNECT BY LEVEL<=4)
                     WHERE DECODE(lvl,1,b.class1,2,b.class2,3,b.class3,name) IS NOT NULL
                  )
            GROUP BY node_path,node
            )
   START WITH node_path = node
   CONNECT BY node_path = prior node_path||node
   ORDER SIBLINGS BY rn
   ) c
   ,(SELECT LEVEL AS lvl FROM DUAL CONNECT BY LEVEL<=4)
WHERE node_level >= lvl
)
SELECT node_level,node,REPLACE(SYS_CONNECT_BY_PATH(node_path4,','),',')||node as tree
FROM (SELECT t2.*, (CASE WHEN (SELECT COUNT(*) FROM t WHERE t.rn2>t2.rn2 AND NVL(t.node_path2,'*')=NVL(t2.node_path2,'*') AND t.node_path3<>t2.node_path3 AND ROWNUM=1)>0
                     THEN (CASE WHEN lvl=node_level THEN '|------' ELSE '|    ' END)
                     WHEN lvl=node_level
                     THEN 'L------'
                     ELSE '    '
                  END) as node_path4
      FROM t t2
   ) t3
WHERE CONNECT_BY_ISLEAF=1
START WITH lvl=1
CONNECT BY node_path=prior node_path AND lvl = PRIOR lvl+1
ORDER BY RN2
;

addm · 发表于 2008-11-21 10:43

原帖由 lfree 于 2008-11-21 08:32 发表
/* Formatted on 2008/11/21 08:30 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE itpub
IS
PROCEDURE format_file_text (FILE VARCHAR2);
END itpub;
/

lfree提供的这个 Formatter Plus工具不错。网上搜了一下，是Toad工具包里的一个。

addm · 发表于 2008-11-21 10:48

原帖由〇〇于 2008-11-21 09:13 发表
用其他用户
SQL> ho mkdir c:\itpub

SQL> CREATE DIRECTORY itpub AS 'D:\itpub';

Directory created.

SQL> conn / as sysdba
Connected.
SQL> GRANT read,WRITE ON DIRECTORY itpub TO tt;

Grant succeeded.

编译出错
Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY ITPUB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
624/13 PL/SQL: Item ignored
624/13 PLS-00201: identifier 'UTL_FILE' must be declared
625/13 PL/SQL: Item ignored
625/13 PLS-00201: identifier 'UTL_FILE' must be declared
630/5 PL/SQL: Statement ignored
631/7 PL/SQL: Statement ignored
631/7 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
用sys用户编译通过但执行不成功
664 /

Package body created.

SQL> exec itpub.format_file_text('test.sql');
BEGIN itpub.format_file_text('test.sql'); END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "SYS.ITPUB", line 631
ORA-06512: at line 1

PLS-00201: identifier 'UTL_FILE' must be declared

这个错误应该是没有授权给普通用户UTL_FILE包的执行权限的原因。
执行以下语句，给普通用户chench授权utl_file包的execute权限：

grant execute on utl_file to chench;

[精华] “ITPUB论坛代码语法显亮工具 v1.0”发布

浏览过的版块