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>

相关阅读:
10.导弹拦截
 学习Python中pywifi模块的基本用法
 Oracle数据库SQL优化详解
 像MySql一样在控制台输出打印漂亮的表格
 【C语言】插入排序详解
 关于XML一些介绍和注意事项
 C++学习——vector类的使用
 【IO多路转接】poll&epoll
线程池|单例模式|STL、智能指针线程安全|读者写者问题
 YOLOv5、v7改进之二十六：改进特征融合网络PANet为ASFF自适应特征融合网络
原文地址：https://blog.csdn.net/m0_63064861/article/details/133950840