今天小編給各位分享存儲過程語法的知識，其中也會對sql server存儲過程語法進(jìn)行解釋，如果能碰巧解決你現(xiàn)在面臨的問題，別忘了關(guān)注本站，現(xiàn)在開始吧！

oracle存儲過程基本語法

一：存儲過程

創(chuàng)建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限? ?

刪除存儲過程，是過程的創(chuàng)建者或者擁有DROP ANY PROCEDURE系統(tǒng)權(quán)限?

修改存儲過程，則只能是過程的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人

執(zhí)行(或調(diào)用)存儲過程，是過程的創(chuàng)建者或是擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE權(quán)限的人

1：語法

?1）創(chuàng)建

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

{AS|IS}

[說明部分]

BEGIN

可執(zhí)行部分

[EXCEPTION

錯(cuò)誤處理部分]

END[過程名];

?說明：

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

? ?參數(shù)有三種形式：IN、OUT和IN OUT。則默認(rèn)為IN。

??關(guān)鍵字AS也可以寫成IS，后跟過程的說明部分，可以在此定義過程的局部變量。

2）刪除存儲過程：

DROP PROCEDURE 存儲過程名；

3）修改存儲過程：

? ? ? ALTER PROCEDURE 存儲過程名 COMPILE；?

4）執(zhí)行存儲過程

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

? ? ?或

? ? ? ?BEGIN?

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

END;

另外：參數(shù)可以是變量、常量或表達(dá)式

?要其它用戶執(zhí)行存儲過程須要給其它用戶授權(quán)

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

?2：參數(shù)說明

IN 定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程

OUT 定義一個(gè)輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)

IN OUT 定義一個(gè)輸入、輸出參數(shù)變量，兼有以上兩者的功能

?1）參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值；

? ? ? ? ??定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程。

? ? ? ? ??可以是常量、有值變量或表達(dá)式

? ? ? ? ??DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。如果在調(diào)用存儲過程時(shí)不指明參數(shù)，則參數(shù)變量取默認(rèn)值

? ? ? ? ??在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進(jìn)行賦值。

2）參數(shù)名 OUT 數(shù)據(jù)類型；

? ? ? ? ??定義一個(gè)輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)，即變量從存儲過程中返回值給主程序。

? ? ? ? ? 在調(diào)用存儲過程時(shí)，主程序的實(shí)際參數(shù)只能是一個(gè)變量，而不能是常量或表達(dá)式。

? ? ? ? ??在存儲過程中，參數(shù)變量只能被賦值而不能將其用于賦值，而且必須給輸出變量至少賦值一次。

3）參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值；?

? ? ? ? ??定義一個(gè)輸入、輸出參數(shù)變量，兼有以上兩者的功能。

? ? ? ? ??在調(diào)用存儲過程時(shí)，主程序的實(shí)際參數(shù)只能是一個(gè)變量，而不能是常量或表達(dá)式。

? ? ? ? ??DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。

? ? ? ? ??在存儲過程中，變量接收主程序傳遞的值，同時(shí)可以參加賦值運(yùn)算，也可以對其進(jìn)行賦值。在存儲過程中必須給變量至少賦值一次。

補(bǔ)充：如果省略IN、OUT或IN OUT，則默認(rèn)模式是IN。?

? ? ? ? ? 調(diào)用它時(shí)參數(shù)個(gè)數(shù)與位置可以不一致，用以下形式調(diào)用：

EXECUTE CHANGE_SALARY(P_RAISE=80,P_EMPNO=7788); ?//=運(yùn)算符左側(cè)是參數(shù)名，右側(cè)是參數(shù)表達(dá)式

?二：函數(shù)

創(chuàng)建函數(shù)，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限

刪除函數(shù)，需要是函數(shù)的創(chuàng)建者或者是擁有DROP ANY PROCEDURE系統(tǒng)權(quán)限的人

修改函數(shù)，需要是函數(shù)的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人

執(zhí)行函數(shù)，需要是函數(shù)的創(chuàng)建者或擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人

1：語法

1）創(chuàng)建：

CREATE[ORREPLACE]FUNCTION函數(shù)名[(參數(shù)[IN] 數(shù)據(jù)類型…)]

