oraclr存儲過程
1. 怎麼在oracle中查看存儲過程
語句查詢法:
sql">SELECTtext
FROMuser_source
WHERENAME='ProcereName'
ORDERBYline;--其中ProcereName為存儲過程名字,需要全部用大寫英文。
工具查詢法:
1、登錄第三方工具(如pl/sql)登錄到指定資料庫。
2. 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的存儲過程是失敗的還是成功的呢?答案是成功的。
3. oracle 存儲過程
create or replace procere sp_add_emp2( --創建名為sp_add_emp2存儲過程
v_empno emp.empno%type, --------- 傳入存儲過程中的參數
v_ename emp.ename%type,
v_deptno dept.deptno%type,
v_dname dept.dname%type,
num out number ----------存儲過程執行完畢後返回的值
)as
num1 number; ------自定義變數
num2 number;
begin
--查詢dept表中的數據總共有多少行,賦值給num1,
--條件是欄位deptno等於v_deptno(傳進來的參數)
select count(*) into num1 from dept where deptno=v_deptno;
if(num1=0) then --如果沒數據
--則給dept表的deptno 和dname列插入一條數據,值為v_deptno,v_dname
insert into dept(deptno,dname) values(v_deptno,v_dname);
end if; --If語句結束語
---同上。
select count(*) into num2 from emp where empno=v_empno;
if(num2=0)then
insert into emp(empno,ename,deptno) values(v_empno,v_ename,v_deptno);
else --如果查詢到有數據則提示錯誤信息,員工ID重復,不插入數據
raise_application_error(-202021,'員工id 重復!!!');
end if;
num:=num1; --定義的輸出參數等於num1。
commit; --結束存儲過程。
4. oracle中儲存過程是什麼意思
存儲過程就好象一個批處理文件一樣,你可以在裡面執行很多的SQL,來得到你最好需要的結果.當你發現一個復雜的統計在一條SQL語句中不能實現或是很難實現的時候,那最好用存儲過程來實現 存儲過程的執行速度更快
存儲過程在伺服器端早已編輯執行過的代碼。用戶要做的只是調用和接收存儲過返回的結果。所以調用存儲過程比普通的用查詢語句返回值要快得多。
5. 怎麼才oracle中查看存儲過程
可用語句查看或用第三方工具查看。
一、語句查看方法:
1、執行語句:
SELECTtext
FROMuser_source
WHERENAME='ProcereName'
ORDERBYline;--其中ProcereName為存儲過程名字,需要全部用大寫英文。
2、如要查P_TEST的存儲過程:
SELECTtext
FROMuser_source
WHERENAME='P_TEST'
ORDERBYline;
3、內容如下:
6. oracle存儲過程如何輸出信息
可用DBMS_OUTPUT.PUT_LINE()對存儲過程的進行輸出。
編寫存儲過程:
create or replace procere test_pro(in_num number)
as
M number;
begin
M := in_num;
if 0 < M then
dbms_output.put_line('輸出SQL語句1');
elsif M < 3 then
dbms_output.put_line('輸出SQL語句2');
else
dbms_output.put_line('nothing');
end if;
end;
(6)oraclr存儲過程擴展閱讀;
存儲在資料庫的數據字典中,存儲在當前的應用中安全性由資料庫提供安全保證,必須通過授權才能使用存儲子程序,安全性靠應用程序來保證,如果能執行應用程序,就能執行該子程序。模式描述IN參數用來從調用環境中向存儲過程傳遞值,不能給IN參數賦值,給此參數傳遞的值可以是常量、有值的變數、表達式等。
7. 如何查看oracle的存儲過程
使用tode或者Navicat 或者navigtor等連接oracle的工具
找到procere的選項即可查看所有的存儲過程,點擊某個名字可以看到其中的定義
8. 關於Oracle的存儲過程
存儲過程(Stored Procere)是在大型資料庫系統中,一組為了完成特定功能的SQL 語句集,經編譯後存儲在資料庫中,用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。
詳細:http://ke..com/view/68525.htm
9. oracle怎麼執行存儲過程
sqlplus或者PL/SQL SQL窗口
輸入(單獨調用存儲過程):
無參數
EXEC 過程名;
或者
BEGIN
過程名
END;
IN 參數
EXEC 過程名(入參數..);
或者
BEGIN
過程名(入參數...)
END;
OUT參數,IN OUT參數
variable 綁定變數名 數據類型; //定義一個變數接收出參數的值;
EXEC 過程名(:綁定變數名);
10. oracle 存儲過程
1.基本結構
CREATE OR REPLACE PROCEDURE 存儲過程名字
(
參數1 IN NUMBER,
參數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 存儲過程名字
你那個 END P_UPDATELOCKCMPINFO; 沒看懂是啥意思
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中,可以同時將多個列存儲多個變數中,必須有一條
記錄,否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)
例子:
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 循環
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.帶參數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連接資料庫後建立一個Test WINDOW
在窗口輸入調用SP的代碼,F9開始debug,CTRL+N單步調試
簡單實例,通過DBMS_OUTPUT來看結果
CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)
AS
temp VARCHAR2 (100);
BEGIN
SELECT lic_no
INTO temp
FROM t_vehicle_info
WHERE lic_no = lic_para;
out_para:=temp;
DBMS_OUTPUT.put_line (out_para);
END bb;
下面是調用:
begin
-- Call the procere
bb(lic_para => :lic_para,
out_para => :out_para);
end;
可以在命令行里敲sqlplus 「yonghuming/mima@dbname」接著調試存儲過程。但是最好用的是在pl/sql Developer工具裡面用點存儲過程里的TEST來調用,它能自動生成調用的語句並有欄目讓你輸入參數值,包括輸入參數和輸出參數,並把結果返回到輸出參數裡面,在結果欄可見,這點pl/sql Developer比TOAD要強,TOAD在存儲過程上右鍵後點擊EXECUTE Procere也可以執行,只是結果在那看我不曉得,而在pl/sql Developer按F9可以調試,ctrl+N可以單步跟蹤,的確爽