REF CURSOR to print output from given input [message #540954] |
Thu, 26 January 2012 13:24 |
|
ora_nov
Messages: 35 Registered: January 2012
|
Member |
|
|
create table a_user
(
u_name varchar2(30)
, grp varchar2(30)
)
;
INSERT INTO a_user VALUES ('abc', 'new');
INSERT INTO a_user VALUES ('def', 'new');
INSERT INTO a_user VALUES ('ref', 'old');
INSERT INTO a_user VALUES ('ven', 'old');
create table a_grp_count
(
grp varchar(30)
, u_count NUMBER
)
;
INSERT INTO a_grp_count VALUES ('new', 100);
INSERT INTO a_grp_count VALUES ('old', 200);
INSERT INTO a_grp_count VALUES ('old', 100);
INSERT INTO a_grp_count VALUES ('old', 400);
INSERT INTO a_grp_count VALUES ('old', 300);
INSERT INTO a_grp_count VALUES ('new', 600);
INSERT INTO a_grp_count VALUES ('new', 200);
INSERT INTO a_grp_count VALUES ('new', 0);
INSERT INTO a_grp_count VALUES ('new', 50);
sql:
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
grp AS grp
, sum(u_count) AS g_tot
FROM
a_grp_count
WHERE
u_count > 0
group by grp
) a
WHERE b.grp = getgrpnam(p_u_name)
;
getgrpname is a function to get grp when I input the u_name
Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:
create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
grp AS grp
, sum(u_count) AS g_tot
FROM
a_grp_count
WHERE
u_count > 0
group by grp
) a
WHERE b.grp = getgrpnam(p_u_name)
;
END get_u_name_data;
Can you guys help me out how to print output in SQL developer
|
|
|
|
Re: REF CURSOR to print output from given input [message #541190 is a reply to message #540954] |
Sun, 29 January 2012 08:06 |
transfer
Messages: 53 Registered: August 2007 Location: transfer
|
Member |
|
|
This works for me in SQL Developer:VARIABLE RC REFCURSOR
EXEC GET_U_NAME_DATA('abc', :RC);
print :rc; However, I would not write the procedure that way. You are doing an extra "context switch" between SQL and PL/SQL by calling the function in the SELECT. I would prefer calling the function directly in PL/SQL, then using the value in the SQL.create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR)
IS
l_grp a_user.grp%type;
BEGIN
l_grp := getgrpnam(p_u_name);
OPEN rc_data FOR
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
grp AS grp
, sum(u_count) AS g_tot
FROM
a_grp_count
WHERE
u_count > 0
group by grp
) a
WHERE b.grp = l_grp;
END GET_U_NAME_DATA;
/ P.S. If getgrpnam() has logic that can be implemented in SQL, it would be far better to express the logic within the main SQL statement and forget the function.
P.P.S. In this case I suggest creating a function that returns the ref cursor, rather than a procedure with an OUT parameter.
[Updated on: Sun, 29 January 2012 14:47] Report message to a moderator
|
|
|