select e.employee_id into id_ww_762 from employees e
where e.salary in
(
select max(d.salary) from employees d group by d.department_id
having e.department_id=d.department_id
);
这是从一个表中读取各部门工资最高的员工的ID。结果是十多个ID号。我想把ID号传到id_ww_762。如上写语句行吗?id_ww_762里面也是十多个ID号吗? 刚刚接受ORACLE,不熟,好心人帮忙看一下。
where e.salary in
(
select max(d.salary) from employees d group by d.department_id
having e.department_id=d.department_id
);
这是从一个表中读取各部门工资最高的员工的ID。结果是十多个ID号。我想把ID号传到id_ww_762。如上写语句行吗?id_ww_762里面也是十多个ID号吗? 刚刚接受ORACLE,不熟,好心人帮忙看一下。
TYPE ints IS TABLE OF int INDEX BY BINARY_INTEGER;
id_ww_762 ints;begin select e.employee_id BULK COLLECT INTO id_ww_762 from employees e
where e.salary in
(
select max(d.salary) from employees d group by d.department_id
having e.department_id=d.department_id
); --打印出来
FOR i IN 1..id_ww_762.COUNT
LOOP
dbms_output.put_line(id_ww_762(i));
END LOOP;end;
FOR id_ww_762 IN
(select e.employee_id into from employees e
where e.salary in
(
select max(d.salary) from employees d group by d.department_id
having e.department_id=d.department_id
))
LOOP
//每次取一个值
/*你的代码*/
dbms_output.put_line(id_ww_762.employee_id);
END LOOP;2、简单循环
DECLARE
v_employee_id employees.employee_id%TYPE; CURSOR id_ww_762 IS
select e.employee_id into from employees e
where e.salary in
(
select max(d.salary) from employees d group by d.department_id
having e.department_id=d.department_id
);
BEGIN
OPEN id_ww_762;
FETCH id_ww_762 INTO v_employee_id; LOOP
EXIT WHEN id_ww_762%NOTFOUND; /*你的代码*/
dbms_output.put_line(v_employee_id);
FETCH id_ww_762 INTO v_employee_id;
END LOOP CLOSE id_ww_762;
END;
写的不错,用table类型或者用array都可以实现你的要求。
------- 测试表
CREATE TABLE TEST_T
(
BEGIN_DATE DATE,
END_DATE DATE
);
------ 插入测试数据
BEGIN
DELETE FROM TEST_T;
COMMIT;
FOR V_I IN 1 .. 20
LOOP
INSERT INTO TEST_T
VALUES
(TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10)),
TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10)));
END LOOP;
COMMIT;
END;--------方法一:FOR循环
BEGIN
FOR ID_WW_762 IN (SELECT T.BEGIN_DATE
FROM TEST_T T)
LOOP
/*你的代码*/
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ID_WW_762.BEGIN_DATE,
'yyyymmdd'));
END LOOP;
END;---------方法二:简单循环
DECLARE
V_BEGIN_DATE TEST_T.BEGIN_DATE%TYPE; CURSOR ID_WW_762 IS
SELECT T.BEGIN_DATE
FROM TEST_T T;
BEGIN
OPEN ID_WW_762;
LOOP
FETCH ID_WW_762
INTO V_BEGIN_DATE;
EXIT WHEN ID_WW_762%NOTFOUND;
/*你的代码*/
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_BEGIN_DATE,
'yyyymmdd'));
END LOOP;
CLOSE ID_WW_762;
END;
-----
DROP TABLE TEST_T;