RETURN數(shù)據(jù)類型

{AS|IS}

[說明部分]

BEGIN

可執(zhí)行部分

RETURN(表達(dá)式)

[EXCEPTION

? ? 錯(cuò)誤處理部分]

END[函數(shù)名];

?說明：

? ? ?參數(shù)是可選的，但只能是IN類型(IN關(guān)鍵字可以省略)。

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

? ? ?在可執(zhí)行部分的RETURN(表達(dá)式)，用來生成函數(shù)的返回值，其表達(dá)式的類型應(yīng)該和定義部分說明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個(gè)RETURN語句，但只有一個(gè)RETURN語句會被執(zhí)行，一旦執(zhí)行了RETURN語句，則函數(shù)結(jié)束并返回調(diào)用環(huán)境。?

2）刪除

? ? ??DROP FUNCTION 函數(shù)名；

3）修改

? ? ??ALTER PROCEDURE 函數(shù)名 COMPILE；

4）執(zhí)行

? ? ??變量名:=函數(shù)名(…)?

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

?1：查詢某個(gè)存儲過程序內(nèi)容

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

?2：查看數(shù)據(jù)字殿

DESCRIBE USER_SOURCE ; //命令行中

?3：查看存儲過程的參數(shù)

DESCRIBE say_hello; //后面是過程名

?4：查看發(fā)生編輯錯(cuò)誤

SHOW ERRORS ;

?5：查詢一個(gè)存儲過程或函數(shù)是否是有效狀態(tài)(即編譯成功)

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

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

6：查看存儲過程與表的依賴關(guān)系

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

?說明

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

問題：

如果一個(gè)用戶A被授予執(zhí)行屬于用戶B的一個(gè)存儲過程的權(quán)限，在用戶B的存儲過程中，訪問到用戶C的表，用戶B被授予訪問用戶C的表的權(quán)限，但用戶A沒有被授予訪問用戶C表的權(quán)限，那么用戶A調(diào)用用戶B的存儲過程是失敗的還是成功的呢？答案是成功的。

在SQL中存儲過程的一般語法是什么？

1、 創(chuàng)建語法

create?proc?|?procedure?pro_name

[{@參數(shù)數(shù)據(jù)類型}?[=默認(rèn)值]?[output],

{@參數(shù)數(shù)據(jù)類型}?[=默認(rèn)值]?[output],

….

]

as

SQL_statements

2、 創(chuàng)建不帶參數(shù)存儲過程

–創(chuàng)建存儲過程

if?(exists?(select?*?from?sys.objects?where?name?=?‘proc_get_student’))

drop?proc?proc_get_student

go

create?proc?proc_get_student

as

select?*?from?student;

–調(diào)用、執(zhí)行存儲過程

exec?proc_get_student;

3、 修改存儲過程

–修改存儲過程

alter?proc?proc_get_student

as

select?*?from?student;

4、 帶參存儲過程

–帶參存儲過程

if?(object_id(‘proc_find_stu’,?‘P’)?is?not?null)

drop?proc?proc_find_stu

go

create?proc?proc_find_stu(@startId?int,?@endId?int)

as

select?*?from?student?where?id?between?@startId?and?@endId

go

exec?proc_find_stu?2,?4;

5、 帶通配符參數(shù)存儲過程

–帶通配符參數(shù)存儲過程

if?(object_id(‘proc_findStudentByName’,?‘P’)?is?not?null)

drop?proc?proc_findStudentByName

go

create?proc?proc_findStudentByName(@name?varchar(20)?=?‘%j%’,?@nextName?varchar(20)?=?‘%’)

as

select?*?from?student?where?name?like?@name?and?name?like?@nextName;

go

exec?proc_findStudentByName;exec?proc_findStudentByName?‘%o%’,?‘t%’;

擴(kuò)展資料：

SQL存儲過程優(yōu)點(diǎn)：

1、重復(fù)使用。存儲過程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫開發(fā)人員的工作量。

2、減少網(wǎng)絡(luò)流量。存儲過程位于服務(wù)器上，調(diào)用的時(shí)候只需要傳遞存儲過程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。

