Oracle預存程序入門

 

學習的時候要靜下心去學習，不要心浮氣躁。其中找了一些網上的資料和自己的理解，再做以實踐，由於預存程序內容是比較多的，我這裡只能做個入門，希望大家能自己深入學習。

 

--  文中這個符號，等於注釋的意思

 

1.基本結構
　　CREATE OR REPLACE PROCEDURE 預存程序名字
　　(
　　參數1 IN 資料類型,
　　參數2 IN 資料類型
　　) IS
　　變數1 資料類型;
　　變數2 資料類型;
　　BEGIN

         ********* --預存程序的業務，也就是你想要做的事
　　END 預存程序名字

 

      上面的大寫字母是保留字，資料類型有很多，說幾個基本的，以後靠大家自己去學習，比如：NUMBER,VARCHAR,VARCHAR2,這裡的等於符號是 :=    這裡定義變數的方式比較獨特採用的是

      變數  資料類型;

      eg

      t_name VARCHAR2(100);

      t_age    NUMBER;

 

     如何給變數賦值

     t_name := 'aaaaaaa';

     t_age := 10;

 

 

　　2.SELECT INTO STATEMENT

　　將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條記錄，否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)
　　例子：
　   BEGIN
　　   select col1,col2 into 變數1,變數2 FROM 表名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

      CREATE OR REPLACE PROCEDURE TEST(name in number)
　   IS
　　CURSOR cur IS select * FROM xxx; --這裡是定義一個遊標，把查詢結果放入遊標中，

      --遊標就象指標，大家可以這麼去理解

     
　   BEGIN
　      FOR cur_result in cur LOOP
　         BEGIN
　　         V_SUM :=cur_result.列名;
　         END;
　      END LOOP;
　　END;

 

      看看下面的例子

 

      CREATE OR REPLACE PROCEDURE TEST(name in number)
      is  
      V_SUM varchar2(200);
      cursor cur is select * from LAD_USER;
      BEGIN
         for V_RESULT in cur LOOP
             BEGIN
                   V_SUM := V_RESULT.User_Name;
             END;
         end LOOP;
         DBMS_OUTPUT.put_line(V_SUM); --輸出命令
      END TEST;

 

　　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單步調試

 

      9.運行

      我第一次學習的時候很苯，連預存程序都沒建立就去執行調用命令，請大家別和我一樣。

 

      有2種方式去執行

      1.首先dos環境串連到oracle資料庫

 

       sqlplus 使用者名稱/密碼@資料庫地址，通過這個命令可以串連到資料庫。

 

       比如：sqlplus TEST_ZJ/111111@192.168.10.10_ORCL     

 

      

       這樣接串連上資料庫了，下面把要建立的預存程序複製到一個txt文字檔，然後把文字檔修改為TEST.sql 這樣類型的檔案。

      這裡我直接把EST.sql存在了E:/根目錄

 

     串連上以後，接著執行命令  @TEST

                                            /

   

    執行完這2個命令，預存程序就被建立了。

 

    調用預存程序  call TEST(1); 

 

    這樣dos的建立和調用就結束了，我感覺還是挺麻煩的，不過這是基礎，還是瞭解的好。

 

 

    另一種辦法，就是用工具了，就是第8點說的辦法，下一個PLSQL Developer ，推薦，非常好用，串連上後，直接建立一個預存程序，通過執行，才能把預存程序寫進資料庫，再用Test WinDow進行測試，命令直接寫call TEST(1) 然後點左上方的執行，就可以看到了結果了

 

  

 

 

 

 

 

 

 

 

 

 

 

1.命令格式

 
預存程序是一個PL/SQL程式塊，接受零個或多個參數作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數不同，
預存程序沒有傳回值，預存程序不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程式塊內部調用，定義預存程序的文法如下：PROCEDURE
 Name 
[
(Parameter[,Parameter,
]
)]

IS
|
AS

  
[
Local Declarations
]


BEGIN

  
Execute
 statements;
  
[
EXCEPTION Exception Handlers
]


END
 
[
Name
]
;

2.調用

  預存程序可以直接用EXECUT命令調用或PL/SQL程式塊內部調用。用EXECUT命令調用預存程序的格式如下：SQL
>
EXCUTE  Proc_Name(par1, par2…);  預存程序也可以被另外的PL/SQL塊調用，調用的語句是：DECLARE
 par1, par2;

BEGIN

  Proc_Name(par1, par2…);

END
;

3.釋放

  當某個預存程序不再需要時，應將其從記憶體中刪除，以釋放它佔用的記憶體資源。釋放過程的語句格式如下：SQL
>
DROP
 
PROCEDURE
 Proc_Name;4.執行個體：


  編寫預存程序，顯示所指定僱員名所在的部門名和位置。CREATE
 
OR
 
REPLACE
 
PROCEDURE
 DeptMesg(pename emp.ename
%
TYPE,
                                     pdname OUT dept.dname
%
TYPE,
                                     ploc   OUT dept.loc
%
TYPE) 
AS


BEGIN

  
SELECT
 dname, loc
    
INTO
 pdname, ploc
    
FROM
 emp, dept
   
WHERE
 emp.deptno 
=
 dept.deptno
     
AND
 emp.ename 
=
 pename;

END
;  調用：VARIABLE vdname 
VARCHAR2
(
14
);
VARIABLE vloc 
VARCHAR2
(
13
);

EXECUTE
 DeptMesg(
'
SMITH
'
, :vdname￡? :vloc);

PRINT
 vdname vloc;

 

 

看完後，我自己學習寫的demo

 

 

CREATE OR REPLACE PROCEDURE DeptMesg(peid OUT LAD_USER.USER_ID%TYPE,pename OUT LAD_USER.USER_NAME%TYPE) IS
BEGIN
  SELECT USER_ID, USER_NAME INTO peid,pename
    FROM LAD_USER
   WHERE USER_ID = 1;
END DeptMesg;

 

 

測試代碼

 

-- Created on 2009-7-22 by ADMINISTRATOR
declare
  -- Local variables here
  id integer;
  names varchar2(100);
begin
  -- Test statements here 
  Dbms_Output.put_line('sssssssssssssss');
  DeptMesg(peid => id, pename => names);
  Dbms_Output.put_line('id:'||id);
  Dbms_Output.put_line('names:'||names);
end;

 

 

希望大家能看明白