学步园

--1、任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
begin
  update emp set ename = 'alerk' where empno = 7369;
   if sql%isopen then
     dbms_output.put_line('opening');
     else
        dbms_output.put_line('closing');
        end if;
        if sql%found then
           dbms_output.put_line('found');
           else
              dbms_output.put_line('not found');
              end if;
             dbms_output.put_line(sql%rowcount);
             exception
               when no_data_found then
                       dbms_output.put_line('nodata ');
                       when too_many_rows then
                            dbms_output.put_line('too many rows ');
                            end;
                            
    --2使用游标和loop循环来显示所有部门的名称
    declare
    cursor cur_name is select dname from dept;
    v_name cur_name%rowtype;
    begin
      for v_name in cur_name  loop
        dbms_output.put_line(v_name.dname);
        exit when cur_name%notfound;
        end loop;      
      end;
      --使用游标和while循环来显示所有部门的的地理位置（用%found属性）
      declare
      cursor cur_dept is
      select loc from dept;
      v_loc cur_dept%rowtype;
      begin
        open cur_dept;
        fetch cur_dept into v_loc;
        while cur_dept%found loop
          dbms_output.put_line(v_loc.loc);
            fetch cur_dept into v_loc;
            end loop;
        end;
     
    --接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
    declare
    cursor cur_emp is
    select  * from emp where empno = &v_deptno;
     r_emp emp%rowtype;
     
    begin
      for r_emp in cur_emp  loop
         dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
         end loop;
      end;
      
          declare
    cursor cur_emp(v_deptno varchar2) is
    select  * from emp where empno = v_deptno;
     r_emp emp%rowtype;
     
    begin
      for r_emp in cur_emp(&v_deptno)  loop
         dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
         end loop;
      end;
      --向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
          declare
cursor cur_emp(jobname varchar2) is
 select * from emp where job = jobname;
 r_emp emp%rowtype;
 begin
   --传递字符要加''  例如'CLERK'
   for r_emp in cur_emp(&jobname) loop
         dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
        end loop;
        end;
      
      
            declare
cursor cur_emp(jobname varchar2) is
 select * from emp where job = jobname;
 r_emp emp%rowtype;
 begin
   --传递字符要加''  例如'CLERK'
   for r_emp in cur_emp('CLERK') loop
         dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
        end loop;
        end;