3、安全性。參數(shù)化的存儲過程可以防止SQL注入式攻擊，而且可以將Grant、Deny以及Revoke權(quán)限應(yīng)用于存儲過程。

參考資料來源：百度百科—存儲過程

Oracle 存儲過程中的基本語法有哪些

Oracle存儲過程基本語法介紹參考下

Oracle存儲過程基本語法 存儲過程

1 CREATE OR REPLACE PROCEDURE 存儲過程名

2 IS

3 BEGIN

4 NULL;

5 END;

行1:

CREATE OR REPLACE PROCEDURE 是一個(gè)SQL語句通知Oracle數(shù)據(jù)庫去創(chuàng)建一個(gè)叫做skeleton存儲過程, 如果存在就覆蓋它;

行2:

IS關(guān)鍵詞表明后面將跟隨一個(gè)PL/SQL體。

行3:

BEGIN關(guān)鍵詞表明PL/SQL體的開始。

行4:

NULL PL/SQL語句表明什么事都不做，這句不能刪去，因?yàn)镻L/SQL體中至少需要有一句;

行5:

END關(guān)鍵詞表明PL/SQL體的結(jié)束

存儲過程創(chuàng)建語法：

create or replace procedure 存儲過程名（param1 in type，param2 out type）

as

變量1 類型（值范圍）; –vs_msg VARCHAR2(4000);

變量2 類型（值范圍）;

復(fù)制代碼 代碼如下:

Begin

Select count(*) into 變量1 from 表A where列名=param1；

If (判斷條件) then

Select 列名 into 變量2 from 表A where列名=param1；

Dbms_output。Put_line(‘打印信息’);

Elsif (判斷條件) then

Dbms_output。Put_line(‘打印信息’);

Else

Raise 異常名（NO_DATA_FOUND）;

End if;

Exception

When others then

Rollback;

End;

注意事項(xiàng)：

1， 存儲過程參數(shù)不帶取值范圍，in表示傳入，out表示輸出

類型可以使用任意Oracle中的合法類型。

2， 變量帶取值范圍，后面接分號

3， 在判斷語句前最好先用count（*）函數(shù)判斷是否存在該條操作記錄

4， 用select 。。。into。。。給變量賦值

5， 在代碼中拋異常用 raise+異常名

復(fù)制代碼 代碼如下:

CREATE OR REPLACE PROCEDURE存儲過程名

(

–定義參數(shù)

is_ym IN CHAR(6) ,

the_count OUT NUMBER,

)

AS

–定義變量

vs_msg VARCHAR2(4000); –錯(cuò)誤信息變量

vs_ym_beg CHAR(6); –起始月份

vs_ym_end CHAR(6); –終止月份

vs_ym_sn_beg CHAR(6); –同期起始月份

vs_ym_sn_end CHAR(6); –同期終止月份

–定義游標(biāo)(簡單的說就是一個(gè)可以遍歷的結(jié)果集)

CURSOR cur_1 IS

SELECT 。。。

FROM 。。。

WHERE 。。。

GROUP BY 。。。;

BEGIN

–用輸入?yún)?shù)給變量賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS

TO_DATE 等很常用的函數(shù)。

vs_ym_beg := SUBSTR(is_ym,1,6);

vs_ym_end := SUBSTR(is_ym,7,6);

vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,’yyyymm’), -12),’yyyymm’);

vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,’yyyymm’), -12),’yyyymm’);

–先刪除表中特定條件的數(shù)據(jù)。

DELETE FROM 表名 WHERE ym = is_ym;

–然后用內(nèi)置的DBMS_OUTPUT對象的put_line方法打印出影響的記錄行數(shù)，其中用到一個(gè)系統(tǒng)變量SQL%rowcount

DBMS_OUTPUT.put_line(‘del上月記錄=’||SQL%rowcount||’條’);

INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)

SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000

FROM BGD_AREA_CM_M_BASE_T

WHERE ym = vs_ym_beg

AND ym = vs_ym_end

GROUP BY area_code,CMCODE;

DBMS_OUTPUT.put_line(‘ins當(dāng)月記錄=’||SQL%rowcount||’條’);

–遍歷游標(biāo)處理后更新到表。遍歷游標(biāo)有幾種方法，用for語句是其中比較直觀的一種。

