oracle 儲存過程以及plsql語句塊

this_heart_add_add發表於2016-04-13

1、 PLSQL語法

1.1、程式結構

PL/SQL程式都是以塊（block）為基本單位。整個PL/SQL塊分三部分：宣告部分（用declare開頭）、執行部分（以 begin開頭）和異常處理部分（以exception開頭）。其中執行部分是必須的，其他兩個部分可選。無論PL/SQL程式段的程式碼量有多大，其基本結構就是由這三部分組成。而且每條語句均由分號隔開。

/*宣告部分，以declare開頭*/

DECLARE

--declare statement

/*執行部分，以begin開頭,以end結束*/

BEGIN

--execute statement

/*異常處理部分，以exception開始*/

EXCEPTION

WHEN exceptioncase THEN

--Exception handle

END;

例子：

/*宣告部分，以declare開頭*/

DECLARE

v_job NUMBER(10);

/*執行部分，以begin開頭*/

BEGIN

DELETE FROM employee;

INSERT INTO employee

(depno, job

)

VALUES (20, 30

);

SELECT job

INTO v_job

FROM employee

WHERE depno = 20;

dbms_output.PUT_LINE(v_job);

/*異常處理部分，以exception開始*/

EXCEPTION

WHEN no_data_found THEN

dbms_output.PUT_LINE('No data.');

END;

執行結果：

今天做專案突然用到了oracle的declare，本以為和SqlServer一樣，最後發現還是有一定的語法區別的，現特此總結下：

declare --宣告變數的關鍵字
v_c varchar(200);
abc varchar(20);
begin
abc := '22'; --給宣告的變數賦值，不支援宣告時就賦值
select GAMECODE into v_c from LOTTERY.t_lot_darwnumber t where t.gamecode = abcand rownum <2;
dbms_output.put_line(v_c); --輸出變數值

end;

-學習塊的結構
--學習定義變數（瞭解資料型別）及賦值
--瞭解：各種PL/SQL塊的型別

--塊的結構
--案例：根據工號，輸出員工姓名
SELECT * FROM emp;

--塊！
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
BEGIN
   v_empno := &請輸入工號;
   --必須要將查詢結果放到變數
   SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);
END;

--異常
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
BEGIN
   v_empno := &請輸入工號;
   --必須要將查詢結果放到變數
   SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);

   EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('執行出錯了，老闆！');
END;

