oracle编程 if， for ， while， loop， 存储过程，触发器，游标演示

    dept,employee和《oracle常用语句，带详细的说明》中的表一致！

 

/*作用：sql编程   说明：declare 申明变量，constant 常量，语言块要放在begin end 中 ，dbms_output.put_line 打印， exception when ZERO_DIVIDE then 当出现ZERO_DIVIDE错误，将执行 dbms_output.put_line('被零除！');  */

declare  
   pi constant  float:=3.1415; /*一个float类型的常量*/
   r1 int:=12; /*变量*/
   r2 int:=0;
begin
      dbms_output.put_line('面积：'||(pi*r1*r1));
      dbms_output.put_line('除法：'||(r1/r2));
      exception when ZERO_DIVIDE then
      dbms_output.put_line('被零除！');
end;


/*作用：一个简单的登录sql编程  说明：if流程判断， if  then,elsif then  相当于java中的else if ，else, end if ，<> 不等于*/
declare
      v_name nvarchar2(20):='christine';
      v_password nvarchar2(40):='pual';    
begin 
     if v_name <> 'christine'then
        dbms_output.put_line('用户名错误！');
     elsif v_password <> 'pual' then
        dbms_output.put_line('密码错误！');
    else 
        dbms_output.put_line('欢迎'||v_name||'登录！');
  end if;
end;


/*作用：求1+2+3+……+10的和   说明：Loop循环，loop循环开始, exit when 退出循环的条件， end loop 结束loop循环  */
declare 
     var_count int:=0;
     var_sum int:=0;
begin 
    loop
       var_count:=var_count+1;
       var_sum:=var_count+var_sum;
    exit when var_count=10;    
    end loop;
     dbms_output.put_line('sum：'||var_sum);
end;
    
/*作用：求1+2+3+……6的和  说明：while循环，while loop循环条件,end loop 结束循环  */
declare 
      var_count int:=0;
      var_sum int:=0;
begin
  while var_count<6 loop
      var_count:=var_count+1;
      var_sum:=var_count+var_sum;
  end loop;
     dbms_output.put_line('sum:'||var_sum);  
end;
           
/*作用；求1+2+3+……6的和 说明：for循环   for in  loop 在某某闭区间循环 */
declare 
    var_sum int:=0;
begin 
   for var_count in 1..6 loop
      var_sum:=var_count+var_sum;
   end loop;
      dbms_output.put_line('sum:'||var_sum);
end;


/*作用：特殊类型变量演示  针对一列  说明：var_salary employee.salary%type  var_salary的类型与employee.salary中的类型一致*/
declare
     var_salary employee.salary%type;
begin 
    select e.salary into var_salary from employee e 
    where e.empId=2;
    dbms_output.put_line('var_salary:'||var_salary);
end;


/*作用：特殊类型变量演示  针对一行 说明：var_employee employee%rowtype  var_employee相当于一个javaBean 包含Bean中所有的属性*/
declare
     var_employee employee%rowtype;
begin 
     select * into var_employee from employee
     where empid=2;
     dbms_output.put_line(var_employee.empName||':('||var_employee.empId||','||var_employee.birthday||','||var_employee.sex||')');
  end;
  
/*作用；单一类型游标演示  说明：employSalary 申明的游标，在使用游标之前要打开游标，事后关闭，fatch into 读取游标中的值*/
declare
    var_salary employee.salary%type;
    var_sum int:=0;
    cursor employSalary is select salary from employee;  
begin 
     open employSalary;
     loop
        fetch employSalary into var_salary;
        exit when employSalary%notfound;
           var_sum:=var_salary+var_sum;
        end loop;
         close employSalary;  
           dbms_output.put_line('sum:'||var_sum);
 end;
 


   
/*作用：行集类型游标演示  说明；for会自动打开游标，事后关闭游标*/
declare
     cursor employeeCursor is select * from employee ;
begin 
   
    for emp in employeeCursor loop
       dbms_output.put_line('employee:'||emp.empName||'('||emp.empId||emp.sex||')'); 
    end loop;
end;


/*作用：带参数的游标演示*/
declare 
    cursor employeeSex (var_sex employee.sex%type) is select * from employee where sex=var_sex;
    paraSex employee.sex%type;
    
begin 
       paraSex:='Male';
     for emp in employeeSex(paraSex) loop
       dbms_output.put_line('employee:'||emp.empName||'('||emp.empId||emp.sex||')');

     end loop;

end;

  
/*作用：触发器演示 在修改和添加employee记录时会触发  说明：replace 替换之前存在的tri_emp,before insert or update of salary  on employee 在修改和添加之前取得employee中的sslary,

 referencing old as old_sal  new as new_sal  引用新 旧两个变量，*/

create or replace trigger tri_emp
   before insert or update of salary  on employee
   referencing old as old_sal  new as new_sal
   for each row when(new_sal.salary<0)
begin
         dbms_output.put_line('只有new_sal.salsry小于0时：行集触发器才会被执行！');
       if:old_sal.salary is null then
           dbms_output.put_line('当添加的new_sal.salsr小于0时：salary将被修改变0');
          :new_sal.salary:=0;
       else
          dbms_output.put_line('当修改的new_sal.salsr小于0时：修改不成功！');
          :new_sal.salary:=old_sal.salary;
       end if;
end; 
    
   
/*作用；测试触发器*/
update employee set salary=-1000 where empid=1; 
insert into employee(empId,empName,sex,salary) values(seqEmployee.NextVal,'a','femal',-80);
select * from employee;
   
    


/*作用：查询某一部门员工的总薪水 存储过程*/
create or replace procedure pro_employee
(did in employee.deptid%type,sum_salary out number)
as
        temp_salary employee.salary%type;
        cursor emp_cur is 
               select salary from employee e 
               where e.salary is not null and e.deptid=did;
begin
        sum_salary:=0;
        open emp_cur;
        loop
             fetch emp_cur into temp_salary;
             exit when emp_cur%notfound;
             sum_salary:=sum_salary+temp_salary;
        end loop;
        close emp_cur;
end;


/*作用：测试存储过程*/
declare
        deptid employee.deptid%type;
        sum_salary employee.salary%type;
begin
        deptid:=1;
        pro_employee(deptid,sum_salary);
        dbms_output.put_line('sum:'||sum_salary);
end;


/*作用：函数*/
create or replace function fun_employee
(did in employee.deptid%type)
return employee.salary%type is
       temp_salary employee.salary%type;
       sum_salary employee.salary%type;
       cursor emp_cur is 
              select salary from employee e 
              where e.salary is not null and e.deptid=did; 
begin
    sum_salary:=0;
    open emp_cur;
    loop
         fetch emp_cur into temp_salary;
         exit when emp_cur%notfound;
         sum_salary:=sum_salary+temp_salary;
    end loop;
    close emp_cur;
    return sum_salary;
end;


/*函数测试*/
select fun_employee(1) from dual;