Oracle存儲(chǔ)過(guò)程語(yǔ)法
Oracle的存儲(chǔ)過(guò)程語(yǔ)法如下：
create procedure 存儲(chǔ)過(guò)程名稱（隨便?。?
is
在這里可以定義常量、變量、游標(biāo)、復(fù)雜數(shù)據(jù)類型這里可以定義變量、常量
begin
執(zhí)行部分
end;
（2）帶參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法：
create procedure 存儲(chǔ)過(guò)程名稱（隨便取） (變量1 數(shù)據(jù)類型,變量2 數(shù)據(jù)類型,...,變量n 數(shù)據(jù)類型)
is
在這里可以定義常量、變量、游標(biāo)、復(fù)雜數(shù)據(jù)類型這里可以定義變量、常量
begin
執(zhí)行部分
end;
（3）帶輸入、輸出參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法：
create procedure 存儲(chǔ)過(guò)程名稱（隨便?。?(變量1 in(或out) 數(shù)據(jù)類型,變量2 in(或out) 數(shù)據(jù)類型,...,變量n in(或out) 數(shù)據(jù)類型)
is
在這里可以定義常量、變量、游標(biāo)、復(fù)雜數(shù)據(jù)類型這里可以定義變量、常量
begin
執(zhí)行部分
end;
注意：用上面的語(yǔ)法創(chuàng)建存儲(chǔ)過(guò)程時(shí)可能會(huì)碰到數(shù)據(jù)庫(kù)中已經(jīng)有了同名的存儲(chǔ)過(guò)程，這樣Oracle就會(huì)彈框報(bào)錯(cuò)，說(shuō)名字已被現(xiàn)有對(duì)象使用。解決方法有兩種：
方法一：換個(gè)存儲(chǔ)過(guò)程名
方法二：在最開頭的create procedure 之間加上 or replace 關(guān)鍵字，例如：create or replace procedure 存儲(chǔ)過(guò)程名稱。但是這種方法不建議使用，因?yàn)檫@種方法會(huì)把之前同名的存儲(chǔ)過(guò)程替換為你當(dāng)前寫的這個(gè)
存儲(chǔ)過(guò)程案例一：沒(méi)參數(shù)的存儲(chǔ)過(guò)程
create replace procedure procedure_1
is
begin
dbms_output.put_line('procedure_1.......');
end;
存儲(chǔ)過(guò)程案例二：帶參數(shù)的的存儲(chǔ)過(guò)程
create procedure procedure_2(v_i number,v_j number)
is
v_m number(5);
begin
dbms_output.put_line('procedure_2.......');
v_m := v_i + v_j;
dbms_output.put_line(v_i||' + '||v_j||' = '||v_m);
end;
存儲(chǔ)過(guò)程案例三：帶輸入、輸出參數(shù)的存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)，
輸入?yún)?shù)：輸入?yún)?shù)一般會(huì)在變量名和數(shù)據(jù)類型之間加in來(lái)表示該參數(shù)是輸入?yún)?shù)
輸出參數(shù)：輸出參數(shù)一般會(huì)在變量名和數(shù)據(jù)類型之間加out來(lái)表示該變量是輸出參數(shù)
不寫in和out的話，默認(rèn)為輸入?yún)?shù)
create procedure procedure_3(v_i in number,v_j in number ,v_m out number)
is
begin
dbms_output.put_line('procedure_3.......');
v_m:=v_i - v_j;
dbms_output.put_line(v_i||' - '||v_j||' = '||v_m);
end;
PL/SQL塊中調(diào)用存儲(chǔ)過(guò)程
下面以調(diào)用上面三個(gè)存儲(chǔ)過(guò)程為例
declare
v_param1 number(5):=2;
v_param2 number(5):=8;
v_result number(5);
begin
--調(diào)用上面案例一的存儲(chǔ)過(guò)程
procedure_1();
--調(diào)用上面案例二的存儲(chǔ)過(guò)程
procedure_2(v_param1,v_param2);
--調(diào)用上面案例三的存儲(chǔ)過(guò)程
procedure_3(v_param1,v_param2,v_result);
dbms_output.put_line(v_result);
end;
/*執(zhí)行結(jié)果：*/
procedure_1.......
procedure_2.......
2 + 8 = 10
procedure_3.......
2 - 8 = -6
10
java調(diào)用存儲(chǔ)過(guò)程
案例一：java調(diào)用沒(méi)有返回值的存儲(chǔ)過(guò)程
要求：編寫一個(gè)像數(shù)據(jù)庫(kù)emp表插入一條編號(hào)為6666，姓名為張三，職位為MANAGER的記錄
/*存儲(chǔ)過(guò)程*/
create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )
is
begin
insert into emp (empno,ename,job) values (v_empno,v_ename,v_job);
end;
//java調(diào)用存儲(chǔ)過(guò)程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
//java調(diào)用存儲(chǔ)過(guò)程
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
cs=conn.prepareCall("{call procedure_4(?,?,?)}");
//給輸入?yún)?shù)賦值
cs.setInt(1, 6666);
cs.setString(2, "張三");
cs.setString(3, "MANAGER");
cs.execute();//執(zhí)行
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//關(guān)閉資源
}
}
//執(zhí)行后就會(huì)向數(shù)據(jù)庫(kù)的emp表中插入一條編號(hào)為6666，姓名為張三，職位為MANAGER的記錄
案例二：java調(diào)用返回單列單行的存儲(chǔ)過(guò)程
要求：編寫一個(gè)根據(jù)員工編號(hào)查找員工姓名的存儲(chǔ)過(guò)程，并用java調(diào)用該存儲(chǔ)過(guò)程
/*存儲(chǔ)過(guò)程*/
create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)
is
begin
select ename into v_ename from emp where empno=v_empno;
end;
//java調(diào)用存儲(chǔ)過(guò)程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_5(?,?)}");
cs.setInt(1, 6666);//給輸入?yún)?shù)賦值
/*指定輸出參數(shù)的數(shù)據(jù)類型
語(yǔ)法：oracle.jdbc.OracleTypes.輸出參數(shù)的數(shù)據(jù)類型
此例輸出參數(shù)的數(shù)據(jù)類型是varchar，所以是oracle.jdbc.OracleTypes.VARCHAR*/
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();//執(zhí)行
//獲取輸出參數(shù)的值，位置要和輸出參數(shù)對(duì)應(yīng)？的位置對(duì)應(yīng)起來(lái)，該例輸出參數(shù)對(duì)應(yīng)第2個(gè)問(wèn)號(hào)，而且輸出參數(shù)的數(shù)據(jù)類型為字符型，所以是cs.getString(2)
String a=cs.getString(2);
System.out.println("員工姓名："+a);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//關(guān)閉資源
}
}
/*執(zhí)行結(jié)果,控制臺(tái)打?。?/
結(jié)果：?jiǎn)T工姓名：張三
案例三：java調(diào)用返回單行多列的存儲(chǔ)過(guò)程
要求：編寫一個(gè)根據(jù)員工編號(hào)查找員工姓名、職位和工資的存儲(chǔ)過(guò)程，并用java調(diào)用該存儲(chǔ)過(guò)程
/*存儲(chǔ)過(guò)程*/
create procedure procedure_6(v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type)
is
begin
select ename,job,sal into v_ename,v_job,v_sal from emp where empno=v_empno;
end;
//java調(diào)用存儲(chǔ)過(guò)程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_6(?,?,?,?)}");
cs.setInt(1, 7788);
//指定輸出參數(shù)的數(shù)據(jù)類型，注意：順序要對(duì)應(yīng)起來(lái)
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);
cs.execute();//執(zhí)行
//獲取返回值
String ename=cs.getString(2);//獲取姓名
String job=cs.getString(3);//獲取職位
double sal=cs.getDouble(4);//獲取薪水
System.out.println("員工編號(hào)為7788的姓名為："+ename+" 職位是："+job+" 薪水是："+sal);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//關(guān)閉資源
}
}
/*執(zhí)行結(jié)果，控制臺(tái)打?。?/
員工編號(hào)為7788的姓名為：SCOTT 職位是：ANALYST 薪水是：3000.0
案例四：java調(diào)用返回多行多列（返回列表）的存儲(chǔ)過(guò)程
要求：編寫一個(gè)根據(jù)部門編號(hào)查找部門所有員工信息的存儲(chǔ)過(guò)程，并用java調(diào)用該存儲(chǔ)過(guò)程
/*定義游標(biāo)*/
create package my_package as
type emp_cursor is ref cursor;
end my_package;
/*存儲(chǔ)過(guò)程*/
create procedure procedure_7(v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor)
is
begin
open emp_cursor for select * from emp where deptno=v_deptno;
end;
//java調(diào)用存儲(chǔ)過(guò)程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_7(?,?)}");
cs.setInt(1, 20);//給輸入?yún)?shù)賦值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定輸出參數(shù)的數(shù)據(jù)類型
cs.execute();
rs=(ResultSet) cs.getObject(2);//獲取輸出參數(shù)的值
while(rs.next()){
//順序?yàn)閿?shù)據(jù)庫(kù)中字段前后順序，例如數(shù)據(jù)庫(kù)emp表中第5列為hiredate，數(shù)據(jù)類型為Date，所以獲取第5列值時(shí)就應(yīng)該用rs.getDate(5)
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getDate(5));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//關(guān)閉資源
}
}
/*以下就是20號(hào)部門所有員工的信息，這里為方便我們只打印了編號(hào)、姓名和入職時(shí)間
運(yùn)行結(jié)果，控制臺(tái)打?。?/
7369 SMITH 1980-12-17
7566 JONES 1981-04-02
7788 SCOTT 1987-04-19
7876 ADAMS 1987-05-23
7902 FORD 1981-12-03
這是上面java調(diào)用存儲(chǔ)過(guò)程代碼中關(guān)閉資源方法的代碼
public static void closeResource(Connection conn,CallableStatement cs,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
最后給個(gè)應(yīng)用，分頁(yè)的存儲(chǔ)過(guò)程
分頁(yè)存儲(chǔ)過(guò)程：
/*定義游標(biāo)*/
create package page_package as
type page_cursor is ref cursor;
end page_package;
/*存儲(chǔ)過(guò)程*/
create procedure pro_paging (
v_page_size in number,--每頁(yè)顯示多少條
v_page_count out number,--總頁(yè)數(shù)
v_current_page in number,--當(dāng)前頁(yè)
v_total_count out number,--記錄總條數(shù)
emp_cursor out page_package.page_cursor--返回查詢結(jié)果集的游標(biāo)
)
is
v_begin number(5):=v_page_size*(v_current_page-1)+1;--查詢起始位置
v_end number(5):=v_page_size*v_current_page;--查詢結(jié)束位置
v_sql varchar2(1000):='select empno,ename from
(select a.empno,a.ename,rownum rn from
(select empno,ename from emp) a
where rownum='|| v_end ||') b
where b.rn>='||v_begin;
/*不能像下面這么寫，不然調(diào)用該存儲(chǔ)過(guò)程時(shí)會(huì)報(bào)類型不一致的錯(cuò)，因?yàn)樽罾锩娌榈闹挥衑mpno,ename，因此外面也要和里面保持一致
v_sql varchar2(1000):=\'select * from
(select a.*,rownum rn from
(select empno,ename from emp) a
where rownum='|| v_end ||\') b
where b.rn>='||v_begin;*/
v_ename varchar2(10);
v_empno number(4);
begin
open emp_cursor for v_sql;
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename);
end loop;
v_sql:='select count(empno) from emp';
execute immediate v_sql into v_total_count;
if(mod(v_total_count,v_page_size)=0) then
v_page_count:=v_total_count/v_page_size;
else
v_page_count:=trunc(v_total_count/v_page_size)+1;
end if;
dbms_output.put_line('共 '||v_total_count||' 條記錄');
dbms_output.put_line('共 '||v_page_count||' 頁(yè)');
dbms_output.put_line('當(dāng)前頁(yè)： '||v_current_page);
dbms_output.put_line('每頁(yè)顯示 '||v_page_size||' 條');
end;
Java調(diào)用的話和上面java調(diào)用存儲(chǔ)過(guò)程的例子一樣。這里為了方便 ，就直接在pl/sql中調(diào)用了
/*調(diào)用分頁(yè)存儲(chǔ)過(guò)程*/
declare
v_page_count number(5);
v_cursor page_package.page_cursor;
v_total_count number(5);
begin
dbms_output.put_line('第一頁(yè)數(shù)據(jù)。。。。。。。。。');
pro_paging(5,--每頁(yè)顯示5條
v_page_count,--總頁(yè)數(shù)
1,--當(dāng)前頁(yè)
v_total_count,--記錄總條數(shù)
v_cursor--游標(biāo)
);
dbms_output.put_line('--------------------------');
dbms_output.put_line('第二頁(yè)數(shù)據(jù)。。。。。。。。。');
--顯示第二頁(yè)數(shù)據(jù)
pro_paging(5,--每頁(yè)顯示5條
v_page_count,--總頁(yè)數(shù)
2,--當(dāng)前頁(yè)
v_total_count,--記錄總條數(shù)
v_cursor--游標(biāo)
);
end;
/*運(yùn)行結(jié)果：*/
第一頁(yè)數(shù)據(jù)。。。。。。。。。
6666 張三
20 empSu2
19 empSave2
7369 SMITH
7499 ALLEN
共 17 條記錄
共 4 頁(yè)
當(dāng)前頁(yè)： 1
每頁(yè)顯示 5 條
--------------------------
第二頁(yè)數(shù)據(jù)。。。。。。。。。
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
共 17 條記錄
共 4 頁(yè)
當(dāng)前頁(yè)： 2
每頁(yè)顯示 5 條
以上所述是小編給大家介紹的Oracle存儲(chǔ)過(guò)程及調(diào)用，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
您可能感興趣的文章:- oracle 存儲(chǔ)過(guò)程、函數(shù)和觸發(fā)器用法實(shí)例詳解
- windows中oracle存儲(chǔ)過(guò)程加密的實(shí)例代碼
- Oracle帶輸入輸出參數(shù)存儲(chǔ)過(guò)程(包括sql分頁(yè)功能)
- oracle中print_table存儲(chǔ)過(guò)程實(shí)例介紹
- Mybatis調(diào)用Oracle存儲(chǔ)過(guò)程的方法圖文詳解
- 詳解Oracle調(diào)試存儲(chǔ)過(guò)程
- Oracle存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)創(chuàng)建方法(詳解)
- Oracle存儲(chǔ)過(guò)程、包、方法使用總結(jié)(推薦)
- Oracle生成單據(jù)編號(hào)存儲(chǔ)過(guò)程的實(shí)例代碼
- Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過(guò)程的示例詳解
