尚学堂马士兵Oracle学习笔记之六：PL/SQL中的游标，存储过程，触发器

---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多----------------------

       declare

              cursorc is

select * from emp; //此处的语句不会立刻执行，而是当下面的open c的时候，才会真正去数据库中取数据

              v_empc%rowtype;

       begin

              openc;

                     fetchc into v_emp;

dbms_output.put_line(v_emp.ename); //这样会只输出一条数据 134将使用循环的方法输出每一条记录

         close c;

       end;

----------------------使用do while  循环遍历游标中的每一个数据---------------------

       declare

              cursorc is

                     select* from emp;

              v_empc%rowtype;

       begin

              openc;   

              loop

                     fetchc into v_emp;

(1)   exit when(c%notfound);  //notfound是oracle中的关键字，作用是判断是否还有下一条数据

(2)   dbms_output.put_line(v_emp.ename);  //(1)(2)的顺序不能颠倒，否则会把最后一条结果再多打印一次。

          end loop;

          close c;

       end;

------------------------使用while循环，遍历游标---------------------

       declare

              cursorc is

                     select* from emp;

              v_empemp%rowtype;

       begin

              openc;

              fetchc into v_emp;

              while(c%found)loop

                 dbms_output.put_line(v_emp.ename);

                 fetch c into v_emp;

          end loop;

          close c;

       end;

--------------------------使用for 循环，遍历游标（最方便快捷的方法！）---------------------

       declare

              cursorc is

                 select * from emp;

       begin

              forv_emp in c loop

                     dbms_output.put_line(v_emp.ename);

              endloop;

       end;

 

---------------------------带参数的游标（相当于函数）---------------------

       declare

              cursorc(v_deptno emp.deptno%type, v_job emp.job%type)

              is

                 select ename, sal from emp wheredeptno=v_deptno and job=v_job;

       begin

              forv_temp in c(30, 'CLERK') loop

                     dbms_output.put_line(v_temp.ename);

              endloop;

       end;

-------------------------可更新的游标-----------------------------

       declare

              cursorc

              is

                 select * from emp2 for update;

       begin

          for v_temp in c loop

              if(v_temp.sal< 2000) then

                     update emp2 set sal = sal * 2 where current of c;

             elsif (v_temp.sal =5000) then

              deletefrom emp2 where current of c;

              end if;

            end loop;

            commit;

       end;

 

49、------------------------------store procedure存储过程(带有名字的程序块) -------------------

       createor replace procedure p

              is--除了这两句替代declare，下面的语句全部都一样 

           cursor c is

                     select* from emp2 for update;

       begin

            forv_emp in c loop

              if(v_emp.deptno= 10) then

                     updateemp2 set sal = sal +10 where current of c;

              elseif(v_emp.deptno =20) then

                     updateemp2 set sal =  sal + 20 where current ofc;

              else

                     updateemp2 set sal = sal + 50 where current of c;

              endif;

           end loop;

         commit;

        end;

      

       执行存储过程的两种方法：

       （1）exec p;(p是存储过程的名称)

       （2）begin

                     p;

               end;

              /

-------------------------------带参数的存储过程

先创建存储过程：（in标识传入参数，out标识传出参数，默认为传入参数）

       createor replace procedure p

              (v_ain number, v_b number, v_ret outnumber, v_temp inout number)

       is

       begin

              if(v_a> v_b) then

                     v_ret:= v_a;

              else

                     v_ret:= v_b;

              endif;

              v_temp:= v_temp + 1;

       end;

再调用：

       declare

              v_a  number := 3;

              v_b  number := 4;

              v_retnumber;

              v_tempnumber := 5;

       begin

              p(v_a,v_b, v_ret, v_temp);

              dbms_output.put_line(v_ret);

              dbms_output.put_line(v_temp);

       end;

 

------------------删除存储过程---------------------------

       dropprocedure p;

 

50、------------------------创建函数计算个人所得税的税率-------------------------------------

       createor replace function sal_tax

              (v_sal  number)   

              returnnumber

       is

       begin

              if(v_sal< 2000) then

                     return0.10;

              elsif(v_sal<2750) then

                     return0.15;

              else

                     return0.20;

              endif;

       end;

 

-----------------------------创建触发器（trigger）       触发器不能单独的存在，必须依附在某一张表上

写主语 谓语 宾语 游戏

       创建触发器的依附表：

      

       createtable emp2_log

       (

       enamevarchar2(30) ,

       eactionvarchar2(20),

       etimedate

       );

 

       create or replace trigger trig

after insert or delete orupdate on emp2 for each row --加上此句，每更新一行，触发一次，不加入则值触发一次

       begin

              ifinserting then

                     insertinto emp2_log values(USER, 'insert', sysdate);

              elsifupdating then

                     insertinto emp2_log values(USER, 'update', sysdate);

              elsifdeleting then

                     insertinto emp2_log values(USER, 'delete', sysdate);

              endif;

       end;

51、--------------------触发器用法之一：通过触发器更新约束的相关数据-------------------

       createor replace trigger trig

              afterupdate on dept

              foreach row

       begin

              updateemp set deptno =:NEW.deptno where deptno =: OLD.deptno;

       end;

      

 

       //只编译不显示的解决办法 set serveroutput on;

52、-------------------------------通过创建存储过程完成递归

       createor replace procedure p(v_pid article.pid%type,v_level binary_integer) is

              cursorc is select * from article where pid = v_pid;

              v_preStrvarchar2(1024) := '';

       begin

         for i in 0..v_leave loop

              v_preStr:= v_preStr || '****';

         end loop;

 

         for v_article in c loop

              dbms_output.put_line(v_article.cont);

              if(v_article.isleaf= 0) then

                     p(v_article.id);

              endif;

              endloop;

       end;

-------------------------------查看当前用户下有哪些表---

       首先，用这个用户登录然后使用语句：

       select* from tab;

      

-----------------------------用Oracle进行分页！--------------

       因为Oracle中的隐含字段rownum不支持'>'所以：

       select* from (

              selectrownum rn, t.* from (

                     select* from t_user where user_id <> 'root'

              )t where rownum <6

       )where rn >3

------------------------Oracle下面的清屏命令----------------

       clearscreen; 或者 cle scr;

 

-----------将创建好的guohailong的这个用户的密码改为abc--------------

   alter user guohailong identified by abc

    当密码使用的是数字的时候可能会不行