oracle区号,Oracle 存儲過程
1.創建和刪除存儲過程
創建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統權限。該權限可由系統管理員授予。創建一個存儲過程的基本語句如下：
CREATE [OR REPLACE] PROCEDURE 存儲過程名[(參數[IN|OUT|IN OUT] 數據類型...)]
{AS|IS}
[說明部分]
BEGIN
可執行部分
[EXCEPTION
錯誤處理部分]
END [過程名];
其中：
可選關鍵字OR REPLACE 表示如果存儲過程已經存在，則用新的存儲過程覆蓋，通常用于存儲過程的重建。
參數部分用于定義多個參數(如果沒有參數，就可以省略)。參數有三種形式：IN、OUT和IN OUT。如果沒有指明參數的形式，則默認為IN。
關鍵字AS也可以寫成IS，后跟過程的說明部分，可以在此定義過程的局部變量。
編寫存儲過程可以使用任何文本編輯器或直接在SQL*Plus環境下進行，編寫好的存儲過程必須要在SQL*Plus環境下進行編譯，生成編譯代碼，原代碼和編譯代碼在編譯過程中都會被存入數據庫。編譯成功的存儲過程就可以在Oracle環境下進行調用了。
一個存儲過程在不需要時可以刪除。刪除存儲過程的人是過程的創建者或者擁有DROP ANY PROCEDURE系統權限的人。刪除存儲過程的語法如下：
DROP PROCEDURE 存儲過程名；
如果要重新編譯一個存儲過程，則只能是過程的創建者或者擁有ALTER ANY PROCEDURE系統權限的人。語法如下：
ALTER PROCEDURE 存儲過程名 COMPILE；
執行(或調用)存儲過程的人是過程的創建者或是擁有EXECUTE ANY PROCEDURE系統權限的人或是被擁有者授予EXECUTE權限的人。執行的方法如下：
方法1：
EXECUTE 模式名.存儲過程名[(參數...)];
方法2：
BEGIN
模式名.存儲過程名[(參數...)];
END;
傳遞的參數必須與定義的參數類型、個數和順序一致(如果參數定義了默認值，則調用時可以省略參數)。參數可以是變量、常量或表達式，用法參見下一節。
如果是調用本賬戶下的存儲過程，則模式名可以省略。要調用其他賬戶編寫的存儲過程，則模式名必須要添加。
以下是一個生成和調用簡單存儲過程的訓練。注意要事先授予創建存儲過程的權限。
【訓練1】? 創建一個顯示雇員總人數的存儲過程。
步驟1：登錄SCOTT賬戶(或學生個人賬戶)。
步驟2：在SQL*Plus輸入區中，輸入以下存儲過程：
CREATE OR REPLACE PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM EMP;
DBMS_OUTPUT.PUT_LINE('雇員總人數為：'||V_TOTAL);
END;
步驟3：按“執行”按鈕進行編譯。
如果存在錯誤，就會顯示:
警告: 創建的過程帶有編譯錯誤。
如果存在錯誤，對腳本進行修改，直到沒有錯誤產生。
如果編譯結果正確，將顯示：
過程已創建。
步驟4：調用存儲過程，在輸入區中輸入以下語句并執行：
EXECUTE EMP_COUNT;
顯示結果為：
雇員總人數為：14
PL/SQL 過程已成功完成。
說明：在該訓練中，V_TOTAL變量是存儲過程定義的局部變量，用于接收查詢到的雇員總人數。
注意：在SQL*Plus中輸入存儲過程，按“執行”按鈕是進行編譯，不是執行存儲過程。
如果在存儲過程中引用了其他用戶的對象，比如表，則必須有其他用戶授予的對象訪問權限。一個存儲過程一旦編譯成功，就可以由其他用戶或程序來引用。但存儲過程或函數的所有者必須授予其他用戶執行該過程的權限。
存儲過程沒有參數，在調用時，直接寫過程名即可。
【訓練2】? 在PL/SQL程序中調用存儲過程。
步驟1：登錄SCOTT賬戶。
步驟2：授權STUDENT賬戶使用該存儲過程，即在SQL*Plus輸入區中，輸入以下的命令：
GRANT EXECUTE ON EMP_COUNT TO STUDENT
授權成功。
步驟3：登錄STUDENT賬戶，在SQL*Plus輸入區中輸入以下程序：
SET SERVEROUTPUT ON
BEGIN
SCOTT.EMP_COUNT;
END;
步驟4：執行以上程序，結果為：
雇員總人數為：14
PL/SQL 過程已成功完成。
說明：在本例中，存儲過程是由SCOTT賬戶創建的，STUDEN賬戶獲得SCOTT賬戶的授權后，才能調用該存儲過程。
注意：在程序中調用存儲過程，使用了第二種語法。
【訓練3】? 編寫顯示雇員信息的存儲過程EMP_LIST，并引用EMP_COUNT存儲過程。
步驟1：在SQL*Plus輸入區中輸入并編譯以下存儲過程：
CREATE OR REPLACE PROCEDURE EMP_LIST
AS
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
FOR Emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
END LOOP;
EMP_COUNT;
END;
執行結果：
過程已創建。
步驟2：調用存儲過程，在輸入區中輸入以下語句并執行：
EXECUTE EMP_LIST
顯示結果為：
7369SMITH
7499ALLEN
7521WARD
7566JONES
執行結果：
雇員總人數為：14
PL/SQL 過程已成功完成。
說明：以上的EMP_LIST存儲過程中定義并使用了游標，用來循環顯示所有雇員的信息。然后調用已經成功編譯的存儲過程EMP_COUNT，用來附加顯示雇員總人數。通過EXECUTE命令來執行EMP_LIST存儲過程。
【練習1】編寫顯示部門信息的存儲過程DEPT_LIST，要求統計出部門個數。
2? 參數傳遞
參數的作用是向存儲過程傳遞數據，或從存儲過程獲得返回結果。正確的使用參數可以大大增加存儲過程的靈活性和通用性。
參數的類型有三種，如下所示。
in? 傳入參數，默認值
out輸出參數
inout定義一個輸入輸出參數，在賦值時為輸入參數，輸出時為輸出參數
參數的定義形式和作用如下：
參數名 IN 數據類型 DEFAULT 值；
定義一個輸入參數變量，用于傳遞參數給存儲過程。在調用存儲過程時，主程序的實際參數可以是常量、有值變量或表達式等。DEFAULT 關鍵字為可選項，用來設定參數的默認值。如果在調用存儲過程時不指明參數，則參數變量取默認值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進行賦值。
參數名 OUT 數據類型；
定義一個輸出參數變量，用于從存儲過程獲取數據，即變量從存儲過程中返回值給主程序。
在調用存儲過程時，主程序的實際參數只能是一個變量，而不能是常量或表達式。在存儲過程中，參數變量只能被賦值而不能將其用于賦值，在存儲過程中必須給輸出變量至少賦值一次。
參數名 IN OUT 數據類型 DEFAULT 值；
定義一個輸入、輸出參數變量，兼有以上兩者的功能。在調用存儲過程時，主程序的實際參數只能是一個變量，而不能是常量或表達式。DEFAULT 關鍵字為可選項，用來設定參數的默認值。在存儲過程中，變量接收主程序傳遞的值，同時可以參加賦值運算，也可以對其進行賦值。在存儲過程中必須給變量至少賦值一次。
如果省略IN、OUT或IN OUT，則默認模式是IN。
【訓練1】? 編寫給雇員增加工資的存儲過程CHANGE_SALARY，通過IN類型的參數傳遞要增加工資的雇員編號和增加的工資額。
步驟1：登錄SCOTT賬戶。
步驟2：在SQL*Plus輸入區中輸入以下存儲過程并執行：
CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
AS
V_ENAME VARCHAR2(10);
V_SAL NUMBER(5);
BEGIN
SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('雇員'||V_ENAME||'的工資被改為'||TO_CHAR(V_SAL+P_RAISE));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('發生錯誤，修改失敗！');
ROLLBACK;
END;
執行結果為：
過程已創建。
步驟3：調用存儲過程，在輸入區中輸入以下語句并執行：
EXECUTE CHANGE_SALARY(7788,80)
顯示結果為：
雇員SCOTT的工資被改為3080
說明：從執行結果可以看到，雇員SCOTT的工資已由原來的3000改為3080。
參數的值由調用者傳遞，傳遞的參數的個數、類型和順序應該和定義的一致。如果順序不一致，可以采用以下調用方法。如上例，執行語句可以改為：
EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);
可以看出傳遞參數的順序發生了變化，并且明確指出了參數名和要傳遞的值，=>運算符左側是參數名，右側是參數表達式，這種賦值方法的意義較清楚。
【練習1】創建插入雇員的存儲過程INSERT_EMP，并將雇員編號等作為參數。
在設計存儲過程的時候，也可以為參數設定默認值，這樣調用者就可以不傳遞或少傳遞參數了。
【訓練2】? 調用存儲過程CHANGE_SALARY，不傳遞參數，使用默認參數值。
在SQL*Plus輸入區中輸入以下命令并執行：
EXECUTE CHANGE_SALARY
顯示結果為：
雇員SCOTT的工資被改為3090
說明：在存儲過程的調用中沒有傳遞參數，而是采用了默認值7788和10，即默認雇員號為7788，增加的工資為10。
【訓練3】? 使用OUT類型的參數返回存儲過程的結果。
步驟1：登錄SCOTT賬戶。
步驟2：在SQL*Plus輸入區中輸入并編譯以下存儲過程：
CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
AS
BEGIN
SELECT COUNT(*) INTO P_TOTAL FROM EMP;
END;
執行結果為：
過程已創建。
步驟3：輸入以下程序并執行：
DECLARE
V_EMPCOUNT NUMBER;
BEGIN
EMP_COUNT(V_EMPCOUNT);
DBMS_OUTPUT.PUT_LINE('雇員總人數為：'||V_EMPCOUNT);
END;
顯示結果為：
雇員總人數為：14
PL/SQL 過程已成功完成。
說明：在存儲過程中定義了OUT類型的參數P_TOTAL，在主程序調用該存儲過程時，傳遞了參數V_EMPCOUNT。在存儲過程中的SELECT...INTO...語句中對P_TOTAL進行賦值，賦值結果由V_EMPCOUNT變量帶回給主程序并顯示。
以上程序要覆蓋同名的EMP_COUNT存儲過程，如果不使用OR REPLACE選項，就會出現以下錯誤：
ERROR 位于第 1 行:
ORA-00955: 名稱已由現有對象使用。
【練習2】創建存儲過程，使用OUT類型參數獲得雇員經理名。
【訓練4】? 使用IN OUT類型的參數，給電話號碼增加區碼。
步驟1：登錄SCOTT賬戶。
步驟2：在SQL*Plus輸入區中輸入并編譯以下存儲過程：
CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
AS
BEGIN
P_HPONE_NUM:='0755-'||P_HPONE_NUM;
END;
執行結果為：
過程已創建。
步驟3：輸入以下程序并執行：
SET SERVEROUTPUT ON
DECLARE
V_PHONE_NUM VARCHAR2(15);
BEGIN
V_PHONE_NUM:='26731092';
ADD_REGION(V_PHONE_NUM);
DBMS_OUTPUT.PUT_LINE('新的電話號碼：'||V_PHONE_NUM);
END;
顯示結果為：
新的電話號碼：0755-26731092
PL/SQL 過程已成功完成。
說明：變量V_HPONE_NUM既用來向存儲過程傳遞舊電話號碼，也用來向主程序返回新號碼。新的號碼在原來基礎上增加了區號0755和-。
總結
以上是生活随笔為你收集整理的oracle区号,Oracle 存儲過程的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： oracle 12c安装psu,Orac
- 下一篇： oracle数据库缓存 c,Oracle