#
方法一：
在目前絕大部分數據庫有分布式查詢的需要。下面簡單的介紹如何在oracle中配置實現跨庫訪問。
比如現在有2個數據庫服務器，安裝了2個數據庫。數據庫server A和B。現在來實現在A庫中訪問B的數據庫。
第一步、配置A服務器端的tnsnames.ora文件（TNSNAMES.ORA Network Configuration File），該文件存放的位置為：
$ORACLE_HOME/network/admin/tnsnames.ora
需要在該文件中增加對B庫的配置項，格式如下
ZBCDB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.50.6)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = zbcdb3)
)
)
若在A庫的另外一臺客戶端來訪問B的數據庫的話，同時也應該修改客戶端的相應的文件。
第二步、在A服務器的一個庫中建立B的一個數據的DBLINK。語法如下：
create database link dcmdb connect to dcmdb identified by dcmoptr using 'zbcdb3' ;
然后可以實現分布式查詢：
select * from tabname@dcmdb where 1=1;
方法二：
首先創建數據庫鏈接:
CREATE PUBLIC DATABASE LINK 數據鏈名稱 CONNECT TO 登陸用戶名 IDENTIFIED BY 密碼 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 對方Oracle服務器的IP地址)(PORT = 端口號))
)
(CONNECT_DATA =
(SERVICE_NAME = 對方Oracle服務器服務名)
)
)'
其中 數據鏈名稱 為添加到本地Oracle數據庫控制臺(Oracle Enterprise Manager Console)樹節點的服務名
要查詢對方數據庫的表TableName語句如下:
SELECT 字段名 FROM TableName@數據鏈名稱;
復制表數據：
insert into 表名（字段名） （SELECT 字段名 FROM TableName@數據鏈名稱）;
通常，你需要獲得當前日期和計算一些其他的日期，例如，你的程序可能需要判斷一個月的第一天或者最后一天。你們大部分人大概都知道怎樣把日期進行分割（年、月、日等），然后僅僅用分割出來的年、月、日等放在幾個函數中計算出自己所需要的日期！在這篇文章里，我將告訴你如何使用DATEADD和DATEDIFF函數來計算出在你的程序中可能你要用到的一些不同日期。
在使用本文中的例子之前，你必須注意以下的問題。大部分可能不是所有例子在不同的機器上執行的結果可能不一樣，這完全由哪一天是一個星期的第一天這個設置決定。第一天（DATEFIRST）設定決定了你的系統使用哪一天作為一周的第一天。所有以下的例子都是以星期天作為一周的第一天來建立，也就是第一天設置為7。假如你的第一天設置不一樣，你可能需要調整這些例子，使它和不同的第一天設置相符合。你可以通過@@DATEFIRST函數來檢查第一天設置。
為了理解這些例子，我們先復習一下DATEDIFF和DATEADD函數。DATEDIFF函數計算兩個日期之間的小時、天、周、月、年等時間間隔總數。DATEADD函數計算一個日期通過給時間間隔加減來獲得一個新的日期。要了解更多的DATEDIFF和DATEADD函數以及時間間隔可以閱讀微軟聯機幫助。
使用DATEDIFF和DATEADD函數來計算日期，和本來從當前日期轉換到你需要的日期的考慮方法有點不同。你必須從時間間隔這個方面來考慮。比如，從當前日期到你要得到的日期之間有多少時間間隔，或者，從今天到某一天（比如1900-1-1）之間有多少時間間隔，等等。理解怎樣著眼于時間間隔有助于你輕松的理解我的不同的日期計算例子。
一個月的第一天
第一個例子，我將告訴你如何從當前日期去這個月的最后一天。請注意：這個例子以及這篇文章中的其他例子都將只使用DATEDIFF和DATEADD函數來計算我們想要的日期。每一個例子都將通過計算但前的時間間隔，然后進行加減來得到想要計算的日期。
這是計算一個月第一天的SQL 腳本：
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
我們把這個語句分開來看看它是如何工作的。最核心的函數是getdate()，大部分人都知道這個是返回當前的日期和時間的函數。下一個執行的函數DATEDIFF(mm,0,getdate())是計算當前日期和“1900-01-01 00:00:00.000”這個日期之間的月數。記住：時期和時間變量和毫秒一樣是從“1900-01-01 00:00:00.000”開始計算的。這就是為什么你可以在DATEDIFF函數中指定第一個時間表達式為“0”。下一個函數是DATEADD，增加當前日期到“1900-01-01”的月數。通過增加預定義的日期“1900-01-01”和當前日期的月數，我們可以獲得這個月的第一天。另外，計算出來的日期的時間部分將會是“00:00:00.000”。
這個計算的技巧是先計算當前日期到“1900-01-01”的時間間隔數，然后把它加到“1900-01-01”上來獲得特殊的日期，這個技巧可以用來計算很多不同的日期。下一個例子也是用這個技巧從當前日期來產生不同的日期。
本周的星期一
這里我是用周(wk)的時間間隔來計算哪一天是本周的星期一。
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
一年的第一天
現在用年(yy)的時間間隔來顯示這一年的第一天。
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
季度的第一天
假如你要計算這個季度的第一天，這個例子告訴你該如何做。
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
當天的半夜
曾經需要通過getdate()函數為了返回時間值截掉時間部分，就會考慮到當前日期是不是在半夜。假如這樣，這個例子使用DATEDIFF和DATEADD函數來獲得半夜的時間點。
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
深入DATEDIFF和DATEADD函數計算
你可以明白，通過使用簡單的DATEDIFF和DATEADD函數計算，你可以發現很多不同的可能有意義的日期。
目前為止的所有例子只是僅僅計算當前的時間和“1900-01-01”之間的時間間隔數量，然后把它加到“1900-01-01”的時間間隔上來計算出日期。假定你修改時間間隔的數量，或者使用不同的時間間隔來調用DATEADD函數，或者減去時間間隔而不是增加，那么通過這些小的調整你可以發現和多不同的日期。
這里有四個例子使用另外一個DATEADD函數來計算最后一天來分別替換DATEADD函數前后兩個時間間隔。
上個月的最后一天
這是一個計算上個月最后一天的例子。它通過從一個月的最后一天這個例子上減去3毫秒來獲得。有一點要記住，在Sql Server中時間是精確到3毫秒。這就是為什么我需要減去3毫秒來獲得我要的日期和時間。
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
計算出來的日期的時間部分包含了一個Sql Server可以記錄的一天的最后時刻(“23:59:59:997”)的時間。
去年的最后一天
連接上面的例子，為了要得到去年的最后一天，你需要在今年的第一天上減去3毫秒。
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
本月的最后一天
現在，為了獲得本月的最后一天，我需要稍微修改一下獲得上個月的最后一天的語句。修改需要給用DATEDIFF比較當前日期和“1900-01-01”返回的時間間隔上加1。通過加1個月，我計算出下個月的第一天，然后減去3毫秒，這樣就計算出了這個月的最后一天。這是計算本月最后一天的SQL腳本。
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
本年的最后一天
你現在應該掌握這個的做法，這是計算本年最后一天腳本
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
本月的第一個星期一
好了，現在是最后一個例子。這里我要計算這個月的第一個星期一。這是計算的腳本。
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
在這個例子里，我使用了“本周的星期一”的腳本，并作了一點點修改。修改的部分是把原來腳本中“getdate()”部分替換成計算本月的第6天，在計算中用本月的第6天來替換當前日期使得計算可以獲得這個月的第一個星期一。
總結
我希望這些例子可以在你用DATEADD和DATEDIFF函數計算日期時給你一點啟發。通過使用這個計算日期的時間間隔的數學方法，我發現為了顯示兩個日期之間間隔的有用歷法是有價值的。注意，這只是計算出這些日期的一種方法。要牢記，還有很多方法可以得到相同的計算結果。假如你有其他的方法，那很不錯，要是你沒有，我希望這些例子可以給你一些啟發，當你要用DATEADD和DATEDIFF函數計算你程序可能要用到的日期時。
---------------------------------------------------------------
附錄，其他日期處理方法
1)去掉時分秒
declare @ datetime
set @ = getdate() --'2003-7-1 10:00:00'
SELECT @,DATEADD(day, DATEDIFF(day,0,@), 0)
2）顯示星期幾
select datename(weekday,getdate())
3）如何取得某個月的天數
declare @m int
set @m=2 --月份
select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15')
另外，取得本月天數
select datediff(day,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate()) as varchar)+'-15' ,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate())+1 as varchar)+'-15')
或者使用計算本月的最后一天的腳本，然后用DAY函數區最后一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
4）判斷是否閏年：
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '閏年' end
或者
select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))
when 28 then '平年' else '閏年' end
5）一個季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
Oracle與DB2獲取當前系統時間的方法：
Oracle中系統時間比較熟悉 是select sysdate from dual;
db2中則是 SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1
還有
select CURRENT DATE from SYSIBM.SYSDUMMY1;獲得日期
select CURRENT TIME from SYSIBM.SYSDUMMY1;獲得時間
查詢
SELECT語句用于從數據庫中查詢數據，當在PL/SQL中使用SELECT語句時，要與INTO子句一起使用，查詢的返回值被賦予INTO子句中的變量，變量的聲明是在DELCARE中。SELECT INTO語法如下：
SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
FROM {table|(sub-query)}[alias]
WHERE............
PL/SQL中SELECT語句只返回一行數據。如果超過一行數據，那么就要使用顯式游標（對游標的討論我們將在后面進行），INTO子句中要有與SELECT子句中相同列數量的變量。INTO子句中也可以是記錄變量。
%TYPE屬性
在PL/SQL中可以將變量和常量聲明為內建或用戶定義的數據類型，以引用一個列名，同時繼承他的數據類型和大小。這種動態賦值方法是非常有用的，比如變量引用的列的數據類型和大小改變了，如果使用了%TYPE,那么用戶就不必修改代碼，否則就必須修改代碼。
例：
v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;
不但列名可以使用%TYPE,而且變量、游標、記錄，或聲明的常量都可以使用%TYPE。這對于定義相同數據類型的變量非常有用。
DELCARE
V_A NUMBER(5):=10;
V_B V_A%TYPE:=15;
V_C V_A%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE
('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END
SQL>/
V_A=10 V_B=15 V_C=
PL/SQL procedure successfully completed.
SQL>
其他DML語句
其它操作數據的DML語句是:INSERT、UPDATE、DELETE和LOCK TABLE,這些語句在PL/SQL中的語法與在SQL中的語法相同。我們在前面已經討論過DML語句的使用這里就不再重復了。在DML語句中可以使用任何在DECLARE部分聲明的變量，如果是嵌套塊，那么要注意變量的作用范圍。
例：
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
AS
v_ename EMP.ENAME%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno=p_empno;
INSERT INTO FORMER_EMP(EMPNO,ENAME)
VALUES (p_empno,v_ename);
DELETE FROM emp
WHERE empno=p_empno;
UPDATE former_emp
SET date_deleted=SYSDATE
WHERE empno=p_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
END
DML語句的結果
當執行一條DML語句后，DML語句的結果保存在四個游標屬性中，這些屬性用于控制程序流程或者了解程序的狀態。當運行DML語句時，PL/SQL打開一個內建游標并處理結果，游標是維護查詢結果的內存中的一個區域，游標在運行DML語句時打開，完成后關閉。隱式游標只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三個屬性.SQL%FOUND,SQL%NOTFOUND是布爾值，SQL%ROWCOUNT是整數值。
SQL%FOUND和SQL%NOTFOUND
在執行任何DML語句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在執行DML語句后，SQL%FOUND的屬性值將是：
. TRUE :INSERT
. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.
. TRUE :SELECT INTO至少返回一行
當SQL%FOUND為TRUE時,SQL%NOTFOUND為FALSE。
SQL%ROWCOUNT
在執行任何DML語句之前，SQL%ROWCOUNT的值都是NULL,對于SELECT INTO語句，如果執行成功，SQL%ROWCOUNT的值為1,如果沒有成功，SQL%ROWCOUNT的值為0，同時產生一個異常NO_DATA_FOUND.
SQL%ISOPEN
SQL%ISOPEN是一個布爾值，如果游標打開，則為TRUE, 如果游標關閉，則為FALSE.對于隱式游標而言SQL%ISOPEN總是FALSE，這是因為隱式游標在DML語句執行時打開，結束時就立即關閉。
事務控制語句
事務是一個工作的邏輯單元可以包括一個或多個DML語句，事物控制幫助用戶保證數據的一致性。如果事務控制邏輯單元中的任何一個DML語句失敗，那么整個事務都將回滾，在PL/SQL中用戶可以明確地使用COMMIT、ROLLBACK、SAVEPOINT以及SET TRANSACTION語句。
COMMIT語句終止事務，永久保存數據庫的變化，同時釋放所有LOCK,ROLLBACK終止現行事務釋放所有LOCK，但不保存數據庫的任何變化,SAVEPOINT用于設置中間點，當事務調用過多的
數據庫操作時，中間點是非常有用的，SET TRANSACTION用于設置事務屬性，比如read-write和隔離級等。
顯式游標
當查詢返回結果超過一行時，就需要一個顯式游標，此時用戶不能使用select into語句。PL/SQL管理隱式游標，當查詢開始時隱式游標打開，查詢結束時隱式游標自動關閉。顯式游標在PL/SQL塊的聲明部分聲明，在執行部分或異常處理部分打開，取數據,關閉。
使用游標
這里要做一個聲明，我們所說的游標通常是指顯式游標，因此從現在起沒有特別指明的情況，我們所說的游標都是指顯式游標。要在程序中使用游標，必須首先聲明游標。
聲明游標
語法：
CURSOR cursor_name IS select_statement;
在PL/SQL中游標名是一個未聲明變量，不能給游標名賦值或用于表達式中。
例：
DELCARE
CURSOR C_EMP IS SELECT empno,ename,salary
FROM emp
WHERE salary>2000
ORDER BY ename;
........
BEGIN
在游標定義中SELECT語句中不一定非要表可以是視圖，也可以從多個表或視圖中選擇的列，甚至可以使用*來選擇所有的列 。
打開游標
使用游標中的值之前應該首先打開游標，打開游標初始化查詢處理。打開游標的語法是：
OPEN cursor_name
cursor_name是在聲明部分定義的游標名。
例：
OPEN C_EMP;
關閉游標
語法：
CLOSE cursor_name
例：
CLOSE C_EMP;
從游標提取數據
從游標得到一行數據使用FETCH命令。每一次提取數據后，游標都指向結果集的下一行。語法如下：
FETCH cursor_name INTO variable[,variable,...]
對于SELECT定義的游標的每一列，FETCH變量列表都應該有一個變量與之相對應，變量的類型也要相同。
例：
SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
CLOSE c_emp;
END
這段代碼無疑是非常麻煩的，如果有多行返回結果，可以使用循環并用游標屬性為結束循環的條件，以這種方式提取數據，程序的可讀性和簡潔性都大為提高，下面我們使用循環重新寫上面的程序：
SET SERVERIUTPUT ON
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);
END
記錄變量
定義一個記錄變量使用TYPE命令和%ROWTYPE，關于%ROWsTYPE的更多信息請參閱相關資料。
記錄變量用于從游標中提取數據行，當游標選擇很多列的時候，那么使用記錄比為每列聲明一個變量要方便得多。
當在表上使用%ROWTYPE并將從游標中取出的值放入記錄中時，如果要選擇表中所有列，那么在SELECT子句中使用*比將所有列名列出來要
安全得多。
例：
SET SERVERIUTPUT ON
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
%ROWTYPE也可以用游標名來定義，這樣的話就必須要首先聲明游標：
SET SERVERIUTPUT ON
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
帶參數的游標
與
存儲過程和函數相似，可以將參數傳遞給游標并在查詢中使用。這對于處理在某種條件下打開游標的情況非常有用。它的語法如下：
CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定義參數的語法如下：
Parameter_name [IN] data_type[{:=|DEFAULT} value]
與存儲過程不同的是，游標只能接受傳遞的值，而不能返回值。參數只定義數據類型，沒有大小。
另外可以給參數設定一個缺省值，當沒有參數值傳遞給游標時，就使用缺省值。游標中定義的參數只是一個占位符，在別處引用該參數不一定可靠。
在打開游標時給參數賦值，語法如下：
OPEN cursor_name[value[,value]....];
參數值可以是文字或變量。
例：
DECALRE
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
游標FOR循環
在大多數時候我們在設計程序的時候都遵循下面的步驟：
1、打開游標
2、開始循環
3、從游標中取值
4、檢查那一行被返回
5、處理
6、關閉循環
7、關閉游標
可以簡單的把這一類代碼稱為游標用于循環。但還有一種循環與這種類型不相同，這就是FOR循環，用于FOR循環的游標按照正常的聲明方式聲明，它的優點在于不需要顯式的打開、關閉、取數據，測試數據的存在、定義存放數據的變量等等。游標FOR循環的語法如下：
FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_difinition)
LOOP
statements
END LOOP;
下面我們用for循環重寫上面的例子：
DECALRE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || 'salary:' || v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;
在游標FOR循環中使用查詢
在游標FOR循環中可以定義查詢，由于沒有顯式聲明所以游標沒有名字，記錄名通過游標查詢來定義。
DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN (SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;
游標中的子查詢
語法如下：
CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!='ACCOUNTING');
可以看出與SQL中的子查詢沒有什么區別。
游標中的更新和刪除
在PL/SQL中依然可以使用UPDATE和DELETE語句更新或刪除數據行。顯式游標只有在需要獲得多行數據的情況下使用。PL/SQL提供了僅僅使用游標就可以執行刪除或更新記錄的方法。
UPDATE或DELETE語句中的WHERE CURRENT OF子串專門處理要執行UPDATE或DELETE操作的表中取出的最近的數據。要使用這個方法，在聲明游標時必須使用FOR UPDATE子串，當對話使用FOR UPDATE子串打開一個游標時，所有返回集中的數據行都將處于行級（ROW-LEVEL)獨占式鎖定，其他對象只能查詢這些數據行，不能進行UPDATE、DELETE或SELECT...FOR UPDATE操作。
語法：
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]
在多表查詢中，使用OF子句來鎖定特定的表,如果忽略了OF子句，那么所有表中選擇的數據行都將被鎖定。如果這些數據行已經被其他會話鎖定，那么正常情況下ORACLE將等待，直到數據行解鎖。
在UPDATE和DELETE中使用WHERE CURRENT OF子串的語法如下：
WHERE{CURRENT OF cursor_name|search_condition}
例：
DELCARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
END
觸發器一 觸發器介紹
觸發器是一種特殊的存儲過程，它在插入，刪除或修改特定表中
的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的
數據控制能力。數據庫觸發器有以下的作用：
* 安全性。可以基于數據庫的值使用戶具有操作數據庫的某種權利。
# 可以基于時間限制用戶的操作，例如不允許下班后和節假日
修改數據庫數據。
# 可以基于數據庫中的數據限制用戶的操作，例如不允許股票
的價格的升幅一次超過10%。
* 審計。可以跟蹤用戶對數據庫的操作。
# 審計用戶操作數據庫的語句。
# 把用戶對數據庫的更新寫入審計表。
* 實現復雜的數據完整性規則。
# 實現非標準的數據完整性檢查和約束。觸發器可產生比規則
更為復雜的限制。與規則不同，觸發器可以引用列或數據庫對
象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。
# 提供可變的缺省值。
* 實現復雜的非標準的數據庫相關完整性規則。觸發器可以對數
據庫中相關的表進行連環更新。例如，在auths表author_code列上的
刪除觸發器可導致相應刪除在其它表中的與之匹配的行。
# 在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時把其它表中的與之匹配的行設成NULL值。
# 在修改或刪除時把其它表中的與之匹配的行級聯設成缺省值。
# 觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試
圖進行數據更新的事務。當插入一個與其主健不匹配的外部鍵
時，這種觸發器會起作用。例如，可以在books.author_code
列上生成一個插入觸發器，如果新值與auths.author_code列
中的某值不匹配時，插入被回退。
* 同步實時地復制表中的數據。
* 自動計算數據值，如果數據的值達到了一定的要求，則進行特
定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人
員發送警告數據。
ORACLE與SYBASE數據庫的觸發器有一定的區別，下面將分別講述
這兩種數據庫觸發器的作用和寫法。
二 ORACLE 觸發器
ORACLE產生數據庫觸發器的語法為：
create [or replace] trigger 觸發器名 觸發時間 觸發事件
on 表名
[for each row]
pl/sql 語句
其中：
觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行
的，因此該名稱只是一個名稱，沒有實質的用途。
觸發時間：指明觸發器何時執行，該值可取：
before---表示在數據庫動作之前觸發器執行；
after---表示在數據庫動作之后出發器執行。
觸發事件：指明哪些數據庫動作會觸發此觸發器：
insert：數據庫插入會觸發此觸發器；
update：數據庫修改會觸發此觸發器；
delete：數據庫刪除會觸發此觸發器。
表 名：數據庫觸發器所在的表。
for each row：對表的每一行觸發器執行一次。如果沒有這一
選項，則只對整個表執行一次。
舉例：下面的觸發器在更新表auths之前觸發，目的是不允許在
周末修改表：
create trigger auth_secure
before insert or update or delete //對整表更新前觸發
on auths
begin
if(to_char(sysdate,'DY')='SUN'
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
end if;
end
三 SYBASE數據庫觸發器
SYBASE數據庫觸發器的作用與ORACLE非常類似，僅有較小的差異。
SYBASE產生觸發器的語法為：
CREATE TRIGGER 觸發器名
ON 表名
FOR INSERT，UPDATE，DELETE
AS
SQL_statement |
FOR INSERT，UPDATE
AS
IF UPDATE(column_name) [AND|OR UPDATE(column_name)]...
SQL_statements
上面FOR子句用來指定在觸發器上的哪些數據更新命令可激活該
觸發器。IF UPDATE子句檢查對指定列的操作類型，在IF UPDATE子句
中可指定多個列。
與ORACLE不同，對于每條SQL語句，觸發器只執行一次。觸發器
在數據更新語句完成以后立即執行。觸發器和啟動它的語句被當作一
個事務處理，事務可以在觸發器中回退。
下面舉例說明SYBASE觸發器的寫法。
create trigger forinsert_books
on books
for insert
as
if(select count(*) from auths,inserted
where auths.author_code=insert.author_code)!=@@rowcount
begin
rollback transaction
print "books 表中 author_code 列的值在auths 表中不存在。"
end
----------------------------------------------------------------------------------------------------------
存儲過程一 存儲過程介紹
存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優化
后存儲在數據庫服務器中，使用時只要調用即可。在ORACLE中，若干個
有聯系的過程可以組合在一起構成程序包。
使用存儲過程有以下的優點：
* 存儲過程的能力大大增強了SQL語言的功能和靈活性。存儲過程可
以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的
運算。
* 可保證數據的安全性和完整性。
# 通過存儲過程可以使沒有權限的用戶在控制之下間接地存取數據
庫，從而保證數據的安全。
# 通過存儲過程可以使相關的動作在一起發生，從而可以維護數據
庫的完整性。
* 再運行存儲過程前，數據庫已對其進行了語法和句法分析，并給出
了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。
由于執行SQL語句的大部分工作已經完成，所以存儲過程能以極快的速度執
行。
* 可以降低網絡的通信量。
* 使體現企業規則的運算程序放入數據庫服務器中，以便：
# 集中控制。
# 當企業規則發生變化時在服務器中改變存儲過程即可，無須修改
任何應用程序。企業規則的特點是要經常變化，如果把體現企業規則的運
算程序放入應用程序中，則當企業規則發生變化時，就需要修改應用程序
工作量非常之大（修改、發行和安裝應用程序）。如果把體現企業規則的
運算放入存儲過程中，則當企業規則發生變化時，只要修改存儲過程就可
以了，應用程序無須任何變化。
不同數據庫存儲過程的寫法不一，在后面的講座中將分別介紹ORACLE
和SYBASE存儲過程的用法。
二 ORACLE 的存儲過程
ORACLE 創建存儲過程的語法為：
create [or replace] procedure 過程名
參數1 [in|out|in out] 數據類型
[,參數2 [in|out|in out] 數據類型]...
{is|as} pl/sql 語句
下面舉例說明ORACLE數據庫存儲過程的寫法和用法。
可以建立一個存儲過程，每當用戶修改數據庫的重要數據時，即把
用戶的用戶名、日期和操作類型記錄下來：
create procedure update_log is
begin
insert into update_log_tab(use_name,update_date,operation)
values(user,sysdate,'update'
end;
可以在恰當的位置調用這個存儲過程來記錄用戶對表的修改。例如下面在
表sal_comm上建立一個修改觸發器，每當用戶修改此表后，用戶的名稱、修改
時間和操作即被記錄在了表update_log_tab中：
create trigger audit_update
after update on sal_comm
for each row
begin
update_log
end
三 Sybase的存儲過程
盡管Sybase存儲過程的功能和寫法與ORACLE類似，但他們之間還是
有一定的差別。下面講述SYBASE的存儲過程。
SYBASE可以用CREATE PROCedure命令生成存儲過程：
CREATE PROCedure 存儲過程名 [；number]
[[(] @parameter_name datatype [=default] [OUTput]
[, @parameter_name datatype [=default] [OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
下面是一個查詢作者編碼、名稱和生日的存儲過程：
create proc p_auths @author_code varchar(10)
as
select author_code, name, birthdate
from auths
where author_code=@author_code
下面執行過程p_auths:
p_auths @author_code=A00001
在CREATE PROC語句中，可以為參數賦缺省值，該值可以是任何常量。
當用戶不提供參數值時，該值便作為參數值提供給過程。
Sybase的存儲過程是集中存儲在SQL Server中的預先定義且已經編譯好的事務。存儲過程由SQL語句和流程控制語句組成。
它的功能包括:接受參數;調用另一過程;返回一個狀態值給調用過程或批處理,指示調用成功或失敗;返回若干個參數值給調
用過程或批處理,為調用者提供動態結果;在遠程SQL Server中運行等。
存儲過程的性能特
1.存儲過程是預編譯過的,這就意味著它與普通的SQL語句或批處理的SQL語句不同。當首次運行一個存儲過程時,SQL Server的查詢處理器對其進行分析,在排除了語法錯誤之后形成存儲在系統中的可執行方案。由于查詢處理的大部分工作已經完成,所以存儲過程執行速度很快。
2.存儲過程和待處理的數據都放在同一臺運行SQL Server的計算機上,使用存儲過程查詢當地的數據,效率自然很高。
3.存儲過程一般多由Client端通過存儲過程的名字進行調用,即跨網傳送的只是存儲過程的名字及少量的參數(如果有的話),而不是構成存儲過程的許多SQL語句,因此可以減少網絡傳輸量,加快系統響應速度。
4.存儲過程還有著如同C語言子函數那樣的被調用和返回值的方便特性。所以,存儲過程大大增強了SQL語言的功能、效率和靈活性。掌握和應用好存儲過程,對進一步發揮Sybase數據庫系統的強大功能有著重要的意義。
存儲過程的語法規則
建立存儲過程的語法規則為:
CREATE PROCedure[owner.]procedurename[;number]
[[(]@parameter_name datatype[=default][OUTput]
[,@parameter_name datatype[=default][OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
使用存儲過程的語法規則為:
[EXECute][@return-status=]
[[[server.]database.]owner.]procedurename[;number]
[[@parameter_name=]value|[@parameter_name=]@varialbe[OUT put]
[,[@parameter_name=]value|[@parameter_name=]@variable[OU Tput]...]]
[WITH RECOMPILE]
下面簡要介紹這兩個命令的常用選項以及建立和使用存儲過程的要點,關于選項的更為詳細的說明請參考有關手冊。
1.[[[server.]database.]owner.]procedure_name:存儲過程的名字。
2.@parameter_name datatype[=default][OUTput]:形式參數(形參)的名稱、類型。dfault是賦予的缺省值(可選),OUTput指定本參數為輸出參數(可選)。形參是存儲過程中的自變量,可以有多個,名字必須以@打頭,最長30個字符。
3.SQL_statements:定義存儲過程功能的SQL語句。
4.@return_status:接受存儲過程返回狀態值的變量。
5.[@parameter_name=]value:實際參數(實參),@parameter_name 為實參的名稱(可選)。如果某個實參以@parameter_name=value提供, 那么隨后的實參也都要采用這一形式提供。
6.[@parameter_name=]@varialbe[OUTput]:將變量@varialbe中的值作為實參傳遞給形參@parameter_name(可選),如果變量@varialb e是用來接受返回的參數值,則選項OUTput不可缺少。
存儲過程的建立和使用
我們將通過幾個例子進行介紹。
假設有一個用下述語句生成的技能工資表RS_LS_GZ_JiNeng:
create table RS_LS_GZ_JiNeng /*技能工資表*/
(GeRen_id char(4), /*個人代碼*/
RiQi smalldatetime, /*執行日期*/
YuanYin_id char(1) null, /*變動原因代碼*/
JinE smallmoney) /*技能工資金額*/
該表存儲著某單位員工多年來技能工資的歷史檔案。
例1.如果要查詢全體員工的技能工資變動歷史,則可先建立一個存儲過程p_RsGz_JiNeng_All:
create procedure p_RsGz_JiNeng_All
as
select *
from RS_LS_GZ_JiNeng
order by GeRenid,RiQi
然后用批處理語句調用存儲過程p_RsGz_JiNeng_All進行查詢:
execute p_RsGz_JiNeng_All
本例只顯示查詢到的數據,無輸入、輸出參量,是最簡單的一個存儲過程。
例2.如果要查詢某人技能工資的變動歷史,可建立另一個存儲過程p_RsGz_JiNeng:
create procedure p_RsGz_JiNeng
@c_GeRenId char(4)
as
select *from RS_LS_GZ_JiNeng
where GeRen_id=@c_GeRenId
order by RiQi
之后用批處理語句調用存儲過程p_Rs_Gz_JiNeng進行查詢:
declare @GeRenId char(4)
select @GeRenId="0135" /*設要查詢員工的個人代碼為"0135" */
execute p_RsGz_JeNeng @c_GeRenId=@GeRenId
存儲過程p_RsGz_JiNeng中定義了一個形參@c_GeRenId,是字符型變量。在調用該過程的批處理中,既可以用具體的值也可以用變量作為實參。用變量作實參(如本例)時,必須用delare語句加以說明。注意,在批處理的調用過程語句@c_GeRenId=@GeRenId中的@c_GeRenId是存儲過程p_RsGz_JiNeng中的形參名,不是批處理中的變量,所以不能將它列入d eclare語句的變量單中。
例3.如果要計算當月工資,就必須從工資歷史中查出員工距離當前最近的一次技能工資變動的結果:
create procedure p_RsGz_JiNeng_Slt
(@c_GeRenId char(4),@sm_JinE smallmoney output)
as
select @sm_JinE=JinE
from RS_LS_GZ_JiNeng
where RiQi=(select max(RiQi)
from RS_LS_GZ_JiNeng
where GeRenid=@c-GeRenId)/*找出歷史記錄中距離當前最近的日期*/
調用存儲過程p_RsGz_JiNeng_Slt進行查詢:
declare @GeRenId char(4),@JinE smallmoney
select @GeRenid="0135"/*設要查詢員工的個人代碼為"0135"*/
select @JinE=0
execute p_RsGz_JiNeng_slt @c_GeRenId=@GeRenId,@sm_JinE=@ JinE output
這里,變量@JinE用來存儲過程形參@sm_JinE傳回的金額。在調用過程語句中,@sm_JiE = @JinE output中的output不可省略。否則, 變量@JinE將得不到形參傳回的數值而始終為零(等于初值)。
例4.查到了個人代碼為"0135"員工的技能工資就顯示其歷史紀錄,查不到則顯示一條出錯信息。
create procedure p_RsGz_JiNeng_Rtn
@c_GeRenId char(4)
as
declare @ErrCode smallint
select @ErrCode=0
if exists(select* from RS-LS-GZ-JiNeng
where GeRenid=@c-GeRenId)
begin
select *
from RS_LS_GZ_JiNeng
whrer GeRen_id=@c_GeRenId
order by RiQi
return @ErrCode
end
esle
begin
select @ErrCode=1
return @ErrCode
end
調用存儲過程p_RsGz_JiNeng_Rtn:
declare @GeRenId char(4),@RtnCode smallint
select @GeRenId="0135"
select @RtnCode=0
execute @RtnCode=p_RsGz_JiNeng_Rtn @c_GeRenId=@GeRenId
if @RtnCode=1
print"No this one!"
存儲過程p_RsGz_JiNeng_Rtn向調用者返回一個存儲在變量@ErrC ode里的值,這個值被稱為狀態值,它向調用者反映存儲過程執行的成敗狀態。在本例中,如果查不到指定員工技能工資的任何記錄時,就認為"查無此人",返回出錯狀態值1。否則,返回成功狀態值0。
調用過程的批處理語句使用變量@RtnCode存儲返回的狀態值,一旦檢出存儲過程p_RsG_ JiNeng_Rtn返回了錯誤標志(@RtnCode=1),就顯示一條信息"No this one!"。
小結
上述四個例子簡要介紹了存儲過程常用的幾種形式,從中我們已經可以領略到它的編程特色以及使用上的靈活性和方便性。
雖然上述例子在調用存儲過程時都是用SQL的批處理語句實現的, 但并不意味著這是唯一的方法。例如在存儲過程中調用存儲過程(即所謂過程嵌套)的現象就很常見。另外,在其它Sybase數據庫開發系統(如PowerBuilder)的script語句中調用Sybase的存儲過程也非常普遍。
需求描述：目前遠程服務器有兩個數據庫AA和BB，兩個數據庫的數據庫版本都為10.2.1.0.1，兩個數據庫中的用戶不一樣，但數據庫中的表結構都是一樣的，數據庫AA表中的數據比數據庫BB表中的數據多很多，現在需要把數據庫AA中幾十張表的數據遷移到數據庫BB的表中。如果在數據庫AA中存在的數據，但在數據庫BB中沒有的數據，則需要把這些數據導入到數據庫BB中。
比如：數據庫AA 的數據庫名稱為AA，所有的表都在A_UserName用戶下，改用戶的密碼為A_Password;
數據庫BB 的數據庫名稱為BB，所有的表都在B_UserName用戶下，改用戶的密碼為B_Password;
假設本機是一臺windows系統的PC機，裝有Oracle數據庫10.2.1.0.1的版本（服務器版或客戶端版都可以），在該系統已經配置好數據庫的監聽器（$ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora）.
下面導出、導入兩個表為例子（當然幾十個表也是沒有問題的）
1 導出數據庫AA中兩表的所有數據：
D:\>expA_UserName/A_Password@AAtables=(DH_REP_WORK，DRILL_ADM_YEAR_TARGET) file=20081029 log=20081029.log
Export: Release 10.2.0.1.0 - Production on 星期三 10月 29 16:11:20 2008
Copyright (c) 1982， 2005， Oracle. All rights reserved. |
|
連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning， OLAP and Data Mining options
|
已導出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即將導出指定的表通過常規路徑...
. . 正在導出表 DH_REP_WORK導出了 1 行
. . 正在導出表 DRILL_ADM_YEAR_TARGET導出了 40 行
成功終止導出， 沒有出現警告。 |
從上面可以看出來成功導出來遠程數據庫AA中兩表的數據。
2 導入到數據庫BB中(請先看下面的錯誤原因)
D:\>impB_UserName/B_Password@BBtables=(DH_REP_WORK，DRILL_ADM_YEAR_TARGET) file=20081029 log=20081029imp.log
Import: Release 10.2.0.1.0 - Production on 星期三 10月 29 16:13:33 2008
Copyright (c) 1982， 2005， Oracle. All rights reserved.
連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning， OLAP and Data Mining options
經由常規路徑由 EXPORT:V10.02.01 創建的導出文件IMP-00013: 只有 DBA 才能導入由其他 DBA 導出的文件IMP-00000: 未成功終止導入
不能導入原來是B_UserName用戶沒有具有DBA角色的權限。
3、登陸到數據庫BB中，用超級用戶sys給B_UserName用戶授權
SQL> grant dba to B_UserName;
Grant succeeded. |
4 、現在重新導入到數據庫BB中，為了減少日志的錯誤使用ignore=yes（忽略錯誤），rows=y是導入表中的數據，grants=no表示不導入授權（假如在數據庫AA中表DH_REP_WORK授權給了test用戶，但數據庫BB中沒有test用戶，則不用執行授權給test用戶）。
D:\>imp B_UserName/B_Password@BB tables=(DH_REP_WORK，DRILL_ADM_YEAR_TARGET) rows=y ignore=yes grants=no file=20081029 log=20081029Imp.log
執行上面的語句，可能會有些錯誤提示，一般是在數據庫BB中的已經存在該數據，由于主鍵的唯一性，所以不能插入。但數據庫AA中存在、但數據庫BB中不存在的數據就可以順利的插入到數據庫BB中了。
5、登陸到數據庫BB中，用超級用戶sys回收B_UserName用戶dba的角色：
SQL> revoke dba from B_UserName;
Revoke succeeded. |
從兩個表達式返回一個非 null 值。
語法
NVL(eExpression1, eExpression2)
參數
eExpression1, eExpression2
如果 eExpression1 的計算結果為 null 值，則 NVL( ) 返回 eExpression2。如果 eExpression1 的計算結果不是 null 值，則返回 eExpression1。eExpression1 和 eExpression2 可以是任意一種數據類型。如果 eExpression1 與 eExpression2 的結果皆為 null 值，則 NVL( ) 返回 .NULL.。
返回值類型
字符型、日期型、日期時間型、數值型、貨幣型、邏輯型或 null 值
說明
在不支持 null 值或 null 值無關緊要的情況下，可以使用 NVL( ) 來移去計算或操作中的 null 值。
select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID
注意：兩個參數得類型要匹配
實際的寫法可以這樣，如果在對材料做入庫的時候有個入庫金額的字段rkje,然后在做累加操作的時候可以這樣寫：
rkje=nvl(rkje,0.00)+"+rkje2_1+"，表示如果開始數據庫值是null的話就用0.00表示，然后再累加新的數據rkje2_1。
去掉字符串的前后空格
代碼 ：
function trim(str){
return str.replace(/^\ +|\ +$/ig,"");
}
Java實現四舍五入方法
方法一 :
double source = 3.129;
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00"); // 保留小數點后兩位
String aveprice=df.format(source);
方法二 :
double source = 3.129;
BigDecimal bd = new BigDecimal(source);
bd.setScale(2,BigDecimal.ROUND_HALF_EVEN).floatValue() ; // 保留小數點后兩位
各種數據庫取表前N條記錄的寫法：
1．Oracle
Select * from table where rowNum<=n
2.Informix
Select first n* from table
3.DB2
Select * row_number() over(order by col1 desc) as rownum where rownum<=n
DB2
Select column from table fetch first n rows only
4.SqlServer
Select top n* from table
5.sysbase
Select top n* from table
6.mysql
Select * from table limit n