# 替换变量
SQL> DEFINE table_name = ‘ORDERS’
SQL> SELECT COUNT(*) FROM &table_name;
old 1: SELECT COUNT(*) FROM &table_name
new 1: SELECT COUNT(*) FROM ORDERS

COUNT(*)
———-
10000

# 双&符号变量（只提示一次）
SQL> SELECT * FROM &&table_name WHERE order_id = 1001;
Enter value for table_name: ORDERS
old 1: SELECT * FROM &&table_name WHERE order_id = 1001
new 1: SELECT * FROM ORDERS WHERE order_id = 1001

# 绑定变量
SQL> VARIABLE v_count NUMBER
SQL> EXEC :v_count := 0;

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) INTO :v_count FROM orders;

SQL> PRINT v_count

V_COUNT
———-
10000

# 使用ACCEPT命令
SQL> ACCEPT p_date DATE FORMAT ‘YYYY-MM-DD’ PROMPT ‘Enter date: ‘
Enter date: 2026-04-05

SQL> SELECT * FROM orders WHERE order_date = TO_DATE(‘&p_date’, ‘YYYY-MM-DD’);

# WHENEVER SQLERROR命令
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> WHENEVER SQLERROR EXIT FAILURE ROLLBACK

# WHENEVER OSERROR命令
SQL> WHENEVER OSERROR EXIT FAILURE

# 完整脚本示例
— fgedu_daily_stats.sql
— 每日统计信息收集脚本
— Author: FengGe
— Date: 2026-04-05

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT ON
SET SERVEROUTPUT ON

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE

DEFINE v_date = TO_CHAR(SYSDATE, ‘YYYY-MM-DD’)

SPOOL /logs/stats_&v_date..log

PROMPT Starting daily statistics collection…
PROMPT Date: &v_date

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => ‘FGEDU’,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
cascade => TRUE,
degree => 4
);
END;
/

PROMPT Statistics collection completed.

SPOOL OFF

EXIT SUCCESS

# COLUMN命令
SQL> COLUMN order_id FORMAT 999999 HEADING ‘Order ID’
SQL> COLUMN order_date FORMAT A12 HEADING ‘Order Date’
SQL> COLUMN customer_id FORMAT A10 HEADING ‘Customer’
SQL> COLUMN amount FORMAT $99,999.99 HEADING ‘Amount’

SQL> SELECT order_id, order_date, customer_id, amount
FROM orders
WHERE ROWNUM <= 5;

Order ID Order Date Customer Amount
——– ———— ———- ———-
1001 2026-04-01 C001 $1,000.00
1002 2026-04-02 C002 $2,000.00
1003 2026-04-03 C003 $1,500.00
1004 2026-04-04 C004 $3,000.00
1005 2026-04-05 C005 $2,500.00

# BREAK和COMPUTE命令
SQL> BREAK ON customer_id SKIP 1
SQL> COMPUTE SUM OF amount ON customer_id

SQL> SELECT customer_id, order_id, amount
FROM orders
ORDER BY customer_id, order_id;

CUSTOMER_ID ORDER_ID AMOUNT
———- ———- ———-
C001 1001 $1,000.00
1006 $1,500.00
1011 $2,000.00
********** ———-
sum $4,500.00

C002 1002 $2,000.00
1007 $2,500.00
********** ———-
sum $4,500.00

# TTITLE和BTITLE命令
SQL> TTITLE CENTER ‘Daily Sales Report’ SKIP 1 –
> LEFT ‘Date: ‘ _DATE SKIP 1 –
> RIGHT ‘Page: ‘ FORMAT 999 SQL.PNO SKIP 2

SQL> BTITLE CENTER ‘Confidential’ SKIP 1 –
> RIGHT ‘FGEDU Database Report’

# REPHEADER和REPFOOTER命令
SQL> REPHEADER PAGE LEFT ‘Sales Summary Report’
SQL> REPFOOTER PAGE RIGHT ‘End of Report’

# 完整报表脚本
— fgedu_fgfgfgsales_report.sql
SET PAGESIZE 60
SET LINESIZE 120
SET FEEDBACK OFF
SET TERMOUT OFF

COLUMN order_date FORMAT A12 HEADING ‘Order Date’
COLUMN customer_id FORMAT A10 HEADING ‘Customer’
COLUMN amount FORMAT $99,999.99 HEADING ‘Amount’

TTITLE CENTER ‘Daily Sales Report’ SKIP 1 –
LEFT ‘Report Date: ‘ _DATE SKIP 2

BTITLE CENTER ‘— End of Report —‘

SPOOL /reports/fgfgfgsales_report.txt

SELECT order_date, customer_id, SUM(amount) AS total_amount
FROM orders
WHERE order_date >= TRUNC(SYSDATE) – 7
GROUP BY order_date, customer_id
ORDER BY order_date, customer_id;

SPOOL OFF

TTITLE OFF
BTITLE OFF

PROMPT Report generated successfully.

# 生成HTML报表
SQL> SET MARKUP HTML ON SPOOL ON
SQL> SPOOL /reports/fgfgfgsales_report.html

SQL> SELECT * FROM orders WHERE ROWNUM <= 10;

SQL> SPOOL OFF
SQL> SET MARKUP HTML OFF

# 完整HTML报表脚本
— fgedu_html_report.sql
SET MARKUP HTML ON SPOOL ON HEAD “”

SET PAGESIZE 100
SET LINESIZE 200

SPOOL /reports/database_status.html

SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables
ORDER BY table_name;

SPOOL OFF

SET MARKUP HTML OFF