--小結
DECLARE
   --變數區(不能在begin..end中定義）
BEGIN
    --業務程式碼區
    excetipn
    --異常捕獲區
END;

--定義變數（瞭解資料型別）
--2種：儲存一個值＝簡單變數；儲存多個值＝複合變數

--簡單變數型別:char,varchar2,number,date(同時也列型別),boolean,表.欄位%type
--複合變數:表%rowtype,record
DECLARE
  v_empno NUMBER(4);
  v_ename VARCHAR2(10);
  v_job emp.job%TYPE; --如果欄位型別改變，指令碼不需要改變
BEGIN
   v_empno := 7566;
   SELECT ename,job INTO v_ename,v_job FROM emp WHERE empno=v_empno;
   dbms_output.put_line(v_ename);
   IF(v_job='MANAGER')THEN
      dbms_output.put_line('經理，吃過飯沒。');
   END IF;
END;

--需要同上，將多個變數放到一個記錄物件中
DECLARE
  v_empno NUMBER(4);
  my_row emp%ROWTYPE;
BEGIN
   v_empno := 7566;
   SELECT * INTO my_row FROM emp WHERE empno=v_empno;
   dbms_output.put_line(my_row.ename||','||my_row.sal);
END;

--有時不需要一整列，只需要其中幾個列。此時用record
DECLARE
    --自定義複合型別 my_emp
   TYPE my_emp_type IS RECORD(
      yg_name emp.ename%TYPE,
      yg_sal emp.sal%TYPE
   );
   my_row my_emp_type; --變數名  資料型別
BEGIN
   SELECT ename,sal INTO my_row FROM emp WHERE empno=7788;
   dbms_output.put_line(my_row.yg_name||','||my_row.yg_sal);
END;

--擴充套件：Oracle沒有boolean型別？
--思考：性別 true=男,false=女。缺點？性別＝保密。所以Oracle建立用char(1)替代，1＝男2＝女3＝保密4＝人妖

--Oracle的各種集合型別，對應 java的陣列。
--引用變數：在講解儲存過程以後再講解。

--繫結變數(命令視窗中執行，繫結變數用了 :）
SQL> var name varchar2(10)
SQL> execute :name :='hello';
PL/SQL procedure successfully completed
name
---------
hello

SQL> print name;
name
---------
hello

Oracle儲存過程學習

儲存過程建立語法：

create or replace procedure 儲存過程名（param1 in type，param2 out type）

as 【這個as類似declare】

變數1 型別（值範圍）;

變數2 型別（值範圍）;

Begin

Select count(*) into 變數1 from 表A where列名=param1；

If (判斷條件) then

Select 列名 into 變數2 from 表A where列名=param1；

Dbms_output。Put_line(‘列印資訊’);

Elsif (判斷條件) then

Dbms_output。Put_line(‘列印資訊’);

Else

Raise 異常名（NO_DATA_FOUND）;

End if;

Exception

When others then

Rollback;

End;

注意事項：

1，儲存過程引數不帶取值範圍，in表示傳入，out表示輸出

2，變數帶取值範圍，後面接分號

3，在判斷語句前最好先用count（*）函式判斷是否存在該條操作記錄

4，用select 。。。into。。。給變數賦值

5，在程式碼中拋異常用 raise+異常名

以命名的異常

命名的系統異常產生原因

ACCESS_INTO_NULL 未定義物件

CASE_NOT_FOUND CASE 中若未包含相應的 WHEN ，並且沒有設定

ELSE 時

COLLECTION_IS_NULL 集合元素未初始化

CURSER_ALREADY_OPEN 遊標已經開啟

DUP_VAL_ON_INDEX 唯一索引對應的列上有重複的值

INVALID_CURSOR 在不合法的遊標上進行操作

INVALID_NUMBER 內嵌的 SQL 語句不能將字元轉換為數字

NO_DATA_FOUND 使用 select into 未返回行，或應用索引表未初始化的

TOO_MANY_ROWS 執行 select into 時，結果集超過一行

ZERO_DIVIDE 除數為 0

SUBSCRIPT_BEYOND_COUNT 元素下標超過巢狀表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT 使用巢狀表或 VARRAY 時，將下標指定為負數

VALUE_ERROR 賦值時，變數長度不足以容納實際資料

LOGIN_DENIED PL/SQL 應用程式連線到 oracle 資料庫時，提供了不

正確的使用者名稱或密碼

NOT_LOGGED_ON PL/SQL 應用程式在沒有連線 oralce 資料庫的情況下

訪問資料

PROGRAM_ERROR PL/SQL 內部問題，可能需要重灌資料字典＆ pl./SQL

系統包

ROWTYPE_MISMATCH 宿主遊標變數與 PL/SQL 遊標變數的返回型別不相容

SELF_IS_NULL 使用物件型別時，在 null 物件上呼叫物件方法

STORAGE_ERROR 執行 PL/SQL 時，超出記憶體空間

SYS_INVALID_ID 無效的 ROWID 字串

TIMEOUT_ON_RESOURCE Oracle 在等待資源時超時
---------------------------------------------------------------------------------------------------------------------

oracle 儲存過程的基本語法及注意事項

oracle 儲存過程的基本語法

1.基本結構
CREATE OR REPLACE PROCEDURE 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN

END 儲存過程名字

2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
     xxxx;
END;
...

3.IF 判斷
IF V_TEST=1 THEN
    BEGIN
       do something
    END;
END IF;

4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

5.變數賦值
V_TEST := 123;

6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
END LOOP;
END;

7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
    do something
END LOOP;
CLOSE C_USER;

8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯

關於oracle儲存過程的若干問題備忘

1.在oracle中，資料表別名不能加as，如：

select a.appname from appinfo a;-- 正確

select a.appname from appinfo as a;-- 錯誤

也許，是怕和oracle中的儲存過程中的關鍵字as衝突的問題吧

2.在儲存過程中，select某一欄位時，後面必須緊跟into，如果select整個記錄，利用遊標的話就另當別論了。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正確編譯

select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 沒有into，編譯報錯，提示：Compilation

Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...語法時，必須先確保資料庫中有該條記錄，否則會報出"no data found"異常。

可以在該語法之前，先利用select count(*) from 檢視資料庫中是否存在該記錄，如果存在，再利用select...into...

4.在儲存過程中，別名不能和欄位名稱相同，否則雖然編譯可以通過，但在執行階段會報錯

select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正確執行

select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 執行階段報錯，提示

ORA-01422:exact fetch returns more than requested number of rows

5.在儲存過程中，關於出現null的問題

假設有一個表A，定義如下：

create table A(

id varchar2(50) primary key not null,

vcount number(8) not null,

bid varchar2(50) not null -- 外來鍵

);

如果在儲存過程中，使用如下語句：

select sum(vcount) into fcount from A where bid='xxxxxx';

如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時設定了預設值，如：fcount number(8):=0依然無效，fcount還是會變成null)，這樣以後使用fcount時就可能有問題，所以在這裡最好先判斷一下：

if fcount is null then

fcount:=0;

end if;

這樣就一切ok了。

6.Hibernate呼叫oracle儲存過程

this.pnumberManager.getHibernateTemplate().execute(

new HibernateCallback() {

public Object doInHibernate(Session session)

throws HibernateException, SQLException {

CallableStatement cs = session

.connection()

.prepareCall("{call modifyapppnumber_remain(?)}");

cs.setString(1, foundationid);

cs.execute();

return null;

}

});

oracle儲存過程和遊標的使用

(2011-04-19 14:52:47)

轉載▼

標籤：

布林

遊標

語句

變數

oracle資料庫

oracle儲存過程

雜談

遊標：

用來查詢資料庫，獲取記錄集合（結果集）的指標，我們所說的遊標通常是指顯式遊標，因此從現在起沒有特別指明的情況，我們所說的遊標都是指顯式遊標。要在程式中使用遊標，必須首先宣告遊標分類：

靜態遊標：
分為顯式遊標和隱式遊標。

REF遊標：
是一種引用型別，類似於指標。

顯式遊標：

CURSOR 遊標名 ( 引數 ) [返回值型別] IS
Select 語句

生命週期：

在大多數時候我們在設計程式的時候都遵循下面的步驟：
　1、開啟遊標 open cs1;
　2、開始迴圈 while cs1%found loop | for column_name in .. LOOP
　3、從遊標中取值 fetch .. into.. |
　4、檢查那一行被返回
　5、處理
　6、關閉迴圈 end loop;
　7、關閉遊標 if cs1&isopen then close cs1;

選項：引數和返回型別

set serveroutput on
declare
cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;

l_emp employees%rowtype;
begin
dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;

dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
end loop;
close emp_cur;
end;
/

隱式遊標：

不用明確建立遊標變數，分兩種：
1.在PL/SQL中使用DML語言，使用ORACLE提供的名為SQL的隱示遊標
2.CURSOR FOR LOOP，用於for loop 語句

1舉例：

declare
begin
update departments set department_name=department_name;
--where 1=2;

dbms_output.put_line('update '|| sql%rowcount ||' records');
end;
/

2舉例：

declare
begin
for my_dept_rec in ( select department_name, department_id from departments)
loop
dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
end loop;
end;
/

3舉例：

單獨select

declare
l_empno emp.EMPLOYEE_ID%type;
-- l_ename emp.ename%type;
begin
select EMPLOYEE_ID
into l_empno
from emp;
--where rownum =1;
dbms_output.put_line(l_empno);
end;
/
使用INTO獲取值，只能返回一行。

遊標屬性：

%FOUND：變數最後從遊標中獲取記錄的時候，在結果集中找到了記錄。
%NOTFOUND：變數最後從遊標中獲取記錄的時候，在結果集中沒有找到記錄。
%ROWCOUNT：當前時刻已經從遊標中獲取的記錄數量。
%ISOPEN：是否開啟。

Declare
Cursor emps is
Select * from employees where rownum<6 order by 1;

Emp employees%rowtype;
Row number :=1;
Begin
Open emps;
Fetch emps into emp;

Loop
  If emps%found then
   Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount);
   Fetch emps into emp;
   Row := row + 1;
  Elsif emps%notfound then
   Exit;  ---exit loop, not IF
  End if;
