Oracle数据库----第七周实验____循环与游标

骑猪看日落 2024-03-27 19:01 75阅读 0赞

**目录**

Oracle数据库----第七周实验

循环与游标

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

## Oracle数据库----第七周实验 ##

##    循环与游标 ##

循环与游标
    	循环
    	首先设置显示输出结果
    
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
    Connected as system@ORCL
    SQL> set serveroutput on;
    1.简单循环
    例9-11.利用简单循环求1-100之间偶数的和。
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3    v_sum number :=0;
      4  begin
      5    loop
      6      if mod(v_counter,2)=0 then
      7      v_sum:=v_sum+v_counter;
      8      end if;
      9      v_counter :=v_counter +1;
     10      exit when v_counter>100;
     11    end loop;
     12    dbms_output.put_line(v_sum);
     13  end;
     14  
     15  /
    2550
    PL/SQL procedure successfully completed
    SQL>
    2.while循环
    
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3    v_sum number :=0;
      4  begin
      5    while v_counter<100 loop
      6    if mod(v_counter,2)=0 then
      7    v_sum :=v_sum+v_counter;
      8    end if;
      9    v_counter:=v_counter+1;
     10    end loop;
     11    Dbms_Output.put_line(v_sum);
     12  end;
     13  /
    2450
    PL/SQL procedure successfully completed
    3．for循环
    SQL> 
    SQL> declare
      2  v_sum number :=0;
      3  begin
      4    for v_counter in 1..100 loop
      5      if mod(v_counter,2)=0 then
      6        v_sum :=v_sum+v_counter;
      7        end if;
      8        end loop;
      9        Dbms_Output.put_line(v_sum);
     10  end;
     11  /
     运行结果：
    2550
     
    PL/SQL procedure successfully completed
    4跳转结构
    先创建一个表：
    temp_table
    
    create table temp_table
    (
       v_counter varchar2(255),
       v_sum varchar2(255)
    )
    
    
    SQL> 
    SQL> create table temp_table
      2  (
      3     v_counter varchar2(255),
      4     v_sum varchar2(255)
      5  )
      6  /
     
    Table created
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3  begin
      4    <<label>>
      5    insert into temp_table values(v_counter,'loop index');
      6    v_counter :=v_Counter+1;
      7    if v_counter<=50 then
      8      goto label;
      9      end if;
     10  end;
     11  /
     
    PL/SQL procedure successfully completed
     
    SQL>
    	游标
    【9-15】
    例9-15.根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。
    SQL> 
    SQL> declare
      2    v_deptno hr.employees.department_id%type;
      3    cursor c_emp is select * from hr.employees where department_id=v_deptno;
      4    v_emp c_emp%rowtype;
      5  begin
      6    v_deptno :=&x;
      7    open c_emp;
      8    loop
      9      fetch c_emp into v_emp;
     10      exit when c_emp%notfound;
     11      dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
     12    end loop;
     13    close c_emp;
     14  end;
     15  /
    弹出输入框：
    输入x的值：100
    则， 运行结果为：
    108 Nancy Greenberg 12000 100
    109 Daniel Faviet 9000 100
    110 John Chen 8200 100
    111 Ismael Sciarra 7700 100
    112 Jose Manuel Urman 7800 100
    113 Luis Popp 6900 100
     
    PL/SQL procedure successfully completed
    SQL>
    
    【9-16】
    利用简单循环统计并输出各个部门的平均工资
    
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8     loop
      9       fetch c_dept_stat into  v_dept;
     10       exit when c_dept_stat%notfound;
     11       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     12       end loop;
     13       close c_dept_stat;
     14       end;
     15  /
    运行结果：
    
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL>
    
    【9-17】
    利用WHILE循环统计并输出各个部门的平均工资
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8       fetch c_dept_stat into  v_dept;
      9       while c_dept_stat%found loop
     10       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     11       fetch c_dept_stat into  v_dept;
     12     end loop;
     13     close c_dept_stat;
     14  end;
     15  /
     
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL>
    【9-18】
    利用FOR循环统计并输出各个部门的平均工资
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     FOR v_dept IN (
      8       select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      9   department_id
     10       ) LOOP
     11       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     12     end loop;
     13  end;
     14  /
     
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL>

