A:表内数据
ID(int) NUM(int) price(int)
ma0101 4 10
ma0101 5 13
ma0101 7 14
cz0403 4 10
cz0403 5 13
cz0403 7 14
dg0909 1 13
dg0909 2 15
dg0909 3 17
dg0909 10 45
B表
ID num1 price1
ma0101 1 1
cz0403 2 2
dg0909 1 2
存储过程执行完后
ID num1 price1 num2 price2 num3 price3 num4 price4 num5 price5
ma0101 1 1 4 10 5 13 7 14
cz0403 2 2 4 10 5 13 7 14
dg0909 1 2 1 13 2 15 3 17 10 45实在没有办法 才来发贴求助的
希望有高人能帮下忙!
十分感谢!
ID(int) NUM(int) price(int)
ma0101 4 10
ma0101 5 13
ma0101 7 14
cz0403 4 10
cz0403 5 13
cz0403 7 14
dg0909 1 13
dg0909 2 15
dg0909 3 17
dg0909 10 45
B表
ID num1 price1
ma0101 1 1
cz0403 2 2
dg0909 1 2
存储过程执行完后
ID num1 price1 num2 price2 num3 price3 num4 price4 num5 price5
ma0101 1 1 4 10 5 13 7 14
cz0403 2 2 4 10 5 13 7 14
dg0909 1 2 1 13 2 15 3 17 10 45实在没有办法 才来发贴求助的
希望有高人能帮下忙!
十分感谢!
固定列就用sum_decode
不固定列
参考此贴
http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html
--存储过程定义省略了
v_colnum NUMBER(2); --记录返回结果总共多少列
v_sql_qry VARCHAR2(2000); --最终查询语句
v_param VARCHAR2(60);
v_i VARCHAR2(3);
BEGIN
--取得返回结果总列数
SELECT MAX(t.counter)
INTO v_colnum
FROM (SELECT COUNT(B.ID) counter FROM B GROUP BY B.ID) t;
v_sql_qry := ' SELECT A.ID,A.NUM,A.PRICE ';
FOR i IN 1 .. v_colnum LOOP
v_i := TO_CHAR(i);
v_param := ' , PKG_AB.FUN_GETNUM(A.ID,' || v_i ||
'),PKG_AB.FUN_GETPRICE(A.ID,' || v_i || ') ';
v_sql_qry := v_sql_qry || v_param;
END LOOP;
v_sql_qry := v_sql_qry || ' FROM A ';
OPEN result_cur FOR v_sql_qry;
END proc_oper; --一个函数,从B表中取得相应的NUM
FUNCTION FUN_GETNUM(p_i_id IN B.ID%TYPE, p_i_rownum IN NUMBER)
RETURN NUMBER IS
v_num NUMBER;
BEGIN
SELECT B2.num
INTO v_num
FROM (SELECT B1.num, ROWNUM row1
FROM (SELECT B.num FROM B WHERE B.id = p_i_id) B1) B2
WHERE B2.row1 = p_i_rownum;
RETURN v_num;
END FUN_GETNUM; --一个函数,从B表中取得相应的PRICE
FUNCTION FUN_GETPRICE(p_i_id IN B.ID%TYPE, p_i_rownum IN NUMBER)
RETURN NUMBER IS
v_price NUMBER;
BEGIN
SELECT B2.price
INTO v_price
FROM (SELECT B1.price, ROWNUM row1
FROM (SELECT B.price FROM B WHERE B.id = p_i_id) B1) B2
WHERE B2.row1 = p_i_rownum;
RETURN v_price;
END FUN_GETPRICE;end PKG_AB;
TYPE type_cur IS REF CURSOR; PROCEDURE proc_oper(result_cur OUT type_cur); FUNCTION FUN_GETNUM(p_i_id IN B.ID%TYPE, p_i_rownum IN NUMBER)
RETURN NUMBER;
FUNCTION FUN_GETPRICE(p_i_id IN B.ID%TYPE, p_i_rownum IN NUMBER)
RETURN NUMBER;end PKG_AB;包头也来了,哈哈
SQL> execute PKG_AB.proc_oper(:c_cur);
BEGIN PKG_AB.proc_oper(:c_cur); END; *
第 1 行出现错误:
ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 PKG_AB 无效
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored执行的时候也报错呀!