存储过程，触发器，游标，if语句，三种循环

 

 
存储过程
1).创建存储过程        create [OR REPLACE] PROCEDURE 过程名             [(参数名[IN|OUT|IN OUT]数据类型...)]            {IS | AS}            [说明部分]         BEGIN           语句系列          [EXCEPTION 出错处理]        END[过程名];
参数说明：IN OUT IN OUT如下：         IN 输入参数：用来从调用环境中向存储过程传递值，即IN模式参数不能出现在赋值语句式边         OUT输出参数：用来从存储过程中返回值给调用者，在过程体内，必须给OUT模式参数赋值，OUT模式参数可以出现在赋值语句的左边.没有值的OUT模式参数不能出现赋值语句的右边.         IN OUT：输入参数，输出参数.即可从调用者向存储过程传递值，过程执行后又可返回改变后的值给调用者.

        

        存储过程的语句系列可以是DML(操作），DQL（查询），TPL（事务）,DCL（控制）,DDL（数据定义）,CCL（指针控制）语句。
 
       下面创建一个简单的存储过程，查询指定部门编号的员工的姓名
 
create or replace procedure find(v_deptno in number)
is
   cursor c is
     select * from emp;
begin
   for v_emp in c loop
      if(v_emp.deptno=v_deptno) then
        dbms_output.put_line(v_emp.ename);
      end if;
   end loop;
end;
调用存储过程
declare
	v_a number:=10;
begin
  find(v_a);
end;


触发器
DML 触发器： 


对表执行Insert、Update、Delete操作时激发 

可以用于执行校验、设置初使值、审核改变、甚至禁止某种DML操作 

语法： 

CREATE OR REPLACE TRIGGER 触发器名称 

{AFTER|BEFORE } -- 指定触发时机 

{INSERT OR DELETE OR UPDATE} -- 指定触发器事件 

ON 表名 --指定所监控的表 

{FOR EACH ROW|FOR EACH STATEMENT} -- 指定触发器次数 

BEGIN 

--代码; 

END; 



相关概念： 

AFTER|BEFORE：在什么事件之前或之后执行 

INSERT|DELETE|UPDATE：什么事件 

ON 表名：触发器建在什么表上，即监控什么表 

FOR EACH ROW：行级触发，示例：delete from t1,删除1000行，则执行1000次(一行一次) 

FOR EACH STATEMENT：语句级触发，示例： delete from t1,删除1000行，则执行1次(一句一次) 

:new 行变量：保存事件发生时新数据所在行，只有insert事件和update事件才有新数据 

:old 行变量：保存事件发生时旧数据所在行，只有delete事件和update事件才有旧数据 


 判断发生的是什么触发事件：
 inserting表示插入事件，
 updating表示更新事件，
 deleting表示删除事件。
 
create or replace trigger trig_emp2
   after insert or delete or update of sal,ename on emp2
   --行级触发，每一行有相应的事件发生时，都会触发begin里面的方法体
   for each row
begin
      if inserting then
           dbms_output.put_line('插入数据了');
           dbms_output.put_line('插入的员工姓名是:'|| :new.ename || '  员工月薪是：'|| :new.sal);
      elsif updating  then
            dbms_output.put_line('更新数据了，有人涨工资咯');
      elsif deleting then
            dbms_output.put_line('删除数据了');
      end if;
end;



我们如果想更新指定字段时，才触发事件，可以使用 update of 字段名，就像上面的update of sal 一样。
 
 
游标
 pl/sql游标操作的步骤：
     1：声明游标
           cursor c is
                select * from emp;
     2：打开游标
           open c;
     3：迭代抓取游标
            fetch c into 变量名
     4：关闭游标
            close c;
     利用for循环遍历游标，会自动打开和关闭游标
declare
	cursor c is
		select * from emp;
	v_emp emp%rowtype;
begin
	open c;
	loop
		fetch c into v_emp;
	  exit when(c%notfound);
   dbms_output.put_line(v_emp.ename);
	end loop;
	close c;
end;
利用while遍历游标
<SPAN style="FONT-SIZE: 24px">declare
	cursor c is
		select * from emp;
	v_emp emp%rowtype;
begin
	open c;
	fetch c into v_emp;
	while(c%found) loop
		dbms_output.put_line(v_emp.ename);
		fetch c into v_emp;
	end loop;
	close c;
end;</SPAN>
下面我们利用游标实现： 更新emp表，工资小于1200的，加5快，工资小于1800的，加3快，其他工资的加1快


<SPAN style="FONT-SIZE: 24px">declare
  cursor c 
      is select * from emp for update;
  v_emp emp%rowtype;
begin
    for v_emp in c loop
      if(v_emp.sal < 1200) then
           update emp set sal = sal +5 where current of c;
      elsif(v_emp.sal <1800) then
          update emp set sal = sal + 3 where current of c ;
      else 
         update emp set sal = sal +  1 where current of c ;
      end if;
    end loop;
end;</SPAN>


可更新的游标

declare  cursor c is  select * from emp2 for update;当后面加上for update时，我们就可以利用current of c 来获取当前的游标。




  游标有带参数的游标
<SPAN style="FONT-SIZE: 24px">declare
	cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
	select ename,sal from emp where deptno=v_deptno and job=v_job;
begin
	for v_temp in c(30,'CLERK') loop
		dbms_output.put_line(v_temp.ename);
	end loop;
end;</SPAN>


 
if 语句
使用 if（条件） then
              语句；
           elsif（语句） then
              语句；
           else
              语句；
          end if；
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno ='7900';
  if(v_sal<1300) then
     update emp set sal = sal +500 where empno ='7900';
     commit;
  elsif(v_sal<2300) then
     update emp set sal = sal +300 where empno ='7900';
     commit;
  else
     update emp set sal = sal +100 where empno ='7900';
     commit;
  end if;
end;


PL/SQL三种循环
 for循环：（输出1-10）
declare 
  v_i Binary_Integer:=0;
begin
  for v_i in 1..10 
  loop
   dbms_output.put_line(v_i);
  end loop;
end;


如果我们要 输出的是 10 -1 ，则需使用reverse
--for循环 输出 10到1
declare 
  v_i Binary_Integer:=0;
begin
  for v_i in reverse 1..10 
  loop
   dbms_output.put_line(v_i);
  end loop;
end;


 while循环
--while循环
declare 
  v_i Binary_Integer:=0;
begin
  while(v_i<=10)  loop
    dbms_output.put_line(v_i);
    v_i := v_i +1;
  end loop;
end;


do。。。while循环
--do while循环
declare 
  v_i Binary_Integer:=0;
begin
  loop
    dbms_output.put_line(v_i);
    v_i := v_i +1;
    exit when(v_i>=11);
  end loop;
end;