select ename from (select rownum,ename from  emp order by ename) where  rownum<6;

insert into wjk_test4 values(1,'a');
insert into wjk_test4 values(1,'a');
insert into wjk_test4 values(1,'a');
insert into wjk_test4 values(1,'a');
insert into wjk_test4 values(2,'a');
insert into wjk_test4 values(2,'a');

user
rowid id name
 1    1 a
 2   1 a
 3   1 a

delete from wjk_test4 where rowid not in(
select max(rowid) from wjk_test4 group by id,name);
declare
v_id int;
begin
v_id:=10;
dbms_output.put_line(v_id);
end;

declare
 v_name emp.ename%TYPE;
 v_emp emp%ROWTYPE;
begin
 v_emp.ename:='wjk';
 v_name:='tarena';
 dbms_output.put_line(v_name);
 dbms_output.put_line(v_emp.ename);
end;
 
自定义 记录类型
declare
 type student is record(
  id emp.empno%TYPE,
  name emp.ename%TYPE
 );
 v_stu student;
begin
 v_stu.id:=88;
 v_stu.name:='wjk';
 dbms_output.put_line(v_stu.id||' '||v_stu.name);
end;

declare
 type array_name  is table of emp.ename%type
 INDEX  BY   BINARY_INTEGER;

 r_name array_name;
begin
 r_name(1):='a';
 r_name(2):='b';
 
 dbms_output.put_line(r_name(1));
 dbms_output.put_line(r_name(2));
end;

declare  
 type student is record(
  id emp.empno%TYPE,
  name emp.ename%TYPE
 );
 
 type array_name  is table of emp.ename%type
 INDEX  BY   BINARY_INTEGER;

 r_name array_name;--table类型
 v_stu student;--记录类型
 v_name emp.ename%type;--单列单值
 v_emp emp%rowtype;--行类型 
begin
 select ename into v_name from emp where empno=7369;
 dbms_output.put_line(v_name);

 select * into v_emp from emp where empno=7369;
 dbms_output.put_line(v_emp.job||' '||v_emp.sal);

 select empno,ename into v_stu from emp where empno=7369;
 dbms_output.put_line(v_stu.id||' '||v_stu.name);

end;

 select ename into r_name from emp;--错误，要使用游标
 
declare
    id int:=3;
begin
 if id<5 then
     dbms_output.put_line('<5');
                 elsif id<10 then
    dbms_output.put_line('<10');
 else
    dbms_output.put_line('>10');
                  end if;
end;

declare
    id int:=0;
begin
    loop
 dbms_output.put_line(id);
 exit when id>10;
 
 id:=id+1;
   end loop;
end;

declare
    id int:=0;
begin
    loop
 dbms_output.put_line(id);
 --exit when id>10;
 if id>10 then
      exit;
 end if;
 id:=id+1;
   end loop;
end;

declare
    id int:=0;
begin
    while id<10 loop
 dbms_output.put_line(id);
 id:=id+1;
   end loop;
end;

declare
    id int:=0;
begin
  for id in 1..10 loop
 dbms_output.put_line(id);
 --id:=id+1;
   end loop;
end;

create table wjk_test5(id number,name varchar2(20));
 
declare
    v_index number :=0;
begin
         while v_index<10 loop
 insert into wjk_test5 values(v_index,'tarena'||v_index);
 v_index:=v_index+1;
         end loop;
end;

declare
   v_name emp.ename%type;
  cursor c1 is select ename from emp;
begin
 open c1;
 loop
  fetch c1 into v_name;
  exit when c1%notfound;
  dbms_output.put_line(v_name);
 end loop;
 close c1;
end;
 
declare
   v_name emp.ename%type;
  cursor c1 is select ename from emp;
begin
 open c1;
 fetch c1 into v_name;
 while c1%found loop   
  dbms_output.put_line(v_name);
  fetch c1 into v_name;
 end loop;
 close c1;
end;
 
 
declare
  -- v_name emp.ename%type;
   type array_name  is table of emp.ename%type
 INDEX  BY   BINARY_INTEGER;
   a_name array_name;
   cursor c1 is select ename from emp;
  cnt number:=1;
begin 
 for i in c1 loop
     a_name(cnt):=i.ename;
     cnt:=cnt+1;
 end loop;
 
 cnt:=a_name.first;
 while cnt<a_name.last+1 loop
  dbms_output.put_line(a_name(cnt));
  cnt:=a_name.next(cnt);
 end loop;
 dbms_output.put_line('=============');

 for i in a_name.first..a_name.last loop
  dbms_output.put_line(a_name(i)); 
 end loop;
 
end;

 create or replace procedure  wjk_hello(
v_id emp.empno%type
)
 is
   v_name emp.ename%type;
begin
   select ename into v_name from emp where empno=v_id;
   dbms_output.put_line(v_name);
end wjk_hello;

 create or replace procedure  wjk_hello(
v_id in emp.empno%type,
v_name out emp.ename%type
)
 is
 
begin
   select ename into v_name from emp where  empno=v_id; 
end wjk_hello;

declare
   v_name emp.ename%type;
  v_id emp.empno%type:=7369;
begin
     wjk_hello(v_id,v_name);
     dbms_output.put_line(v_name);
end;

create or replace function detpavg(
v_deptno emp.deptno%type)
 return number
is
 v_avg number(6,2);
begin
 select avg(sal) into v_avg from emp where deptno=v_deptno;
 return v_avg;
end;

create or replace package avg_package
as
 function deptavg(v_deptno emp.deptno%type) return number;
 v_id emp.empno%type;
  type array_name  is table of emp.ename%type
 INDEX  BY   BINARY_INTEGER;
  a_name array_name;
   cursor c1 is select ename from emp;
end  avg_package ;

create or replace package body avg_package
is
 function deptavg(v_deptno emp.deptno%type) return number
is
 v_avg number(6,2);
begin
 select avg(sal) into v_avg from emp where deptno=v_deptno;
 return v_avg;
  end deptavg;

end avg_package;

select avg_package.deptavg(10) from dual;