Oracle存儲過程

什么是存儲過程

存儲過程是一組為了完成特定功能的SQL語句，經編譯后存在數據庫，存儲過程是數據庫中的一個重要對象。

ps:有人說：任何一個設計良好的數據庫應用程序都應該用到存儲過程，我覺得這個不一定，其實很多互聯網公司的WEB應用程序都沒有用到存儲過程，難道就能說他們的應用程序設計的不好嗎？現實中，銀行、金融相關的公司使用存儲過程多一些。

存儲過程的優缺點

優點

  1. 效率高：創建的時候就會編譯，調用時不需要重新編譯，而直接寫SQL每次執行都先編譯再執行。
  2. 安全：與直接寫SQL語句相比，存儲過程不存在SQL注入的問題。可以向用戶授予存儲過程的訪問權限，而不是直接授予表的訪問權限，控制對特定數據的訪問。
  3. 降低網絡流量：存儲過程直接存儲在數據庫中，所以不會產生大量的SQL代碼流量。

缺點

  1. 可移植性很差：應用程序更換其他數據庫，需要將原有的存儲過程重新寫一遍，并且如果你的存儲過程中包含大量的業務邏輯，這樣就會更加麻煩。
  2. 代碼可復用性很差：面向對象的思維在這里完全沒有用，兩個很相近的功能，需要寫兩個存儲過程，雖然可以相互調用，但是不能利用繼承等面向對象的功能。

語法

create or replace procedure <存儲過程名>
[(
參數列表：p_name1 in <輸入參數數據類型>,p_name2 out <輸出參數數據類型>
,p_name3 in out <參數數據類型>
)]
as或is
[定義變量：v_name1 <變量數據類型>]
begin
? <執行特定的操作或者任務，可以進行增刪改查>
? end;

注意

如果沒有參數（）要去掉，存儲過程主體不能為空（begin和end之間不能什么都不做）

說明

  1. in表示輸入參數，out表示輸出參數，可以同時使用in和out表示既是輸入參數又是輸出參數。
  2. 聲明變量時可以使用 列名+%type 來聲明類型
  3. 存儲過程主體做select查詢時，必須使用into將結果集保存到變量中
  4. :=表示賦值操作

DEMO

登錄Oracle數據庫scott用戶，初始密碼tigger

1.定義一個存儲過程，用來查詢指定部門的員工的人數和平均工資，如果部門不存在，就引發一個自定義異常，顯示“部門編號不存在！”(部門編號作為存儲過程的輸入參數，在調用存儲過程前由用戶輸入，該部門員工的人數和平均工資需要返回)

創建

create or replace procedure proc1(
p_deptno in emp.deptno%type,
p_count_emp out number ,
p_avg_sal out emp.sal%type
)
is
v_row_num number:=0;
error1 exception;
beginselect count(*) into p_count_emp from emp e where e.deptno=p_deptno;if p_count_emp!=0  thenselect avg(e.sal) into p_avg_sal from emp e where e.deptno=p_deptno;else raise error1;end if;exception when error1 thendbms_output.put_line('部門編號不存在');
end;

語句塊調用

declare
a emp.deptno%type;
b number:=0;
c emp.sal%type:=0;
begina:=&部門編號;proc1(a,b,c);dbms_output.put_line('部門編號為'||a||'的員工人數為' ||b||'，平均工資為：'||c);end;

2.存儲過程相互調用

創建

--根據員工編號刪除員工
create or replace procedure proc3
(p_empno in emp.empno%type
)
as
begindelete from emp e where e.empno=p_empno;commit;end;--首先調用上面的那個存儲過程，然后用輸出參數返回剩下的員工人數
create or replace procedure proc4(
p_empno in emp.empno%type,
p_nums_afterdelete out number)
as
beginproc3(p_empno);select count(*) into p_nums_afterdelete from emp;end;

語句塊調用

declare
p_empno emp.empno%type:=&請輸入員工編號;
p_nums_afterdelete number:=0;
beginproc4(p_empno,p_nums_afterdelete);dbms_output.put_line('已成功刪除員工編號為'||p_empno||'的員工'||',當前還剩的員工總數為'||p_nums_afterdelete);end;

