大家好,又见面了,我是你们的朋友全栈君。
存储过程:
存储过程是 SQL, PL/SQL, Java 语句的组合, 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是,代码存储一次但是能够被多个程序使用。是存放在数据库服务器上的SQL语句块,其效率高于同等SQL语句6-10倍
下面通过例子让你了解存储过程对数据的增删查改(对Oracle中的emp操作)
一、Oracle存储过程语法:
Create [or replace] procedure 存储过程名称 (输入参数或输出参数)]as
变量
Begin
执行主体
End;
二、IN, OUT , IN OUT 用来修饰参数。
IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。
OUT 表示 PRCEDURE 通过这个变量将值传回给调用者。
IN OUT 则是这两种的组合。
三、执行存储过程方式:
1、Call 存储过程名称(参数);
2、Execute 存储过程名称(参数);
注意:在oracle 数据库中,call命令任何窗口都能使用,但是execute只能在命令窗口使用,否则会报无效的SQL语句的异常。
四、在存储过程中需要注意事项:
1、在oracle数据库存储过程中,表别名不能用as
2、在oracle数据库存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录则必须使用游标处理
3、在使用select….into….时必须保证数据库有该数据,否则报”no data found”异常
4、在存储过程中,别名不能和字段名相同,否则虽然编译能通过,但是运行结果会报错
五、存储过程基本语法
--案例一:无参存储过程 --1.创建结构 CREATE PROCEDURE procedureName--存储过程名字 AS --as可替换成 is --声明变量 BEGIN --执行主体 END; --2.案例 create or replace procedure firstPro is begin dbms_output.put_line('Hello World');--打印输出 exception --存储过程异常 WHEN OTHERS THEN ROLLBACK; end firstPro; --3.数据库调用存储过程:执行结果:output: Hello World --(21) begin-end begin firstPro(); end; --(22)call call firstPro(); --4.删除存储过程 drop procedure firstPro;
--案例二:带参存储过程(in:入参) --1.案例 create or replace procedure secondPro(num in number) as begin dbms_output.put_line('The input num is :'||num); end; --2.调用 --(21) call secondPro(4); --(22) begin secondPro(7); end; --(23) declare n number; begin n := 1; secondPro(num=>n); end; --or Begin secondPro(num=>1); end; --备注:=> 是 Oracle 中调用 存储过程的时候, 指定 参数名进行调用 --一般如果是按顺序填写参数的时候,是不需要用=>符号的, --但是Oracle可以让你在调用的时候,指定"参数名称=>参数值", 这样就可以不按参数的顺序进行调用. -- => 前面的变量为存储过程的“形参”且必须于存储过程中定义的一致,而=>后的参数为“实际参数”。
--案例三:存储过程:声明变量 --1.案例 CREATE OR REPLACE procedure thirdPro is n_start number; n_end number; count_num number; use_time number; begin n_start:=dbms_utility.get_time; dbms_output.put_line('This statement start time : '|| n_start ); --查看oracle数据库版本 SELECT count(*) into count_num FROM v$version; n_end:=dbms_utility.get_time; dbms_output.put_line('This statement end time : '|| n_end ); use_time:= n_end - n_start; dbms_output.put_line('This statement cost '|| use_time ||' miliseconds'); end; --备注: --(1)dbms_utility.get_time 返回当前时间的1/100秒,毫秒 --它是用以前后两个取点做对s比的,单个是没有具体意义的,就是用来取差值的! --2.执行存储过程 --(21) call thirdPro(); --(22) begin thirdPro(); end;
--案例四:动态sql语句执行 --1.案例 CREATE OR REPLACE PROCEDURE FourthProc(id varchar2 ,dicName VARCHAR2) AS mysql VARCHAR2(500); BEGIN mysql:='UPDATE sys_dictionary SET dic_name=:1 WHERE id=:2'; EXECUTE IMMEDIATE mysql USING dicName,id; commit; END; --2.执行存储过程 CALL FourthProc('22ff8102-95cd-4862-a2ec-d011eca75ef1','男')
--案例五:返回结果集 --1.案例 create or replace procedure FifthPro( cur_OUT OUT SYS_REFCURSOR ) is begin OPEN cur_OUT FOR select cname,merch_no,taxno zjid,'营业执照' zjname,BUSINESS_LICENSE_VALIDITY validity,trunc(BUSINESS_LICENSE_VALIDITY-sysdate) gqdate from quas.base_merchant where BUSINESS_LICENSE_VALIDITY-sysdate < 60 ; end FifthPro;
2.案例五:Test测试存储过程步骤:Test-》点击Start Debugger 按钮:开始执行存储过程-》点击run按钮存储过程直接执行到结束,返回如图2的结果Cursor->点击右上角的按钮,查看结果集:如图3所示。(若点击step into 按钮,则进入存储过程详细代码,按步执行)
3.java代码执行存储过程:
/** * */ package kklazy.reportquery.service; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.transaction.Transactional; import org.hibernate.Session; import org.hibernate.internal.SessionFactoryImpl; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import kklazy.ctps.service.DefaultCtpsService; import kklazy.reportquery.model.ReportQueryEntity; import oracle.jdbc.internal.OracleTypes; /** * @author Administrator * */ @Service("fifthProService") @Transactional(rollbackOn=Exception.class) public class FifthProService extends DefaultCtpsService<ReportQueryEntity, String>{ //注入jdbc连接参数 @Value("${ctps.database.driver}") private String driverClass; @Value("${ctps.database.url}") private String url; @Value("${ctps.database.username}") private String username; @Value("${ctps.database.password}") private String password; /** * 执行存储过程 */ public List<ReportQueryEntity> execute() { Session session = (Session) this.getJpa().getManager().getDelegate(); SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory(); Connection conn = null; ResultSet result = null; List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>(); try { conn = sessionFactory.getConnectionProvider().getConnection(); if (conn == null || conn.isClosed()) { try { Class.forName(driverClass); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } try { CallableStatement call = conn.prepareCall("{call FifthPro(?,?)}"); System.out.println(); call.setString(1, "00"); call.registerOutParameter(2, OracleTypes.CURSOR); call.execute(); result = (ResultSet)call.getObject(2); if(result == null) { System.out.println("查询失败!"); }else { allList = getFindList(result); System.out.println("查询成功"); } System.out.println("执行存储过程的结果是:" + result); } catch (SQLException e) { e.printStackTrace(); System.out.println(e); }finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return allList; } public List<ReportQueryEntity> getFindList(ResultSet result) throws SQLException{ List<ReportQueryEntity> allList = new ArrayList<ReportQueryEntity>(); if(null !=result ) { while (result.next()) { ReportQueryEntity reportEntity = new ReportQueryEntity(); reportEntity.setData1(result.getString(1)); reportEntity.setData2(result.getString(2)); reportEntity.setData3(result.getString(3)); reportEntity.setData4(result.getString(4)); reportEntity.setData5(result.getString(5)); reportEntity.setData6(result.getString(6)); allList.add(reportEntity); } } return allList; } }
案例六、查询所有数据(游标的具体使用详见:https://www.cnblogs.com/xiaoliu66007/p/7495753.html)
PL/SQL 中 SELECT 语句只返回一行数据。如果超过一行数据,那么就要使用显式游标,INTO 子句中要有 SELECT子句中相同列数量的变量。
INTO 子句中也可以是记录变量。
--案例六:显式游标 --------1.用游标显示查询所有的结果 CREATE OR REPLACE procedure sys_dictionary_proc AS CURSOR dictionary_emp IS SELECT * FROM sys_dictionary where dic_group ='OPERATE_TYPE';--定义游标,该游标指向查询结果 rowresult sys_dictionary%ROWTYPE; BEGIN OPEN dictionary_emp;--打开游标 LOOP FETCH dictionary_emp INTO rowresult;--将游标中的值赋给rowresult EXIT WHEN dictionary_emp%NOTFOUND;--判断:游标不存在时跳出循环 dbms_output.put_line('分组:'||rowresult.dic_group||'值:'||rowresult.DIC_VALUE||'显示名:'||rowresult.dic_name); END LOOP; CLOSE dictionary_emp;--关闭游标 END; drop procedure sys_dictionary_proc; --2.调用 CALL sys_dictionary_proc();
3.output结果:
分组:OPERATE_TYPE值:1显示名:新增
分组:OPERATE_TYPE值:2显示名:修改
分组:OPERATE_TYPE值:3显示名:删除
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/154777.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...