17,377
社区成员
发帖
与我相关
我的任务
分享
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 ;
这样就没问题了。
这是我写的存储过程和调用:
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;
--创建类型
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;
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;