3.使用call關鍵字直接調用存儲過程

create or replace procedure proc2(a in number,b in number)
as 
c number;
beginc :=a+b;dbms_output.put_line(c);end;call proc2(1,1);

JAVA調用存儲過程

首先需要下載classes12.jar、ojdbc14.jar，并導入到自己的工程里。

插入數據

package java_procedure;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;//只需ojdbcjar包就行
/*--java調用存儲過程
create table tb_student(
stu_id varchar2(20),
stu_name varchar2(20)
);
--①沒有返回值的存儲過程
create or replace procedure TESTA(
p_stu_id   in tb_student.stu_id%type,
p_stu_name in tb_student.stu_name%type) 
as
begininsert into tb_student(stu_id, stu_name)values(p_stu_id, p_stu_name);
end TESTA;
/*/
public class TestProcedureOne {public static void main(String[] args) {CallableStatement cs=null;Connection conn=null;String driver="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@localhost:1521:orcl";String userName="a3";String pwd="a123";try {Class.forName(driver);conn=DriverManager.getConnection(url,userName,pwd);cs=conn.prepareCall("{call testa(?,?)}");cs.setString(1,"001");cs.setString(2, "zhangsan");cs.execute();} catch (ClassNotFoundException e) {e.printStackTrace();}catch (SQLException e) {e.printStackTrace();}finally{try {if (cs!=null) {cs.close();}if (conn!=null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}}

條件查詢

package java_procedure;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;//只需ojdbcjar包就行
/*
--②有返回值的存儲過程
CREATE OR REPLACE PROCEDURE TESTB(
p_stu_id   in tb_student.stu_id%type,
p_stu_name out tb_student.stu_name%type) AS
BEGINSELECT stu_name INTO p_stu_name FROM tb_student WHERE stu_id = p_stu_id;
END TESTB;
/*/
public class TestProcedureTwo {public static void main(String[] args) {CallableStatement cs=null;Connection conn=null;String driver="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@localhost:1521:orcl";String userName="a3";String pwd="a123";try {Class.forName(driver);conn=DriverManager.getConnection(url,userName,pwd);cs=conn.prepareCall("{call testb(?,?)}");cs.setString(1,"001");cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//registerOutParameter()方法的第二個參數：如果是MySQL的驅動，使用Types.VARCHARcs.execute();String stuName=cs.getString(2);System.out.println(stuName);} catch (ClassNotFoundException e) {e.printStackTrace();}catch (SQLException e) {e.printStackTrace();}finally{try {if (cs!=null) {cs.close();}if (conn!=null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}}

查詢所有

package java_procedure;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;//要用classes12.jar，ojdbcjar包有bug
/*
--返回一個列表的存儲過程
--建一個程序包
CREATE OR REPLACE PACKAGE TESTPACKAGE  ASTYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
--建立存儲過程
CREATE OR REPLACE PROCEDURE TESTC(
p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGINOPEN p_CURSOR FOR SELECT * FROM tb_student;
END TESTC;*/
public class TestProcedureThree {public static void main(String[] args) {CallableStatement cs=null;Connection conn=null;ResultSet rs=null;String driver="oracle.jdbc.driver.OracleDriver";String url="jdbc:oracle:thin:@localhost:1521:orcl";String userName="a3";String pwd="a123";try {Class.forName(driver);conn=DriverManager.getConnection(url,userName,pwd);cs=conn.prepareCall("{call testc(?)}");cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);cs.execute();rs=(ResultSet)cs.getObject(1);//上面一行代碼可以這樣寫: rs=((OracleCallableStatement)cs).getCursor(1);while(rs.next()){System.out.println("ID:"+rs.getString(1)+"\t"+"姓名："+rs.getString(2));}} catch (ClassNotFoundException e) {e.printStackTrace();}catch (SQLException e) {e.printStackTrace();}finally{try {if (rs!=null) {rs.close();}if (cs!=null) {cs.close();}if (conn!=null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}}

存儲過程返回游標類型

