oracle存储过程初见

DBMS_OUTPUT.PUT_LINE('hello world');

declare 
  --1.普通变量声明并赋值
  putong varchar(255) := '我是普通变量';
  --2.也可以指定类型为该表某字段的类型
  dongtai hero.name%type;
  --3.也可以设置类似对象的类型，直接接收一行数据
  yihang hero%rowtype;
begin
  --4.可以在这里赋值
  dongtai := '我也能赋值';
  --5.可以用select语句赋值
  select * into yihang from hero where name = '张三';
  dbms_output.put_line(putong || dongtai || yihang.name);
end;

  --if分支
declare 
  num number;
begin
  select count(1) into num from hero;
  if num > 2 then
    dbms_output.put_line('表中行数大于2');
    elsif num < 2 then
      dbms_output.put_line('表中行数小于2');
    else
      dbms_output.put_line('不知道');
    end if;
end;

declare 
  --声明循环变量
  num number := 1;
begin
 --循环打印1到10
  loop
    exit when num > 10;
    dbms_output.put_line(num);
    num := num + 1;
  end loop;
end;

declare 
  --声明游标(相当于集合，接收多行数据)
  cursor c_hero is select name,hp from hero;
  --声明变量接收游标中的数据
  v_name hero.name%TYPE;
  v_hp hero.hp%TYPE;  
begin
  --打开游标
  open c_hero;
  --遍历游标
  loop
    --获取游标中的数据
    FETCH c_hero into v_name,v_hp;
    dbms_output.put_line(v_name || ',' || v_hp);
    --退出循环条件
    exit when c_hero%NOTFOUND;
  end loop;
  --关闭游标
  close c_hero;
end;

declare 
  --声明游标,可以设置参数，例如java中的方法
  cursor c_hero(v_age hero.hp%TYPE) is select * from hero where age = v_age;
  --声明变量接收游标中的数据
  v_name hero.name%TYPE;
  v_hp hero.hp%TYPE;
  v_age hero.age%TYPE;  
begin
  --打开游标，同时可以传入参数
  open c_hero(20);
  --遍历游标
  loop
    --获取游标中的数据
    FETCH c_hero into v_name,v_hp,v_age;
    --退出循环条件
    exit when c_hero%NOTFOUND;
    dbms_output.put_line(v_name || ',' || v_hp || ',' || v_age);
  end loop;
  --关闭游标
  close c_hero;
end;

所有存储过程，其实就是把上面的plsql程序封装起来，形成一个“方法”。

create or replace procedure p_hello is
--虽然没有declare，但是这里可以声明变量
hello varchar(200) := '四叶猫';
begin
  dbms_output.put_line('hello world!' || hello);
end p_hello;

如果调用呢，可以在plsql程序中直接像方法一样进行调用：

declare   
begin
  p_hello;
  
end;

也可以在命令窗口中使用exec命令调用：

--先将输出打印打开，否则看不到输出值
set serveroutput on
exec p_hello

--在方法名后面加入参数，参数的指定类型只能用表名.字段名%TYPE这种形式
create or replace procedure p_hello(word in hero.name%TYPE) as
begin
	--下面可以直接使用参数
  dbms_output.put_line('hello world!' || word);
end p_hello;

同样调用时放入指定参数就行：

declare 
begin
  p_hello('四叶猫');
end;

--有返回值的存储过程，返回参数定义在过程名后面的括号中
create or replace procedure p_test(i_name in hero.name%TYPE,
                                   o_hp out hero.hp%TYPE) as
begin
  --将hp放入出参
  select hp into o_hp from hero where name = i_name;
end;

调用时需要定义变量接收：

declare 
--定义变量接收返回值
hp number;
begin
  p_test('王五',hp);
  dbms_output.put_line(hp);
end;

import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("oracle.jdbc.OracleDriver");
        //2.获取连接对象
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "yc";
        String password = "yc";
        Connection conn = DriverManager.getConnection(url, user, password);
        //3.获取语句对象
        String sql = "{call p_test1(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
        //4.设置输入参数
        call.setString("i_name","张三");
        //5.设置输出参数
        call.registerOutParameter("o_hp", OracleTypes.DOUBLE);
        //6.执行存储过程
        call.execute();
        //7.获取输出参数
        double result = call.getDouble("o_hp");
        System.out.println(result);
        //8.释放资源
        call.close();
        conn.close();
    }
}