select * from employees e where e.employeeid in
( select w2.employeeid from (select * from wage w ORDER BY w.wage DESC) w2 where rownum<=6);这个是pl/sql语句。就是用存储过程得到。wage表中工资(wage)最多的员工信息。。
我写了一下。可是要返回多条数据。。不懂了。。请高手。
( select w2.employeeid from (select * from wage w ORDER BY w.wage DESC) w2 where rownum<=6);这个是pl/sql语句。就是用存储过程得到。wage表中工资(wage)最多的员工信息。。
我写了一下。可是要返回多条数据。。不懂了。。请高手。
select * from wage t where t.sal=(select max(sal) from wage)
select * from employee order by wage desc ) t
wher rownum <=6;
没有提示就是用存储过程查出工资最大的六条记录
-- i_lines 是存储过程的输入参数，o_cur是存储过程的输出游标参数，用以获取返回的结果集!
CREATE OR REPLACE PROCEDURE emp_max_wage_proc(
i_lines NUMBER, -- 你要返回的记录行数
o_cur OUT SYS_REFCURSOR
)
IS
sqlstr VARCHAR2(200); -- 定义变量，用以存放SQL语句
BEGIN
sqlstr := 'select * from (select * from employee order by wage desc ) t wher rownum <=:i_lines'; -- 给SQL变量赋值，其中 :i_lines 是绑定变量，以提高执行效率!
OPEN o_cur FOR sqlstr USING i_lines; -- 给游标变量赋值
END;
/
set serveroutput on;
var c_cur refcursor;
exec emp_max_wage_proc(6,:c_cur);
print c_cur;
-- Step 1: (搭建环境代码)建表、插入数据
CREATE TABLE VelTrace(
posTime date,
lon varchar2(20),
lat varchar2(20),
UintName varchar2(20)
);INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2007-11-03 13:05:52','yyyy-mm-dd hh24:mi:ss'),'116:28E','39:54N','京B3662');
INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2008-01-24 10:04:02','yyyy-mm-dd hh24:mi:ss'),'117:10E','39:10N','津NB888');
INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(to_date('2009-03-09 08:56:59','yyyy-mm-dd hh24:mi:ss'),'112:55E','28:12N','湘G0054');COMMIT;DECLARE
v_posTime DATE;
v_lon VelTrace.lon%type;
v_lat VelTrace.lat%type;
v_UintName VelTrace.UintName%type; cursor cur is SELECT posTime,lon,lat,UintName from VelTrace order by UintName;
v_rand_line NUMBER(18,0); -- 每个车牌号将要插入的数据行数(在1400到8888之间)
v_lon_rand NUMBER(18,0); -- 车辆经度变化随机数
v_lat_rand NUMBER(18,0); -- 车辆纬度变化随机数
v_lon_new NUMBER(18,0); -- 车辆新的经度数
v_lat_new NUMBER(18,0); -- 车辆新的纬度数BEGIN
for rur in cur loop
v_posTime := rur.posTime;
v_lon := rur.lon;
v_lat := rur.lat;
v_UintName := rur.UintName; SELECT dbms_random.value(1400,8888) INTO v_rand_line FROM dual;
FOR i in 1 .. v_rand_line LOOP
SELECT dbms_random.value(1,4) INTO v_lon_rand FROM DUAL;
SELECT dbms_random.value(1,3) INTO v_lat_rand FROM DUAL;
v_lon_new := to_number(substr(v_lon,5,2)) + v_lon_rand;
v_lat_new := to_number(substr(v_lat,4,2)) + v_lon_rand; v_posTime := v_posTime + 1/86400; if v_lon_new >= 100 then
v_lon := to_char(to_number(substr(v_lon,1,3))+1)||':'||lpad(to_char(mod(v_lon_new,100)),2,'0')||'E';
else
v_lon := to_char(to_number(substr(v_lon,1,3)))||':'||lpad(to_char(v_lon_new),2,'0')||'E';
end if; if v_lat_new >= 100 then
v_lat := to_char(to_number(substr(v_lat,1,2))+1)||':'||lpad(to_char(mod(v_lat_new,100)),2,'0')||'N';
else
v_lat := to_char(to_number(substr(v_lon,1,2)))||':'||lpad(to_char(v_lat_new),2,'0')||'N';
end if; INSERT INTO VelTrace(posTime,lon,lat,UintName) VALUES(v_posTime,v_lon,v_lat,v_UintName); END LOOP;
END LOOP;
COMMIT;
END;
/
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select * from (
select * from VelTrace order by UintName, posTime ) t
where rownum<20;POSTIME LON LAT UINTNAME
------------------- ---------------------------------------- ---------------------------------------- ---------------------
2007-11-03 13:05:52 116:28E 39:54N 京B3662
2007-11-03 13:05:53 116:30E 11:56N 京B3662
2007-11-03 13:05:54 116:34E 11:60N 京B3662
2007-11-03 13:05:55 116:37E 11:63N 京B3662
2007-11-03 13:05:56 116:41E 11:67N 京B3662
2007-11-03 13:05:57 116:44E 11:70N 京B3662
2007-11-03 13:05:58 116:46E 11:72N 京B3662
2007-11-03 13:05:59 116:50E 11:76N 京B3662
2007-11-03 13:06:00 116:51E 11:77N 京B3662
2007-11-03 13:06:01 116:55E 11:81N 京B3662
2007-11-03 13:06:02 116:58E 11:84N 京B3662
2007-11-03 13:06:03 116:61E 11:87N 京B3662
2007-11-03 13:06:04 116:64E 11:90N 京B3662
2007-11-03 13:06:05 116:65E 11:91N 京B3662
2007-11-03 13:06:06 116:67E 11:93N 京B3662
2007-11-03 13:06:07 116:69E 11:95N 京B3662
2007-11-03 13:06:08 116:71E 11:97N 京B3662
2007-11-03 13:06:09 116:73E 11:99N 京B3662
2007-11-03 13:06:10 116:74E 12:00N 京B3662已选择19行。
from VelTrace t1
where exists (select t2.uintName, min(t2.postime) as postime
from VelTrace t2
where t2.uintName=t1.uintName
group by t2.uintName
having mod((t1.postime-min(t2.postime))*24*60*60,600)<0.5 -- 由于微秒字段可能有些许误差
-- (此处若用=0，等式左边必须用trunc()函数将其变成整型，才正确)
-- 为提高运行效率，尽量少用函数，所以用<0.5)
)
order by t1.uintname, t1.posTime;-- 其实最好用存储过程，用临时表：将每个车牌号的起始时间存储到临时表，再去链接查询，其效率应该高一些!
POSTIME LON LAT UINTNAME
------------------- ---------------------------------------- ---------------------------------------- ------------------------
2007-11-03 13:05:52 116:28E 39:54N 京B3662
2007-11-03 13:15:52 131:45E 13:71N 京B3662
2007-11-03 13:25:52 146:68E 14:94N 京B3662
2007-11-03 13:35:52 161:44E 16:70N 京B3662
2007-11-03 13:45:52 176:66E 17:92N 京B3662
2007-11-03 13:55:52 191:60E 19:86N 京B3662
2007-11-03 14:05:52 206:73E 20:99N 京B3662
2007-11-03 14:15:52 221:86E 22:12N 京B3662
2008-01-24 10:04:02 117:10E 39:10N 津NB888
2008-01-24 10:14:02 132:25E 13:25N 津NB888
2008-01-24 10:24:02 147:01E 14:01N 津NB888
2008-01-24 10:34:02 162:60E 16:60N 津NB888
2008-01-24 10:44:02 177:70E 17:70N 津NB888
2008-01-24 10:54:02 192:74E 19:74N 津NB888
2008-01-24 11:04:02 207:56E 20:56N 津NB888
2008-01-24 11:14:02 222:14E 22:14N 津NB888
2009-03-09 08:56:59 112:55E 28:12N 湘G0054
2009-03-09 09:06:59 127:42E 12:99N 湘G0054
2009-03-09 09:16:59 142:47E 14:04N 湘G0054
2009-03-09 09:26:59 157:17E 15:74N 湘G0054
2009-03-09 09:36:59 172:15E 17:72N 湘G0054
2009-03-09 09:46:59 187:19E 18:76N 湘G0054
2009-03-09 09:56:59 201:80E 20:37N 湘G0054
2009-03-09 10:06:59 217:05E 21:62N 湘G0054
2009-03-09 10:26:59 246:81E 24:38N 湘G0054
2009-03-09 10:36:59 261:73E 26:30N 湘G0054
2009-03-09 10:46:59 276:88E 27:45N 湘G0054
2009-03-09 10:56:59 291:91E 29:48N 湘G0054
2009-03-09 11:06:59 307:10E 30:67N 湘G0054
2009-03-09 11:16:59 322:11E 32:68N 湘G0054已选择30行。
create table test1 --建立测试表
(
tname varchar2(10),
tclass varchar2(10),
tscore int
);insert into test1 select '无念','一年级','98' from dual union all --插入测试数据
select '无缘','二年级','99' from dual union all
select '无尘','三年级','95' from dual union all
select '无法','四年级','59' from dual;
commit;
create or replace package tpk --包头
is
type tcur is ref cursor; --定义返回的结果集
procedure t_p(t_name varchar2 , t_cur out tcur);--定义存储过程
end;
/create or replace package body tpk --包体
is
procedure t_p(t_name varchar2 , t_cur out tcur)--存储过程的实现
is
begin
open t_cur for select * from test1 where test1.tname =t_name;--游标记录结果集
end t_p;
end tpk;
/--在PL/SQL DEVELOPER里面测试一下吧，存储过程是在包体中的 调用存储过程要写包体的名称，例如tpk.t_p如此返回结果集的例子，可以参考我写的这个简单例子吧，更换一下你的SQL应该无什么问题
额。。我不知道弄图片呀汗。
对于lzf616的答案。。是不能有where的。。呵呵。
不能有WHERE，开玩笑吧。不明白你的不能有WHERE是什么意思，汗
要是简单就查出来前6个
你直接把你的查询语句放在过程里面
CREATE OR REPLACE PROCEDURE emp_max_wage_proc(
i_lines NUMBER, -- 你要返回的记录行数
o_cur OUT SYS_REFCURSOR
)
IS
BEGIN
open o_cur for
select * from (
select * from employee order by wage desc ) t
wher rownum <=6;
END;~~~~~~~~~~~~~~~~~
o_cur OUT SYS_REFCURSOR
)
IS
BEGIN
open o_cur for
select * from (
select * from employee order by wage desc ) t
wher rownum <=6;
END;
存储过程是给谁用的？web程序还是什么？
如果是web程序，做界面的人员知道怎么调用的