oracle函數存儲過程

1. oracle中函數和存儲過程的區別和聯系

1. 函數有1個返回值,而存儲過程可以有多個或者沒有。

2. 函數可以在其他語句中直接調用,而存儲過程必須單獨調用。

3. 函數通常用於計算或較為單一的數據功能，存儲過程相對完成更復雜的復合性的數據功能。

4. 最關鍵普通語句每次執行都要編譯，而存儲過程只在創建時編譯之後直接調用，速度更快，在大數據復雜功能時尤其明顯。

5. 存儲過程還可以指定用戶許可權。

2. 107 ORACLE 創建存儲過程，存儲函數

基本概念

存儲過程和存儲函數相當於一個東西。

存儲過程在Oracle里叫procere。

存儲過程沒有返回值。

存儲函數在Oracle里叫function。

存儲函數有返回值。

基本語法

create or replace procere 名字

--create or replace 意思是創建或者替換

as

--可以在此定義參數

begin

語句;

end;

例：

create  or  replace   procere   sayhello

as

--說明 相當與declare

begin

dbms_output.put_line('Hello World');

end;

基本調用

begin

-- Call the procere

  sayhello;

  sayhello;

  sayhello;

   end;

帶參數的存儲過程--查詢某個員工的年收入

create or replace procere upmoney(testname in test_procere.name%type) 

as

begin 

update test_procere t set t.money = t.money + 1000

 where t.name = testname; 

end 

upmoney;

特別的地方，參數要指明是輸入參數還是輸出參數。

存儲函數

create or replace function Fupmoney(tname in varchar2)    return number

as     --定義月薪參數

tmoney test_procere.money%type;

begin

  --得到月薪

  select t.money

    into tmoney

    from test_procere t

  where t.name = tname;

  dbms_output.put_line(tmoney*12);

  return(tmoney*12);

end;

創建一個多輸出參數的存儲函數例子

create or replace procere manyparm(tname in varchar2,

                                    tjob out varchar2,

                                    tmoney out number,

                                    tdept out varchar2)

is

begin

    select t.job,t.money,t.dept

      into tjob,tmoney,tdept

      from test_procere t

      where t.name = tname;

end manyparm;

3. oracle存儲過程基本語法

一：存儲過程

創建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統許可權   

刪除存儲過程，是過程的創建者或者擁有DROP ANY PROCEDURE系統許可權 

修改存儲過程，則只能是過程的創建者或者擁有ALTER ANY PROCEDURE系統許可權的人

執行(或調用)存儲過程，是過程的創建者或是擁有EXECUTE ANY PROCEDURE系統許可權的人或是被擁有者授予EXECUTE許可權的人

1：語法

 1）創建

CREATE[ORREPLACE]PROCEDURE存儲過程名[(參數[IN|OUT|INOUT] 數據類型...)]

{AS|IS}

[說明部分]

BEGIN

可執行部分

[EXCEPTION

錯誤處理部分]

END[過程名];

 說明：

   OR REPLACE 表示如果存在就覆蓋存儲過程

   參數有三種形式：IN、OUT和IN OUT。則默認為IN。

  關鍵字AS也可以寫成IS，後跟過程的說明部分，可以在此定義過程的局部變數。

2）刪除存儲過程：

DROP PROCEDURE 存儲過程名；

3）修改存儲過程：

      ALTER PROCEDURE 存儲過程名 COMPILE； 

4）執行存儲過程

      EXECUTE 模式名.存儲過程名[(參數...)]; 

     或

       BEGIN 

模式名.存儲過程名[(參數...)];

END;

另外：參數可以是變數、常量或表達式

 要其它用戶執行存儲過程須要給其它用戶授權

GRANT EXECUTE ON 存儲過程名 TO 用戶名

 2：參數說明

IN 定義一個輸入參數變數，用於傳遞參數給存儲過程

OUT 定義一個輸出參數變數，用於從存儲過程獲取數據

IN OUT 定義一個輸入、輸出參數變數，兼有以上兩者的功能

 1）參數名 IN 數據類型 DEFAULT 值；

          定義一個輸入參數變數，用於傳遞參數給存儲過程。

          可以是常量、有值變數或表達式

          DEFAULT 關鍵字為可選項，用來設定參數的默認值。如果在調用存儲過程時不指明參數，則參數變數取默認值

          在存儲過程中，輸入變數接收主程序傳遞的值，但不能對其進行賦值。

2）參數名 OUT 數據類型；

          定義一個輸出參數變數，用於從存儲過程獲取數據，即變數從存儲過程中返回值給主程序。

          在調用存儲過程時，主程序的實際參數只能是一個變數，而不能是常量或表達式。

          在存儲過程中，參數變數只能被賦值而不能將其用於賦值，而且必須給輸出變數至少賦值一次。

3）參數名 IN OUT 數據類型 DEFAULT 值； 

          定義一個輸入、輸出參數變數，兼有以上兩者的功能。

          在調用存儲過程時，主程序的實際參數只能是一個變數，而不能是常量或表達式。

          DEFAULT 關鍵字為可選項，用來設定參數的默認值。

          在存儲過程中，變數接收主程序傳遞的值，同時可以參加賦值運算，也可以對其進行賦值。在存儲過程中必須給變數至少賦值一次。

補充：如果省略IN、OUT或IN OUT，則默認模式是IN。 

          調用它時參數個數與位置可以不一致，用以下形式調用：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);  //=>運算符左側是參數名，右側是參數表達式

 二：函數

創建函數，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統許可權

刪除函數，需要是函數的創建者或者是擁有DROP ANY PROCEDURE系統許可權的人

