游标:
隐式游标:sql%found,sql%notfound;
显示游标:cursor my_cur is .....;
open ..,
fetch ..into,
while xx loop
(fetch into);
end loop;
for 打开游标;
for my_row in my_cursor loop
end loop;
参数游标;
更新游标;for update of username;
打开游标 open cur_student;
循环游标
loop
fetch cur_student into rs_student;
exit when cur_student%notfound;
update t_user set username = 'newusername' where current of cur_student;
end loop;
关闭游标
close cur_student;
commit;
ref 游标:
TYPE ref_cursor is REF cursor;
cur_student ref_cursor;
rs_student t_student%rowtype;
打开游标 open cur_student for select * from t_student;
循环游标
loop
fetch cur_student into rs_student;
exit when cur_student%notfound;
dbms_output.put_line(rs_student.name || rs_student.id);
end loop;
关闭游标
close cur_student;
存储过程:
1.不带任何参数
2.带默认参数
3.带in,out参数
4.带游标
函数
1.带参数,返回参数
2.带变量,返回变量,返回值后无须加分号。
3.sql调用函数
程序包
1.create or replace package my_package
is
procedure my_pro(userid number);
function my_fun(userid number) return varchar2;
end;
2.定义游标
cursor my_cursor(user_id number) return t_user%rowtype;
调用游标
cursor my_cursor(user_id number) return t_user%rowtype
is select * from t_user >user_id;
procedure my_pro(user_id number)
as
user_row t_user%rowtype;
begin
open my_cursor(user_id);
loop
fetch my_cursor into user_row;
exit when my_cursor%notfound;
dbms_output.put_line(user_row.username||'12');
end loop;
end;
触发器
create or replace trigger first_trigger before insert on t_user
for each row
begin
select seq_t_user.nextval into :new.userid from dual;
end;
instead of触发器
create or replace trigger third_trigger instead of update on t_user_view
for each row
begin
update t_user set username=:new.username where userid=:new.userid;
end;
------------------------------------模式触发器
create or replace trigger four_trigger after drop or create on schema
begin
INSERT INTO t_user(username,email)
VALUES( ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_TYPE);
end;
drop table test;
----------------------------------------------for update 和 for update of 字段
1
select * from TTable1 for update
锁定表的所有行,只能读不能写
2
select * from TTable1 where pkid = 1 for update
只锁定pkid=1的行
3
select * from Table1 a join Table2 b on a.pkid=b.pkid for update
锁定两个表的所有记录
4
select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update
锁定两个表的中满足条件的行
5.
select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update of a.pkid
只锁定Table1中满足条件的行
-----------------------
for update 是把所有的表都锁点
for update of 根据of 后表的条件锁定相对应的表
-------------------------------------------------------------
多个表关联的时候for update 会锁定所有的表
for update of table_name.column_name 可以控制要锁定的表
--------------------
组成视图的各表中如果都有主键,那对视图和表for update of 效果基本一样。但如果各个基表中没有主键,更新视图就不允许了
select a.*,b.* from t_user a,t_user_address b where a.userid=b.userid for update