End loop;

If emps%isopen then
  Close emps;
End if;
End;
/

顯式和隱式遊標的區別：

儘量使用隱式遊標，避免編寫附加的遊標控制程式碼（宣告，開啟，獲取，關閉），也不需要宣告變數來儲存從遊標中獲取的資料。

REF CURSOR遊標：

動態遊標，在執行的時候才能確定遊標使用的查詢。分類：
強型別（限制）REF CURSOR，規定返回型別
弱型別（非限制）REF CURSOR，不規定返回型別，可以獲取任何結果集。

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

Declare
Type refcur_t is ref cursor;

Type emp_refcur_t is ref cursor return employee%rowtype;
Begin
Null;
End;
/

強型別舉例：

declare
--宣告記錄型別
type emp_job_rec is record(
  employee_id number,
  employee_name varchar2(50),
  job_title varchar2(30)
);
--宣告REF CURSOR，返回值為該記錄型別
type emp_job_refcur_type is ref cursor
  return emp_job_rec;
--定義REF CURSOR遊標的變數
emp_refcur emp_job_refcur_type;

emp_job emp_job_rec;
begin
open emp_refcur for
  select e.employee_id,
    e.first_name || ' ' ||e.last_name "employee_name",
    j.job_title
  from employees e, jobs j
  where e.job_id = j.job_id and rownum < 11 order by 1;