修改函數，需要是函數的創建者或者擁有ALTER ANY PROCEDURE系統許可權的人

執行函數，需要是函數的創建者或擁有EXECUTE ANY PROCEDURE系統許可權的人

1：語法

1）創建：

CREATE[ORREPLACE]FUNCTION函數名[(參數[IN] 數據類型...)]

RETURN數據類型

{AS|IS}

[說明部分]

BEGIN

可執行部分

RETURN(表達式)

[EXCEPTION

    錯誤處理部分]

END[函數名];

 說明：

     參數是可選的，但只能是IN類型(IN關鍵字可以省略)。

     在定義部分的RETURN 數據類型，用來表示函數的數據類型，也就是返回值的類型，不可省略。

     在可執行部分的RETURN(表達式)，用來生成函數的返回值，其表達式的類型應該和定義部分說明的函數返回值的數據類型一致。在函數的執行部分可以有多個RETURN語句，但只有一個RETURN語句會被執行，一旦執行了RETURN語句，則函數結束並返回調用環境。 

2）刪除

      DROP FUNCTION 函數名；

3）修改

      ALTER PROCEDURE 函數名 COMPILE；

4）執行

      變數名:=函數名(...) 

三：對存儲過程 和 函數的查看（可以通過對數據字典【USER_SOURCE】的訪問來查詢存儲過程或函數的有關信息）

 1：查詢某個存儲過程序內容

select TEXT from user_source WHERE NAME='存儲過程名';

 2：查看數據字殿

DESCRIBE USER_SOURCE ; //命令行中

 3：查看存儲過程的參數

DESCRIBE say_hello; //後面是過程名

 4：查看發生編輯錯誤

SHOW ERRORS ;

 5：查詢一個存儲過程或函數是否是有效狀態(即編譯成功)

SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='過程名';//注意大小寫

VALID表示該存儲過程有效(即通過編譯)，INVALID表示存儲過程無效或需要重新編譯。它的狀態會改變，這與它依賴外部表（表刪除修改等操作）有關系

6：查看存儲過程與表的依賴關系

SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='SAY_HELLO';

 說明

NAME為實體名，TYPE為實體類型，REFERENCED_OWNER為涉及到的實體擁有者賬戶，REFERENCED_NAME為涉及到的實體名，REFERENCED_TYPE 為涉及到的實體類型。

問題：

如果一個用戶A被授予執行屬於用戶B的一個存儲過程的許可權，在用戶B的存儲過程中，訪問到用戶C的表，用戶B被授予訪問用戶C的表的許可權，但用戶A沒有被授予訪問用戶C表的許可權，那麼用戶A調用用戶B的存儲過程是失敗的還是成功的呢？答案是成功的。

4. Oracle 存儲過程和函數的區別

1. 返值區別,函數1返值,存儲程通參數返,或者沒
2.調用區別,函數查詢語句直接調用,存儲程必須單獨調用.
函數般情況用計算並返計算結存儲程般用完特定數據操作（比修改、插入資料庫表或執行某些DDL語句等等）

5. oracle 函數中能調用存儲過程嗎

oracle 函數中能調用存儲過程，舉例如下：

1、無參數存儲過程的調用

sql">--創建無參存儲過程
CREATEORREPLACEFUNCTIONstu_procRETURNVARCHAR2IS
--聲明語句段
v_namevarchar2(20);
BEGIN
--執行語句段
SELECTo.snameINTOv_nameFROMstudentowhereo.id=1;
RETURNv_name;
END;

--調用無參存儲過程
DECLARE
BEGIN
DBMS_OUTPUT.put_line('在PL/SQL中列印的結果：'||stu_proc);
END;

2、入參存儲過程的調用

--創建入參存儲過程
CREATEORREPLACEFUNCTIONstu_proc(v_idINNUMBER)RETURNVARCHAR2IS
--聲明語句段
v_namevarchar2(20);
BEGIN
--執行語句段
SELECTo.snameINTOv_nameFROMstudentowhereo.id=v_id;
RETURNv_name;
END;

--調用入參存儲過程
DECLARE
BEGIN
DBMS_OUTPUT.put_line('在PL/SQL中列印的結果：'||stu_proc(1));
END;

6. Oracle 存儲過程和函數的區別

Oracle中函數和存儲過程僅體現在一個地方：

1. 函數有返回值，而存儲過程沒有返回值（當然，兩者都可以通過定義out參數來實現返回值）

其實，Oracle函數和存儲過程差別很小，對比其他資料庫，比如SqlServer：

1. sqlserver中，函數是不能存在非select的dml語句的，換句話說，函數中，sqlserver是不能出現update、delete、insert等語句的

2. 而Oracle中，是十分自由和寬容的，update、delete、insert等語句都可以出現

3. 但因為Oracle的自由度，會引起其它一些問題：如果使用C#來調用Oracle的函數時，會出現因函數含有update等語句而無法調用，只能換成存儲過程或者在函數外再加一層存儲過程來實現調用。
   

7. Oracle中 存儲過程,過程和函數有什麼區別和聯系

過程：函數和存儲過程的統稱。

函數：有且僅有一個返回值，可以用於SQL語句中。 如：x := get_X()，select get_X() from al，返回值為函數調用的結果。

存儲過程：無返回值。要返回結果，必須聲明為out參數。
如：sp_x(x,y,z)，x和y為in類型參數，z為out/in out類型參數。

在oracle中，封裝工具還有自定義類型、包等。包可以封裝過程和函數，包頭類似JAVA中的介面，包體類似JAVA中實現介面的類。