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;
posted on 2009-04-19 02:37
雨飞 阅读(99)
评论(0) 编辑 收藏