  • 創建新表，使用bulk collect into
create or replace procedure getHighSalPerson(p_emp out sys_refcursor)
is
v_avg_sal number;
beginselect avg(sal) into v_avg_sal from emp ;open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;create table t_emp_high_sal(empno varchar2(100 byte),ename varchar2(100 byte),job varchar2(100 byte),sal number);declare
type row_type is table of  t_emp_high_sal%rowtype;
emp_cursor sys_refcursor;
emp_row row_type;
begingetHighSalPerson(emp_cursor);fetch emp_cursor bulk collect into emp_row;for i in 1..emp_row.count loopdbms_output.put_line('員工編號:'||emp_row(i).empno||',員工姓名：'||emp_row(i).ename||',職位：'||emp_row(i).job||',工資：'||emp_row(i).sal);end loop;close emp_cursor;
end;
  • 定義包類型
create or replace procedure getHighSalPerson(p_emp out getHighsalperson_package.type_cursor)
is
v_avg_sal number;
beginselect avg(sal) into v_avg_sal from emp ;open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;create or replace package getHighsalperson_package
istype type_cursor is ref cursor;type type_record is record(empno varchar2(100 byte),ename varchar2(100 byte),job varchar2(100 byte),sal number);
end;declare
emp_cursor getHighsalperson_package.type_cursor;
emp_row getHighsalperson_package.type_record;
begingetHighSalPerson(emp_cursor);loop fetch emp_cursor into emp_row;exit when emp_cursor%notfound;dbms_output.put_line('員工編號:'||emp_row.empno||',員工姓名：'||emp_row.ename||',職位：'||emp_row.job||',工資：'||emp_row.sal);end loop;close emp_cursor;
end;
  • 直接用空結果集的游標
create or replace procedure getHighSalPerson(p_emp out sys_refcursor)
is
v_avg_sal number;
beginselect avg(sal) into v_avg_sal from emp ;open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;declare
cursor emp_cur is select empno,ename,job,sal from emp where 1=2;
emp_cursor sys_refcursor;
emp_row emp_cur%rowtype;
begingetHighSalPerson(emp_cursor);loop fetch emp_cursor into emp_row;exit when emp_cursor%notfound;dbms_output.put_line('員工編號:'||emp_row.empno||',員工姓名：'||emp_row.ename||',職位：'||emp_row.job||',工資：'||emp_row.sal);end loop;close emp_cursor;
end;

?

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/530421.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/530421.shtml
英文地址，請注明出處：http://en.pswp.cn/news/530421.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

解決debug JDK source無法查看局部變量的問題

解決debug JDK source無法查看局部變量的問題

首先進入傳送門&#xff1a;https://blog.csdn.net/majian_1987/article/details/51273609 進行上面的操作之后&#xff0c;這個時候我們來debug一下jdk源碼&#xff0c;發現可以查看局部變量。但是這時候又發現一個問題&#xff0c;在我自己的代碼里按住ctrl鼠標左鍵單擊無法…
閱讀更多...
【JAVA基礎篇】多線程

【JAVA基礎篇】多線程

學習Java的多線程知識之前&#xff0c;我們先來了解一下進程和線程的概念&#xff0c;以及他們之間的關系。 進程 基本概念 進程是具有獨立功能的程序在某個數據集合上的一次執行過程。 特點 進程是操作系統進行資源分配的基本單位。每個進程都有自己的地址空間&#xff0…
閱讀更多...
配置JAVA開發環境

配置JAVA開發環境

1、首先去Oracle官方網站下載所需版本的JDKhttp://java.sun.com/products/archive/&#xff0c;然后安裝&#xff0c;其實只需要安裝jdk就可以了&#xff0c;不需要安裝jre 2、按照后配置環境變量JAVA_HOME 然后在path最前面添加%JAVA_HOME%\bin; 3、cmd中輸入java -version,提…
閱讀更多...
【JAVA基礎篇】枚舉

【JAVA基礎篇】枚舉

/*** 一組相同類型的常量的集合* author cc**/ public class EnumDemo {public static void main(String[] args) {//遍歷for(WorkDay workDay:WorkDay.values()){System.out.println("序號&#xff1a;"workDay.ordinal());//ordinal&#xff0c;返回枚舉常量的序號…
閱讀更多...
【JAVA基礎篇】集合框架

【JAVA基礎篇】集合框架

一、集合框架圖 Java集合框架主要包含兩種類型的容器&#xff0c;一是集合(Collection)&#xff0c;存儲元素集合&#xff0c;二是圖(Map)&#xff0c;存儲鍵(key)-值(value)對.Collection接口下面有兩個重要的子接口List和Set&#xff0c;再下面是一些抽象類&#xff0c;最后是…
閱讀更多...
【JAVA基礎篇】對象初始化過程

【JAVA基礎篇】對象初始化過程

我們都知道&#xff0c;創建對象是由 new關鍵字調用構造方法 返回類實例&#xff08;實際上還可以通過反射來創建實例&#xff09;。 例如 : Person jack new Person(); 這句話到底做了什么事情呢 &#xff1f; 其實就是講對象的初始化過程。 1、 new 用到了Person.class,所…
閱讀更多...
【Java基礎篇】try catch finally語句包含return語句時的執行過程

【Java基礎篇】try catch finally語句包含return語句時的執行過程

網上有很多人探討Java中異常捕獲機制try…catch…finally塊中的finally語句是不是一定會被執行&#xff1f; 很多人都說不是&#xff0c;當然他們的回答是正確的&#xff0c;經過我試驗&#xff0c;至少有兩種情況下finally語句是不會被 try語句沒有被執行到&#xff0c;如在…
閱讀更多...
eclipse指定JDK版本啟動，解決version XXX of the JVM is not suitable for this product.Version:XXX 問題

eclipse指定JDK版本啟動，解決version XXX of the JVM is not suitable for this product.Version:XXX 問題

問題描述&#xff1a;啟動eclipse時&#xff0c;提示version 1.7.0 of the JVM is not suitable for this product.Version:1.8 or greater is required. 原因分析&#xff1a;原因是我的筆記本安裝了多個JDK版本&#xff0c;但是現在我的JAVA_HOME配置的是jdk1.7的路徑&#x…
閱讀更多...
【JAVA基礎篇】Socket編程

【JAVA基礎篇】Socket編程

一、Socket的概念 Socket是一種通訊機制&#xff0c;通常稱為套接字。英文原意是插座&#xff0c;顧明思義&#xff0c;Socket像是一個多孔插座&#xff0c;可以提供多個端口的連接服務 ps:至于socket在計算機術語中怎么就翻譯成了“套接字”這個令人費解的詞&#xff0c;這真…
閱讀更多...
【JAVA基礎篇】注解

【JAVA基礎篇】注解

一、什么是注解&#xff1f; 注解是元數據&#xff0c;所謂元數據就是描述數據的數據。 在annotation誕生之前&#xff08;jdk1.5誕生了注解&#xff09;&#xff0c;甚至之后&#xff0c;xml被廣泛的由于描述元數據。但是后來&#xff0c;有一些應用開發工程師和架構師覺得它…
閱讀更多...
【JAVA基礎篇】IO流

【JAVA基礎篇】IO流

一、流的概念 “對語言設計人員來說&#xff0c;創建好的輸入&#xff0f;輸出系統是一項特別困難的任務。” ――《Think in Java》 無論是系統、還是語言的設計中IO的設計都是異常復雜的。面臨的最大的挑戰一般是如何覆蓋所有可能的因素&#xff0c;我們不僅僅要考慮文件、…
閱讀更多...
SpringMVC注解

SpringMVC注解

一&#xff0c;RequestMapping 可以用在類和方法上 1.1 作用&#xff1a; 將客戶端請求映射到可匹配的類和方法中 1.2 屬性&#xff1a; name 給映射指定一個名字 path(同value相同&#xff09; 請求的url&#xff0c;path{"/mixedAttribute1","/mixedA…
閱讀更多...
【JAVA基礎篇】運算符

【JAVA基礎篇】運算符

一、表達式 表達式由運算符和操作數組成 例如&#xff1a; 5 num1 num1num2 sumnum1num2 二、運算符分類 算數運算符、賦值運算符、關系運算符、邏輯運算符、條件運算符、位運算符 三、算數運算符 四、賦值運算符 格式&#xff1a;變量表達式 例如&#xff1a;int n3…
閱讀更多...
a4紙網頁打印 table_打印模板不愁人，你還在打印單調的A4紙嗎？

a4紙網頁打印 table_打印模板不愁人，你還在打印單調的A4紙嗎？

軟件介紹早在幾年前&#xff0c;社會上就已經開始了數字化、無紙化的推廣&#xff0c;但是就算再怎么無紙化&#xff0c;紙張還是有它必要的存在&#xff0c;在工作、學習過程中&#xff0c;打印的需求也必不可少的。但是一般的打印都是比較平庸的&#xff0c;要做會議記錄&…
閱讀更多...
IP地址、子網掩碼、網關、默認網關、DNS的理解

IP地址、子網掩碼、網關、默認網關、DNS的理解

IP地址 Internet上為了區分數以億計的主機而給每個主機分配一個專門的地址&#xff0c;通過IP地址可以訪問到每臺主機。 子網掩碼 子網掩碼又稱網絡掩碼、地址掩碼、子網絡遮罩。它是用來指明一個IP地址哪些位標識的是主機所在的子網&#xff0c;以及哪些位標識的是主機的位…
閱讀更多...
上證指數30年k線圖_技術預判2020：上證指數要突破3500點才會“井噴”

上證指數30年k線圖_技術預判2020：上證指數要突破3500點才會“井噴”

2019年的行情很快就要收官了&#xff0c;截止目前&#xff0c;上證指數今年的漲幅是20.5%&#xff0c;不過可能有部分投資者今年的收益率還沒達到大盤指數的平均水平。不管怎樣&#xff0c;今年很快就要翻篇了&#xff0c;關鍵是看2020年股市能不能迎來更好的行情了。而總結得失…
閱讀更多...
【JAVA基礎篇】基本數據類型及自動類型轉換

【JAVA基礎篇】基本數據類型及自動類型轉換

一、8種基本數據類型以及占用內存空間大小 boolean 1byte或4byte byte 8bit/1byte char 16bit/2byte short 16bit/2byte float 32bit/4byte int 32bit/4byte long 64bit/8byte double 64bit/8byte 二、自動類型轉換 …
閱讀更多...
的優缺點_淺談桉木家具的優缺點

的優缺點_淺談桉木家具的優缺點

家具現在的材質是有很多的&#xff0c;木質的&#xff0c;石材的&#xff0c;還有真空充氣的&#xff0c;都是很不錯的類型。桉木家具是現在很多人都喜歡的一種材質&#xff0c;但是很多人對桉木家具的優缺點不是很清楚&#xff0c;為了能夠讓大家更加清楚的了解桉木家具&#…
閱讀更多...
【算法篇】遞歸

【算法篇】遞歸

一、遞歸的概念 程序調用自身的編程技巧稱為遞歸。 遞歸的核心思想就是將一個大規模復雜的問題層層轉化為一個與原問題相似的規模較小的問題來求解。 二、遞歸的優點 使用遞歸的好處是只需要少量的代碼就可以描述出求解問題過程中多次重復的計算&#xff0c;大大減少了程序…
閱讀更多...
客戶說發貨慢怎么回復_女生微信說身體不舒服怎么回復關心她？

客戶說發貨慢怎么回復_女生微信說身體不舒服怎么回復關心她？

當你不在女生身邊&#xff0c;女生微信給你說身體不舒服&#xff0c;肯定需要說點話來安慰她了。多喝熱水肯定是不行了&#xff0c;一點用處都沒有&#xff0c;還會讓女生覺得你根本不重視她&#xff0c;是在敷衍她&#xff0c;那女生微信說身體不舒服怎么回復關心她呢&#xf…
閱讀更多...
最新文章

Copyright @ 2022~2023