begin k:=# select * into myRecord from emp where empno=k; dbms_output.put_line('empno: '||myRecord.empno||' ename: '||myRecord.ename||' job: '||myRecord.job||' mgr: '||myRecord.mgr); end;
set serveroutput ondeclare cursor cur is select DEPTNO,avg(sal) from emp group by DEPTNO; v_DEPTNO emp.DEPTNO%type; v_sal emp.sal%type; begin open cur; fetch cur into v_DEPTNO,v_sal; while cur%found loop dbms_output.put_line(v_DEPTNO||','||v_sal); fetch cur into v_DEPTNO,v_sal; end loop; close cur; end;
我终于也写出来了,成功运行了declare mn emp.deptno%type; cursor myCursor is select distinct deptno from emp ; cursor myCur(cc number) is select sal from emp where deptno=cc; k number; s_totle number; v_sum number :=0;
begin open myCursor; fetch myCursor into mn; while myCursor%FOUND LOOP select count(empno) into s_totle from emp where deptno=mn; open myCur(mn); v_sum:=0; fetch myCur into k; while myCur%found loop v_sum:=v_sum+k; fetch myCur into k; end loop; dbms_output.put_line(v_sum/s_totle); close myCur; fetch myCursor into mn; end loop; close myCursor; end;
------给你写了个记录的操作,参考下。。 SQL> SQL> declare 2 v_empno number; 3 type v_rtype is record( 4 EMPNO scott.emp.empno%type, 5 ENAME scott.emp.ename%type, 6 JOB scott.emp.job%type, 7 MGR scott.emp.mgr%type); 8 myrecord v_rtype; 9 begin 10 v_empno := &empno; 11 select empno,ename,job,mgr into myrecord from scott.emp where empno = v_empno; 12 dbms_output.put_line('empno: ' || myRecord.empno || ' ename: ' || 13 myRecord.ename || ' job: ' || myRecord.job || 14 ' mgr: ' || myRecord.mgr); 15 exception 16 when others then 17 dbms_output.put_line('empno=' || v_empno || ' no date found!'); 18 end; 19 /empno: 7369 ename: SMITH job: CLERK mgr: 7902PL/SQL procedure successfully completedSQL> SQL> declare 2 v_empno number; 3 type v_rtype is record( 4 EMPNO scott.emp.empno%type, 5 ENAME scott.emp.ename%type, 6 JOB scott.emp.job%type, 7 MGR scott.emp.mgr%type); 8 myrecord v_rtype; 9 begin 10 v_empno := &empno; 11 select empno,ename,job,mgr into myrecord from scott.emp where empno = v_empno; 12 dbms_output.put_line('empno: ' || myRecord.empno || ' ename: ' || 13 myRecord.ename || ' job: ' || myRecord.job || 14 ' mgr: ' || myRecord.mgr); 15 exception 16 when others then 17 dbms_output.put_line('empno=' || v_empno || ' no date found!'); 18 end; 19 /empno=1 no date found!PL/SQL procedure successfully completedSQL>
EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
declare
k integer;
myRecord emp%ROWTYPE;
begin
k:=#
select * into myRecord from emp where empno=k;
dbms_output.put_line('empno: '||myRecord.empno||' ename: '||myRecord.ename||' job: '||myRecord.job||' mgr: '||myRecord.mgr);
end;
set serveroutput ondeclare
cursor cur is select DEPTNO,avg(sal) from emp group by DEPTNO;
v_DEPTNO emp.DEPTNO%type;
v_sal emp.sal%type;
begin
open cur;
fetch cur into v_DEPTNO,v_sal;
while cur%found loop
dbms_output.put_line(v_DEPTNO||','||v_sal);
fetch cur into v_DEPTNO,v_sal;
end loop;
close cur;
end;
mn emp.deptno%type;
cursor myCursor is select distinct deptno from emp ;
cursor myCur(cc number) is select sal from emp where deptno=cc;
k number;
s_totle number;
v_sum number :=0;
begin
open myCursor;
fetch myCursor into mn;
while myCursor%FOUND
LOOP
select count(empno) into s_totle from emp where deptno=mn;
open myCur(mn);
v_sum:=0;
fetch myCur into k;
while myCur%found
loop
v_sum:=v_sum+k;
fetch myCur into k;
end loop;
dbms_output.put_line(v_sum/s_totle);
close myCur;
fetch myCursor into mn;
end loop;
close myCursor;
end;
------给你写了个记录的操作,参考下。。
SQL>
SQL> declare
2 v_empno number;
3 type v_rtype is record(
4 EMPNO scott.emp.empno%type,
5 ENAME scott.emp.ename%type,
6 JOB scott.emp.job%type,
7 MGR scott.emp.mgr%type);
8 myrecord v_rtype;
9 begin
10 v_empno := &empno;
11 select empno,ename,job,mgr into myrecord from scott.emp where empno = v_empno;
12 dbms_output.put_line('empno: ' || myRecord.empno || ' ename: ' ||
13 myRecord.ename || ' job: ' || myRecord.job ||
14 ' mgr: ' || myRecord.mgr);
15 exception
16 when others then
17 dbms_output.put_line('empno=' || v_empno || ' no date found!');
18 end;
19 /empno: 7369 ename: SMITH job: CLERK mgr: 7902PL/SQL procedure successfully completedSQL>
SQL> declare
2 v_empno number;
3 type v_rtype is record(
4 EMPNO scott.emp.empno%type,
5 ENAME scott.emp.ename%type,
6 JOB scott.emp.job%type,
7 MGR scott.emp.mgr%type);
8 myrecord v_rtype;
9 begin
10 v_empno := &empno;
11 select empno,ename,job,mgr into myrecord from scott.emp where empno = v_empno;
12 dbms_output.put_line('empno: ' || myRecord.empno || ' ename: ' ||
13 myRecord.ename || ' job: ' || myRecord.job ||
14 ' mgr: ' || myRecord.mgr);
15 exception
16 when others then
17 dbms_output.put_line('empno=' || v_empno || ' no date found!');
18 end;
19 /empno=1 no date found!PL/SQL procedure successfully completedSQL>