oracle存儲過程定義

㈠ oracle中的存儲過程,有什麼作用，以及怎麼在代碼中使用

定義：存儲過程（Stored Procere ）是一組為了完成特定功能的sql 語句
集，經編譯後存儲在資料庫中。用戶通過指定存儲過程的名字並給出參數
（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要
對象，任何一個設計良好的資料庫應用程序都應該用到存儲過程。
存儲過程是由流控制和SQL 語句書寫的過程，這個過程經編譯和優化後
存儲在資料庫伺服器中，應用程序使用時只要調用即可。在ORACLE 中，
若干個有聯系的過程可以組合在一起構成程序包。

優點：存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。
2.當對資料庫進行復雜操作時(如對多個表進行Update、Insert、Query、Delete時），可將此復雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。
3.存儲過程可以重復使用,可減少資料庫開發人員的工作量。
4.安全性高,可設定只有某用戶才具有對指定存儲過程的使用權。
proc = connection.prepareCall("{ call SIM_MEDICARE.P_Isinblacklist(?,?,?,?,?) }");
proc.setString(1, 「」);設置傳入參數
proc.registerOutParameter(4, Types.VARCHAR);注冊傳出參數
proc.execute();執行過程
proc.getString(4)獲取結果

㈡ 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的存儲過程是失敗的還是成功的呢？答案是成功的。

㈢ datagrips oracle編寫存儲過程

定義方式：
變數名 類型(長度);

普通變數類型的賦值方式有兩種：
1、通過 := 直接賦值；
2、通過 select (表中變數名) into (定義的普通變數) from ... where ... 賦值;
使用方法如下:

定義方式
變數名 表名.列名%type;
使用方法如下:

定義方式
對象名 表名%rowtype
使用方法如下:

定義
用於臨時存儲一個查詢返回的多行數據，通過遍歷的方式可以逐行訪問處理該結果集的數據；
游標屬性

㈣ oracle存儲過程怎麼寫

存儲過程就是若干sql語句的集合而已。
一般語法就是
create or replace 存儲過程名(參數1，參數2，……) is
sql語句
end 存儲過程名；

㈤ PostgreSQL Oracle 兼容性之存儲過程

 在oracle中，函數和存儲過程是經常使用到的，並且有所區別；而postgresql中函數和存儲過程都是相同定義的。

 1.定義：定義存儲過程的關鍵字為procere。
 2.創建存儲過程

例：
（無參數）

（有參有返）

總結 ：
 1.創建存儲過程的關鍵字為procere。
 2.傳參列表中的參數可以用in，out，in out修飾，參數類型一定不能寫大小。列表中可以有多個輸入輸出參數。
 3.存儲過程中定義的參數列表不需要用declare聲明，聲明參數類型時需要寫大小的一定要帶上大小。
 4.as可以用is替換。
 5.調用帶輸出參數的過程必須要聲明變數來接收輸出參數值。
 6.執行存儲過程有兩種方式，一種是使用execute，另一種是用begin和end包住。

 1.定義：定義函數的關鍵字為function。
 2.創建函數

總結 ：
 1.定義函數的關鍵字為function 。
 2.必須有返回值，且聲明返回值類型時不需要加大小。
 3.函數中定義的參數列表不需要用declare聲明，聲明參數類型時需要寫大小的一定要帶上大小。
 4.as可以用is替換。
  5.執行存儲過程有兩種方式，一種是使用select，另一種是用begin和end包住。

不同點：
 1.存儲過程定義關鍵字用procere，函數定義用function。
 2.存儲過程中不能用return返回值，但函數中可以，而且函數中必須有return子句。
 3.執行方式略有不同，存儲過程的執行方式有兩種（1.使用execute2.使用begin和end），函數除了存儲過程的兩種方式外，還可以當做表達式使用，例如放在select中（select f1() form al;）。

 postgresql則將函數和存儲過程合為一體，不再明確區分存儲過程與函數。
 1.定義：定義函數（存儲過程 ）的關鍵字為function。
 2.創建

例：

 1.必須有有returns（注意是returns不是return）子句，無返回值時returns viod
 2.執行時，有返回值用select，無返回值時用perform
 3.必須指定語言LANGUAGE

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

在oracle中，函數和存儲過程是經常使用到的，他們的語法中有很多相似的地方，但也有自己的特點。剛學完函數和存儲過程，下面來和大家分享一下自己總結的關於函數和存儲過程的區別。
一、存儲過程

1.定義

存儲過程是存儲在資料庫中提供所有用戶程序調用的子程序，定義存儲過程的關鍵字為procere。

2.創建存儲過程

create [or replace] procere 存儲過程名

[(參數1 類型，參數2 out 類型……)]

as

變數名類型；

begin

程序代碼體

end;

示例一：無參無返

create or replace procere p1
--or replace代表創建該存儲過程時，若存儲名存在，則替換原存儲過程，重新創建
--無參數列表時，不需要寫()
as
begin
dbms_output.put_line('hello world');
end;

--執行存儲過程方式1
set serveroutput on;
begin
p1();
end;
--執行存儲過程方式2
set serveroutput on;
execute p1();

示例二：有參有返

create or replace procere p2
(name in varchar2,age int,msg out varchar2)
--參數列表中，聲明變數類型時切記不能寫大小，只寫類型名即可，例如參數列表中的name變數的聲明
--參數列表中，輸入參數用in表示，輸出參數用out表示，不寫時默認為輸入參數。
------------輸入參數不能攜帶值出去，輸出參數不能攜帶值進來，當既想攜帶值進來，又想攜帶值出去，可以用in out
as
begin
msg:='姓名'||name||',年齡'||age;
--賦值時除了可以使用：=，還可以用into來實現
--上面子句等價於select '姓名'||name||',年齡'||age into msg from al;
end;
--執行存儲過程
set serveroutput on;
declare
msg varchar2(100);
begin
p2('張三',23,msg);
dbms_output.put_line(msg);
end;

示例三：參數列表中有in out參數

create or replace procere p3
(msg in out varchar2)
--當既想攜帶值進來，又想攜帶值出去，可以用in out
as
begin
dbms_output.put_line(msg); --輸出的為攜帶進來的值
msg:='我是從存儲過程中攜帶出來的值';
end;

--執行存儲過程
set serveroutput on;
declare
msg varchar2(100):='我是從攜帶進去的值';
begin
p3(msg);
dbms_output.put_line(msg);
end;

示例四：存儲過程中定義參數

create or replace procere p4
as
--存儲過程中定義的參數列表
name varchar(50);
begin
name := 'hello world';
dbms_output.put_line(name);
end;
---執行存儲過程
set serveroutput on;
execute p4();

總結：1.創建存儲過程的關鍵字為procere。

2.傳參列表中的參數可以用in，out，in out修飾，參數類型一定不能寫大小。列表中可以有多個輸入輸出參數。

3.存儲過程中定義的參數列表不需要用declare聲明，聲明參數類型時需要寫大小的一定要帶上大小。

4.as可以用is替換。

5.調用帶輸出參數的過程必須要聲明變數來接收輸出參數值。

6.執行存儲過程有兩種方式，一種是使用execute，另一種是用begin和end包住。

存儲過程雖然有很多優點，但是它卻不能使用return返回值。當需要使用return返回值時，我們可以使用函數。

二、存儲函數

1.函數與存儲過程的結構類似，但是函數必須有一個return子句，用於返回函數值。

create or replace function f1
return varchar--必須有返回值，且聲明返回值類型時不需要加大小
as
msg varchar(50);
begin
msg := 'hello world';
return msg;
end;

--執行函數方式1
select f1() from al;
--執行函數方式2
set serveroutput on;
begin
dbms_output.put_line(f1());
end;

三、存儲過程與存儲函數的區別和聯系

相同點：1.創建語法結構相似，都可以攜帶多個傳入參數和傳出參數。

2.都是一次編譯，多次執行。

不同點：1.存儲過程定義關鍵字用procere，函數定義用function。

2.存儲過程中不能用return返回值，但函數中可以，而且函數中必須有return子句。

3.執行方式略有不同，存儲過程的執行方式有兩種（1.使用execute2.使用begin和end），函數除了存儲過程的兩種方式外，還可以當做表達式使用，例如放在select中（select f1() form al;）。

總結：如果只有一個返回值，用存儲函數，否則，一般用存儲過程。