PL/SQL

在Oracle SQL Developer 环境中，一下的"declare"声明文字不必写出，直接写入"declare"一下的不分即可。

一.创建一个简单的PL/SQL程序块

   使用不同的程序块组件工作

   使用编程结构编写PL/SQL程序块

   处理PL/SQL程序块中的错误

 

  1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。

declare

  v_empemp%rowtype;

  begin

  select * into v_emp from emp where ename='SMITH';

 dbms_output.put_line('员工的工作是：'||v_emp.job||'； 他的薪水是：'||v_emp.sal);

  end;

  2.编写一个程序块，接受用户输入一个部门号，从dept表中显示该部门的名称与所在位置。

方法一：（传统方法）

declare

v_loc deptcp.dname%type;

v_dname deptcp.dname%type;

v_deptno deptcp.deptno%type;

begin

v_deptno :=&部门编号;

select loc,dnameinto v_loc,v_dnamefromdeptcp where deptno=v_deptno;

dbms_output.put_line('员工所在地是：'||v_loc||'；部门名称是：'||v_dname);

exception

when no_data_found

  thendbms_output.put_line('您输入的部门编号不存在，请从新输入，谢谢');

end;

方法二：（使用%rowtype）

  declare

   v_dept dept%rowtype;

   begin

   select * into v_dept from deptwhere deptno=&部门号;

  dbms_output.put_line(v_dept.dname||'--'||v_dept.loc);

   end;

 

  3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。（*期末考试试题*）

declare

   v_salemp.sal%type;

  begin

    selectsal+commintov_salfromempwhereempno=&雇员号;

   dbms_output.put_line(v_sal);

  end;

 

  4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。

方式一：（错误程序）（让学生思考错在哪里？）

  declare

   v_emp empcp%rowtype;

   begin

   select*into v_empfromempcpwhere empno = &雇员编号;

   dbms_output.put_line('整体薪水是：'||v_emp.sal+v_emp.comm);

   end;

 

 

  declare

    v_empemp%rowtype;

   begin

    select *intov_empfromempwhereempno=&雇员号;

    dbms_output.put_line(v_emp.sal+v_emp.comm);

  end;

 

 

  5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：

              Designation    Raise

              -----------------------

              Clerk          500

              Salesman       1000

              Analyst        1500

              Otherwise      2000

编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。（*期末考试试题*）

 

 

declare

    v_empemp%rowtype;

 begin

      select *intov_empfromempwhereename='&name';

      ifv_emp.job='CLERK'then

         updateempsetsal=sal+500whereempno=v_emp.empno;

      elsifv_emp.job='SALESMAN'then

         updateempsetsal=sal+1000whereempno=v_emp.empno;

      elsifv_emp.job='ANALYST'then

         updateempsetsal=sal+1500whereempno=v_emp.empno;

      else 

         updateempsetsal=sal+2000whereempno=v_emp.empno; 

      endif;

       commit;

end;

 

 

 

 

 

 

 

 

6.编写一个程序块，将emp表中雇员名全部显示出来。

  declare

   cursorv_cursorisselect *fromemp;

   begin

    forv_empinv_cursor

       loop

        dbms_output.put_line(v_emp.ename);

       endloop;  

   end;

 

  7.编写一个程序块，将emp表中前5人的名字显示出来。

declare

   cursorv_cursorisselect *fromemp;

   v_countnumber :=1;

   begin

    forv_empinv_cursor

       loop

        dbms_output.put_line(v_emp.ename);

        v_count :=v_count+1;

        exitwhenv_count>5;

       endloop;  

   end;

 

 

  8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。（*期末考试试题*）

declare

   v_empemp%rowtype;

   my_exceptionException;

 begin

  

   select *intov_empfromempwhereename='&name';

   raisemy_exception;

  

   exception

         whenno_data_foundthen

              dbms_output.put_line('该雇员不存在!');

         whenothersthen

          dbms_output.put_line(v_emp.job||'---'||v_emp.sal);

 end; 

  

 

 

  9.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”(课堂未讲)。

declare

  v_dividendfloat;

  v_divisorfloat;

  v_resultfloat;

  my_exceptionException;

  begin

        v_dividend:=&被除数;

        v_divisor:=&除数;

        v_result:=v_dividend/v_divisor;

        raisemy_exception;

        exception

      whenmy_exceptionthen

        dbms_output.put_line(v_result);

      whenothersthen

        dbms_output.put_line('除数不能为0');

  end;

 

二.声明和使用游标

   使用游标属性

   使用游标For循环工作

   声明带参数的游标

   （使用FOR UPDATEOF和CURRENT OF子句工作）

 

 

1.       通过使用游标来显示dept表中的部门名称。

declare

   cursorv_cursorisselect *fromdept;

  begin

    forv_deptinv_cursor

     loop

       dbms_output.put_line(v_dept.dname);

     endloop;  

 end;

 

 

2.       使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。

declare

     cursorv_cursorisselect *fromempwheredeptno=&部门号;

  begin

     forv_empinv_cursor

