DECLARE TYPE INFO IS RECORD( BNAME BOOK.BOOK_NAME%TYPE, WRITER BOOK.WRITER%TYPE, PRICE BOOK.PRICE%TYPE); RES INFO; CURSOR C_INFO(P IN NUMBER DEFAULT 30) IS SELECT DISTINCT BOOK_NAME, WRITER, PRICE FROM BOOK WHERE PRICE > P; BEGIN OPEN C_INFO(35); LOOP
FETCH C_INFO INTO RES; DBMS_OUTPUT.PUT('ÊéÃû: ' || RES.BNAME||' '); DBMS_OUTPUT.PUT('×÷Õß: ' || RES.WRITER||' '); DBMS_OUTPUT.PUT_LINE('¼Û¸ñ: ' || RES.PRICE||' '); EXIT WHEN C_INFO%NOTFOUND; END LOOP; CLOSE C_INFO; END; 供参考
--创建类型create or replace package mytest is -- Author : ADMINISTRATOR -- Created : 2008-5-13 11:19:28 -- Purpose : test
TYPE record_type IS RECORD( code VARCHAR2(18), p_name VARCHAR2(16));TYPE ref_cur_type IS REF CURSOR; end mytest;CREATE OR REPLACE TYPE table_type IS TABLE OF record_type;--创建过程CREATE OR REPLACE PROCEDURE testproc( v_ref_cur out mytest.ref_cur_type) IS v_record record_type; v_table table_type := table_type();BEGIN FOR i IN 1 .. 9 LOOP v_record := NEW record_type(''45212319830810435'' || i, ''侯廷文'' || i); v_table.EXTEND; v_table(i) := v_record; END LOOP; OPEN v_ref_cur FOR SELECT * FROM TABLE(CAST(v_table AS table_type));END testproc;
对,就是要返回游标这是我写的存储过程和调用: create or replace procedure pro_orders(customID in varchar2,res_cursor out sys_refcursor) as v_customid sot.orders.customid%type; begin v_customid:= customID; dbms_output.put_line(v_customid); open res_cursor for select * from sot.orders t where t.customid = customID; end; ---------------------------------------------------- declare cur_order sys_refcursor; customID sot.orders.customid%type; goodsid sot.orders.goodsid%type; goodsnum sot.orders.goodsnum%type; buyprice sot.orders.buyprice%type; buydate sot.orders.buydate%type; orderid sot.orders.orderid%type; begin customID:=302; pro_orders(customID,cur_order); LOOP FETCH cur_order INTO customID,goodsid,goodsnum,buyprice,buydate,orderid; EXIT WHEN cur_order%NOTFOUND; dbms_output.put_line('顾客号:'||customid||',商品号:'||goodsid||',购买数量:'||goodsnum||',购买价格' ||buyprice||',购买时间'||to_char(buydate,'yyyy-mm-dd')||',订单号'||orderid); END LOOP; CLOSE cur_order;end;为什么存储过程的查询条件不起作用呢?我想查customID:=302的那些记录,结果所有记录都出现了
open res_cursor for select * from sot.orders t where t.customid = customID /*问题在这里,因为sql中是不区分大小写的,这里你的变量名customID和customid 其实是一样的,这样判断就如 where 字段名=字段名 。结果总是为真,就会返回所有记录。 你可以把比昂定义为var_customid */ open res_cursor for select * from sot.orders t where t.customid = var_customid ; 这样就没问题了。
TYPE INFO IS RECORD(
BNAME BOOK.BOOK_NAME%TYPE,
WRITER BOOK.WRITER%TYPE,
PRICE BOOK.PRICE%TYPE);
RES INFO;
CURSOR C_INFO(P IN NUMBER DEFAULT 30) IS
SELECT DISTINCT BOOK_NAME, WRITER, PRICE FROM BOOK WHERE PRICE > P;
BEGIN
OPEN C_INFO(35);
LOOP
FETCH C_INFO
INTO RES;
DBMS_OUTPUT.PUT('ÊéÃû: ' || RES.BNAME||' ');
DBMS_OUTPUT.PUT('×÷Õß: ' || RES.WRITER||' ');
DBMS_OUTPUT.PUT_LINE('¼Û¸ñ: ' || RES.PRICE||' ');
EXIT WHEN C_INFO%NOTFOUND;
END LOOP;
CLOSE C_INFO;
END;
供参考
我想的是,在一个存储过程中创建游标,然后这个存储过程有out的参数,out的就是查询的每条记录,然后调用这个存储过程就能过的返回的参数,,,请问这样该怎么做?是得返回数组吗?
然后在写相关的procedure时候,定义你的参数为你自定义的复合变量类型。
这样就可以存储游标的返回值了。
-- Created : 2008-5-13 11:19:28
-- Purpose : test
TYPE record_type IS RECORD(
code VARCHAR2(18),
p_name VARCHAR2(16));TYPE ref_cur_type IS REF CURSOR;
end mytest;CREATE OR REPLACE TYPE table_type IS TABLE OF record_type;--创建过程CREATE OR REPLACE PROCEDURE testproc( v_ref_cur out mytest.ref_cur_type) IS v_record record_type; v_table table_type := table_type();BEGIN
FOR i IN 1 .. 9 LOOP
v_record := NEW record_type(''45212319830810435'' || i, ''侯廷文'' || i);
v_table.EXTEND;
v_table(i) := v_record;
END LOOP; OPEN v_ref_cur FOR
SELECT * FROM TABLE(CAST(v_table AS table_type));END testproc;
create or replace procedure pro_orders(customID in varchar2,res_cursor out sys_refcursor)
as
v_customid sot.orders.customid%type;
begin
v_customid:= customID;
dbms_output.put_line(v_customid);
open res_cursor for select * from sot.orders t where t.customid = customID;
end;
----------------------------------------------------
declare
cur_order sys_refcursor;
customID sot.orders.customid%type;
goodsid sot.orders.goodsid%type;
goodsnum sot.orders.goodsnum%type;
buyprice sot.orders.buyprice%type;
buydate sot.orders.buydate%type;
orderid sot.orders.orderid%type;
begin
customID:=302;
pro_orders(customID,cur_order);
LOOP
FETCH cur_order
INTO customID,goodsid,goodsnum,buyprice,buydate,orderid;
EXIT WHEN cur_order%NOTFOUND;
dbms_output.put_line('顾客号:'||customid||',商品号:'||goodsid||',购买数量:'||goodsnum||',购买价格'
||buyprice||',购买时间'||to_char(buydate,'yyyy-mm-dd')||',订单号'||orderid);
END LOOP;
CLOSE cur_order;end;为什么存储过程的查询条件不起作用呢?我想查customID:=302的那些记录,结果所有记录都出现了
/*问题在这里,因为sql中是不区分大小写的,这里你的变量名customID和customid 其实是一样的,这样判断就如 where 字段名=字段名 。结果总是为真,就会返回所有记录。
你可以把比昂定义为var_customid */
open res_cursor for select * from sot.orders t where t.customid = var_customid ;
这样就没问题了。