### 测试全程代码 ###

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
    Connected as system@ORCL
     
    SQL> set serveroutput on;
    SQL> 
    SQL> DECLARE
      2  v_counter BINARY_INTEGER :=1;
      3  v_sum NUMBER :=0;
      4  BEGIN
      5    LOOP
      6      IF mod(v_counter,2)=0 THEN
      7        v_sum :=v_sum+v_counter
      8    END IF
      9    v_counter :=v_counter+1;
     10    EXIT WHEN v_counter>100;
     11    END LOOP
     12    DBMS_OUTPUT.PUT_LINE(v_sum);
     13  END;
     14  
     15  /
     
    DECLARE
    v_counter BINARY_INTEGER :=1;
    v_sum NUMBER :=0;
    BEGIN
      LOOP
        IF mod(v_counter,2)=0 THEN
          v_sum :=v_sum+v_counter
      END IF
      v_counter :=v_counter+1;
      EXIT WHEN v_counter>100;
      END LOOP
      DBMS_OUTPUT.PUT_LINE(v_sum);
    END;
     
    ORA-06550: 第 9 行, 第 3 列: 
    PLS-00103: 出现符号 "END"在需要下列之一时：
     . ( * @ % & = - + ; < / >
       at in is mod remainder not rem <an exponent (**)>
       <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
       between || member SUBMULTISET_
     
    SQL> 
    SQL> DECLARE
      2  v_counter BINARY_INTEGER :=1;
      3  v_sum NUMBER :=0;
      4  BEGIN
      5    LOOP
      6      IF mod(v_counter,2)=0 THEN
      7        v_sum :=v_sum+v_counter
      8    END IF;
      9    v_counter :=v_counter+1;
     10    EXIT WHEN v_counter>100;
     11    END LOOP
     12    DBMS_OUTPUT.PUT_LINE(v_sum);
     13  END;
     14  /
     
    DECLARE
    v_counter BINARY_INTEGER :=1;
    v_sum NUMBER :=0;
    BEGIN
      LOOP
        IF mod(v_counter,2)=0 THEN
          v_sum :=v_sum+v_counter
      END IF;
      v_counter :=v_counter+1;
      EXIT WHEN v_counter>100;
      END LOOP
      DBMS_OUTPUT.PUT_LINE(v_sum);
    END;
     
    ORA-06550: 第 9 行, 第 3 列: 
    PLS-00103: 出现符号 "END"在需要下列之一时：
     . ( * @ % & = - + ; < / >
       at in is mod remainder not rem <an exponent (**)>
       <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
       between || member SUBMULTISET_
    符号 ";" 被替换为 "END" 后继续。
    ORA-06550: 第 13 行, 第 14 列: 
    PLS-00103: 出现符号 "."在需要下列之一时：
     ;
    ORA-06550: 第 14 行, 第 1 列: 
    PLS-00103: 出现符号 "END"
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3    v_sum number :=0;
      4  begin
      5    loop
      6      if mod(v_counter,2)=0 then
      7      v_sum:=v_sum+v_counter;
      8      end if;
      9      v_counter :=v_counter +1;
     10      exit when v_counter>100;
     11    end loop;
     12    dbms_output.put_line(v_sum);
     13  end;
     14  
     15  /
     
    2550
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3    v_sum number :=0;
      4  begin
      5    while v_counter<100 loop
      6    if mod(v_counter,2)=0 then;
      7    v_sum :=v_sum+v_counter;
      8    end if;
      9    v_counter:=v_counter+1;
     10    end loop;
     11    Dbms_Output.put_line(v_sum);
     12  end;
     13  /
     
    declare
      v_counter binary_integer :=1;
      v_sum number :=0;
    begin
      while v_counter<100 loop
      if mod(v_counter,2)=0 then;
      v_sum :=v_sum+v_counter;
      end if;
      v_counter:=v_counter+1;
      end loop;
      Dbms_Output.put_line(v_sum);
    end;
     
    ORA-06550: 第 7 行, 第 29 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     begin case declare exit for
       goto if loop mod null pragma raise return select update while
       with <an identifier> <a double-quoted delimited-identifier>
       <a bind variable> << close current delete fetch lock insert
       open rollback savepoint set sql execute commit forall merge
       pipe
    符号 "exit" 被替换为 ";" 后继续。
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3    v_sum number :=0;
      4  begin
      5    while v_counter<100 loop
      6    if mod(v_counter,2)=0 then
      7    v_sum :=v_sum+v_counter;
      8    end if;
      9    v_counter:=v_counter+1;
     10    end loop;
     11    Dbms_Output.put_line(v_sum);
     12  end;
     13  /
     
    2450
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2  v_sum number :=0;
      3  begin
      4    for v_counter in 1..100 loop
      5      if mod(v_counter,2)=0 then
      6        v_sum=v_sum+v_counter;
      7        end if;
      8        end loop;
      9        Dbms_Output.put_line(v_sum);
     10  end;
     11  /
     
    declare
    v_sum number :=0;
    begin
      for v_counter in 1..100 loop
        if mod(v_counter,2)=0 then
          v_sum=v_sum+v_counter;
          end if;
          end loop;
          Dbms_Output.put_line(v_sum);
    end;
     
    ORA-06550: 第 7 行, 第 12 列: 
    PLS-00103: 出现符号 "="在需要下列之一时：
     := . ( @ % ;
    ORA-06550: 第 8 行, 第 7 列: 
    PLS-00103: 出现符号 "END"
     
    SQL> 
    SQL> declare
      2  v_sum number :=0;
      3  begin
      4    for v_counter in 1..100 loop
      5      if mod(v_counter,2)=0 then
      6        v_sum :=v_sum+v_counter;
      7        end if;
      8        end loop;
      9        Dbms_Output.put_line(v_sum);
     10  end;
     11  /
     
    2550
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3  begin
      4    <<label>>
      5    insert into temp_table values(v_counter,'loop index');
      6    v_counter :=v_Counter+1;
      7    if v_counter<=50 then
      8      goto label;
      9      enf if;
     10  end;
     11  /
     
    declare
      v_counter binary_integer :=1;
    begin
      <<label>>
      insert into temp_table values(v_counter,'loop index');
      v_counter :=v_Counter+1;
      if v_counter<=50 then
        goto label;
        enf if;
    end;
     
    ORA-06550: 第 10 行, 第 9 列: 
    PLS-00103: 出现符号 "IF"在需要下列之一时：
     := . ( @ % ;
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3  begin
      4    <<label>>
      5    insert into temp_table values(v_counter,'loop index');
      6    v_counter :=v_Counter+1;
      7    if v_counter<=50 then
      8      goto label;
      9      end if;
     10  end;
     11  /
     
    declare
      v_counter binary_integer :=1;
    begin
      <<label>>
      insert into temp_table values(v_counter,'loop index');
      v_counter :=v_Counter+1;
      if v_counter<=50 then
        goto label;
        end if;
    end;
     
    ORA-06550: 第 6 行, 第 15 列: 
    PL/SQL: ORA-00942: 表或视图不存在
    ORA-06550: 第 6 行, 第 3 列: 
    PL/SQL: SQL Statement ignored
     
    SQL> 
    SQL> create table temp_table
      2  (
      3     v_counter varchar2(255),
      4     v_sum varchar2(255)
      5  )
      6  /
     
    Table created
     
    SQL> 
    SQL> declare
      2    v_counter binary_integer :=1;
      3  begin
      4    <<label>>
      5    insert into temp_table values(v_counter,'loop index');
      6    v_counter :=v_Counter+1;
      7    if v_counter<=50 then
      8      goto label;
      9      end if;
     10  end;
     11  /
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2  v_deptno hr.employees.department_id%type;
      3  cursor c_emp is select * from hr.employees where department_id=v_deptno;
      4  v_emp c_emp%rowtype;
      5  begin
      6    v_deptno :=&x;
      7    open c_emp;
      8    loop
      9       fetch c_emp into v_emp;
     10       exit when c_emp%notfound;
     11       Dbms_Output.put_line(v_emp.employees_id||''||v_emp.first_name||''||v_emp.last_name||''||v_emp.salary||' '||v_deptno);
     12       end loop;
     13       close c_emp;
     14  end;
     15  /
     
    declare
    v_deptno hr.employees.department_id%type;
    cursor c_emp is select * from hr.employees where department_id=v_deptno;
    v_emp c_emp%rowtype;
    begin
      v_deptno :=10;
      open c_emp;
      loop
         fetch c_emp into v_emp;
         exit when c_emp%notfound;
         Dbms_Output.put_line(v_emp.employees_id||''||v_emp.first_name||''||v_emp.last_name||''||v_emp.salary||' '||v_deptno);
         end loop;
         close c_emp;
    end;
     
    ORA-06550: 第 12 行, 第 33 列: 
    PLS-00302: 必须声明 'EMPLOYEES_ID' 组件
    ORA-06550: 第 12 行, 第 6 列: 
    PL/SQL: Statement ignored
     
    SQL> 
    SQL> declare
      2  v_deptno hr.employees.department_id%type;
      3  cursor c_emp is select * from hr.employees where department_id=v_deptno;
      4  v_emp c_emp%rowtype;
      5  begin
      6    v_deptno :=&x;
      7    open c_emp;
      8    loop
      9       fetch c_emp into v_emp;
     10       exit when c_emp%notfound;
     11       Dbms_Output.put_line(v_emp.employees_id||' '||v_emp.first_name||' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
     12       end loop;
     13       close c_emp;
     14  end;
     15  /
     
    declare
    v_deptno hr.employees.department_id%type;
    cursor c_emp is select * from hr.employees where department_id=v_deptno;
    v_emp c_emp%rowtype;
    begin
      v_deptno :=100;
      open c_emp;
      loop
         fetch c_emp into v_emp;
         exit when c_emp%notfound;
         Dbms_Output.put_line(v_emp.employees_id||' '||v_emp.first_name||' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
         end loop;
         close c_emp;
    end;
     
    ORA-06550: 第 12 行, 第 33 列: 
    PLS-00302: 必须声明 'EMPLOYEES_ID' 组件
    ORA-06550: 第 12 行, 第 6 列: 
    PL/SQL: Statement ignored
     
    SQL> 
    SQL> declare
      2    v_deptno hr.employees.department_id%type;
      3    cursor c_emp is select * from hr.employees where department_id=v_deptno;
      4    v_emp c_emp%rowtype;
      5  begin
      6    v_deptno :=&x;
      7    open c_emp;
      8    loop
      9      fetch c_emp into v_emp;
     10      exit when c_emp%notfound;
     11      dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
     12    end loop;
     13    close c_emp;
     14  end;
     15  /
     
    108 Nancy Greenberg 12000 100
    109 Daniel Faviet 9000 100
    110 John Chen 8200 100
    111 Ismael Sciarra 7700 100
    112 Jose Manuel Urman 7800 100
    113 Luis Popp 6900 100
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2  v_deptno hr.employees.department_id%type;
      3  cursor c_emp is select * from hr.employees where department_id=v_deptno;
      4  v_emp c_emp%rowtype;
      5  begin
      6    v_deptno :=&x;
      7    open c_emp;
      8    loop
      9       fetch c_emp into v_emp;
     10       exit when c_emp%notfound;
     11       Dbms_Output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.last_name||' '||v_emp.salary||' '||v_deptno);
     12       end loop;
     13       close c_emp;
     14  end;
     15  
     16  /
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8     loop
      9       fetch c_dept_stat into  v_dept;
     10       exit when c_dept_stat%notfound;
     11       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     12       end loop;
     13       close c_dept_stat;
     14       end;
     15  /
     
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8       fetch c_dept_stat into  v_dept;
      9       while c_dept_stat%notfound;
     10       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     11       fetch c_dept_stat into  v_dept;
     12       end loop;
     13       close c_dept_stat;
     14       end;
     15  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
     begin
       open c_dept_stat;
         fetch c_dept_stat into  v_dept;
         while c_dept_stat%notfound;
         Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
         fetch c_dept_stat into  v_dept;
         end loop;
         close c_dept_stat;
         end;
     
    ORA-06550: 第 10 行, 第 32 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     . ( * @ % & = - + < / > at in
       is loop mod remainder not rem <an exponent (**)>
       <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
       between || multiset member SUBMULTISET_
    ORA-06550: 第 14 行, 第 6 列: 
    PLS-00103: 出现符号 "CLOSE"在需要下列之一时：
     end not pragma final
       instantiable order overriding static member constructor map
    ORA-06550: 第 17 行, 第 0 列: 
    PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
     pragma
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8       fetch c_dept_stat into  v_dept;
      9       while c_dept_stat%found loop;
     10       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     11       fetch c_dept_stat into  v_dept;
     12     end loop;
     13     close c_dept_stat;
     14  end;
     15  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
     begin
       open c_dept_stat;
         fetch c_dept_stat into  v_dept;
         while c_dept_stat%found loop;
         Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
         fetch c_dept_stat into  v_dept;
       end loop;
       close c_dept_stat;
    end;
     
    ORA-06550: 第 10 行, 第 34 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     begin case declare exit for
       goto if loop mod null pragma raise return select update while
       with <an identifier> <a double-quoted delimited-identifier>
       <a bind variable> << close current delete fetch lock insert
       open rollback savepoint set sql execute commit forall merge
       pipe
    符号 "exit" 被替换为 ";" 后继续。
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     open c_dept_stat;
      8       fetch c_dept_stat into  v_dept;
      9       while c_dept_stat%found loop
     10       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     11       fetch c_dept_stat into  v_dept;
     12     end loop;
     13     close c_dept_stat;
     14  end;
     15  /
     
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     FOR V_DEPT IN (
      8       select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      9   department_id;
     10       )LOOP
     11  
     12       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     13     end loop;
     14  end;
     15  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
     begin
       FOR V_DEPT IN (
         select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
         )LOOP
    
         Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
       end loop;
    end;
     
    ORA-06550: 第 10 行, 第 15 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     loop
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     FOR V_DEPT IN (
      8       select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      9   department_id
     10       )LOOP
     11  
     12       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     13     end loop;
     14  end;
     15  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
     begin
       FOR V_DEPT IN (
         select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
         )LOOP
    
         Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
       end loop;
    end;
     
    ORA-06550: 第 10 行, 第 15 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     loop
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     FOR V_DEPT IN (
      8       select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      9   department_id
     10       )LOOP
     11  
     12       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     13     end loop;
     14  end;
     15  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
     begin
       FOR V_DEPT IN (
         select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
         )LOOP
    
         Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
       end loop;
    end;
     
    ORA-06550: 第 10 行, 第 15 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     loop
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6   begin
      7     FOR v_dept IN (
      8       select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      9   department_id
     10       ) LOOP
     11       Dbms_Output.put_line(v_dept.department_id||' '||v_dept.avgsal);
     12     end loop;
     13  end;
     14  /
     
    100 8600
    30 4150
     7000
    20 9500
    70 10000
    90 19333.3333333333333333333333333333333333
    110 10150
    50 3475.555555555555555555555555555555555556
    40 6500
    80 8955.882352941176470588235294117647058824
    10 4400
    60 5760
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> declare
      2   cursor c_dept_stat is
      3   select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
      4   department_id;
      5   v_dept c_dept_stat%rowtype;
      6  begin
      7    update hr.employees set hr.employees.salary=salary+100 where employee_id=1000;
      8    if sql%notfound then
      9      insert into hr.employees(emploee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','zs@neusoft.edu.cn',sysdate,'AC_ACCOUNT',200);
     10      end if;
     11      end;
     12  /
     
    declare
     cursor c_dept_stat is
     select hr.employees.department_id,avg(hr.employees.salary) avgsal from hr.employees group by
     department_id;
     v_dept c_dept_stat%rowtype;
    begin
      update hr.employees set hr.employees.salary=salary+100 where employee_id=1000;
      if sql%notfound then
        insert into hr.employees(emploee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','zs@neusoft.edu.cn',sysdate,'AC_ACCOUNT',200);
        end if;
        end;
     
    ORA-06550: 第 10 行, 第 107 列: 
    PL/SQL: ORA-02289: 序列不存在
    ORA-06550: 第 10 行, 第 5 列: 
    PL/SQL: SQL Statement ignored
     
    SQL> 
    SQL> begin
      2    update hr.employees set hr.employees.salary=salary+100 where employee_id=1000;
      3    if sql%notfound then
      4      insert into hr.employees(emploee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','zs@neusoft.edu.cn',sysdate,'AC_ACCOUNT',200);
      5      end if;
      6      end;
      7  /
     
    begin
      update hr.employees set hr.employees.salary=salary+100 where employee_id=1000;
      if sql%notfound then
        insert into hr.employees(emploee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','zs@neusoft.edu.cn',sysdate,'AC_ACCOUNT',200);
        end if;
        end;
     
    ORA-06550: 第 5 行, 第 107 列: 
    PL/SQL: ORA-02289: 序列不存在
    ORA-06550: 第 5 行, 第 5 列: 
    PL/SQL: SQL Statement ignored
     
    SQL>

![fe7fbc9e3c0b4cb3b13b1cb3622e6708.png][]

![d2a03e3471a84c71bede52d2d719b326.png][]

[fe7fbc9e3c0b4cb3b13b1cb3622e6708.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/466597a982f045dba223dc37fbfb0eb6.png
[d2a03e3471a84c71bede52d2d719b326.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7f73d6ddf3c346799200b4bbd6d697ab.png