如何在Oracle数据库内格式化SQL或PL/SQL ？ |
您所在的位置：网站首页 › plsqlsql格式化 › 如何在Oracle数据库内格式化SQL或PL/SQL ？ |
|
在一些情况下，梳理复杂的SQL或PL/SQL代码逻辑时最好是格式化一下文本。 当然我们有Toad或PL/SQL Developer等其它第三方客户端程序作为辅助，但如果在数据库内容方面可以将SQL格式化则更佳。 当前数据库12c以后的软件安装介质中已经包含了Oracle/ SQLcl或Oracle SQL Developer，使用这两者任一自带工具中的JavaLib的dbtools-common.jar中的oracle.dbtools.app.Format函数就可以实现。 下面是实际安装步骤，环境为19.3 1、加载java 19.3中自带的发现无法使用： [oracle@oel7db1 lib]$ loadjava -u anbob/anbob@cdb1pdb1 /u01/app/oracle/product/19.2.0/db_1/sqldeveloper/sqldeveloper/lib/dbtools-common.jar [oracle@oel7db1 lib]$ sqlplus anbob/anbob@cdb1pdb1创建3#的Java函数时会提示下面的错误： Warning: Java created with compilation errors. SSQL> show error Errors for JAVA SOURCE "SQLFORMATTER": LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 Note: SQLFORMATTER uses or overrides a deprecated API. 0/0 ORA-29534: referenced object ANBOB.oracle/dbtools/app/Format could not be resolved 0/0 Note: Recompile with -Xlint:deprecation for details.下载新版的SQLcl: https://www.oracle.com/tools/downloads/sqlcl-downloads.html 使用目录里的 dbtools-common.jar 可以解决。 我这里把SQLcl放到了$ORACLE_HOME下: loadjava -u anbob/anbob@cdb1pdb1 /u01/app/oracle/product/19.2.0/db_1/sqlcl/lib/dbtools-common.jar2、JAVA授权 SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' ); SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' ); SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' ); SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.util.PropertyPermission', 'polyglot.js.nashorn-compat', 'write' )注意：如果不授权SYS：java.util.PropertyPermission 在后面执行时会提示无权限。 3、创建JAVA函数 CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS /* Imports */ import oracle.dbtools.app.Format; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.sql.BLOB; import oracle.sql.CLOB; import java.io.StringWriter; import java.io.PrintWriter; public class SQLFormatter { private static String getStackTrace(Exception e) { StringWriter writer = new StringWriter(); PrintWriter printWriter = new PrintWriter( writer ); e.printStackTrace( printWriter ); printWriter.flush(); return writer.toString(); } public static Format getFormat() { oracle.dbtools.app.Format format = new oracle.dbtools.app.Format(); format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged); format.options.put("kwCase", Format.Case.UPPER); format.options.put("idCase", Format.Case.NoCaseChange); // default: Format.Case.lower format.options.put("adjustCaseOnly", false); // default: false (set true to skip formatting) format.options.put("formatThreshold", 1); // default: 1 (disables deprecated post-processing logic) // Alignment format.options.put("alignTabColAliases", false); // default: true format.options.put("alignTypeDecl", true); format.options.put("alignNamedArgs", true); format.options.put("alignEquality", false); format.options.put("alignAssignments", true); // default: false format.options.put("alignRight", false); // default: false // Indentation format.options.put("identSpaces", 3); // default: 4 format.options.put("useTab", false); // Line Breaks format.options.put("breaksComma", Format.Breaks.Before); // default: Format.Breaks.After format.options.put("breaksProcArgs", false); format.options.put("breaksConcat", Format.Breaks.Before); format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before); format.options.put("breaksAfterSelect", true); // default: true format.options.put("commasPerLine", 1); // default: 5 format.options.put("breakOnSubqueries", true); format.options.put("breakAnsiiJoin", true); // default: false format.options.put("breakParenCondition", true); // default: false format.options.put("maxCharLineSize", 120); // default: 128 format.options.put("forceLinebreaksBeforeComment", false); // default: false format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep); // default: Format.BreaksX2.X2 format.options.put("flowControl", Format.FlowControl.IndentedActions); // White Space format.options.put("spaceAroundOperators", true); format.options.put("spaceAfterCommas", true); format.options.put("spaceAroundBrackets", Format.Space.Default); //format.options.put("formatProgramURL", "default"); return format; } public static String format(String str) { String res; try { //res = new Format().format(str); Format f = SQLFormatter.getFormat(); res = f.format(str); } catch (Exception e){ res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]"; } return res; } public static CLOB formatClob(oracle.sql.CLOB clob) throws SQLException { String str = clob.getSubString(1, (int) clob.length()); String res = SQLFormatter.format(str); Connection conn = DriverManager.getConnection("jdbc:default:connection:"); CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION); resClob.setString(1L, res); return resClob; } } /注：也可以从https://github.com/xtender/xt_scripts/blob/master/extra/SQLFormatte 下载最新版 4、创建PL/SQL PACKAGE create or replace package SQLFormatter as FUNCTION Format(str in varchar2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String'; FUNCTION FormatClob(str in clob) RETURN CLOB AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB'; end; /5、测试 SQL> select anbob.SQLFormatter.formatclob(to_clob('select 1 a from dual')) qtext from dual; QTEXT --------------------------------------------------------------------------------------- SELECT 1 a FROM dual SQL> select anbob.SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual; QTEXT ---------------------------------------------------------------------------------------- SELECT 1 a , 2 /*123 */ b , 3 c , d FROM dual , dual d2墨天轮原文链接：https://www.modb.pro/db/100274（复制链接至浏览器或点击文末阅读原文查看） |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |