Table of Contents
Oracle Database—-Seventh Week Experiment
Loops and cursors
Oracle Database—-Seventh Week Experiment
Loops and Cursors
Loops and Cursors ? Loop ? First set the display output results Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as system@ORCL SQL> set serveroutput on; 1. Simple loop Example 9-11. Use a simple loop to find the sum of even numbers between 1 and 100. SQL> SQL> declare 2 v_counter binary_integer :=1; 3 v_sum number :=0; 4 begin 5 loops 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 loop 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 loop 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/ operation result: 2550 PL/SQL procedure successfully completed 4 jump structure First create a table: 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> ? Cursor 【9-15】 Example 9-15. Query the employee information of a certain department based on the entered department number. The department number is specified when the program is running. 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 := & amp;x; 7 open c_emp; 8 loops 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/ Pop-up input box: Enter the value of x: 100 Then, the running result is: 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】 Use simple cycle statistics and output the average salary of each department 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 loops 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/ operation result: 100 8600 30 4150 7000 20 9500 70 10000 90 19333.33333333333333333333333333333333333 110 10150 50 3475.555555555555555555555555555555555556 40 6500 80 8955.882352941176470588235294117647058824 10 4400 60 5760 PL/SQL procedure successfully completed SQL> 【9-17】 Use WHILE loop to count and output the average salary of each department 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.33333333333333333333333333333333333 110 10150 50 3475.555555555555555555555555555555555556 40 6500 80 8955.882352941176470588235294117647058824 10 4400 60 5760 PL/SQL procedure successfully completed SQL> 【9-18】 Use FOR loop to count and output the average salary of each department 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.33333333333333333333333333333333333 110 10150 50 3475.555555555555555555555555555555555556 40 6500 80 8955.882352941176470588235294117647058824 10 4400 60 5760 PL/SQL procedure successfully completed SQL>
Test the entire code
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 LOOPs
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);
13END;
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: line 9, column 3:
PLS-00103: Symbol "END" occurs when one of the following is expected:
. ( * @ % & amp; = - + ; </ / >
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 LOOPs
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);
13END;
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: line 9, column 3:
PLS-00103: Symbol "END" occurs when one of the following is expected:
. ( * @ % & amp; = - + ; </ / >
at in is mod remainder not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
between || member SUBMULTISET_
The ";" symbol is replaced by "END" before continuing.
ORA-06550: line 13, column 14:
PLS-00103: Symbol "." occurs when one of the following is expected:
;
ORA-06550: line 14, column 1:
PLS-00103: Symbol "END" appears
SQL>
SQL> declare
2 v_counter binary_integer :=1;
3 v_sum number :=0;
4 begin
5 loops
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: line 7, column 29:
PLS-00103: Symbol ";" occurs when one of the following is expected:
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
The symbol "exit" is replaced by ";" before continuing.
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: line 7, column 12:
PLS-00103: Symbol "=" occurs when one of the following is required:
:= . ( @ % ;
ORA-06550: line 8, column 7:
PLS-00103: Symbol "END" appears
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: line 10, column 9:
PLS-00103: Symbol "IF" occurs when one of the following is expected:
:= . ( @ % ;
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: line 6, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 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 := & amp;x;
7 open c_emp;
8 loops
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: line 12, column 33:
PLS-00302: 'EMPLOYEES_ID' component must be declared
ORA-06550: line 12, column 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 := & amp;x;
7 open c_emp;
8 loops
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: line 12, column 33:
PLS-00302: 'EMPLOYEES_ID' component must be declared
ORA-06550: line 12, column 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 := & amp;x;
7 open c_emp;
8 loops
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 := & amp;x;
7 open c_emp;
8 loops
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 loops
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.33333333333333333333333333333333333
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: line 10, column 32:
PLS-00103: Symbol ";" occurs when one of the following is expected:
. ( * @ % & amp; = - + </ > at in
is loop mod remainder not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
between || multiset member SUBMULTISET_
ORA-06550: line 14, column 6:
PLS-00103: Symbol "CLOSE" occurs when one of the following is required:
end not pragma final
instantiable order overriding static member constructor map
ORA-06550: line 17, column 0:
PLS-00103: Symbol "end-of-file" occurs when one of the following is expected:
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: line 10, column 34:
PLS-00103: Symbol ";" occurs when one of the following is expected:
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
The symbol "exit" is replaced by ";" before continuing.
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.33333333333333333333333333333333333
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: line 10, column 15:
PLS-00103: Symbol ";" occurs when one of the following is expected:
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: line 10, column 15:
PLS-00103: Symbol ";" occurs when one of the following is expected:
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: line 10, column 15:
PLS-00103: Symbol ";" occurs when one of the following is expected:
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.33333333333333333333333333333333333
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(employee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','[email protected]',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(employee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','[email protected]',sysdate,'AC_ACCOUNT', 200);
end if;
end;
ORA-06550: line 10, column 107:
PL/SQL: ORA-02289: sequence does not exist
ORA-06550: line 10, column 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(employee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','[email protected]',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(employee_id,first_name,last_name,email,hire_date,job_id,department_id)values (employees_seq.nextval,'san','zhang','[email protected]',sysdate,'AC_ACCOUNT', 200);
end if;
end;
ORA-06550: line 5, column 107:
PL/SQL: ORA-02289: sequence does not exist
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
SQL>
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry skill treeUsing database Create and delete database 76329 people are learning the system