I've written my first table function and my first stored procedure. I think
I have a clear idea this is a lot different from Microsoft SQL Server. I
have a few things I can't seem to figure out that I hope some of you can
shed some light on.
- My procedure signature is CREATE OR REPLACE PROCEDURE FOO.BAR (IN
ROW_ID BIGINT DEFAULT 0). I can use table.ID=ROW_ID in my WHERE clauses in
the SELECT statements in my SQL just fine. However, I cannot pass it to a
tabular stored procedure that looks like this. What am I doing wrong:
DECLARE GLOBAL TEMPORARY TABLE BAR_LOAD AS (
SELECT * FROM TABLE(FOO.BARBYID(ROW_ID)) a
How do pass a parameter?
- I verified by experiment and the docs that global temporary tables are
global to the job (and therefore JDBC connection if I'm using SQL
workbench. Is there any way to scope a temporary tables to just inside a
stored procedure? If not, is there some keyword to make a stored procedure
an atomic transaction so I don't have to worry about two jobs calling the
same stored procedure at the same time and overwriting my stored procedure?
- I seem to gather that outside of a stored procedure variables are only
global to the database instance. Is there some way to declare a variable
that is only visible to a job?
Is there a section of the 7.2 manuals besides the SQL Statement reference,
or a redbook I should be reading that would tell me these things? Is there
a good crash course in writing stored procedures with all these gotchas for
someone coming from other RDBMSes?