oracle sql格式化

1、实用的 SQL*Plus 格式化脚本

-- format.sql - SQL*Plus 格式化脚本
SET ECHO OFF
SET FEEDBACK ON
SET VERIFY OFF
SET HEADING ON
SET PAGESIZE 50
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET NULL "NULL"-- 常用列格式预设
COLUMN object_name FORMAT A30
COLUMN owner FORMAT A20
COLUMN created FORMAT A20
COLUMN status FORMAT A10
COLUMN bytes FORMAT 999,999,999
COLUMN tablespace_name FORMAT A20-- 员工表相关格式
COLUMN employee_id FORMAT 99999 HEADING "EMP_ID"
COLUMN first_name FORMAT A15 HEADING "FIRST_NAME"
COLUMN last_name FORMAT A15 HEADING "LAST_NAME"
COLUMN email FORMAT A25 HEADING "EMAIL"
COLUMN phone_number FORMAT A20 HEADING "PHONE"
COLUMN hire_date FORMAT A12 HEADING "HIRE_DATE"
COLUMN job_id FORMAT A15 HEADING "JOB_ID"
COLUMN salary FORMAT $999,999.99 HEADING "SALARY"
COLUMN commission_pct FORMAT 0.99 HEADING "COMM_PCT"
COLUMN manager_id FORMAT 99999 HEADING "MGR_ID"
COLUMN department_id FORMAT 999 HEADING "DEPT_ID"-- 部门表相关格式
COLUMN department_name FORMAT A20 HEADING "DEPT_NAME"
COLUMN location_id FORMAT 99999 HEADING "LOC_ID"PROMPT SQL*Plus 格式化已加载...

 2、列格式化

-- 设置列显示格式
COLUMN column_name FORMAT A20      -- 字符列，宽度20
COLUMN sal FORMAT 999,999.99       -- 数字列，千位分隔符
COLUMN hiredate FORMAT A12         -- 日期列-- 示例
COLUMN employee_name FORMAT A30
COLUMN salary FORMAT $999,999.99
COLUMN hire_date FORMAT A10-- 查看当前列格式
COLUMN-- 清除列格式
COLUMN column_name CLEAR

 3、页面设置

-- 设置每页行数
SET PAGESIZE 100-- 设置每行长度
SET LINESIZE 200-- 关闭分页显示
SET PAGESIZE 0-- 关闭列标题
SET HEADING OFF-- 关闭反馈信息
SET FEEDBACK OFF