oracle如何使用存储过程实现增删改查？【0基础】

最新推荐文章于 2021-12-16 09:18:31 发布

maocxu

最新推荐文章于 2021-12-16 09:18:31 发布

阅读量1.6k

点赞数 1

分类专栏：存储过程 Oracle 文章标签： oracle 存储 plsql

本文链接：https://blog.csdn.net/fk_d_st/article/details/78296457

版权

Oracle 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

存储过程

3 篇文章 0 订阅

订阅专栏

1、创建表t1

--SQL语句
create table t1
(
sid number not null primary key,
sname varchar2(10)
) 
insert into t1 (sid, sname) values (1, 'sname');
select * from t1;

2、存储过程之【添加】

create or replace procedure proc_m_insert(v_sid   in number,
                                          v_sname in varchar2) is
begin
  insert into t1 (sid, sname) values (v_sid, v_sname);
  dbms_output.put_line('添加成功！更新id=' || v_sid || '; 影响的行数=' || sql%rowcount);
  commit;
end proc_m_insert;

3、存储过程之【更新】

create or replace procedure proc_m_update(v_sid   in number,
                                          v_sname in varchar2) is
begin
  update t1 set sname = v_sname where sid = v_sid;
  if sql%Found  then
    dbms_output.put_line('更新成功！更新id='||v_sid); 
  else
    dbms_output.put_line('更新失败！数据不存在。'); 
  end if;
  commit;
end proc_m_update;

4、存储过程之【删除】

create or replace procedure proc_m_delete(v_sid in number) is
begin
  delete from t1 where sid = v_sid;
  if sql%Found then
    dbms_output.put_line('删除成功！删除id=' || v_sid);
  else
    dbms_output.put_line('删除失败！数据不存在');
  end if;
  commit;
end proc_m_delete;

5、变量(select ....into)：【单行】查询操作

create or replace procedure proc_m_selectbyid(v_sid in t1.sid%type) is
  tid   t1.sid%type; --变量
  tname t1.sname%type; --变量
begin
  select sid, sname into tid, tname from t1 where sid = v_sid;
  dbms_output.put_line('查询结果：{编号=' || tid || ',姓名=' || tname || '};');
exception
  when no_data_found then
    dbms_output.put_line('没有符合条件的记录！数据' || v_sid || '不存在！');
  when too_many_rows then
    dbms_output.put_line('返回的行数太多！');
  when others then
    dbms_output.put_line('发生意外错误！');
end proc_m_selectbyid;

6、显示游标：返【单行】【单列】记录(只查询一个字段)

create or replace procedure proc_m_selectsnamebyid
(v_sid in t1.sid%type) --输入参数 v_sid
 is
  tname t1.sname%type; --声明变量 tname
  cursor cursor_a is --声明显示游标 cursor_a
    select sname from t1 where sid = v_sid;
begin
  open cursor_a;     --打开游标
    fetch cursor_a into tname; --提取数据(单个列、字段),赋给tname
    if cursor_a%found then
      dbms_output.put_line('姓名：'||tname);--游标结果集中只有一列
    else
      dbms_output.put_line('没有符合条件的记录！');
    end if;
  close cursor_a;  --关闭游标
end proc_m_selectsnamebyid;

7、显示游标：返回【单行】【多列】记录

create or replace procedure proc_m_selects
(v_sid in t1.sid%type)--输入参数
is
  t2_user t1%rowtype; --声明变量：表示t2_user与t1表中的各个列相同; 
  cursor cursor1 is --声明游标;
    select * from t1 where sid=v_sid; 
begin
  open cursor1; --打开游标
    fetch cursor1 into t2_user; --提取数据(多个列sid和sname),赋给t2_user
    if cursor1%found then
      dbms_output.put_line('{职工编号：'||t2_user.sid||',职工姓名：'||t2_user.sname||'}');
    else
      dbms_output.put_line('没有符合条件的记录！');
    end if;
  close cursor1; --关闭游标
end proc_m_selects;

8、显示游标(loop循环)：返回【多行】【多列】记录

方法1：基于【表的记录变量t_user】接收游标数据--【多行】【多列】

create or replace procedure proc_m_select_loop_f1
is
  t_user t1%rowtype; --声明表的记录变量t_user：表示t_user与t1表中的各个列相同; 
  cursor cur is --声明游标cur;
    select * from t1;
begin
  open cur; --打开游标
    loop
      fetch cur into t_user; --进入循环流提取数据(多个列sid和sname),赋给t_user
      exit when cur%notfound; --当数据提取完毕后退出。
      dbms_output.put_line('{职工编号：'||t_user.sid||',职工姓名：'||t_user.sname||'}'); 
    end loop;
  close cur; --关闭游标
end proc_m_select_loop_f1;

方法2：基于【游标的记录变量cur_record】接收游标数据--【多行】【多列】

create or replace procedure proc_m_select_loop_f2 
is
  cursor cur is --声明游标cur;
    select * from t1;
  cur_record cur%rowtype; --声明游标的记录变量cur_record
begin
  open cur;--打开游标
    loop
      fetch cur into cur_record; --使用游标的记录变量cur_record接收游标数据
      exit when cur%notfound;  --当数据提取完毕后退出。
      dbms_output.put_line('{编号：'||cur_record.sid||',姓名：'||cur_record.sname||'},'); 
    end loop;
  close cur;--关闭游标
end proc_m_select_loop_f2;

方法3：基于【集合变量】的接收游标数据--【多行】【多列】

create or replace procedure proc_m_select_loop_f3 
is
  cursor cur is
    select * from t1; --声明游标cur;
  type cur_table_type is table of cur%rowtype index by binary_integer;
  cur_table cur_table_type;
  i int; 
