游标
来源:互联网 发布:淘宝外包多少钱 编辑:程序博客网 时间:2024/04/30 06:06
- 游标(主要是配合存储过程),函数(了解),触发器(基本不用了PASS)
-- 连接查询+存储过程
-- 视图
create or replace view view_userinfo
as
select * from userinfo;
-- 存储过程ddl,dml
-- java里面有ResultSet
-----------------------------------------------------------
create or replace procedure up_test
as
v_userpwd userinfo.userpwd%type;
begin
select userpwd into v_userpwd from userinfo;-- 有多条记录
dbms_output.put_line('密码是:'||V_userpwd);
end;
/
-----------------------------------------------------------
-- 解决方式:采用游标
create or replace procedure up_test
as
cursor mycur is select * from userinfo;-- 定义游标,mycur装着N条记录
--v_userinfo userinfo%rowtype;-- 定义一个变量
begin
-- 循环来遍历游标数据
for v_userinfo in mycur loop -- v_userinfo是一个对象变量,mycur相当于rs集合
dbms_output.put_line('帐号是:'||v_userinfo.userid);
dbms_output.put_line('密码是:'||v_userinfo.userpwd);
dbms_output.put_line('金额是:'||v_userinfo.usermoney);
end loop;
end;
/
-- 游标的开启方式
create or replace procedure up_test
as
cursor mycur is select * from userinfo;
v_userinfo userinfo%rowtype;
begin
open mycur;-- 1打开游标
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
dbms_output.put_line('名字:'||v_userinfo.userid);
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
dbms_output.put_line('名字:'||v_userinfo.userid);
end;
/
-- 开启循环模式
create or replace procedure up_test
as
cursor mycur is select * from userinfo;
v_userinfo userinfo%rowtype;
begin
open mycur;-- 1打开游标
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
while(mycur%found) loop -- 游标有数据取出来
dbms_output.put_line('名字:'||v_userinfo.userid); -- 打印当前取出的这一条
fetch mycur into v_userinfo; -- 再取下一条(不写,一直会是第一条)
end loop;
end;
/
例题
-- 给每个员工工资加500.若超过5000则改为5000
create or replace procedure up_test
as
cursor mycur is select * from temp;
v_temp temp%rowtype;
begin
open mycur;
fetch mycur into v_temp;
while(mycur%found) loop
if v_temp.sal+500>5000 then
update temp set sal = 5000 where empno = v_temp.empno;
commit;
else
update temp set sal = v_temp.sal+500 where empno = v_temp.empno;
commit;
end if;
fetch mycur into v_temp;
end loop;
end;
/
-- 事务提交
create or replace procedure up_test
as
my_execption exception;-- 定义一个名为my_exception的异常
cursor mycur is select empno,sal+500 upsal from scott.emp where empno in(7777,888,7839);
begin
for e in mycur loop
if e.upsal=6000 then -- 修改为5000
update scott.emp set sal = 4999 where empno = e.empno;
-- 人为的加一个错误,用于模拟上面这个修改语句报错了
raise my_execption;
update scott.dept set loc = 'ddd' where deptno = 40;
commit;--
else -- 加500
update scott.emp set sal = e.upsal where empno = e.empno;
commit;
end if;
end loop;
exception
when my_execption then
rollback;-- 回滚
end;
/
-- 返回一个整数
create or replace function fun_a
(i number)
return number
as
v_userid userinfo.userid%type;
begin
select userid into v_userid from userinfo where userid=i;
return v_userid;
end;
/
-- 调用函数
select fun_a(83) from dual;
-- 连接查询+存储过程
-- 视图
create or replace view view_userinfo
as
select * from userinfo;
-- 存储过程ddl,dml
-- java里面有ResultSet
-----------------------------------------------------------
create or replace procedure up_test
as
v_userpwd userinfo.userpwd%type;
begin
select userpwd into v_userpwd from userinfo;-- 有多条记录
dbms_output.put_line('密码是:'||V_userpwd);
end;
/
-----------------------------------------------------------
-- 解决方式:采用游标
create or replace procedure up_test
as
cursor mycur is select * from userinfo;-- 定义游标,mycur装着N条记录
--v_userinfo userinfo%rowtype;-- 定义一个变量
begin
-- 循环来遍历游标数据
for v_userinfo in mycur loop -- v_userinfo是一个对象变量,mycur相当于rs集合
dbms_output.put_line('帐号是:'||v_userinfo.userid);
dbms_output.put_line('密码是:'||v_userinfo.userpwd);
dbms_output.put_line('金额是:'||v_userinfo.usermoney);
end loop;
end;
/
-- 游标的开启方式
create or replace procedure up_test
as
cursor mycur is select * from userinfo;
v_userinfo userinfo%rowtype;
begin
open mycur;-- 1打开游标
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
dbms_output.put_line('名字:'||v_userinfo.userid);
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
dbms_output.put_line('名字:'||v_userinfo.userid);
end;
/
-- 开启循环模式
create or replace procedure up_test
as
cursor mycur is select * from userinfo;
v_userinfo userinfo%rowtype;
begin
open mycur;-- 1打开游标
fetch mycur into v_userinfo;-- 2第一行取出放到变量v_userinfo中去
while(mycur%found) loop -- 游标有数据取出来
dbms_output.put_line('名字:'||v_userinfo.userid); -- 打印当前取出的这一条
fetch mycur into v_userinfo; -- 再取下一条(不写,一直会是第一条)
end loop;
end;
/
例题
-- 给每个员工工资加500.若超过5000则改为5000
create or replace procedure up_test
as
cursor mycur is select * from temp;
v_temp temp%rowtype;
begin
open mycur;
fetch mycur into v_temp;
while(mycur%found) loop
if v_temp.sal+500>5000 then
update temp set sal = 5000 where empno = v_temp.empno;
commit;
else
update temp set sal = v_temp.sal+500 where empno = v_temp.empno;
commit;
end if;
fetch mycur into v_temp;
end loop;
end;
/
-- 事务提交
create or replace procedure up_test
as
my_execption exception;-- 定义一个名为my_exception的异常
cursor mycur is select empno,sal+500 upsal from scott.emp where empno in(7777,888,7839);
begin
for e in mycur loop
if e.upsal=6000 then -- 修改为5000
update scott.emp set sal = 4999 where empno = e.empno;
-- 人为的加一个错误,用于模拟上面这个修改语句报错了
raise my_execption;
update scott.dept set loc = 'ddd' where deptno = 40;
commit;--
else -- 加500
update scott.emp set sal = e.upsal where empno = e.empno;
commit;
end if;
end loop;
exception
when my_execption then
rollback;-- 回滚
end;
/
-- 返回一个整数
create or replace function fun_a
(i number)
return number
as
v_userid userinfo.userid%type;
begin
select userid into v_userid from userinfo where userid=i;
return v_userid;
end;
/
-- 调用函数
select fun_a(83) from dual;
0 0
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- 游标
- java多线程详解二互斥同步方式实现多线程
- 存储过程
- 使用google注册教程
- HorizontalScrollView
- 基于canvas的二维码邀请函生成插件
- 游标
- POJ 3561 Pseudographical recognizer G++
- Strust2传值的三种方式
- build修改 默认的app文件名称
- 170218
- Strust2配置
- WebView的使用
- 行人检测论文笔记:Pedestrian Detection - An Evaluation of the State of the Art
- strust2的namespace