如何将sql查询输出显示为HTML表格格式输出。我有 shell 脚本代码将输出显示为 html 表格格式。输入是从 sql 查询中获取的,但没有得到正确的输出。正在获取非结构化的标题和数据,这意味着它仅采用第一列值,其他显示逐一格式,不显示表格格式。任何人都可以帮助解决这个问题。提前致谢。这是我的代码:# SQL 查询从自定义表中检索数据
#!/bin/bash
output_file="output.html"
temp_file="temp.txt"
delimiter="|"
sql_query="SELECT 'REQUEST_ID' AS \"REQUEST_ID\",
'REQUESTED_BY' AS \"REQUEST_BY\",
'USER_NAME' AS \"USER_NAME\",
'REQUEST_DATE' AS \"REQUEST_DATE\",
'CON_PRG_NAME' AS \"CON_PRG_NAME\",
'OUTFILE_NAME' AS \"OUTFILE_NAME\",
'FILE_MOVED_STATUS' AS \"FILE_MOVED_STATUS\",
'CON_STATUS_CODE' AS \"CON_STATUS_CODE\",
'ORG_ID' AS \"ORG_ID\"
FROM DUAL
UNION ALL
SELECT TO_CHAR(request_id),
TO_CHAR(requested_by),
user_name,
TO_CHAR(request_date),
concurrent_program_name,
outfile_name,
file_moved_status,
status_code,
TO_CHAR(org_id)
FROM xx_concurrent_stg
WHERE concurrent_program_name = '$concurrent_program_name'
AND request_date BETWEEN TO_DATE('$from_date', 'DD-MON-YY') AND TO_DATE('$to_date', 'DD-MON-YY');"
# Run the SQL query and save the output to a temporary file
sqlplus -S apps/apps@EBSDB <<EOF > $temp_file
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET COLSEP "$delimiter"
SET TRIMSPOOL ON
$sql_query
EXIT;
EOF
# Generate the HTML table from the temporary file
awk -v delimiter="$delimiter" '
BEGIN {
print "<table border=1 cellspacing=2 cellpadding=2>"
FS = delimiter
}
NR == 1 {
print "<tr>"
for (i = 1; i <= NF; i++) {
printf "<th>%s</th>", $i
}
print "</tr>"
}
NR > 1 {
print "<tr>"
for (i = 1; i <= NF; i++) {
printf "<td>%s</td>", $i
}
print "</tr>"
}
END {
print "</table>"
}' $temp_file > $output_file