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/[email protected]」接著調試存儲過程。但是最好用的是在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;

看一看行不行。