oracle存储过程相关整理

oracle存储过程相关整理存储过程:存储过程是 SQL, PL/SQL, Java 语句的组合, 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是,代

大家好,又见面了,我是你们的朋友全栈君。

存储过程:

存储过程是 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 按钮,则进入存储过程详细代码,按步执行)

<span role="heading" aria-level="2">oracle存储过程相关整理<span role="heading" aria-level="2">oracle存储过程相关整理<span role="heading" aria-level="2">oracle存储过程相关整理

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账号...

(0)
blank

相关推荐

  • SecureCRT的使用教程( 过程非常详细!!值得查看)

    SecureCRT的使用教程( 过程非常详细!!值得查看)SecureCRT的使用教程(过程非常详细!!值得查看)下载和安装简单的介绍一、SecureCRT的使用二、SecureFX的使用下载和安装SecureCRT和SecureFX的下载和安装我这里就不多说了,详细的安装下载教程请参考:SecureCRT的下载、安装(过程非常详细!!值得查看)简单的介绍SecureCRT是一款支持SSH(SSH1和SSH2)的终端仿真程序,简单地说是…

  • WIN-CE系统架构

    WIN-CE系统架构分为素部分:硬件层、OEM层、操作系统层、应用层http://hovertree.com/hvtart/bjae/oy9pqb94.htm硬件层:由CPU、存储器、I/

    2021年12月27日
  • 三十名网友共同自主研发粤语打字软件「建议收藏」

    三十名网友共同自主研发粤语打字软件「建议收藏」来源:羊城晚报 日期:2007-7-23  王许乐是厚街镇前进小学的语文教师。2005年底,他和网上其他29人一起用半年时间研发了一套粤语打字软件,在网友中大受欢迎,下载量过万。王许乐等人研发的这套轻松粤拼输入法目前已经推出了两个版本,他们正打算推出进一步改良版。 王许乐一直致力于粤语研究。2005年底,一个偶然的机会,他在网络上认识了一大批热爱粤语的人,大家一起交流从简单的粤语方言到省

  • mediumtext_【紧急求助】关于mediumtext类型数据的调用

    mediumtext_【紧急求助】关于mediumtext类型数据的调用你的位置:问答吧->PHP基础->问题详情【紧急求助】关于mediumtext类型数据的调用请问各位大大,我如何将mediumtext类型的数据调用输入textarea文本框中呢?主要的问题是mediumtext内没有回车完全可以正常调用,但一旦有回车就无法进行调用,文本框中输入不了任何数据!恳请各位大大不…

  • 5G将撬动万亿物联网 然道阻且长

    5G将撬动万亿物联网 然道阻且长

  • idea 激活码【注册码】

    idea 激活码【注册码】,https://javaforall.cn/100143.html。详细ieda激活码不妨到全栈程序员必看教程网一起来了解一下吧!

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号