(1)SEQNAME.NEXTVAL里面的值如何讀出來
可以直接在insert into test values(SEQNAME.NEXTVAL) 是可以用這樣：
SELECT tmpdata_seq.NEXTVAL
INTO id_temp
FROM DUAL; 然后可以用id_temp
(2)PLS-00103: 出現符號 ">"在需要下列之一時：
代碼如下：
IF (sum>0)
THEN
begin
INSERT INTO emesp.tp_sn_production_log
VALUES (r_serial_number, , id_temp);
EXIT;
end;
一直報sum>0 這是個很郁悶的問題 因為變量用了sum 所以不行，后改為i_sum>0
(3)oracle 語法
1. Oracle應用編輯方法概覽
答：1) Pro*C/C++/... : C語言和數據庫打交道的方法，比OCI更常用;
2) ODBC
3) OCI: C語言和數據庫打交道的方法，和ProC很相似，更底層，很少用;
4) SQLJ: 很新的一種用Java訪問Oracle數據庫的方法，會的人不多;
5) JDBC
6) PL/SQL: 存儲在數據內運行, 其他方法為在數據庫外對數據庫訪問;
2. PL/SQL
答：1) PL/SQL(Procedual language/SQL)是在標準SQL的基礎上增加了過程化處理的語言;
2) Oracle客戶端工具訪問Oracle服務器的操作語言;
3) Oracle對SQL的擴充;
4. PL/SQL的優缺點
答：優點：
1) 結構化模塊化編程，不是面向對象;
2) 良好的可移植性(不管Oracle運行在何種操作系統);
3) 良好的可維護性(編譯通過后存儲在數據庫里);
4) 提升系統性能;
第二章
PL/SQL程序結構
1. PL/SQL塊
答：1) 申明部分, DECLARE(不可少);
2) 執行部分, BEGIN...END;
3) 異常處理，EXCEPTION(可以沒有);
2. PL/SQL開發環境
答：可以運用任何純文本的編輯器編輯，例如：VI ；toad很好用
3. PL/SQL字符集
答：PL/SQL對大小寫不敏感
4. 標識符命名規則
答：1) 字母開頭;
2) 后跟任意的非空格字符、數字、貨幣符號、下劃線、或# ;
3) 最大長度為30個字符(八個字符左右最合適);
5. 變量聲明
答：語法
Var_name type [CONSTANT][NOT NULL][:=value];
注：1) 申明時可以有默認值也可以沒有;
2) 如有[CONSTANT][NOT NULL], 變量一定要有一個初始值;
3) 賦值語句為“:=”;
4) 變量可以認為是數據庫里一個字段;
5) 規定沒有初始化的變量為NULL;
第三章
1. 數據類型
答：1) 標量型：數字型、字符型、布爾型、日期型;
2) 組合型：RECORD(常用)、TABLE(常用)、VARRAY(較少用)
3) 參考型：REF CURSOR(游標)、REF object_type
4) LOB(Large Object)
2. %TYPE
答：變量具有與數據庫的表中某一字段相同的類型
例：v_FirstName studengts.first_name%TYPE;
3. RECORD類型
答：TYPE record_name IS RECORD( /*其中TYPE，IS，RECORD為關鍵字，record_name為變量名稱*/
field1 type [NOT NULL][:=expr1], /*每個等價的成員間用逗號分隔*/
field2 type [NOT NULL][:=expr2], /*如果一個字段限定NOT NULL，那么它必須擁有一個初始值*/
... /*所有沒有初始化的字段都會初始為NULL
fieldn type [NOT NULL][:=exprn]);
4. %ROWTYPE
答：返回一個基于數據庫定義的類型
DECLARE
v_StuRec Student%ROWTYPE; /*Student為表的名字*/
注：與3中定一個record相比，一步就完成，而3中定義分二步：a. 所有的成員變量都要申明; b. 實例化變量;
5. TABLE類型
答：TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;
例：DECLARE
TYPE t_StuTable IS TABLE OF Student%ROWTYPE INDEX BY BINARY_INTERGER;
v_Student t_StuTable;
BEGIN
SELECT * INTO v_Student(100) FROM Student WHERE id = 1001;
END;
注：1) 行的數目的限制由BINARY_INTEGER的范圍決定;
6. 變量的作用域和可見性
答：1) 執行塊里可以嵌入執行塊;
2) 里層執行塊的變量對外層不可見;
3) 里層執行塊對外層執行塊變量的修改會影響外層塊變量的值;
第四章
1. 條件語句
答：IF boolean_expression1 THEN
...
ELSIF boolean_expression2 THEN /*注意是ELSIF，而不是ELSEIF*/
... /*ELSE語句不是必須的，但END IF;是必須的*/
ELSE
...
END IF;
2. 循環語句
答：1) Loop
...
IF boolean_expr THEN /* */
EXIT; /* EXIT WHEN boolean_expr */
END IF; /* */
END LOOP;
2) WHILE boolean_expr LOOP
...
END LOOP;
3) FOR loop_counter IN [REVERSE] low_blound..high_bound LOOP
...
END LOOP;
注：a. 加上REVERSE 表示遞減，從結束邊界到起始邊界，遞減步長為一;
b. low_blound 起始邊界; high_bound 結束邊界;
3. GOTO語句
答：GOTO label_name;
1) 只能由內部塊跳往外部塊;
2) 設置標簽：<<label_name>>
3) 示例：
LOOP
...
IF D%ROWCOUNT = 50 THEN
GOTO l_close;
END IF;
...
END LOOP;
<<l_close>>;
...
4. NULL語句
答：在語句塊中加空語句，用于補充語句的完整性。示例：
IF boolean_expr THEN
...
ELSE
NULL;
END IF;
5. SQL in PL/SQL
答：1) 只有DML SQL可以直接在PL/SQL中使用;
第五章
1. 游標(CURSOR)
答：1) 作用：用于提取多行數據集;
2) 聲明：a. 普通申明：DELCARE CURSOR CURSOR_NAME IS select_statement /* CURSOR的內容必須是一條查詢語句*/
b. 帶參數申明：DELCARE CURSOR c_stu(p_id student.ID%TYPE) SELECT * FROM student WHERE ID = p_id;
3) 打開游標：OPEN Cursor_name; /*相當于執行select語句，且把執行結果存入CURSOR;
4) 從游標中取數：a. FETCH cursor_name INTO var1, var2, ...; /*變量的數量、類型、順序要和Table中字段一致;*/
b. FETCH cursor_name INTO record_var;
注：將值從CURSOR取出放入變量中，每FETCH一次取一條記錄;
5) 關閉游標: CLOSE Cursor_name;
注：a. 游標使用后應該關閉;
b. 關閉后的游標不能FETCH和再次CLOSE;
c. 關閉游標相當于將內存中CURSOR的內容清空;
2. 游標的屬性
答：1) %FOUND: 是否有值;
2) %NOTFOUND: 是否沒有值;
3) %ISOPEN: 是否是打開狀態;
4) %ROWCOUNT: CURSOR當前的記錄號;
3. 游標的FETCH循環
答：1) LOOP
FETCH cursor INTO ...
EXIT WHEN cursor%NOTFOUND; /*當cursor中沒記錄后退出*/
END LOOP;
2) WHILE cursor%FOUND LOOP
FETCH cursor INTO ...
END LOOP;
3) FOR var IN cursor LOOP
FETCH cursor INTO...
END LOOP;
第六章
1. 異常
答：DECLARE
...
e_TooManyStudents EXCEPTION; /* 申明異常 */
...
BEGIN
...
RAISE e_TooManyStudents; /* 觸發異常 */
...
EXCEPTION
WHEN e_TooManyStudents THEN /* 觸發異常 */
...
WHEN OTHERS THEN /* 處理所有其他異常 */
...
END;
2004-9-8 星期三 陰
PL/SQL數據庫編程(下)
1. 存儲過程(PROCEDURE)
答：創建過程：
CREATE [OR REPLACE] PROCEDURE proc_name
[(arg_name[{IN|OUT|IN OUT}]TYPE,
arg_name[{IN|OUT|IN OUT}]TYPE)]
{IS|AS}
procedure_body
1) IN: 表示該參數不能被賦值(只能位于等號右邊);
2) OUT:表示該參數只能被賦值(只能位于等號左邊);
3) IN OUT: 表示該類型既能被賦值也能傳值;
2. 存儲過程例子
答：CREATE OR REPLACE PROCEDURE ModeTest(
p_InParm IN NUMBER,
p_OutParm OUT NUMBER,
p_InOutParm IN OUT NUMBER)
IS
v_LocalVar NUMBER; /* 聲明部分 */
BEGIN
v_LocalVar:=p_InParm; /* 執行部分 */
p_OutParm:=7;
p_InOutParm:=7;
...
EXCEPTION
... /* 異常處理部分 */
END ModeTest;
3. 調用PROCEDURE的例子
答：1) 匿名塊可以調;
2) 其他PROCDEURE可以調用;
例：
DECLARE
v_var1 NUMBER;
BEGIN
ModeTest(12, v_var1, 10);
END;
注：此時v_var1等于7
4. 指定實參的模式
答：1) 位置標示法：調用時添入所有參數，實參與形參按順序一一對應;
2) 名字標示法：調用時給出形參名字，并給出實參
ModeTest(p_InParm=>12, p_OutParm=>v_var1, p_Inout=>10);
注：a. 兩種方法可以混用;
b. 混用時第一個參數必須通過位置來指定。
5. 函數(Function)與過程(Procedure)的區別
答：1) 過程調用本身是一個PL/SQL語句(可以在命令行中通過exec語句直接調用);
2) 函數調用是表達式的一部分;
6. 函數的聲明
答：CREATE [OR REPLACE] PROCEDURE proc_name
[(arg_name[{IN|OUT|IN OUT}]TYPE,
arg_name[{IN|OUT|IN OUT}]TYPE)]
RETURN TYPE
{IS|AS}
procedure_body
注：1) 沒有返回語句的函數將是一個錯誤;
7. 刪除過程與函數
答：DROP PROCEDURE proc_name;
DROP FUNCTION func_name;
第八章
1. 包
答：1) 包是可以將相關對象存儲在一起的PL/SQL的結構;
2) 包只能存儲在數據庫中，不能是本地的;
3) 包是一個帶有名字的聲明;
4) 相當于一個PL/SQL塊的聲明部分;
5) 在塊的聲明部分出現的任何東西都能出現在包中;
6) 包中可以包含過程、函數、游標與變量;
7) 可以從其他PL/SQL塊中引用包，包提供了可用于PL/SQL的全局變量。
8) 包有包頭和包主體，如包頭中沒有任何函數與過程，則包主體可以不需要。
2. 包頭
答：1) 包頭包含了有關包的內容的信息，包頭不含任何過程的代碼。
2) 語法：
CREATE [OR REPLACE] PACKAGE pack_name {IS|AS}
procedure_specification|function_specification|
variable_declaration|type_definition|exception_declaration|
cursor_declaration
END pack_name;
3) 示例：
CREATE OR REPLACE PACKAGE pak_test AS
PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE);
TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER;
END pak_test;
3. 包主體
答：1) 包主體是可選的，如包頭中沒有任何函數與過程，則包主體可以不需要。
2) 包主體與包頭存放在不同的數據字典中。
3) 如包頭編譯不成功，包主體無法正確編譯。
4) 包主體包含了所有在包頭中聲明的所有過程與函數的代碼。
5) 示例：
CREATE OR REPLACE PACKAGE BODY pak_test AS
PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE) IS
BEGIN
...
END RemoveStudent;
TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER;
END pak_test;
4. 包的作用域
答：1) 在包外調用包中過程(需加包名)：pak_test.AddStudent(100010, 'CS', 101);
2) 在包主體中可以直接使用包頭中聲明的對象和過程(不需加包名);
5. 包中子程序的重載
答：1) 同一個包中的過程與函數都可以重載;
2) 相同的過程或函數名字，但參數不同;
6. 包的初始化
答：1) 包存放在數據庫中;
2) 在第一次被調用的時候，包從數據庫中調入內存并被初始化;
3) 包中定義的所有變量都被分配內存;
4) 每個會話都將擁有自己的包內變量的副本。
第九章
1. 觸發器
答：1) 觸發器與過程/函數的相同點
a. 都是帶有名字的執行塊;
b. 都有聲明、執行體和異常部分;
2) 觸發器與過程/函數的不同點
a. 觸發器必須存儲在數據庫中;
b. 觸發器自動執行;
2. 創建觸發器
答：1) 語法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} triggering_event ON table_reference
[FOR EACH ROW [WHEN trigger_condition]]
trigger_body;
2) 范例：
CREATE OR REPLACE TRIGGER UpdateMajorStats AFTER INSERT OR DELETE OR UPDATE ON students
DECLARE
CURSOR c_Statistics IS
SELECT * FROM students GROUP BY major;
BEGIN
...
END Up;
3. 觸發器
答：1) 三個語句(INSERT/UPDATE/DELETE);
2) 二種類型(之前/之后);
3) 二種級別(row-level/statement-level);
所以一共有 3 X 2 X 2 = 12
4. 觸發器的限制
答：1) 不應該使用事務控制語句;
2) 不能聲明任何LONG或LONG RAW變量;
3) 可以訪問的表有限。
5. 觸發器的主體可以訪問的表
答：1) 不可以讀取或修改任何變化表(被DML語句正在修改的表);
2) 不可以讀取或修改限制表(帶有約束的表)的主鍵、唯一值、外鍵列。
(4)Java開發中使用Oracle的ORA-01000
很多朋友在Java開發中，使用Oracle數據庫的時候，經常會碰到有ORA-01000: maximum open cursors exceeded.的錯誤。
實際上，這個錯誤的原因，主要還是代碼問題引起的。
ora-01000: maximum open cursors exceeded.
表示已經達到一個進程打開的最大游標數。
這樣的錯誤很容易出現在Java代碼中的主要原因是：Java代碼在執行conn.createStatement()和conn.prepareStatement()的時候，實際上都是相當與在數據庫中打開了一個cursor。尤其是，如果你的createStatement和prepareStatement是在一個循環里面的話，就會非常容易出現這個問題。因為游標一直在不停的打開，而且沒有關閉。
一般來說，我們在寫Java代碼的時候，createStatement和prepareStatement都應該要放在循環外面，而且使用了這些Statment后，及時關閉。最好是在執行了一次executeQuery、executeUpdate等之后，如果不需要使用結果集（ResultSet）的數據，就馬上將Statment關閉。
對于出現ORA-01000錯誤這種情況，單純的加大open_cursors并不是好辦法，那只是治標不治本。實際上，代碼中的隱患并沒有解除。
而且，絕大部分情況下，open_cursors只需要設置一個比較小的值，就足夠使用了，除非有非常特別的要求。
(5)在store procedure中執行 DDL語句
一是：execute immediate 'update '||table_chan||' set '||column_changed||' = '''||v_trans_name||''' where empid = '''||v_empid||'''' ;
二是：The DBMS_SQL package can be used to execute DDL statements directly from PL/SQL.
這是一個創建一個表的過程的例子。該過程有兩個參數：表名和字段及其類型的列表。
CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS
cursor1 INTEGER;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/
2 如何找數據庫表的主鍵字段的名稱?
SQL>SELECT * FROM user_constraints
WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME';
3 如何查詢數據庫有多少表?
SQL>select * from all_tables;
4 使用sql統配符
通配符 描述 示例 % 包含零個或更多字符的任意字符串。 WHERE title LIKE '%computer%' 將查找處于書名任意位置的包含單詞 computer 的所有書名。 _（下劃線） 任何單個字符。 WHERE au_fname LIKE '_ean' 將查找以 ean 結尾的所有 4 個字母的名字（Dean、Sean 等）。 [ ] 指定范圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字符。 WHERE au_lname LIKE '[C-P]arsen' 將查找以arsen 結尾且以介于 C 與 P 之間的任何單個字符開始的作者姓氏，例如，Carsen、Larsen、Karsen 等。 [^] 不屬于指定范圍 ([a-f]) 或集合 ([abcdef]) 的任何單個字符。 WHERE au_lname LIKE 'de[^l]%' 將查找以 de 開始且其后的字母不為 l 的所有作者的姓氏。
5使普通用戶有查看v$Session的權限
GRANT SELECT
ON "SYS"."V_$OPEN_CURSOR" TO "SFISM4";
GRANT SELECT
ON "SYS"."V_$SESSION" TO "SFISM4";
常用函數
distinct
去掉重復的
minus 相減
在第一個表但不在第二個表
SELECT * FROM FOOTBALL MINUS SELECT * FROM SOFTBALL;
intersect 相交
INTERSECT 返回兩個表中共有的行。
SELECT * FROM FOOTBAL；
UNION ALL 與UNION 一樣對表進行了合并但是它不去掉重復的記錄。
匯總函數
count
select count(*) from test;
SUM
SUM 就如同它的本意一樣它返回某一列的所有數值的和。
SELECT SUM(SINGLES) TOTAL_SINGLES FROM TEST;
SUM 只能處理數字如果它的處理目標不是數字你將會收到如下信息
輸入/輸出
SQL>SELECT SUM(NAME) FROM TEAMSTATS;
ERROR
ORA-01722 invalid number
no rows selected
該錯誤信息當然的合理的因為NAME 字段是無法進行匯總的。
AVG
AVG 可以返回某一列的平均值。
SELECT AVG(SO) AVE_STRIKE_OUTS FROM TEAMSTATS；
MAX
如果你想知道某一列中的最大值請使用MAX。
SELECT MAX(HITS) FROM TEAMSTATS;
MIN
MIN 與MAX 類似它返回一列中的最小數值。
VARIANCE
VARIANCE 方差不是標準中所定義的但它卻是統計領域中的一個至關重要的數值。
SELECT VARIANCE(HITS) FROM TEAMSTATS;
STDDEV
這是最后一個統計函數STDDEV 返回某一列數值的標準差。
SELECT STDDEV HITS FROM TEAMSTATS；
日期時間函數
ADD_MONTHS
ADD_MONTHS也可以工作在select 之外
該函數的功能是將給定的日期增加一個月舉例來說由于一些特殊的原因上述的計劃需要推遲兩個月那么就用到了。
LAST_DAY
LAST_DAY 可以返回指定月份的最后一天.
MONTHS_BETWEEN
如果你想知道在給定的兩個日期中有多少個月可以使用MONTHS_BETWEEN。
select task, startdate, enddate ,months between(Startdate,enddate) duration from project;
返回結果有可能是負值.
可以利用負值來判斷某一日期是否在另一個日期之前下例將會顯示所有在1995 年5 月19 日以前開始的比賽.
SELECT * FROM PROJECT
WHERE MONTHS_BETWEEN ('19-MAY-95', STARTDATE)>0;
NEW_TIME
如果你想把時間調整到你所在的時區你可以使用NEW_TIME.
SQL>SELECT ENDDATE EDT, NEW_TIME('ENDDATE',' EDT',' PDT') FROM PROJECT;
NEXT_DAY
NEXT_DAY 將返回與指定日期在同一個星期或之后一個星期內的你所要求的星期天
數的確切日期如果你想知道你所指定的日期的星期五是幾號可以這樣做.
SQL>SELECT STARTDATE, NEXT_DAY(STARTDATE,' FRIDAY') FROM PROJECT;
SYSDATE
SYSDATE 將返回系統的日期和時間。
SELECT DISTINCT SYSDATE FROM PROJECT；
數學函數
ABS
ABS 函數返回給定數字的絕對值
CEIL 和FLOOR
CEIL 返回與給定參數相等或比給定參數在的最小整數.FLOOR 則正好相反它返回
與給定參數相等或比給定參數小的最大整數.
COS COSH SIN SINH TAN TANH
COS SIN TAN 函數可以返回給定參數的三角函數值默認的參數認定為弧度制.
EXP
EXP 將會返回以給定的參數為指數以e 為底數的冪.
LN and LOG
這是兩個對數函數其中LN 返回給定參數的自然對數.
MOD
知道在ANSI 標準中規定取模運算的符號為%在一些解釋器中被函數MOD 所取代.
POWER
該函數可以返回某一個數對另一個數的冪在使用冪函數時第一個參數為底數第二個為指數。
SIGN
如果參數的值為負數那么SIGN 返回-1 如果參數的值為正數那么SIGN 返回1,如果參數為零那么SIGN 也返回零.
SQRT
該函數返回參數的平方根,由于負數是不能開平方的所以我們不能將該函數應用于負數.
字符函數
CHR
該函數返回與所給數值參數等當的字符返回的字符取決于數據庫所依賴的字符集.
CONCAT
和||一個作用，把兩個字符串連接起來.
INITCAP
該函數將參數的第一個字母變為大寫此外其它的字母則轉換成小寫.
LOWER 和UPPER
如你所料LOWER 將參數轉換為全部小寫字母而UPPER 則把參數全部轉換成大寫字母.
LPAD 與RPAD
這兩個函數最少需要兩個參數最多需要三個參數每一個參數是需要處理的字符串
第二個參數是需要將字符串擴充的寬度第三個參數表示加寬部分用什么字符來做填補
第三個參數的默認值為空格但也可以是單個的字符或字符串.
LTRIM 與RTRIM
LTRIM 和RTRIM至少需要一個參數最多允許兩個參數第一個參數與LPAD 和RPAD
類似是一個字符串第二個參數也是一個字符或字符串默認則是空格如果第二個
數不是空格的話那么該函數將會像剪除空格那樣剪除所指定的字符.
REPLACE
它的工作就如果它的名字所說的那樣該函數需要三個參數第一個參數是需要搜索
的字符串(列名），第二個參數是搜索的內容第三個參數則是需要替換成的字符串如果第三個
參數省略或者是NULL 那么將只執行搜索操作而不會替換任何內容.
SUBSTR
這個函數有三個參數允許你將目標字符串的一部份輸出，第一個參數為目標字符串，
第二個字符串是將要輸出的子串的起點，第三個參數是將要輸出的子串的長度。
如果第二個參數為負數那么將會從源串的尾部開始向前定位至負數的絕對值的位置。
TRANSLATE
這一函數有三個參數，目標字符串、源字符串和目的字符串，在目標字符串與源字符
串中均出現的字符將會被替換成對應的目的字符串的字符。
INSTR
如果需要知道在一個字符串中滿足特定的內容的位置可以使用INSTR ，它的第一個參
數是目標字符串，第二個參數是匹配的內容，第三和第四個參數是數字用以指定開始搜
索的起點以及指出第幾個滿足條件的將會被返回。
LENGTH
LENGTH將返回指定字符串的長度。
轉換函數
轉換函數有三個可以使你方便地將數據從一種類型變換為另一種類型。
TO_CHAR
該函數的最初功能是將一個數字轉換為字符型，不同的解釋器可能會使用它來轉換其它的
數據類型例如日期型轉換為字符型或者是擁有更多的參數。
TO_NUMBER
該函數與TO_CHAR 函數相對應顯而易見它是將一個字符串型數字轉換為數值型。
其他函數
GREATEST 與LEAST
這兩個函數將返回幾個表達式中最大的和最小的。
SELECT GREATEST（‘ALPHA’，‘ BRAVO’，‘ FOXTROT’，‘ DELTA’） FROM CONVERT；
看來有必要使用FROM 子句可是如果FROM 子句沒有的話你將會收到一個錯誤信息每
一個SELECT 語句都需要FROM 子句。
USER
該函數返回當前使用數據庫的用戶的名字