主要针对Oracle的sql使用;
使用方法:
A.将以下代码copy成html;
B.将需要格式化的代码在pl/sql中进行美化;
<!DOCTYPE html>
<html>
<head>
<title>sqlFormmater</title>
</head>
<body>
<textarea name='sql' id='sql' rows="25" cols="190"></textarea>
<br />
<button type="button" οnclick="btnFormatClick();">格式化(5个一组sqlBF)</button>
<button type="button" οnclick="btnFormatOnlySqlClick();">格式化(5个一组纯sql)</button>
<button type="button" οnclick="btnFormatClickAppendSqlBF();">格式化(增加sqlBF)</button>
<button type="button" οnclick="btnClear();">清空</button>
<br>
<br>
<br>
<span id ='msg' name ='msg' style = "color:red;"></span>
<script type="text/javascript">
//清空
function btnClear(){
document.getElementById('sql').value = '';
document.getElementById('msg').innerHTML = '';
}
//5个一组格式化:纯sql
function btnFormatOnlySqlClick(){
fiveFormatSql('0');
}
//5个一组格式化:sqlBF
function btnFormatClick() {
fiveFormatSql('1');
}
//5个一组格式化
function fiveFormatSql(flag) {
var tempPrefix = "";
var tempEndfix = "";
var sqlF = "";
if("1" == flag){
var tempPrefix = "sqlBF.append(\" ";
var tempEndfix = " \");";
sqlF = "sqlBF.setLength(0);\r\n";
}
var tableName = "";
var sql = document.getElementById("sql").value;
if(sql == null || '' == sql){
return;
}
sql = trim(sql);
if (sql.indexOf("insert", 0) == 0) {//insert
//获取表名
var nameIndex = sql.indexOf("(", 0);
tableName = sql.substring(0, nameIndex);
tableName = tableName.replace("insert", "");
tableName = tableName.replace("into", "");
tableName = trim(tableName);
tableName = tableName.replace(" ", "");
//获取字段列表
var colIndex = sql.indexOf(")", nameIndex);
var colList = sql.substring(nameIndex + 1, colIndex);
colList = clearBlank(colList);
colList = clearBr(colList);
var colListArr = colList.split(",");
sqlF = sqlF + tempPrefix + "insert into "+tableName + tempEndfix + "\r\n";
sqlF = sqlF + tempPrefix + " (";
for(var i =0, n = colListArr.length;i<n;i++){
if( i == (n -1)){
sqlF = sqlF + colListArr[i] + ")" + tempEndfix + "\r\n";
continue;
}
sqlF = sqlF + colListArr[i] + ", ";
if((i+1)%5 == 0){
sqlF = sqlF + trim(tempEndfix) +"\r\n";
sqlF = sqlF + tempPrefix + " ";
}
}
} else if (sql.indexOf("select", 0) == 0) {//select
//字段
var colIndex = sql.indexOf("from", 0);
var colList = sql.substring(0, colIndex);
colList = colList.replace("select", "");
colList = clearBlank(colList);
colList = clearBr(colList);
var colListArr = colList.split(",");
sqlF = sqlF + tempPrefix + "select ";
for(var i =0, n = colListArr.length;i<n;i++){
if( i == (n -1)){
sqlF = sqlF + colListArr[i] + tempEndfix +"\r\n";
continue;
}
sqlF = sqlF + colListArr[i] + ", ";
if((i+1)%5 == 0){
sqlF = sqlF + trim(tempEndfix) + "\r\n";
sqlF = sqlF + tempPrefix + " ";
}
}
//表名
var nameIndex = sql.indexOf("where", colIndex);
if(nameIndex < 0){
nameIndex = sql.length;
}
tableName = sql.substring(colIndex, nameIndex);
tableName = tableName.replace("from", "");
tableName = trim(tableName);
tableName = clearBr(tableName);
var tableNameArr = tableName.split(",");
sqlF = sqlF + tempPrefix + " from ";
for(var i =0, n = tableNameArr.length;i<n;i++){
if( i == 0 && n == 1){
sqlF = sqlF + trim(tableNameArr[i])
+ tempEndfix +"\r\n";
continue;
}
if( i == 0 ){
sqlF = sqlF + trim(tableNameArr[i]) + ","
+ tempEndfix + "\r\n";
continue;
}
if( i == (n -1)){
sqlF = sqlF + tempPrefix + " "
+trim(tableNameArr[i]) + tempEndfix +"\r\n";
continue;
}
sqlF = sqlF + tempPrefix + " "+trim(tableNameArr[i])
+ "," +tempEndfix+ "\r\n";
}
sqlF = sqlF + appendSqlBF(sql.substring(nameIndex), "", tempPrefix, tempEndfix);
} else {
document.getElementById('msg').innerHTML = '暂不支持非insert,select相关sql的5个一组格式化';
return;
}
//insert-selectm模式
var selectIndex = sql.indexOf("select", 0);
if (selectIndex > 0) {
var sqlSeTemp = sql.substring(selectIndex);
sqlSeTemp = trim(sqlSeTemp);
//字段
var colIndex = sqlSeTemp.indexOf("from", 0);
var colList = sqlSeTemp.substring(0, colIndex);
colList = colList.replace("select", "");
colList = clearBlank(colList);
colList = clearBr(colList);
var colListArr = colList.split(",");
sqlF = sqlF + tempPrefix + " select ";
for(var i =0, n = colListArr.length;i<n;i++){
if( i == (n -1)){
sqlF = sqlF + colListArr[i] + tempEndfix +"\r\n";
continue;
}
sqlF = sqlF + colListArr[i] + ", ";
if((i+1)%5 == 0){
sqlF = sqlF + trim(tempEndfix) + "\r\n";
sqlF = sqlF + tempPrefix + " ";
}
}
//表名
var nameIndex = sqlSeTemp.indexOf("where", colIndex);
if(nameIndex < 0){
nameIndex = sqlSeTemp.length;
}
tableName = sqlSeTemp.substring(colIndex, nameIndex);
tableName = tableName.replace("from", "");
tableName = trim(tableName);
tableName = clearBr(tableName);
var tableNameArr = tableName.split(",");
sqlF = sqlF + tempPrefix + " from ";
for(var i =0, n = tableNameArr.length;i<n;i++){
if( i == 0 && n == 1){
sqlF = sqlF + trim(tableNameArr[i]) + tempEndfix +"\r\n";
continue;
}
if( i == 0 ){
sqlF = sqlF + trim(tableNameArr[i]) + ","+ tempEndfix +"\r\n";
continue;
}
if( i == (n -1)){
sqlF = sqlF + tempPrefix + " "
+trim(tableNameArr[i]) + tempEndfix +"\r\n";
continue;
}
sqlF = sqlF + tempPrefix + " "
+trim(tableNameArr[i]) + "," +tempEndfix+ "\r\n";
}
sqlF = sqlF
+ appendSqlBF(sqlSeTemp.substring(nameIndex), " ", tempPrefix, tempEndfix);
}
document.getElementById('sql').value = sqlF;
}
function trim(str){ //删除左右两端的空格
return str.replace(/(^\s*)|(\s*$)/g, "");
}
//去除空格
function clearBlank(str) {
return str.replace(/\s+/g, "");
}
//去除换行
function clearBr(key) {
key = key.replace(/<\/?.+?>/g,"");
key = key.replace(/[\r\n]/g, "");
return key;
}
//增加sqlBF
function btnFormatClickAppendSqlBF(){
var sql = document.getElementById("sql").value;
sql = trim(sql);
if(sql == null || '' == sql){
return;
}
var sqlF = "sqlBF.setLength(0);\r\n";
sqlF = sqlF + appendSqlBF(sql);
sqlF = sqlF + "\r\nthis.sql.setSql(sqlBF.toString());";
document.getElementById('sql').value = sqlF;
}
function appendSqlBF(pSql, pBlank, prefix, endfix){
var sql = trim(pSql);
if(sql == null || '' == sql){
return '';
}
if(null == prefix){
prefix = "sqlBF.append(\" ";
}
if(null == endfix){
endfix = " \");";
}
if (sql.indexOf("where", 0) == 0) {
if(pBlank == null){
pBlank = "";
}
sql = " " + pBlank + pSql;
}
var sqlLineArr = sql.split("\n");
var sqlF = "";
for(var i =0, n = sqlLineArr.length;i<n;i++){
var lineStr = clearBr(sqlLineArr[i]);
if(null == trim(lineStr) || '' == trim(lineStr)){
continue;
}
if(i == (n-1)){
lineStr = lineStr.replace(";", "");
}
sqlF = sqlF + prefix + lineStr+ endfix +"\r\n";
}
return sqlF;
}
</script>
</body>
C.将美化完成的sql复制到,打开的html中,点击格式化;将代码格式化为5个一组;
之后又在网络上找到一个比较好的代码格式化工具;支持多种的格式。