SQL編程實戰-使用SQL*Plus

使用SQL*Plus

1、查看表結構

SQL> desc  regions_copy

 Name                                     Null?    Type

 ------------------------------------------------- ----------------------------

 REGION_ID                                 NOT NULLNUMBER

 REGION_NAME                                       VARCHAR2(25)

2、編輯SQL語句

SQL> list

  1 CREATE OR REPLACE PROCEDURE update_product_price (

  2 p_product_id IN products.product_id%TYPE,

  3 p_factor IN NUMBER

  4  ) AS

  5 product_count INTEGER;

  6 BEGIN

  7  --count the number of products with the

  8  --supplied product_id (should be 1 if the product exists)

  9 SELECT COUNT(*)

 10  INTOproduct_count

 11  FROMproducts

 12 WHERE product_id = p_product_id;

 13

 14  --ifthe product exists (product_count =1) then

 15 --update that product's price

 16  IFproduct_count = 1 THEN

 17 UPDATE products

 18  SETprice = price * p_factor

 19 WHERE product_id = p_product_id ;

 20 COMMIT;

 21  ENDIF;

 22 EXCEPTION

 23   WHEN OTHERS THEN

 24*   ROLLBACK;

 25 END update_product_price；

SQL> l

  1* select dbms_metadata.get_ddl('TABLE','GEGIONS_COPY','HR') from dual

SQL>c/GEGIONS_COPY/REGIONS_COPY

  1* select dbms_metadata.get_ddl('TABLE','REGIONS_COPY','HR') from dual

SQL> LIST

  1* select dbms_metadata.get_ddl('TABLE','REGIONS_COPY','HR') from dual

（1）常用編輯命令        

A[PPEND] text        將text附加到當前行之后

C[HANGE] /old/new    將當前行的old替換為new

CL[EAR] BUFF[ER]       清除緩沖區中的所有行

DEL            刪除當前行

DEL x            刪除第x行

L[IST]            列出緩沖區中的所有行

L[IST] x            列出第x行

R[UN]            運行緩沖區中保存的語句

/            同上

x            將第x行作為當前行

SQL> l

  1 SELECT EMAIL

  2* FROM EMPLOYEES

SQL> l 1

  1* SELECT EMAIL

SQL>APPEND, SALARY

  1* SELECT EMAIL, SALARY

SQL> L

  1 SELECT EMAIL, SALARY

  2* FROM EMPLOYEES

SQL> RUN

  1 SELECT EMAIL, SALARY

  2* FROM EMPLOYEES

3、保存、檢索并運行文件

 SQL> select dbms_metadata.get_ddl('TABLE','REGIONS_COPY','HR') from dual

SQL>save  /s01/regions_copy_get_ddl.sql

SQL> get/s01/regions_copy_get_ddl.sql

  1* selectdbms_metadata.get_ddl('TABLE','REGIONS_COPY','HR') from dual

SAVE filename[{REPLACE|APPEND}]   將sqlplus緩沖區的內容保存到由filename所指定的文件中

GET filename    將filename所指定的文件的內容讀入sqlplus緩沖區

START filename |@ filename    運行文件中的內容，首先讀入緩沖區

EDIT        打開一個編輯器，編輯緩沖區內容，然后寫回緩沖區

         SPOOL filename

SPOOL  OFF將輸出的內容寫入文件中

SQL>EDIT

 在LINUX中默認為vi編輯器，在Windows中默認為Notepad編輯器。

修改默認編輯器

DEFINE  _EDITOR = 'editor'

DEFINE   _EDITOR = 'vi'

4、格式化列

COLUMN  {column|alias}[option]

指定要格式化的列名

指定要格式化的列的別名

選項

選項option

FOR[MAT] format    指定列的顯示格式，由format確定

HEA[DING] heading    列標題

JUS[TIFY]        列的輸出的對齊方式

[{left|center|right}]

WRA[PPEND]    將一個字符串的末尾換行顯示

WORD_WRAPPED    同上，保證單個單詞不能跨行

CLE[AR]   清除列的任何設置為默認

對于format

• 1、對于字符串，可以使用Ax，指定字符的寬度

• 2、對于數字，可以指定顯示格式

• 3、對于日期，指定顯示格式，例如MM-DD-YYYY

