procedure存儲過程
⑴ 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的存儲過程是失敗的還是成功的呢?答案是成功的。
⑵ 存儲過程的名詞解釋
存儲過程(Stored Procere)是在大型資料庫系統中,一組為了完成特定功能的sql 語句集,它存儲在資料庫中,一次編譯後永久有效,用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。存儲過程是資料庫中的一個重要對象。在數據量特別龐大的情況下利用存儲過程能達到倍速的效率提升
⑶ 資料庫中 procere 是什麼
在資料庫中PROCEDURE叫存儲過程!
其實它就是函數!
它可以傳入參數和傳出參數!
具體聲明為:
CREATE PROC 名稱
@變數名 類型 INPUT,
@變數名 類型 OUTPUT
AS
BEGIN
函數體。。。。。。。
END
你在SQL上可以查到幫助的!
好好看看!
⑷ 存儲過程中的 procere、function和 package是干什麼的,需要詳細介紹
procere:過程
function:函數
package:包
一、過程
PL/SQL過程是一個編譯到oracle資料庫模式的單機程序。過程可以接受參數。當編譯一個過程時,CREATE PROCEDURE語句的過程標識符成為數
據字典中的對象名。
CREATE OR REPLACE PPROCEDURE procere_name (optional parameters) IS | AS
declarative part
BEGIN
program body
EXCEPTION
exception handler
END procere_name;
Declarative Part 是聲明變數的地方,比如:
local_counter NUMBER := 0;
也可以聲明復合類型結構,如records和tables
也可以聲明異常,但必須在Exception部門進行處理。
Subprogram Body 包含使用PL/SQL控制結構的邏輯演算法實現。PL/SQL控制結構支持loops、if-then-else、case、和聲明塊結構。
Exception handler 可選,類似於其他語言的try-catch模型,你可以對特定錯誤類型或一般異常編寫處理器.
應當以動詞來命名一個過程。過程常常執行一些操作如更新資料庫、寫數據到一個文件或者發送一條消息。
一個過程不必要有參數,如果沒有參數創建過程是不需要括弧。當調用一個過程時(過程沒有參數)括弧是可選的。
例如:
CREATE OR REPLACE PROCEDURE insert_temp IS
BEGIN
INSERT INTO TEMP (n) VALUES (0);
END insert_temp:
sqlplus調用過程:
SQL>execute insert_temp;
SQL>execute insert_temp();
都是正確的。
IS 或 AS關鍵字是一樣的,都可以用。
PROCEDURE insert_temp IS | AS
END關鍵字後加過程名也是可選的,但建議加過程名。一個過程可能跨越幾屏的長度,當滾屏時,有助於看到END子句知道沒有跳到下一個包過
程中。
完整的過程結構如下:
CREATE OR REPLACE PROCEDURE print_temp
IS
v_average NUMBER;
v_sum NUMBER;
BEGIN
SELECT AVG(n), SUM(n) INTO v_average, v_sum
FROM TEMP;
dbms_output.put_line('Average:'||v_average);
dbms_output.put_line('Sum:'||v_sum);
END print_temp;
二、函數
函數提供了獲取對象狀態和情形信息的手段,函數有返回值。
CREATE OR REPLACE FUNCTION student_status(optional parameters)
RETURN VARCHAR2 IS
declarative part
BEGIN
program body
RETURN expression;
EXCEPTION
exception handler code
that should include a RETURN
END student_student_status;
參數是可選的,但RETURN語句是必須的。FUNCTION必須有一個return 語句。
下面的例子是返回一個DATE類型的函數:
CREATE OR REPLACE FUNCTION tomorrow RETURN DATE
IS
next_day DATE;
BEGIN
next_day := SYSDATE + 1;
RETURN next_day;
END tomorrow;
函數中的return語句可以是一個表達式,上面函數可以直接返回一個表達式而不用本地變數:
FUNCTION tomorrow RETURN DATE IS
BEGIN
RETURN SYSDATE + 1;
END tomorrow;
函數於過程結果相似,只是函數返回值,而過程不返回值
三、包說明
1、語法和風格
包說明基本語法:
CREATE PACKAGE package_name IS
Type definitions for records, index-by tables,
varrays, nested tables
Constants
Exceptions
Global variable declarations
PROCEDURE procere_name_1 (parameters & types);
PROCEDURE procere_name_2 (parameter & types);
FUNCTION function_name_1 (parameters & types) RETURN type;
END package_name;
包說明中過程和函數沒有順序限制。
包主體將包含包說明中每一個子程序的PL/SQL代碼。包說明中的每一個子程序包體中必須有相應的子程序體。
在包說明中的數據對象聲明是全局的。因此,僅聲明需要全局定義的對象。
在包體內的過程語句,包括子程序名、參數名、參數模式、參數類型,必須匹配包說明中的過程語句。同樣,函數也是一樣。
包主體模板如下:
CREATE PACKAGE BODY package_name IS
PROCEDURE procere_name_1 (parameters & types)
IS
local variables
BEGIN
body of code
END procere_name_1;
PROCEDURE procere_name_2 (parameter & types)
IS
local variables
BEGIN
body_of_code
END procere_name_2;
FUNCTION function_name_1 (parameters & types) RETURN type
IS
local variables
BEGIN
body of code
RETURN statement;
END function_name_1;
END package_name;
2、示例:
CREATE OR REPLACE PACKAGE students_pkg IS
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL);
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER;
END students_pkg;
四、包主體
下面是上例的包主體實現:
CREATE OR REPLACE PACKAGE BODY students_pkg IS
PROCEDURE add_student
(v_student_name IN students.student_name%TYPE,
v_college_major IN students.college_major%TYPE,
v_status IN students.status%TYPE,
v_state IN students.state%TYPE DEFAULT NULL,
v_license_no IN students.license_no%TYPE DEFAULT NULL)
IS
BEGIN
INSERT INTO students VALUES
('A'||students_pk_seq.NEXTVAL,
v_student_name,
v_college_major,
v_status,
v_state,
v_license_no);
END add_student;
FUNCTION NO_OF_STUDENTS
(v_major IN major_lookup.major_desc%TYPE DEFAULT NULL,
v_status IN students.status%TYPE DEFAULT NULL)
RETURN NUMBER
IS
ccount INTEGER;
BEGIN
SELECT COUNT (*) INTO ccount
FROM students, major_lookup
WHERE students.college_major = major_lookup.major
AND major_lookup.major_desc =
nvl(v_major,major_lookup.major_desc)
AND students.status = nvl(v_status,students.status);
RETURN ccount;
END NO_OF_STUDENTS;
END students_pkg;
開發包主體可能需要其他本地過程和函數,這些是隱藏的,叫私有(過程或函數)。
常常情況下要把一個單獨的過程放入一個新包或一個已存在包裡面。
PACKAGE temp_operations IS
PROCEDURE insert_temp;
END temp_operations;
PACKAGE BODY temp_operations IS
PROCEDURE insert_temp IS
BEGIN
INSERT INTO temp (n) VALUES (0);
END insert_temp;
END temp_operations;
使用下面方式調用:
temp_operations.insert_temp;
temp_operations.insert_temp();
http://yaoayao.javaeye.com/blog/710706
⑸ 什麼事存儲過程
存儲過程(Stored Procere)是一組為了完成特定功能的SQL語句集,經編譯後存儲在資料庫中。用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。存儲過程是資料庫中的一個重要對象,任何一個設計良好的資料庫應用程序都應該用到存儲過程。
儲過程是由流控制和SQL語句書寫的過程,這個過程經編譯和優化後存儲在資料庫伺服器中,應用程序使用時只要調用即可。在ORACLE中,若干個有聯系的過程可以組合在一起構成程序包。 存儲過程是利用SQL Server所提供的Transact-SQL語言所編寫的程序。Transact-SQL語言是SQL Server提供專為設計資料庫應用程序的語言,它是應用程序和SQL Server資料庫間的主要程序式設計界面。它好比Oracle資料庫系統中的PL-SQL和Informix的資料庫系統結構中的Informix- 4GL語言。這類語言主要提供以下功能,讓用戶可以設計出符合引用需求的程序: 1)、變數說明 2)、ANSI兼容的SQL命令(如Select,Update….) 3)、一般流程式控制制命令(if…else…、while….) 4)、內部函數 存儲過程(Stored Procere)是一組為了完成特定功能的SQL 語句集,經編譯後存儲在資料庫中。用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。 存儲過程可由應用程序通過一個調用來執行,而且允許用戶聲明變數 。同時,存儲過程可以接收和輸出參數、返回執行存儲過程的狀態值,也可以嵌套調用。
⑹ 如何編寫存儲過程
//創建存儲過程
CREATE PROCEDURE userData(
IN id INT
)
BEGIN
SELECT * from userdata WHERE userflag = id;
END;
其中IN是傳進去的變數;
drop procere userData;//銷毀這個存儲過程。
call userData(2) //調用存儲過程。
(6)procere存儲過程擴展閱讀:
sql中的存儲過程及相關介紹:
CREATE PROCEDURE [擁有者.]存儲過程名[;程序編號]
[(參數#1,…參數#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行
其中存儲過程名不能超過128個字。每個存儲過程中最多設定1024個參數
(SQL Server 7.0以上版本),參數的使用方法如下:
@參數名數據類型[VARYING] [=內定值] [OUTPUT]。
每個參數名前要有一個「@」符號,每一個存儲過程的參數僅為該程序內部使用,參數的類型除了IMAGE外,其他SQL Server所支持的數據類型都可使用。
[內定值]相當於我們在建立資料庫時設定一個欄位的默認值,這里是為這個參數設定默認值。[OUTPUT]是用來指定該參數是既有輸入又有輸出值的,也就是在調用了這個存儲過程時,如果所指定的參數值是我們需要輸入的參數。
同時也需要在結果中輸出的,則該項必須為OUTPUT,而如果只是做輸出參數用,可以用CURSOR,同時在使用該參數時,必須指定VARYING和OUTPUT這兩個語句。
參考資料來源:網路-儲存過程