存儲過程實例oracle
oracle的動態sql是指在語句塊使用execute immediate 執行sql語句，sql語句可以使用存儲過程傳的參數進行拼接，本文針對varchar2和number兩種類型的參數類型，進行sql拼接並執行。
功能：輸入日期區間，銷售數量滿足上限和下限的產品id
1.正常傳值
輸出結果
2.若果number類型的參數傳空，會報ora-00936:缺失表達式，可以在存儲過程中增加對參數null值的判斷
oracle 動態拼接傳入參數，varchar2類型可以使用'''|| IN_START_DTIME || ''' ，number類型可以使用'||IN_DOWN_LIMIT||' ； 拼接的過程需要注意校驗參數的合法性，增加存儲過程的容錯性。臨時表使用了會話級，存儲過程執行完，可以通過查詢存儲過程獲取結果。
『貳』 oracle存儲過程的例子
基本語法：x0dx0acreate or replace procere p1 --存儲過程名稱x0dx0a[(p_輸入參數 in datatype數據類型,p_輸出參數 out datatype數據類型)]x0dx0aasx0dx0a--v_變數名 datatype數據類型;也可聲明程序中需要的變數x0dx0abeginx0dx0a --執行語句;增刪改查x0dx0a --[exception 異常處理]x0dx0aend;x0dx0a一般創建表不寫存儲過程：x0dx0a create table USERS(x0dx0a userid number(5) identity primary key,x0dx0a username varchar2(20),x0dx0a password varchar2(20)x0dx0a );x0dx0a insert into users values('AA',飓');x0dx0a insert into users values('bb',飓');x0dx0a查詢的存儲過程：輸入用戶名，查詢密碼x0dx0a create or replace procere p1x0dx0a (x0dx0a p_username in users.username%type,p_password out users.password%typex0dx0a )asx0dx0a beginx0dx0a select password into p_password from users where username=p_username;x0dx0a exceptionx0dx0a when no_data_found then dbms_output.put_line('用戶不存在');x0dx0a end;x0dx0a create or replace procere p2x0dx0a (x0dx0a p_username in users.username%type,p_password in users.password%typex0dx0a )asx0dx0a beginx0dx0a insert into users values(p_username,p_password);x0dx0a end;
『叄』 oracle存儲過程的例子
基本語法：
create or replace procere p1 --存儲過程名稱
[(p_輸入參數 in datatype數據類型,p_輸出參數 out datatype數據類型)]
as
--v_變數名 datatype數據類型;也可聲明程序中需要的變數
begin
--執行語句;增刪改查
--[exception 異常處理]
end;
一般創建表不寫存儲過程：
create table USERS(
userid number(5) identity primary key,
username varchar2(20),
password varchar2(20)
);
insert into users values('AA','123');
insert into users values('bb','123');
查詢的存儲過程：輸入用戶名，查詢密碼
create or replace procere p1
(
p_username in users.username%type,p_password out users.password%type
)as
begin
select password into p_password from users where username=p_username;
exception
when no_data_found then dbms_output.put_line('用戶不存在');
end;
create or replace procere p2
(
p_username in users.username%type,p_password in users.password%type
)as
begin
insert into users values(p_username,p_password);
end;
『肆』 oracle資料庫存儲過程實例
創建存儲過程的語句如下：
CREATE[OR REPLACE] PROCEDURE＜過程名＞
＜參數1>，「方式l]＜數據類型1>，
＜參數2>，[ 方式2]＜數據類型2>，
……)
IS|AS （is或as完全等價）
BEGIN
PL/SQL過程體
END＜過程名＞
例如，下面是一個刪除表的存儲過程：
--參數：tableName 要刪除的表名
create or replace procere del_table(tableName varchar2) as
--定義變數
sql_del_table long;
begin
--構造sql語句
sql_del_table := 'drop table ' || tableName;
--執行
EXECUTE IMMEDIATE sql_del_table;
end del_table;
『伍』 如何使用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、首先打開oracle資料庫，如下圖所示。
『柒』 oracle中的存儲過程怎麼寫
Oracle存儲過程寫法實例
總結項目中寫的存儲過程例子：
Oracle存儲過程基本語法 存儲過程
1 CREATE OR REPLACE PROCEDURE 存儲過程名
2 IS/AS
3 BEGIN
4 NULL;
5 EXCEIPTION;
6 END;
1、創建存儲過程，後面可用is或者as：
create or replace procere PRO_COMPLAIN_TEMPLATE as
2、定義變數，此處用到了%TYPE和%ROWTYPE，參考 /database/201211/168564.html ，另外定義了一個游標，TEM_INSTANCE TEMPLATE_CUR%ROWTYPE這個類型定義必須要在游標定義之後：
NEED_DO_FOR_ZL INTEGER;
CURRENT_MAX_ID MEMO_TEMPLET.TEMPLET_ID%TYPE;
CURSOR TEMPLATE_CUR IS SELECT TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_COMPLAINT_TEMPLET;
TEM_INSTANCE TEMPLATE_CUR%ROWTYPE;
3、begin開始塊：
begin
4、該插入語句使用了DBLINK，還有使用DBMS_OUTPUT.put_line('列印信息')進行信息輸出：
insert into MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEMPLET_MEMO) (SELECT * FROM MEMO_TEMPLET@COMPANY);
SELECT COUNT(*) INTO NEED_DO_FOR_ZL FROM SYS_COMPLAINT_TEMPLET;
IF(NEED_DO_FOR_ZL > 0) THEN
DBMS_OUTPUT.put_line('列印信息');
SELECT MAX(TEMPLET_ID)+1 INTO CURRENT_MAX_ID FROM MEMO_TEMPLET;
DBMS_OUTPUT.put_line('MEMO_TEMPLET最大ID' || CURRENT_MAX_ID);
FOR TEM_INSTANCE IN TEMPLATE_CUR LOOP
INSERT INTO MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT,
TEMPLET_TYPE, TEMPLET_MEMO) VALUES (CURRENT_MAX_ID,
TEM_INSTANCE.TEMPLET_CONTENT, '7', NULL);
CURRENT_MAX_ID := CURRENT_MAX_ID + 1;
DBMS_OUTPUT.put_line(TEM_INSTANCE.TEMPLET_CONTENT);
END LOOP;
END IF;
COMMIT;
www.2cto.com
4、exception塊，使用WHEN OTHERS THEN，其中用raise可顯示錯誤信息：
exception
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
raise;
ROLLBACK;
IF TEMPLATE_CUR%ISOPEN THEN
CLOSE TEMPLATE_CUR;
END IF;
DBMS_OUTPUT.put_line('列印信息');
end;
註：關於游標的使用，如果像本例中使用for循環去遍歷游標的話則不需要顯式的去用open/close
cursor打開和關閉游標，此處會自己處理，如果使用fetch
into的話就需要顯式開關游標，另外任意執行一個update操作，用隱式游標sql的屬性%found,%notfound,%rowcount,%isopen觀察update語句的執行情況，也可以使用來判斷游標狀態，如IF
TEMPLATE_CUR%ISOPEN THEN。
『捌』 ORACLE如何創建存儲過程，詳細例子。
--創建存儲過程 IN_SHEETID 為輸入參數CREATE OR REPLACE <a class="-highlight" href="https://www..com/s?wd=PROCEDURE&tn=44039180_cpr&fenlei=-EUBtkP1n1n1RdPjTvP1bvrH6kP10L" target="_blank">PROCEDURE</a> ST_RECEIPT(IN_SHEETID VARCHAR2) ISBEGIN--將 receipt0表中數據插入 receiptINSERT INTO RECEIPTSELECT * FROM RECEIPT0 WHERE SHEETID = IN_SHEETID;--更新receipt表中Flag值UPDATE RECEIPT SET FLAG = 100 WHERE SHEETID = IN_SHEETID;--INSERT INTO RECEIPTITEMSELECT * FROM RECEIPTITEM0 WHERE SHEETID = IN_SHEETID;--刪除ReceiptItem0表數據DELETE FROM RECEIPTITEM0 WHERE SHEETID = IN_SHEETID;--刪除 receipt0表數據DELETE FROM RECEIPT0 WHERE SHEETID = IN_SHEETID;<a class="-highlight" href="https://www..com/s?wd=END&tn=44039180_cpr&fenlei=-EUBtkP1n1n1RdPjTvP1bvrH6kP10L" target="_blank">END</a>;--調用存儲過程BEGINST_RECEIPT('ys141128');<a class="-highlight" href="https://www..com/s?wd=END&tn=44039180_cpr&fenlei=-EUBtkP1n1n1RdPjTvP1bvrH6kP10L" target="_blank">END</a>;--或者EXEC ST_RECEIPT('ys141128');
『玖』 oracle存儲過程實例 為什麼 要INTO
你說的是procere或者function中的select 列 into 變數 from 。。。這種結構吧。
這就是plsql的語法規則。不難理解，你在程序中從一張表中獲取了某列或某些列的值，要想使用肯定要賦值給變數的，那麼select 。。into 就是為了賦值而定義的一種語法。
『拾』 急求！怎麼建立oracle存儲過程(實例)
create or replace PROCEDURE st_receipt(p_sheetid varchar2(16))
AS
BEGIN
insert into receipt select * from receipt0 where sheetid=p_sheetid ; --將 receipt0表中數據插入 receipt
update receipt set Flag=100 wheresheetid=p_sheetid; --更新receipt表中Flag值
insert into ReceiptItem select * from ReceiptItem0 where sheetid=p_sheetid ;
delete from ReceiptItem0 where sheetid=p_sheetid ;--刪除ReceiptItem0表數據
delete from receipt0 where sheetid=p_sheetid ; --刪除 receipt0表數據
end ;
exec st_receipt('ys141128')