PROCEDURE show_nb_cursor (io_cursor OUT t_cursor)
AS
BEGIN
OPEN io_cursor FOR
SELECT product_id,product_NAME,OUT_TIME,BACK_TIME,USER_ID,USER_TEL,A.IMAGE
FROM m_nblog,(SELECT IMAGE
FROM m_notebook
WHERE product_id = m_nblog.product_id) A;
WHERE product_id IN (SELECT product_id
FROM m_notebook
WHERE product_FLG = 1);
END show_nb_cursor;
AS
BEGIN
OPEN io_cursor FOR
SELECT product_id,product_NAME,OUT_TIME,BACK_TIME,USER_ID,USER_TEL,A.IMAGE
FROM m_nblog,(SELECT IMAGE
FROM m_notebook
WHERE product_id = m_nblog.product_id) A;
WHERE product_id IN (SELECT product_id
FROM m_notebook
WHERE product_FLG = 1);
END show_nb_cursor;
需要使用ref cursor, 你应该先定义一个type.
create or replace package types
as type rc is ref cursor;
end; 2. 调用的时候也需要用refcursor
variable c refcursor
exec show_nb_cursor(:c);
print c3. 存储过程中最好加上错误处理部分.
AS
BEGIN
OPEN io_cursor FOR
select product_id,product_name, max(out_time) as out_time , back_time, user_id, user_tel,image from
(
SELECT m_nblog.product_id, a.product_name, out_time, back_time,
user_id, user_tel, a.image
FROM m_nblog,
(SELECT image, product_name
FROM m_notebook, m_nblog
WHERE m_notebook.product_id = m_nblog.product_id) a
WHERE product_id IN (SELECT product_id
FROM m_notebook
WHERE product_flg = 1));
group by product_id,product_name, out_time, back_time, user_id,user_tel,image;
END show_nb_cursor;
END pr_nb;
上面一段抽出来有重复,自己加了一个MAX,GROUP BY的时候总是有问题?
应该是这样:
group by product_id,product_name, back_time,user_id,user_tel,image