FOR rec IN cur_1 LOOP

UPDATE 表名

SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn

WHERE area_code = rec.area_code

AND CMCODE = rec.CMCODE

AND ym = is_ym;

END LOOP;

COMMIT;

–錯(cuò)誤處理部分。OTHERS表示除了聲明外的任意錯(cuò)誤。SQLERRM是系統(tǒng)內(nèi)置變量保存了當(dāng)前錯(cuò)誤的詳細(xì)信息。

EXCEPTION

WHEN OTHERS THEN

vs_msg := ‘ERROR IN xxxxxxxxxxx_p(‘||is_ym||’):’||SUBSTR(SQLERRM,1,500);

ROLLBACK;

–把當(dāng)前錯(cuò)誤記錄進(jìn)日志表。

INSERT INTO LOG_INFO(proc_name,error_info,op_date)

VALUES(‘xxxxxxxxxxx_p’,vs_msg,SYSDATE);

COMMIT;

RETURN;

END;

oracle存儲過程語法

1 、判斷語句:

if 比較式 then begin end; end if;

復(fù)制代碼 代碼如下:

create or replace procedure test(x in number) is

begin

if x 0 then

begin

x := 0 – x;

end;

end if;

if x = 0 then

begin

x: = 1;

end;

end if;

end test;

2 、For 循環(huán)

For … in … LOOP

— 執(zhí)行語句

end LOOP;

(1) 循環(huán)遍歷游標(biāo)

復(fù)制代碼 代碼如下:

create or replace procedure test() as

Cursor cursor is select name from student; name varchar(20);

begin

for name in cursor LOOP

begin

dbms_output.putline(name);

end;

end LOOP;

end test;

(2) 循環(huán)遍歷數(shù)組

復(fù)制代碼 代碼如下:

create or replace procedure test(varArray in myPackage.TestArray) as

–( 輸入?yún)?shù)varArray 是自定義的數(shù)組類型，定義方式見標(biāo)題6)

i number;

begin

i := 1; — 存儲過程數(shù)組是起始位置是從1 開始的，與java 、C 、C++ 等語言不同。因?yàn)樵贠racle 中本是沒有數(shù)組的概念的，數(shù)組其實(shí)就是一張

— 表(Table), 每個(gè)數(shù)組元素就是表中的一個(gè)記錄，所以遍歷數(shù)組時(shí)就相當(dāng)于從表中的第一條記錄開始遍歷

for i in 1..varArray.count LOOP

dbms_output.putline(‘The No.’|| i || ‘record in varArray is:’||varArray(i));

end LOOP;

end test;

3 、While 循環(huán)

while 條件語句 LOOP

復(fù)制代碼 代碼如下:

begin

end;

end LOOP;

E.g

create or replace procedure test(i in number) as

begin

while i10 LOOP

begin

i:= i + 1;

end;

end LOOP;

end test;

4 、數(shù)組

首先明確一個(gè)概念：Oracle 中本是沒有數(shù)組的概念的，數(shù)組其實(shí)就是一張表(Table), 每個(gè)數(shù)組元素就是表中的一個(gè)記錄。

使用數(shù)組時(shí)，用戶可以使用Oracle 已經(jīng)定義好的數(shù)組類型，或可根據(jù)自己的需要定義數(shù)組類型。

(1) 使用Oracle 自帶的數(shù)組類型

x array; — 使用時(shí)需要需要進(jìn)行初始化

e.g:

create or replace procedure test(y out array) is

x array;

begin

x := new array();

y := x;

end test;

(2) 自定義的數(shù)組類型 ( 自定義數(shù)據(jù)類型時(shí)，建議通過創(chuàng)建Package 的方式實(shí)現(xiàn)，以便于管理)

create or replace package myPackage is

Public type declarations type info is record( name varchar(20), y number);

type TestArray is table of info index by binary_integer;

— 此處聲明了一個(gè)TestArray 的類型數(shù)據(jù)，其實(shí)其為一張存儲Info 數(shù)據(jù)類型的Table 而已，及TestArray 就是一張表，有兩個(gè)字段，一個(gè)是name ，一個(gè)是y 。需要注意的是此處使用了Index by binary_integer 編制該Table 的索引項(xiàng)，也可以不寫，直接寫成：type TestArray is

