pl/sql一些常用小例子

游标：
隐式游标：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