fetch emp_refcur into emp_job;
while emp_refcur%found loop
  dbms_output.put_line(emp_job.employee_name || '''s job is ');
  dbms_output.put_line(emp_job.job_title);
  fetch emp_refcur into emp_job;
end loop;
end;
----------------------------------------------------------------------------------------

Oracle資料庫遊標使用大全
SQL是用於訪問ORACLE資料庫的語言，PL/SQL擴充套件和加強了SQL的功能，它同時引入了更
強的程式邏輯。 PL/SQL支援DML命令和SQL的事務控制語句。DDL在PL/SQL中不被支援，這就
意味作在PL/SQL程式塊中不能建立表或其他任何物件。較好的PL/SQL程式設計是在PL/SQL塊中
使用象DBMS_SQL這樣的內建包或執行EXECUTE IMMEDIATE命令建立動態SQL來執行DDL命令，
　下面我們將討論各種用於訪問ORACLE資料庫的DDL和TCL語句。
　查詢
　SELECT語句用於從資料庫中查詢資料，當在PL/SQL中使用SELECT語句時，要與INTO子
句一起使用，查詢的返回值被賦予INTO子句中的變數，變數的宣告是在DELCARE中。SELECT
　　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.

　其他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%FOUND和SQL%NOTFOUND
　在執行任何DML語句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在執行DML語句後，
　. 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，同時產生一個異常
　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用於設定事務屬性，比如
　顯式遊標
　當查詢返回結果超過一行時，就需要一個顯式遊標，此時使用者不能使用select into
語句。PL/SQL管理隱式遊標，當查詢開始時隱式遊標開啟，查詢結束時隱式遊標自動關閉。顯
式遊標在PL/SQL塊的宣告部分宣告，在執行部分或異常處理部分開啟，取資料,關閉。下表顯
　表1 隱式遊標和顯式遊標
　　[[The No.1 Picture.]]
　　使用遊標
　這裡要做一個宣告，我們所說的遊標通常是指顯式遊標，因此從現在起沒有特別指明
的情況，我們所說的遊標都是指顯式遊標。要在程式中使用遊標，必須首先宣告遊標。
　宣告遊標
　語法：
　　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

附[WHERE CURRENT OF ]:

where   current   of   子句只能在定義遊標的時候使用了   for   update語句才可以使用。
        for   v_sor   in   t_sor   loop
        update   aa   set   value='3'   where   current   of   v_sor;       --已限制了條件了，更新只是當前記錄集
        end   loop;
      上面的語句中   v_sor   是   t_sor中的一行資料，更新的時候應該用   current   of   t_sor吧，因為v_sor只是一個隱式遊標，它本身不是通過   for   update定義的，只是代表了迴圈中t_sor的當前記錄。這樣，你的update語句或者delete語句的作用範圍就只在你迴圈的當前行的範圍中了。
      要注意的是，用for   update定義的遊標會讓資料庫對涉及的行加鎖，別的會話如果要訪問該遊標中的行便會進入等待狀態。你也可以明確指明要加鎖的列，用   for   update   of   "列名"     就可以了。
      如果你的select   for   update選中的行已經被別的會話加鎖的話,會話就需要等待解鎖，如果別的會話一直不解鎖，那麼你的select就會一直等待下去，如果你不想等,只需在for   update後面加上nowait就可以解決這個問題了,這樣你的選擇會立即返回。

Oracle儲存過程乾貨（二）：PLSQL控制語句
2024-03-06
Oracle儲存過程SQL
Oracle儲存過程乾貨（三）：PLSQL迴圈語句
2024-03-07
Oracle儲存過程SQL
oracle plsql儲存過程
2012-05-11
OracleSQL儲存過程
Oracle 儲存過程中的DDL語句
2011-10-10
Oracle儲存過程
MySQL儲存過程語句及呼叫
2018-11-30
MySql儲存過程
oracle plsql package_body_procedure_構建語法_sp_儲存過程
2010-06-28
OracleSQLPackage儲存過程
Oracle儲存過程基本語法
2013-05-23
Oracle儲存過程
oracle plsql儲存過程_運算子優先順序
2012-05-11
OracleSQL儲存過程
PostgreSQL獲取建表語句儲存過程
2021-06-07
SQL儲存過程
EF中使用SQL語句或儲存過程
2018-04-10
SQL儲存過程
oracle儲存過程plsql_sp_多個out引數測試語法
2010-07-01
Oracle儲存過程SQL
oracle plsql儲存過程_if判斷與事務commit
2012-05-10
OracleSQL儲存過程MIT
oracle plsql儲存過程_while loop_end loop_exit
2012-05-11
OracleSQL儲存過程WhileOOP
儲存過程與 SQL Server語句大比拼
2008-07-03
儲存過程SQLServer
在.NET中用儲存過程執行SQL語句
2010-04-20
儲存過程SQL
Oracle儲存過程
2020-11-27
Oracle儲存過程
通過SQL語句提取儲存過程中的內容
2010-12-14
SQL儲存過程
PLSQL學習-【7儲存過程、函式】
2014-11-24
SQL儲存過程函式
oracle plsql儲存過程除錯出錯_PLS-00361
2010-07-02
OracleSQL儲存過程除錯
儲存過程語法
2011-12-13
儲存過程
儲存過程if,for,while語句,陣列和遊標使用
2012-08-24
儲存過程While陣列
Oracle中定義package以及儲存過程的使用
2015-12-23
OraclePackage儲存過程
Oracle儲存過程乾貨（一）：儲存過程基礎
2024-03-05
Oracle儲存過程
Oracle儲存過程-1
2021-09-09
Oracle儲存過程
oracle的儲存過程
2018-04-02
Oracle儲存過程
Oracle儲存過程例子
2013-12-25
Oracle儲存過程
Oracle建立儲存過程
2014-08-10
Oracle儲存過程
ORACLE 儲存過程示例
2009-12-02
Oracle儲存過程
在toad_plsql developer_oracle sql developer呼叫儲存過程
2010-06-30
SQLDeveloperOracle儲存過程
儲存過程基本語法
2010-12-14
儲存過程
從sybase的儲存過程轉向oracle的儲存過程
2007-03-05
儲存過程Oracle
Mysql儲存過程變數，條件，迴圈語句用法
2018-06-26
MySql儲存過程變數
asp.net分頁的SQL語句及儲存過程
2011-10-26
ASP.NETSQL儲存過程
mySQL語法中的儲存過程及if語句的使用簡例
2017-03-27
MySql儲存過程
mysql 儲存過程，以及mybatis如何呼叫
2016-08-30
MySql儲存過程MyBatis
原創：oracle 儲存過程
2020-04-06
Oracle儲存過程
oracle 儲存過程批次提交
2016-10-17
Oracle儲存過程
oracle 儲存過程學習
2014-07-02
Oracle儲存過程

oracle 儲存過程以及plsql語句塊

oracle 儲存過程的基本語法

關於oracle儲存過程的若干問題備忘

oracle儲存過程和遊標的使用

相關文章