5、設置頁面大小

 SETPAGESIZE  100

頁面最大大小為50000

6、設置行數大小

SETLINESIZE  100

7、清除列格式

SQL> COLUMN product_id  CLEAR    --清除指定列

SQL> CLERA COLUMN  --重置列格式，還原默認格式

8、使用變量

使用變量

變量被創建以后，可以在SQL語句中代替實際值使用。

SQLPLUS中可以使用兩種基本的變量類型

1、臨時變量，只在使用它的SQL語句中有效，值不能保留

2、已定義變量的值會一直保留到被顯式的刪除

（1）臨時變量

& 使用&定義臨時變量，后面跟變量名。

臨時變量  &variable_name

--控制輸出行

SQL>  SET VERIFY OFF  --禁止顯示原行和新行的信息

SQL>  SETVERIFY  ON  

-- 修改變量定義字符

SQL> SET DEFINE '#'

SQL> SET DEFINE '&'

-- &&防止重復輸入

--  定義變量

在SQL語句中，可以在使用變量之前對變量進行定義。在同一個SQL語句中還可以多次使用這個變量。

已定義變量會一直保持到顯式的將其刪除、重定義或退出SQLPLUS為止。

使用DEFINE命令定義并查看變量

1. 使用ACCEPT命令定義并設置變量

ACCEPT命令用于等待用戶為變量輸入一個值。

ACCEPT命令既可以將現有的變量設置為一個新值，也可以定義一個新變量。

ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]

 type：變量類型，可以指定CHAR、NUMBER、DATE

默認為CHAR

 format：指定變量的格式

 prompt：提示文本，提示用戶輸入變量

 HIDE：隱藏為變量輸入的值，例如密碼信息，隱藏符號為*

9i以前顯示為***，10g中不顯示。

-- 刪除已經定義的變量。

SQL>undefine  definename;

SQL> UNDEFINE v_customer_id

SQL> UNDEFINE v_date

SQL> UNDEFINE v_password

SQL> UNDEFINE v_product_id

9、創建簡單的報表

在sqlplus中可以使用臨時變量或者已定義變量，這樣可以創建腳本來提示用戶輸入一些變量，然后使用這些變量來生成一些報表。

如果需要生成復雜的報表，需要使用oracle reports之類的軟件。

-- 腳本中使用accept

ACCEPT variable_name [type] [FORMAT format]  [PROMAT  prompt]  [HIDE]

[eg]

SQL> ACCEPT v_customer_id NUMBER FORMAT 99 PROMPT 'Customer id'

-- 添加頁眉和頁腳

TTITLE和BTITLE命令可以用來向報表中天劍頁眉和頁腳。

[eg]

TTITLE LEFT 'Run date:' _DATE CENTER 'Run by the ' SQL.USER '  user '

RIGHT 'Page: ' FORMAT  999  SQL.PNO  SKIP 2

Run date: 12-AUG-07             Run by the HR user                         Page:    1

-- 計算小計

break on子句可以讓sqlplus根據列值的范圍分隔輸出結果。

compute子句可以讓sqlplus計算一列的和

兩者可以結合使用

 BREAK ON product_type_id

 COMPUTE SUM  OF price ON product_type_id

 SET ECHO OFF

SET VERIFY OFF

SET PAGESIZE 50

SET LINESIZE 70

CLEAR COLUMNS

COLUMN price HEADING Price FORMAT $999.99

SELECT product_type_id,name,price

FROM products

ORDER BY product_type_id;

CLEAR COLUMNS

10、從SQL*PLUS獲取幫助

SQL> HELP

SQL> HELP INDEX

SQL> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW

11、自動生成SQL語句

現在想生成一個腳本，刪除數據庫中所有的表。

SQL> select 'DROP TABLE ' || table_name || ';'
  2  from user_tables;

'DROPTABLE'||TABLE_NAME||';'
------------------------------------------
DROP TABLE REGIONS;
DROP TABLE LOCATIONS;
DROP TABLE DEPARTMENTS;
DROP TABLE JOBS;
DROP TABLE EMPLOYEES;
DROP TABLE JOB_HISTORY;
DROP TABLE REGIONS_COPY;
DROP TABLE BINARY_TEST;
DROP TABLE COUNTRIES;

9 rows selected.