begin
  open cur;--打开游标
    loop
      i:=cur%rowcount+1;  --读取游标当前行i
      fetch cur into cur_table(i); --基于【集合变量cur_table(i)】的接收游标数据 
      exit when cur%notfound;   --当数据提取完毕后退出。
      dbms_output.put_line('游标当前行row='||cur%rowcount||',{编号：'||cur_table(i).sid||',姓名：'||cur_table(i).sname||'}');      
    end loop;
  close cur; --关闭游标
end proc_m_select_loop_f3;

9、显示游标(while....loop循环)：返回【多行】【多列】记录

create or replace procedure proc_m_select4_while_loop
is
  cursor cur is select * from t1;  --声明游标cur
  t_user t1%rowtype;  --声明表的记录变量t_user：表示t_user与t1表中的各个列相同;
begin
  open cur;
    --基于【表的记录变量t_user】接收游标数据
    fetch cur into t_user; --开始循环之前先提取(fetch)游标数据，否则游标属性不起作用，t_user内无数据。
    while cur%found loop  --开始循环
     dbms_output.put_line('游标row='||cur%rowcount||',员工编号='||t_user.sid||',员工姓名='||t_user.sname);  
     fetch cur into t_user;--循环体内的fetch方法要放在最后。否则就会多次处理，一直循环读取同一行数据，直到缓存溢出。
    end loop;
  close cur;
end proc_m_select4_while_loop;

10、显示游标(for...loop循环)(适合多个记录)：返回【多行、多列】记录

游标使用【for循环】不用open、fetch、close关闭游标.

方法1：典型for循环

create or replace procedure proc_m_select5_for1_loop is
  cursor cur is
    select * from t1;
begin
  --游标使用for循环不用open、fetch、close关闭游标;
  for t_user in cur loop  --t_user接收游标数据;
    dbms_output.put_line('游标row='||cur%rowcount||',员工编号='||t_user.sid||',员工姓名='||t_user.sname);
  end loop;
end proc_m_select5_for1_loop;

方法2：简单for循环

create or replace procedure proc_m_select5_for2_loop is
begin
  --游标使用for循环不用open、fetch、close关闭游标;
  for t_user in (select * from t1) loop   --t_user接收查询数据;
    dbms_output.put_line(',员工编号='||t_user.sid||',员工姓名='||t_user.sname);
  end loop;
end proc_m_select5_for2_loop;

11、ref游标(loop循环)

怎么使用 REF 游标？

①声明一个【REF游标类型】；
⑴强类型REF游标：指定retrun type，REF 游标变量的类型【必须和return type一致】。
语法：Type REF游标类型名(自定义) IS Ref Cursor Return 结果集返回记录类型；
⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
语法：Type REF游标类型名(自定义) IS Ref Cursor；
【注】：能够使用ref【弱类型REF游标】就不要使用强类型REF游标；
因为：【弱类型】的动态游标可以与任何查询语句匹配，
但是【强类型】的动态游标只能与特定的查询语句匹配。
②声明 Ref 【游标类型变量】；
语法：变量名已声明Ref游标类型名；
③打开REF游标，关联结果集；
语法：Open Ref 游标类型变量 For 查询语句返回结果集；
④获取记录，操作记录；
语法：Fetch REF游标名 InTo 临时记录类型变量或属性类型变量列表；
⑤关闭游标，完全释放资源；
语法：Close REF游标名；

案例1：ref弱类型游标:loop循环

create or replace procedure proc_m_select6_ref1_loop
(choice in varchar2) --声明输入的变量
is
  type cur_type is ref cursor; --声明一个动态ref游标的类型(弱)【cur_type】
  cur cur_type;  --声明一个变量为ref游标【cur】
  t_user t1%rowtype;--声明表的记录变量t_user：表示t_user与t1表中的各个列相同;
begin
  --判断用户的输入并进行相应的输出
  if choice='YY' then 
    open cur for  --打开游标cur
      select * from t1;
    loop  --开始循环提取游标中的数据
      fetch cur into t_user;  --用表的记录变量t_user接收游标中的数据
      exit when cur%notfound;
      dbms_output.put_line('游标row='||cur%rowcount||',员工编号='||t_user.sid||',员工姓名='||t_user.sname); 
    end loop;
  elsif choice='LL' then  
    open cur for 
      select * from t1 where rownum<5;
    loop
      fetch cur into t_user;
      exit when cur%notfound;
      dbms_output.put_line('游标row='||cur%rowcount||','||t_user.sid||','||t_user.sname); 
    end loop;
  else  
    dbms_output.put_line('请输入choice的正确值YY或LL!');
  return;  --return表示退出此次操作
  end if; 
  close cur;  --及时的关闭游标cur
end proc_m_select6_ref1_loop;

案例2：ref强类型游标:loop循环

create or replace procedure proc_m_select6_ref2_loop 
is
  type cur_type is ref cursor return t1%rowtype;--声明一个动态ref游标的类型(强)【cur_type】
  cur cur_type;  --声明一个变量为ref游标【cur】
  t_user t1%rowtype; --声明表的记录变量t_user：表示t_user与t1表中的各个列相同;
begin
  open cur for  --打开游标
    select * from t1;
    loop   --开始循环提取游标中的数据
      fetch cur into t_user;  ----用表的记录变量t_user接收游标中的数据
      exit when cur%notfound;
      dbms_output.put_line(cur%rowcount||',员工编号='||t_user.sid||',员工姓名='||t_user.sname);  
    end loop;
  close cur;  --关闭游标
end proc_m_select6_ref2_loop;