关闭

《oracle pl/sql programming》 第一章 介绍 第二章 sql*plus

标签: sqloracledatabasecommandexceptiondocumentation
2260人阅读 评论(0) 收藏 举报
分类:

第一章 介绍

1. what's pl/sql
It is a highly structured, readable, and accessible language
高度結構化易讀的易理解的
 
It is a standard and portable language for Oracle development
標準的輕便的
 
It is an embedded language
嵌入式的運行是需要宿主環境
 
It is a high-performance, highly integrated database language
高性能與數據庫高度集成
 
2.歷史與版本
 
3.代碼樣例
3.1 基本結構
 1   DECLARE
 2      l_book_count INTEGER;
 3
 4   BEGIN
 5     SELECT COUNT(*)
 6        INTO l_book_count
 7        FROM books
 8       WHERE author LIKE '%FEUERSTEIN, STEVEN%';
 9   
10       DBMS_OUTPUT.PUT_LINE (
11          'Steven has written (or co-written) ' ||
12           l_book_count ||
13           ' books.');
14
15       -- Oh, and I changed my name, so...
16       UPDATE books
17          SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
18        WHERE author LIKE '%FEUERSTEIN, STEVEN%';
19    END;
 
 
3.2 procedure樣例
 1   CREATE OR REPLACE PROCEDURE pay_out_balance (
 2      account_id_in IN accounts.id%TYPE)
 3   IS
 4      l_balance_remaining NUMBER;
 5   BEGIN
 6      LOOP
 7         l_balance_remaining := account_balance (account_id_in);
 8
 9         IF l_balance_remaining < 1000
10         THEN
11            EXIT;
12         ELSE
13             apply_balance (account_id_in, l_balance_remaining);
14         END IF;
15      END LOOP;
16    END pay_out_balance;
 
3.3 帶異常處理的procedure結構
 1   CREATE OR REPLACE PROCEDURE check_account (
 2      account_id_in IN accounts.id%TYPE)
 3   IS
 4      l_balance_remaining       NUMBER;
 5      l_balance_below_minimum   EXCEPTION;
 6      l_account_name            accounts.name%TYPE;
 7   BEGIN
 8      SELECT name
 9        INTO l_account_name
10        FROM accounts
11       WHERE id = account_id_in;
12
13      l_balance_remaining := account_balance (account_id_in);
14
15      DBMS_OUTPUT.put_line (
16         'Balance for ' || l_account_name ||
17          ' = ' || l_balance_remaining);
18
19      IF l_balance_remaining < 1000
20      THEN
21         RAISE l_balance_below_minimum;
22      END IF;
23
24   EXCEPTION
25      WHEN NO_DATA_FOUND
26      THEN
27         -- No account found for this ID
28         log_error (...);
29
30      WHEN l_balance_below_minimum
31      THEN
32         log_error (...);
33         RAISE;
34   END;
 
4.學習資源
 
5.一些建議
5.1 欲速則不達
在撰寫代碼之前構建測試用例與編寫測試腳本
建立清晰的應用程序編碼規則及需要程序員共同遵守的約定
使用逐步求精的步驟和方法來簡化復雜性
 
5.2 不恥下問
承認自己的無知
需求幫助
將問題和答案整理歸類
 
5.3 發揮你的激情和創造力不要輕易退縮
 
 

 第二章 pl*sql

1. 啟動sql*plus并連接數據庫
os> sqlplus username/password
 
os> sqlplus /nolog
sql> connect username/password@databaseinfo
 
2 運行代碼
sql> BEGIN
 2     DBMS_OUTPUT.PUT_LINE('Hey look, ma!');
 3 END;
 4 /    --斜杠/表示運行
此時無顯示。
sql>set serveroutput on
sql> BEGIN
 2     DBMS_OUTPUT.PUT_LINE('Hey look, ma!');
 3 END;
 4 /    --斜杠/表示運行
顯示結果:Hey look, ma!
以上begin ...endEXECUTE是等價的
sql>EXECUTE DBMS_OUTPUT.PUT_LINE('Hey look,ma!');
 