table of info ，如果不寫的話使用數(shù)組時(shí)就需要進(jìn)行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

5. 游標(biāo)的使用 Oracle 中Cursor 是非常有用的，用于遍歷臨時(shí)表中的查詢結(jié)果。其相關(guān)方法和屬性也很多，現(xiàn)僅就常用的用法做一二介紹：

(1)Cursor 型游標(biāo)( 不能用于參數(shù)傳遞)

復(fù)制代碼 代碼如下:

create or replace procedure test() is

cusor_1 Cursor is select std_name from student where …; –Cursor 的使用方式1 cursor_2 Cursor;

begin

select class_name into cursor_2 from class where …; –Cursor 的使用方式2

可使用For x in cursor LOOP …. end LOOP; 來實(shí)現(xiàn)對Cursor 的遍歷

end test;

(2)SYS_REFCURSOR 型游標(biāo)，該游標(biāo)是Oracle 以預(yù)先定義的游標(biāo)，可作出參數(shù)進(jìn)行傳遞

create or replace procedure test(rsCursor out SYS_REFCURSOR) is

cursor SYS_REFCURSOR;

name varhcar(20);

begin

OPEN cursor FOR select name from student where … –SYS_REFCURSOR 只能通過OPEN 方法來打開和賦值

LOOP

fetch cursor into name –SYS_REFCURSOR 只能通過fetch into 來打開和遍歷 exit when cursor%NOTFOUND; –SYS_REFCURSOR 中可使用三個(gè)狀態(tài)屬性： —%NOTFOUND( 未找到記錄信息) %FOUND( 找到記錄信息) —%ROWCOUNT( 然后當(dāng)前游標(biāo)所指向的行位置)

dbms_output.putline(name);

end LOOP;

rsCursor := cursor;

end test;

實(shí)例

下面寫一個(gè)簡單的例子來對以上所說的存儲過程的用法做一個(gè)應(yīng)用：

現(xiàn)假設(shè)存在兩張表，一張是學(xué)生成績表(studnet) ，字段為：stdId,math,article,language,music,sport,total,average,step

一張是學(xué)生課外成績表(out_school), 字段為:stdId,parctice,comment

通過存儲過程自動(dòng)計(jì)算出每位學(xué)生的總成績和平均成績，同時(shí)，如果學(xué)生在課外課程中獲得的評價(jià)為A ，就在總成績上加20 分。

復(fù)制代碼 代碼如下:

create or replace procedure autocomputer(step in number) is

rsCursor SYS_REFCURSOR;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

begin

i := 1;

get_comment(commentArray); — 調(diào)用名為get_comment() 的存儲過程獲取學(xué)生課外評分信息

OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;

LOOP

fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;

total := math + article + language + music + sport;

for i in 1..commentArray.count LOOP

record := commentArray(i);

if stdId = record.stdId then

begin

if record.comment = ‘A’ then

begin

total := total + 20;

go to next; — 使用go to 跳出for 循環(huán)

end;

end if;

end;

end if;

end LOOP;

continue average := total / 5;

update student t set t.total=total and t.average = average where t.stdId = stdId;

end LOOP;

end;

end autocomputer;

— 取得學(xué)生評論信息的存儲過程

create or replace procedure get_comment(commentArray out myPackage.myArray) is

rs SYS_REFCURSOR ；

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

begin

open rs for select stdId,comment from out_school

i := 1;

LOOP

fetch rs into stdId,comment; exit when rs%NOTFOUND;

record.stdId := stdId;

record.comment := comment;

recommentArray(i) := record;

i:=i + 1;

end LOOP;

end get_comment;

— 定義數(shù)組類型myArray

create or replace package myPackage is begin

type stdInfo is record(stdId varchar(30),comment varchar(1));

type myArray is table of stdInfo index by binary_integer;

end myPackage;

關(guān)于存儲過程語法和sql server存儲過程語法的相關(guān)介紹到此就結(jié)束了，不知道你從中找到你需要的信息了嗎 ？如果你還想了解更多這方面的信息，記得收藏關(guān)注本站。