From: | alla(at)sergey(dot)com (Alla) |
---|---|
To: | pgsql-general(at)postgresql(dot)org(dot)pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Return cursor |
Date: | 2001-05-24 12:27:27 |
Message-ID: | 9275d56e.0105240427.2fa28c31@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
Alexander Dederer <dederer(at)spb(dot)cityline(dot)ru> wrote in message news:<9ehged$k4f$1(at)news(dot)tht(dot)net>...
> Can you send PL/SQL code and back-end code used this PL/SQL code?
> Myself trubles with CURSOR I resolve use LIMIT ... OFFSET ...
It would look something like this:
create or replace package my_package
AS
type gc_cursor is ref cursor;
procedure load_users
(pp_user_base out gc_cursor);
end my_package;
/
create or replace package body my_package
as
procedure load_users
(pp_user_base out gc_cursor)
as
begin
open pp_user_base for
select column1, column2, column3
from my_table
order by 1;
exception
when others then
raise_application_error(-20100, 'Error while trying to load user base ' ||
sqlerrm);
end load_users;
end my_package;
/
C program would look something like this:
void
load_user_base()
{
EXEC SQL BEGIN DECLARE SECTION;
sql_cursor cUserBase;
EXEC SQL END DECLARE SECTION;
struct USER_PROFILE {
.......
} user_profile[ARRAY_LENGTH];
struct USER_PROFILE_IND {
.......
} user_profile_ind[ARRAY_LENGTH];
EXEC SQL ALLOCATE :cUserBase; /* allocate the cursor variable */
EXEC SQL EXECUTE
BEGIN
my_package.load_users(:cUserBase);
END;
END-EXEC;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind;
if (sqlca.sqlcode != 0) {
fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc);
break;
}
}
........
........
Thanks for your response
Alla
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Clift | 2001-05-24 12:27:46 | [Fwd: info] |
Previous Message | Tom Lane | 2001-05-24 12:14:00 | Re: Estimating costs (was Functional Indices) |
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey E. Volkov | 2001-05-24 12:50:40 | Re: Return cursor |
Previous Message | Chris Ruprecht | 2001-05-24 12:08:29 | Stored Procedures? |