今天小編給各位分享存儲過程語法的知識，其中也會對sql server存儲過程語法進行解釋，如果能碰巧解決你現(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
錯誤處理部分]
END[過程名];
?說明：
? ?OR REPLACE?表示如果存在就覆蓋存儲過程
? ?參數(shù)有三種形式：IN、OUT和IN OUT。則默認為IN。
??關(guān)鍵字AS也可以寫成IS，后跟過程的說明部分，可以在此定義過程的局部變量。
2）刪除存儲過程：
DROP PROCEDURE 存儲過程名；
3）修改存儲過程：
? ? ? ALTER PROCEDURE 存儲過程名 COMPILE；?
4）執(zhí)行存儲過程
? ? ??EXECUTE 模式名.存儲過程名[(參數(shù)…)];?
? ? ?或
? ? ? ?BEGIN?
模式名.存儲過程名[(參數(shù)…)];
END;
另外：參數(shù)可以是變量、常量或表達式
?要其它用戶執(zhí)行存儲過程須要給其它用戶授權(quán)
GRANT EXECUTE ON 存儲過程名 TO 用戶名
?2：參數(shù)說明
IN 定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程
OUT 定義一個輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)
IN OUT 定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能
?1）參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值；
? ? ? ? ??定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程。
? ? ? ? ??可以是常量、有值變量或表達式
? ? ? ? ??DEFAULT 關(guān)鍵字為可選項，用來設(shè)定參數(shù)的默認值。如果在調(diào)用存儲過程時不指明參數(shù)，則參數(shù)變量取默認值
? ? ? ? ??在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進行賦值。
2）參數(shù)名 OUT 數(shù)據(jù)類型；
? ? ? ? ??定義一個輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)，即變量從存儲過程中返回值給主程序。
? ? ? ? ? 在調(diào)用存儲過程時，主程序的實際參數(shù)只能是一個變量，而不能是常量或表達式。
? ? ? ? ??在存儲過程中，參數(shù)變量只能被賦值而不能將其用于賦值，而且必須給輸出變量至少賦值一次。
3）參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值；?
? ? ? ? ??定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能。
? ? ? ? ??在調(diào)用存儲過程時，主程序的實際參數(shù)只能是一個變量，而不能是常量或表達式。
? ? ? ? ??DEFAULT 關(guān)鍵字為可選項，用來設(shè)定參數(shù)的默認值。
? ? ? ? ??在存儲過程中，變量接收主程序傳遞的值，同時可以參加賦值運算，也可以對其進行賦值。在存儲過程中必須給變量至少賦值一次。
補充：如果省略IN、OUT或IN OUT，則默認模式是IN。?
? ? ? ? ? 調(diào)用它時參數(shù)個數(shù)與位置可以不一致，用以下形式調(diào)用：
EXECUTE CHANGE_SALARY(P_RAISE=80,P_EMPNO=7788); ?//=運算符左側(cè)是參數(shù)名，右側(cè)是參數(shù)表達式
?二：函數(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(表達式)
[EXCEPTION
? ? 錯誤處理部分]
END[函數(shù)名];
?說明：
? ? ?參數(shù)是可選的，但只能是IN類型(IN關(guān)鍵字可以省略)。
? ? ?在定義部分的RETURN 數(shù)據(jù)類型，用來表示函數(shù)的數(shù)據(jù)類型，也就是返回值的類型，不可省略。
? ? ?在可執(zhí)行部分的RETURN(表達式)，用來生成函數(shù)的返回值，其表達式的類型應(yīng)該和定義部分說明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個RETURN語句，但只有一個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：查詢某個存儲過程序內(nèi)容
select TEXT from user_source WHERE NAME=’存儲過程名’;
?2：查看數(shù)據(jù)字殿
DESCRIBE USER_SOURCE ; //命令行中
?3：查看存儲過程的參數(shù)
DESCRIBE say_hello; //后面是過程名
?4：查看發(fā)生編輯錯誤
SHOW ERRORS ;
?5：查詢一個存儲過程或函數(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為實體名，TYPE為實體類型，REFERENCED_OWNER為涉及到的實體擁有者賬戶，REFERENCED_NAME為涉及到的實體名，REFERENCED_TYPE 為涉及到的實體類型。
問題：
如果一個用戶A被授予執(zhí)行屬于用戶B的一個存儲過程的權(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ù)類型}?[=默認值]?[output],
{@參數(shù)數(shù)據(jù)類型}?[=默認值]?[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%’;
擴展資料：
SQL存儲過程優(yōu)點：
1、重復(fù)使用。存儲過程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫開發(fā)人員的工作量。
2、減少網(wǎng)絡(luò)流量。存儲過程位于服務(wù)器上，調(diào)用的時候只需要傳遞存儲過程的名稱以及參數(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 是一個SQL語句通知Oracle數(shù)據(jù)庫去創(chuàng)建一個叫做skeleton存儲過程, 如果存在就覆蓋它;
行2:
IS關(guān)鍵詞表明后面將跟隨一個PL/SQL體。
行3:
BEGIN關(guān)鍵詞表明PL/SQL體的開始。
行4:
NULL PL/SQL語句表明什么事都不做，這句不能刪去，因為PL/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;
注意事項：
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); –錯誤信息變量
vs_ym_beg CHAR(6); –起始月份
vs_ym_end CHAR(6); –終止月份
vs_ym_sn_beg CHAR(6); –同期起始月份
vs_ym_sn_end CHAR(6); –同期終止月份
–定義游標(簡單的說就是一個可以遍歷的結(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ù)，其中用到一個系統(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當月記錄=’||SQL%rowcount||’條’);
–遍歷游標處理后更新到表。遍歷游標有幾種方法，用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;
–錯誤處理部分。OTHERS表示除了聲明外的任意錯誤。SQLERRM是系統(tǒng)內(nèi)置變量保存了當前錯誤的詳細信息。
EXCEPTION
WHEN OTHERS THEN
vs_msg := ‘ERROR IN xxxxxxxxxxx_p(‘||is_ym||’):’||SUBSTR(SQLERRM,1,500);
ROLLBACK;
–把當前錯誤記錄進日志表。
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)遍歷游標
復(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ù)組類型，定義方式見標題6)
i number;
begin
i := 1; — 存儲過程數(shù)組是起始位置是從1 開始的，與java 、C 、C++ 等語言不同。因為在Oracle 中本是沒有數(shù)組的概念的，數(shù)組其實就是一張
— 表(Table), 每個數(shù)組元素就是表中的一個記錄，所以遍歷數(shù)組時就相當于從表中的第一條記錄開始遍歷
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ù)組
首先明確一個概念：Oracle 中本是沒有數(shù)組的概念的，數(shù)組其實就是一張表(Table), 每個數(shù)組元素就是表中的一個記錄。
使用數(shù)組時，用戶可以使用Oracle 已經(jīng)定義好的數(shù)組類型，或可根據(jù)自己的需要定義數(shù)組類型。
(1) 使用Oracle 自帶的數(shù)組類型
x array; — 使用時需要需要進行初始化
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ù)類型時，建議通過創(chuàng)建Package 的方式實現(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;
— 此處聲明了一個TestArray 的類型數(shù)據(jù)，其實其為一張存儲Info 數(shù)據(jù)類型的Table 而已，及TestArray 就是一張表，有兩個字段，一個是name ，一個是y 。需要注意的是此處使用了Index by binary_integer 編制該Table 的索引項，也可以不寫，直接寫成：type TestArray is
table of info ，如果不寫的話使用數(shù)組時就需要進行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
5. 游標的使用 Oracle 中Cursor 是非常有用的，用于遍歷臨時表中的查詢結(jié)果。其相關(guān)方法和屬性也很多，現(xiàn)僅就常用的用法做一二介紹：
(1)Cursor 型游標( 不能用于參數(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; 來實現(xiàn)對Cursor 的遍歷
end test;
(2)SYS_REFCURSOR 型游標，該游標是Oracle 以預(yù)先定義的游標，可作出參數(shù)進行傳遞
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 中可使用三個狀態(tài)屬性： —%NOTFOUND( 未找到記錄信息) %FOUND( 找到記錄信息) —%ROWCOUNT( 然后當前游標所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
實例
下面寫一個簡單的例子來對以上所說的存儲過程的用法做一個應(yīng)用：
現(xiàn)假設(shè)存在兩張表，一張是學(xué)生成績表(studnet) ，字段為：stdId,math,article,language,music,sport,total,average,step
一張是學(xué)生課外成績表(out_school), 字段為:stdId,parctice,comment
通過存儲過程自動計算出每位學(xué)生的總成績和平均成績，同時，如果學(xué)生在課外課程中獲得的評價為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)注本站。