loop          dbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'

||v_emp.sal);

        endloop;

  end;

 

 

3.       使用带参数的游标，实现第2题。

declare

   cursorv_cursor(p_deptnonumber)isselect *fromempwheredeptno=p_deptno;

   v_deptnonumber(2);

  begin

   v_deptno:=&部门号;

   forv_empinv_cursor(v_deptno)

    loop

       dbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--'||v_emp.sal);

    endloop;

  end; 

 

  4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。

declare

   cursorv_cursorisselect *fromemp;

 begin

   forv_empinv_cursor

  loop

   ifv_emp.enamelike'A%'then

     updateempsetsal=sal+sal*0.1whereempno=v_emp.empno;

   elsifv_emp.enamelike'S%'then

    updateempsetsal=sal+sal*0.1whereempno=v_emp.empno;

   endif;

   commit;

  endloop;

 end;

 

5.       emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。

declare

  cursorv_cursoris

    select *fromemp;

begin

  forv_empinv_cursorloop

    ifv_emp.sal *1.1 < 5000then

      updateempsetsal =sal * 1.1whereempno =v_emp.empno;

    endif;

    commit;

  endloop;

end;

 

 

三，创建PL/SQL记录和PL/SQL表

    创建过程

    创建函数

 

 

  3.创建一个过程，能向dept表中添加一个新记录.（in参数）

createorreplaceprocedure

insert_dept(dept_noinnumber,dept_nameinvarchar2,dept_locinvarchar2)

 is

 begin

   insertintodeptvalues(dept_no,dept_name,dept_loc);

 end;

 

调用该存储过程：

 begin

    insert_dept(50,'技术部','武汉');

  end;

 

 

  4.创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值。（out参数）

然后调用过程。

createorreplaceprocedure

 find_emp3(emp_nameinvarchar2,emp_saloutnumber)

 is

     v_salnumber(5);

 begin

      selectsalintov_salfromempwhereename = emp_name;

      emp_sal:=v_sal;

     exception

        whenno_data_foundthen

        emp_sal :=0;

 end;

 

调用：

declare

  v_salnumber(5);

 begin

   find_emp3('ALLEN',v_sal);

   dbms_output.put_line(v_sal);

 end;

  

 

  5.编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比（*课堂没讲）。

   （利用过程，in out 参数）

createorreplaceprocedure

   update_sal(emp_noinnumber,parsentinfloat)

 is

   begin

     updateempsetsal=sal+sal*parsentwhereempno=emp_no;

   end;

调用：

 begin

  update_sal(7499,0.5);

 end;

 

 

  6.创建一个函数，它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。

    然后调用此函数。

 

  7.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。

然后调用此函数。

createorreplacefunction

  find_dept(dept_nonumber)

  returndept%rowtype

  is

   v_deptdept%rowtype;

   begin

    select *intov_deptfromdeptwheredeptno=dept_no;

    returnv_dept;

  end;

调用函数：

declare

  v_deptdept%rowtype;

  begin

         v_dept:=find_dept(30);

         dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);

 end;

 

 

 

四，创建程序包

    创建程序件

    创建触发器

 

 

  1.创建在dept表中插入和删除一个记录的数据包，它且有一个函数(返回插入或删除的部门名称)和两个过程。

然后调用包。

createorreplacepackagepack_1

 is

  procedurefind_emp(emp_noinnumber,emp_nameoutvarchar2);

  procedurefind_emp1(emp_nameinvarchar2,emp_nooutnumber);

  functionfind_dname(dept_nonumber)

  returnvarchar2;

  endpack_1;

   

createorreplacepackagebodypack_1

is

 functionfind_dname(dept_nonumber)

 returnvarchar2

is

 v_dnamevarchar2(20);

 begin

  selectdnameintov_dnamefromdeptwheredeptno=dept_no;

  retrunv_dname;

  end;

endpack_1;

 

调用包：

declare

  v_dnamevarchar2(20);

  begin

   v_dname:=pack_1.find_dname(50);

   dbms_output.put_line(v_dname);

   end;

  3.使用单独过程打开游标变量，将dept表中的记录显示出来。只创建程序包，无需主体。

 

  4.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。

createorreplacetriggerdelete_emp

      afterdeleteonemp

     foreachrow

      begin

       insertintoret_empvalues(:old.empno,:old.ename,:old.job,

         :old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

       end;

 

5.创建一个行级别触发器，停止用户删除雇员名为"SMITH"的记录。

  createorreplacetriggerdelete_smith

     beforedeleteonemp

     foreachrow

     when (old.ename='SMITH')

     begin

     raise_application_error(-20001,'不能删除该条信息!');

    end;

 

 

6.       创建一个语句级别触发器，不允许用户在"Sundays"使用emp表。

    createorreplacetriggert_control_emp

      beforeinsertorupdateordeleteonemp

      begin

       ifto_char(sysdate,'DY','nls_date_language=AMERICAN')

        in('SUN')then

        raise_application_error(-20001,'不允许在星期天操作emp表');

        endif;

        end;