欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle存储过程的学习(基础知识)

程序员文章站 2022-09-14 14:33:25
ORACLE中存储过程学习存储过程:可以理解为Java语言中的方法或函数。工具:PL/SQL注:存储过程中不区分大小写一、程序结构介绍1.1 打印hello word创建测试窗口-- Created on 2020/7/2 by 86152 declare -- Local variables here -- 声明变量,游标,不需要可以省略 i integer;begin -- Test statements here -- 执行语句 Dbms_Output....

ORACLE中存储过程学习

存储过程:可以理解为Java语言中的方法或函数。

工具:PL/SQL

注:存储过程中不区分大小写

一、程序结构介绍

1.1 打印hello word

创建测试窗口

-- Created on 2020/7/2 by 86152 
declare 
  -- Local variables here
  -- 声明变量,游标,不需要可以省略
  i integer;
begin
  -- Test statements here
  -- 执行语句
  Dbms_Output.put_line('Hello world!');
  -- 相当于java中 sout('Hello world!')
  -- [异常处理]
end;

1.2

在命令窗口或者SQL plus中执行以上的存储过程无法打印出结果,因为程序默认关闭了输出选项。

可以通过 set serveroutput on 开启。 斜杠( / ) 表示结束输入。

1.3 变量

变量分为两大类:

​ 1、普通数据类型:(char,varchar2,date,number,boolean,long)

​ 2、特殊变量类型(引用型变量、记录型变量)

1.4 变量赋值方式:

-- 两种方式
-- 1、直接赋值  name := '张三'
-- 2、语句赋值  select '张三' into 'name'

1、eg:

-- Created on 2020/7/2 by 86152 
declare 
  -- Local variables here
  i integer;
  
  -- 姓名
  sname varchar2(20) := '张三';
  -- 薪水
  salary number;
  -- 地址
  address varchar2(200);
  
begin
  -- Test statements here
  
  -- 直接赋值
  salary := 2000;
  -- 语句赋值
  select '海南' into address from dual;
  
  -- 打印语句输出
  Dbms_Output.put_line('姓名:'||sname||' 薪水:'||salary||' 地址:'||address);
end;

2、eg:

变量的类型和长度取决于表中字段的类型和长度

sname emp.ename%TYPE

3、记录型变量(引用类型)相当于Java中的一个对象,即数据库中的一行数据

v_emp emp%ROWTYPE;

-- Created on 2020/7/2 by 86152 
declare 
  -- Local variables here
  i integer;
  
  -- 姓名
  sname varchar2(20) := '张三';
  -- 薪水
  salary number;
  -- 地址
  address varchar2(200);
  
  -- 特殊类型:引用型变量
  t_menu_id sys_menu.menu_id%type;
  t_menu_name sys_menu.menu_name%Type;
  
  -- 记录型变量
  t_menu sys_menu%rowtype;
  
begin
  -- Test statements here
  
  -- 直接赋值
  salary := 2000;
  -- 语句赋值
  select '海南' into address from dual;
  
  -- 打印语句输出
  Dbms_Output.put_line('姓名:'||sname||',薪水:'||salary||',地址:'||address);
  
  
  --  =============
  select  a.menu_id,a.menu_name into t_menu_id,t_menu_name from sys_menu a where a.menu_id='200100100200';
  select a.* into t_menu  from sys_menu a where a.menu_id='200100100200';
  
  
  dbms_output.put_line(t_menu_id||','||t_menu_name);
  dbms_output.put_line(t_menu.menu_id||','||t_menu.menu_name);

  
end;

二、流程控制

2.1 条件分析if

-- Created on 2020/7/2 by 86152 
declare 
  -- Local variables here
  num integer;
begin
  -- Test statements here
  select count(1) into num from sys_menu a;
  if num>200 then
    dbms_output.put_line('表中数据为:'||num||',大于200条。');
    elsif num>100 then
    dbms_output.put_line('表中数据为:'||num||',大于100条且不大于200条。');
    else
    dbms_output.put_line('表中数据为:'||num||',小于等于100条');
  end if;
  
end;	

2.2 循环loop

-- Created on 2020/7/2 by 86152 
declare 
  -- Local variables here
  i integer := 1;
begin
  -- Test statements here
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

3、游标Cursor

用来存储一个查询返回的多条数据(Java中的ResultSet)
-- Created on 2020/7/2 by 86152 
declare 
  -- 声明游标
  cursor c_menu(t_level sys_menu.menu_lvl%TYPE) is  
  select a.menu_id,a.menu_name from sys_menu a where a.menu_lvl=t_level;
  -- 声明变量接收游标中的值
  v_id sys_menu.menu_id%type;
  v_name sys_menu.menu_name%type;
  
begin
  -- 打开游标
  open c_menu(3);
  
  --遍历
  loop
    
    -- 获取游标中的值
    fetch c_menu into v_id,v_name;
    --判断退出循环     
    exit when c_menu%notfound;
    
    dbms_output.put_line(v_id||' - '||v_name);
    
  end loop;
  
  -- 关闭游标
  close c_menu;
  
end;

三、存储过程(procedure):

Oracle给的建议:能在数据库中操作的,不要放入程序中。减少数据库的交互,提升效率。

3.1 创建一个存储过程

create or replace procedure p_hello is
-- 声明变量

begin
  dbms_output.put_line('Hello world!');
end p_hello;
-- ||有参
create or replace procedure p_querymenubyid(i_id in sys_menu.menu_id%TYPE) is

t_name sys_menu.menu_name%Type;

begin
       
select a.menu_name into t_name from sys_menu a where a.menu_id = i_id;

dbms_output.put_line(t_name);
  

end p_querymenubyid;

3.2 调用存储过程方法:

1、在测试窗口中

begin
– Test statements here
p_hello;
end;

2、在命令窗口

exec p_hello

3.3 创建带返回值输出的存储过程

create or replace procedure p_querymenubyid(i_id in sys_menu.menu_id%TYPE) is

t_name sys_menu.menu_name%Type;

begin
       
select a.menu_name into t_name from sys_menu a where a.menu_id = i_id;

dbms_output.put_line(t_name);
  

end p_querymenubyid;

3.4 在PL/SQL中调用

declare
-- 声明变量
i_id sys_menu.menu_id%Type := '200200100200';
o_name sys_menu.menu_name%Type;
begin
   p_inid_outname_menu(i_id,o_name);
   dbms_output.put_line(o_name);
end;

3.5 在java中调用

package com.ityj.ssm.utils;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import org.junit.jupiter.api.Test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class ProduceTest {


    @Test
    public void testProduce() throws Exception {

        // 1、注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2、获取链接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:oracle";
        String username = "c##xiao";
        String password = "xiao";
        Connection connection = DriverManager.getConnection(url, username, password);

        // 3、获得语句对象
        String sql = "{call p_inid_outname_menu(?,?)}";
        CallableStatement callableStatement = connection.prepareCall(sql);

        // 4、设置输入参数
        callableStatement.setString(1,"100100100911");

        // 5、注册输出参数
        callableStatement.registerOutParameter(2 , OracleTypes.VARCHAR);

        // 6、执行存储过程
        boolean execute = callableStatement.execute();

        // 7、获取输出参数
        String out = callableStatement.getString(2);
        System.out.println(out);

        callableStatement.close();
        connection.close();


    }

}

本文地址:https://blog.csdn.net/weixin_44588243/article/details/107170105