Oracle資料庫基礎--預存程序和函數

標籤：tab   variables   size   text   方法   not   return   connect   res   

一、預存程序和函數

　　預存程序: 對一個模組的封裝
　　函數: 功能與預存程序幾乎一樣

　　區別:
　　　　函數必須通過return 關鍵字返回一個值
　　預存程序:
　　　　不需要return傳回值

　　參數:
　　　　輸入型參數
　　　　輸出型參數
　　　　輸入(輸出)型參數

　　什麼時候用預存程序, 什麼時候用函數
　　　　一般來講, 當只有一個傳回值的時候用函數,
　　　　當沒有傳回值或者需要多個傳回值的時候, 用預存程序

二.首先看一下SQL基礎

先看要舉例的表的內容：

下面開始操作：

首先建一個新的SQL Window：

DECLARE  --聲明變數  /*    引用型變數--目前使用者下表中的某一列    記錄型變數--目前使用者下表中的某一行  */  I NUMBER;  A STUDENT.SNAME%TYPE; --引用型變數，指student表中的sname列  B STUDENT%ROWTYPE; --記錄型變數，目前使用者下表中的某一行，根據附加條件選出是哪一行BEGIN  --開始  I := 107;  SELECT S.SNAME INTO A FROM STUDENT S WHERE S.SNO = I;  DBMS_OUTPUT.PUT_LINE(‘查詢結果，a的值為：‘ || A); --SOL中字串的拼接用 ||  SELECT * INTO B FROM STUDENT S WHERE S.SNO = I;  DBMS_OUTPUT.PUT_LINE(‘查詢結果，b的值為：‘ || B.SNAME); --通過b.~的形式把b中的某一列取出來END; --結尾

 

 三、遊標（cursor）

學會使用遊標遍曆student表中的學生姓名

--遊標（可以看作集合，裡面裝的是一個個記錄型變數），有四個屬性，/*ISOPEN --遊標是否開啟NOTFOUND --bolean值，返回true 或者 falseFOUND --bolean值，返回true 或者 falseROWCOUNT --已經取出的記錄的行數,相當於改變幾行就輸出幾*/DECLARE  STU STUDENT%ROWTYPE;  CURSOR STUS IS    SELECT * FROM STUDENT;BEGIN  OPEN STUS;    LOOP    FETCH STUS      INTO STU;    EXIT WHEN STUS%NOTFOUND;    DBMS_OUTPUT.PUT_LINE(STU.SNAME);  END LOOP;    CLOSE STUS;  END;

 四、預存程序（procedure）

輸入student表中的班級號，輸出學生姓名

第一步：建立SQL Window，編譯一段SQL語句，把編譯好的語句放到了procedures檔案目錄下（這是一個查詢的預存程序）

CREATE OR REPLACE PROCEDURE HANQI(SCLA IN NUMBER) AS  CURSOR STUS IS    SELECT * FROM STUDENT S WHERE S.CLASS = SCLA;  STU STUDENT%ROWTYPE;BEGIN  OPEN STUS;  LOOP    FETCH STUS      INTO STU;    EXIT WHEN STUS%NOTFOUND;    DBMS_OUTPUT.PUT_LINE(STU.SNAME);  END LOOP;  CLOSE STUS;END;

第二步、重建立立一個Test Window，調用一下預存程序

-- Created on 2017/8/18 by ASUS declare   -- Local variables here  i integer;begin  -- Test statements here  hanqi(95031);--在Test視窗中，直接調用就可以了end;


輸出資訊為：

第三、看一下命令視窗 Command Window，也可以調用預存程序

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 Connected as test@XESQL> select * from student;SNO SNAME  SSEX SBIRTHDAY        CLASS--- ------ ---- ----------- ----------108 曾華   男   1977/9/1         95033105 匡明   男   1975/10/2        95031107 王麗   女   1976/1/23        95033101 李軍   男   1976/2/20        95033109 王芳   男   1975/2/10        95031103 陸君   男   1974/6/3         950316 rows selectedSQL> set serveroutput on;SQL> execute hanqi(95031);匡明王芳陸君PL/SQL procedure successfully completed

 

五、函數（function）

函數的構造方法和預存程序類似

CREATE OR REPLACE FUNCTION CAL_ADD(A IN NUMBER, B IN NUMBER) RETURN NUMBER AS  C NUMBER;BEGIN  C := A + B;  RETURN C;END;

直接調用函數：

 

六、輸出型參數（可以用來當作一個預存程序的傳回值，可以是一個，也可以是多個）

建立Sql Window：

CREATE OR REPLACE PROCEDURE HANQI3(SCLA IN NUMBER, VARI OUT NUMBER) ASBEGIN  UPDATE STUDENT S SET S.SSEX = ‘女‘ WHERE S.CLASS = SCLA;  SELECT COUNT(*) INTO VARI FROM STUDENT S WHERE S.CLASS = SCLA;END;

 

建立Test Window：

-- Created on 2017/8/18 by ASUS DECLARE  -- Local variables here  I INTEGER;BEGIN  -- Test statements here  HANQI3(95031, I);  DBMS_OUTPUT.PUT_LINE(‘被修改的記錄條數：‘ || I);--有輸出型參數的得在Text Window下調用，並列印END;

執行結果為：

可以通過輸出型參數，讓預存程序有傳回值

 

Oracle資料庫基礎--預存程序和函數