oracle存储过程相关整理
大家好,又见面了,我是你们的朋友全栈君。
存储过程:
存储过程是 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显示名:删除
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/154777.html原文链接:https://javaforall.cn
相关文章
- Oracle存储过程及调用
- Oracle存储过程、包、方法使用总结(推荐)
- 深入Oracle:学习过程编写(oracle过程编写)
- 利用Oracle存储过程实现数据输出(oracle存储过程输出)
- 使用 Oracle 动态程序获得更高效率(oracle动态过程)
- 数据库Oracle数据库管理员:专业技能带来持续成功(管理员oracle)
- 存储Oracle数据查询与存储技术简介(oracle查询和)
- 优化数据存储:深度解析Oracle表分区的作用(oracle表分区的作用)
- Oracle 中存储日期:最佳实践(oracle保存日期)
- 提升Oracle工程师:喜迎待遇提升(oracle工程师待遇)
- Oracle数据库存储已满,需要清理或扩容(oracle数据库满了)
- 深入学习 Oracle 查询列名(oracle 查询 列名)
- Oracle安装体验一次典型的安装设置过程(oracle典型安装设置)
- Oracle关键字优化能力分析(oracle 关键字占用)
- Oracle全闪存储最佳的性能体验(oracle全闪存储性能)
- Oracle写入过程提升数据插入性能的简单方法(oracle写入过程)
- Oracle 162升级突破性性能及安全改善(n16.2oracle)
- 使用MD加密技术保护Oracle数据安全性(md加密 oracle)
- 通过命令行连接Oracle数据库(cmd oracle链接)
- Oracle主从复制架构的风险分析(oracle主从风险)
- 过程Oracle数据库中视图与存储过程的比较与利用(Oracle中视图与存储)
- Oracle交换空间占用过高,如何缓解(oracle交换空间过高)
- 存储过程Oracle中不可比拟的存储过程(oracle中不像)
- Oracle查询中的双重条件筛选(oracle两个条件)
- 利用Oracle SaaS满足企业存储需求(oracle saas)
- Oracle OU培训掌握数据库开发技能(oracle ou培训)
- 库Oracle GP数据库深入加强你的数据管理能力(oracle gp数据)
- Oracle CMS软件让你体验不一样的数据管理(oracle cms软件)