sql>@scriptfilename;
or
sql>start scriptfilename;
 
3 什么是當前路徑
當前路徑指的是進入sql*plus前所在的操作系統路徑
c:/> sqlplus username/password
sql>_
此時的當前路徑為 c:/
 
4. define variable
相當于c語言里的宏與變量
SQL> DEFINE x = "the answer is 42"
SQL> VARIABLE x VARCHAR2(10)
    SQL> BEGIN
      2     :x := 'hullo';
      3 END;
      4 /
SQL> SELECT :x, '&x' FROM DUAL;
返回結果如下
    :X                               'THEANSWERIS42'
    -------------------------------- ----------------
    hullo                            the answer is 42
 
5.將輸出結果保存到文件
SQL> SPOOL report
SQL> @run_report
......
SQL> SPOOL OFF 
SQL> SPOOL report.txt
 
6.退出sql*plus
sql>disconnect;
sql>exit;
 
7.編輯腳本
sql> edit mySQLScript.pkg --調用默認的編輯器進行編輯
sql> DEFINE _EDITOR = c:/windows/notepad.exe --設置默認的編輯器
 
8.在啟動時加載自定義環境
sql*plus在啟動時若發現存在$ORACLE_HOME/qlplus/admin/glogin.sql 就會自動運行它。
然后如果在當前目錄下發現login.sql,則自動運行它。
因此可以在這兩個文件中加入自定義sql*plus環境的代碼
    REM Number of lines of SELECT statement output before reprinting headers
    SET PAGESIZE 999
 
    REM Width of displayed page, expressed in characters
    SET LINESIZE 132
 
    REM Enable display of DBMS_OUTPUT messages. Use 1000000 rather than
    REM "UNLIMITED" for databases earlier than Oracle Database 10g Release 2
    SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
 
    REM Change default to "vi improved" editor
    DEFINE _EDITOR = /usr/local/bin/vim
 
    REM Format misc columns commonly retrieved from data dictionary
    COLUMN segment_name FORMAT A30 WORD_WRAP
    COLUMN object_name FORMAT A30 WORD_WRAP
 
    REM set the prompt (works in SQL*Plus from Oracle9i Database or later)
    SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
:REM表示注釋。
 
9. sql*plus的錯誤處理
sql*plus執行代碼時時遇到錯誤時的默認處理方式是返回錯誤消息并繼續處理后續代碼。
如果希望sql*plus遇到錯誤是終止執行則執行如下代碼
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
如果還希望回滾事務則執行如下代碼
SQL> WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK
 
10. sql*plus的愛與恨
love:
With SQL*Plus , you can run "batch" programs, supplying application-specific arguments on the sqlplus command line, and referring to them in the script using &1 (first argument), &2 (second argument), etc.
 
SQL*Plus provides complete and up-to-date support for all SQL and PL/SQL statements. This can be important when you're using features unique to Oracle. Third-party environments may not provide 100% coverage; for example, some have been slow to add support for Oracle's object types, which were introduced a number of years ago.
 
SQL*Plus runs on all of the same hardware and operating system platforms on which the Oracle server runs.
 
hate:
In console versions of SQL*Plus, the statement buffer is limited to the most recently used statement; SQL*Plus offers no further command history.
 
With SQL*Plus, there are no modern command-interpreter features such as automatic completion of keywords or hints about which database objects are available while typing in a statement.
 
Online help consists of minimal documentation of the SQL*Plus command set. (Use HELP command to get help on a specific command.)
 
There is no ability to change the current directory once you've started SQL*Plus. This can be annoying when opening or saving scripts if you don't like typing full pathnames. If you discover that you're in an inconvenient directory, you have to quit SQL*Plus, change directories, and restart SQL*Plus.
 
Unless I break down and use what I consider the dangerous SQLPATH feature, SQL*Plus looks only in the startup directory for login.sql; it would be better if it would fall back to look in my home directory for the startup script.
 
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:233011次
    • 积分:3700
    • 等级:
    • 排名:第9320名
    • 原创:102篇
    • 转载:4篇
    • 译文:0篇
    • 评论:36条
    最新评论