oracle存儲過程規范
① oracle的存儲過程
第一步:找到proceres,右鍵新建
第二步:在name中輸入存儲過程名字(這里輸入的就是你以後需要調用的名字),parameters後是參數,在裡面定義參數的類型。
第三步:編寫存儲過程語句。
4 第四步: 存儲過程調用
CallableStatement
cs=conn.divpareCall("{call
mymd_gis_pro(?,?,?,?,?)}"); //1代表第一個參數,setint表示參數類型是int型 cs.setInt(1,188);
cs.setInt(2,12); cs.setInt(3,12);
...... //執行Oracle存儲過程
cs.execute();
② oracle 存儲過程 begin end
Oracle存儲過程基礎知識
商業規則和業務邏輯可以通過程序存儲在Oracle中,這個程序就是存儲過程。
存儲過程是sql, PL/SQL, java 語句的組合,它使你能將執行商業規則的代碼從你的應用程序中移動到資料庫。這樣的結果就是,代碼存儲一次但是能夠被多個程序使用。
要創建一個過程對象(proceral object),必須有 CREATE PROCEDURE 系統許可權。如果這個過程對象需要被其他的用戶schema 使用,那麼你必須有 CREATE ANY PROCEDURE 許可權。執行 procere 的時候,可能需要excute許可權。或者EXCUTE ANY PROCEDURE 許可權。如果單獨賦予許可權,如下例所示:
grant execute on MY_PROCEDURE to Jelly
調用一個存儲過程的例子:
execute MY_PROCEDURE( 'ONE PARAMETER');
存儲過程(PROCEDURE)和函數(FUNCTION)的區別。
function有返回值,並且可以直接在Query中引用function和或者使用function的返回值。
本質上沒有區別,都是 PL/SQL 程序,都可以有返回值。最根本的區別是: 存儲過程是命令, 而函數是表達式的一部分。比如:
select max(NAME) FROM
但是不能 exec max(NAME) 如果此時max是函數。
PACKAGE是function,procere,variables 和sql 語句的組合。package允許多個procere使用同一個變數和游標。
創建 procere的語法:
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procere
[(argument [IN | OUT | IN OUT ] [NO COPY] datatype
[, argument [IN | OUT | IN OUT ] [NO COPY] datatype]...
)]
[ authid { current_user | definer }]
{ is | as } { pl/sql_subprogram_body |
language { java name 'String' | c [ name, name] library lib_name
}]
Sql 代碼:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no;
END;
可以使用 create or replace procere 語句, 這個語句的用處在於,你之前賦予的excute許可權都將被保留。
IN, OUT, IN OUT用來修飾參數。
IN 表示這個變數必須被調用者賦值然後傳入到PROCEDURE進行處理。
OUT 表示PRCEDURE 通過這個變數將值傳回給調用者。
IN OUT 則是這兩種的組合。
authid代表兩種許可權:
定義者許可權(difiner right 默認),執行者許可權(invoker right)。
定義者許可權說明這個procere中涉及的表,視圖等對象所需要的許可權只要定義者擁有許可權的話就可以訪問。
執行者許可權則需要調用這個 procere的用戶擁有相關表和對象的許可權。
Oracle存儲過程的基本語法
1. 基本結構
CREATE OR REPLACE PROCEDURE 存儲過程名字
(
參數1 IN NUMBER,
參數2 IN NUMBER
) AS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 存儲過程名字
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單步調試
9. Pl/Sql中執行存儲過程
在sql*plus中:
declare
--必要的變數聲明,視你的過程而定
begin
execute yourprocure(parameter1,parameter2,...);
end
/
在SQL/PLUS中調用存儲過程,顯示結果:
SQL>set serveoutput on --打開輸出
SQL>var info1 number; --輸出1
SQL>var info2 number; --輸出2
SQL>declare
var1 varchar2(20); --輸入1
var2 varchar2(20); --輸入2
var3 varchar2(20); --輸入2
BEGIN
pro(var1,var2,var3,:info1,:info2);
END;
/
SQL>print info1;
SQL>print info2;
註:在EXECUTE IMMEDIATE STR語句是SQLPLUS中動態執行語句,它在執行中會自動提交,類似於DP中FORMS_DDL語句,在此語句中str是不能換行的,只能通過連接字元"||",或著在在換行時加上"-"連接字元。
③ Oracle存儲過程及舉例(幾種參數情況的存儲
create table TESTTABLE
(
id1 VARCHAR2(12),
name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');
insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');
insert into TESTTABLE (ID1, NAME)
values ('3', 'wangwu');
insert into TESTTABLE (ID1, NAME)
values ('4', 'xiaoliu');
insert into TESTTABLE (ID1, NAME)
values ('5', 'laowu');
---創建存儲過程
create or replace procere test_count
as
v_total number(1);
begin
select count(*) into v_total from TESTTABLE;
DBMS_OUTPUT.put_line('總人數:'||v_total);
end;
--准備
--線對scott解鎖:alter user scott account unlock;
--應為存儲過程是在scott用戶下。還要給scott賦予密碼
---alter user scott identified by tiger;
---去命令下執行
EXECUTE test_count;
----在ql/spl中的sql中執行
begin
-- Call the procere
test_count;
end;
create or replace procere TEST_LIST
AS
---是用游標
CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;
begin
for Test_record IN test_cursor loop---遍歷游標,在列印出來
DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);
END LOOP;
test_count;--同時執行另外一個存儲過程(TEST_LIST中包含存儲過程test_count)
end;
-----執行存儲過程TEST_LIST
begin
TEST_LIST;
END;
---存儲過程的參數
---IN 定義一個輸入參數變數,用於傳遞參數給存儲過程
--OUT 定義一個輸出參數變數,用於從存儲過程獲取數據
---IN OUT 定義一個輸入、輸出參數變數,兼有以上兩者的功能
--這三種參數只能說明類型,不需要說明具體長度 比如 varchar2(12),defaul 可以不寫,但是作為一個程序員最好還是寫上。
---創建有參數的存儲過程
create or replace procere test_param(p_id1 in VARCHAR2 default '0')
as v_name varchar2(32);
begin
select t.name into v_name from TESTTABLE t where t.id1=p_id1;
DBMS_OUTPUT.put_line('name:'||v_name);
end;
----執行存儲過程
begin
test_param('1');
end;
default '0'
---創建有參數的存儲過程
create or replace procere test_paramout(v_name OUT VARCHAR2 )
as
begin
select name into v_name from TESTTABLE where id1='1';
DBMS_OUTPUT.put_line('name:'||v_name);
end;
----執行存儲過程
DECLARE
v_name VARCHAR2(32);
BEGIN
test_paramout(v_name);
DBMS_OUTPUT.PUT_LINE('name:'||v_name);
END;
-------IN OUT
---創建存儲過程
create or replace procere test_paramINOUT(p_phonenumber in out varchar2)
as
begin
p_phonenumber:='0571-'||p_phonenumber;
end;
----
DECLARE
p_phonenumber VARCHAR2(32);
BEGIN
p_phonenumber:='26731092';
test_paramINOUT(p_phonenumber);
DBMS_OUTPUT.PUT_LINE('新的電話號碼:'||p_phonenumber);
END;
-----sql命令下,查詢當前用戶的存儲過程或函數的源代碼,
-----可以通過對USER_SOURCE數據字典視圖的查詢得到。USER_SOURCE的結構如下:
SQL> DESCRIBE USER_SOURCE ;
Name Type Nullable Default Comments
---- -------------- -------- -------
-------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the object
TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of
source
TEXT VARCHAR2(4000) Y Source text
SQL>
---查詢出存儲過程的定義語句
select text from user_source WHERE NAME='TEST_COUNT';
----查詢存儲過程test_paramINOUT的參數
SQL> DESCRIBE test_paramINOUT;
Parameter Type Mode Default?
------------- -------- ------ --------
P_PHONENUMBER VARCHAR2 IN OUT
SQL>
---查看當前的存儲過程的狀態是否正確,
---VALID為正確,INVALID表示存儲過程無效或需要重新編譯
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='TEST_COUNT';
-----如果要檢查存儲過程或函數的依賴性,可以通過查詢數據字典USER_DENPENDENCIES來確定,該表結構如下:
SQL> DESCRIBE USER_DEPENDENCIES;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(17) Y Type of the object
REFERENCED_OWNER VARCHAR2(30) Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2(64) Y Name of referenced object
REFERENCED_TYPE VARCHAR2(17) Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2(128) Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2(4) Y
SQL>
---查詢存儲過程TEST_COUNT的依賴關系
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='TEST_COUNT';
④ Oracle資料庫的存儲過程怎麼寫
1 CREATE OR REPLACE PROCEDURE 存儲過程名
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一個SQL語句通知Oracle資料庫去創建一個叫做skeleton存儲過程, 如果存在就覆蓋它;
行2:
IS關鍵詞表明後面將跟隨一個PL/SQL體。
行3:
BEGIN關鍵詞表明PL/SQL體的開始。
行4:
NULL PL/SQL語句表明什麼事都不做,這句不能刪去,因為PL/SQL體中至少需要有一句;
行5:
END關鍵詞表明PL/SQL體的結束.
存儲過程創建語法:
create or replace procere 存儲過程名(param1 in type,param2 out type)
as
變數1 類型(值范圍); --vs_msgVARCHAR2(4000);
變數2 類型(值范圍);
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;
⑤ oracle 存儲過程
create [or replace] procere procere_name (varible {IN|OUT|IN OUT} type) {IS|AS}
[varible { in | out | in out }] type;
[varible { in | out | in out }] type;
......
[cursor curor_name IS select subclause]
begin
body
end [procere_name];
for example:
create or replace procere sp_login_error_count(user_name_var varchar2) IS
--創建名為sp_login_error_count的存儲過程,並定義傳參
--informix的語法(drop procere sp_login_error_count;create procere sp_login_error_count(user_name_var varchar(50)))
error_count_var integer;
--變數定義
--informix語法(define error_count_var integer;)
begin
--程序開始
--informix語法不用begin,直接開始
select error_count into error_count_var from users where user_name=user_name_var;
--select語句,找到的值放到變數中
--informix語法一樣
error_count_var :=error_count_var+1;
--變數加1,這是pl/sql的變數賦值運算。
--informix語法(let error_count_var=error_count_var+1;)
update users set error_count= error_count_var where user_name =user_name_var ;
--更新統計數
--informix語法一樣
commit;
--提交
--informix不需要提交
end sp_login_error_count;
--結束存儲過程
--informix語法(end procere;)
ORACLE存儲過程執行為execute procere_name或exec procere_name
INFORMIX存儲過程執行為execute procere procere_name
pl/sql程序由三個塊組成:聲明部分、執行部分、異常處理部分
declare
/* 聲明部分: 在此聲明PL/SQL用到的變數,類型及游標 */
begin
/* 執行部分: 過程及SQL語句 , 即程序的主要部分 */
Exception
/* 執行異常部分: 錯誤處理 */
End;
其中 執行部分是必須的。
⑥ Oracle存儲過程語法規則,不熟,我的錯在哪裡求高手
你這個完全是 sqlserver的語法啊。源正
大致如下:
create or replace PROCEDURE 編號規則
(
企業編號 IN NUMBER
, p_表名 IN VARCHAR2
, 編號 OUT NUMBER
) AS
n_key number;
v_cnt number;
BEGIN
n_key:=企業編號*1000+1;
begin
select count(表名) into v_cnt from 編號表 where 表名=p_表名;
if v_cnt = 0 then
insert into 編號表 values(表名,n_key);
else
select 編號 into n_key from 編號表圓裂隱where 表名= p_表名;
n_key:=n_key+1;
--update the key value by table name
update 編號表 set 編號=n_key where 表名=p_表名;
end if;
--set ouput value
編號橘廳:=n_key;
commit;
EXCEPTION
WHEN OTHERS THEN
rollback ;
END 編號規則;
建議好好看看oracle的語法,然後再改吧:
⑦ 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可以單步跟蹤,的確爽
⑧ oracle存儲過程技術怎麼就那麼不規范if else if 再多個else if就不能用了
按照下列語句改一下試一下:
if title_b is not null and title_b != ' ' and title_s is not null and
title_s != ' ' then
title := '從' || dqmc || '所轄縣級子公司四個維度2014年上半年平均得分來看,' || title_b ||
'維度管理水平較2013年上半年有所提升,' || title_s || '維度管理水平較2013年上半年有所下降。';
else
if title_b is not null and title_b != ' ' then
title := '從' || dqmc || '所轄縣級子公司四個維度2014年上半年平均得分來看,' || title_b ||
'維度管理水平較2013年上半年有所提升。';
else 注釋部分
if title_s is not null and title_s != ' ' then
title := '從' || dqmc || '局所轄縣級子公司四個維度2014年上半年平均得分來看,' || title_s ||
'維度管理水平較2013年上半年有所下降。';
end if;
end if;
看一看行不行。