Oracle SQL users (PL/SQL question) > General Discussion

Posted: 11/16/2004 5:51:48 AM EDT

I want to take a table I have, read it in a row at a time, and change/update the values in some of the fields in each row using std programming logic (loops, if then, declaring variables, etc).

I haven't used any of the pl/sql crap before, just the normal query stuff (select, create tables, decode, etc.). I could do what I need using the normal query tools, but it's getting to be a pain in the ass.

I want to see if the pl/sql tools can make it easier,

Suppose I have a table with lots of rows and columns A,B,C,D etc.

Is it possible to do somethign like

grab a row,

create a dummy variable
set dummy = 0

do a loop while dummy is less than the value in column B of the row
dummy = dummy + 1
end the loop

column C = dummy

if column D > column C then column D = .223

get the next row

etc.

The result being that after I run the code, the table I started with has been altered with the calculations I do in the code being applied to each row.

The only book I have is "oracle the complete reference" book from oracle. It has a couple of small sections on pl/sql, but it doesn't have many examples of using pl/sql to do things. Are things like what I describe above possible? If so, can you give me some example code?

Posted: 11/16/2004 5:54:04 AM EDT

[#1]

I have that same PL/SQL written for work that I do. Send me your email and I'll shoot it over.

Posted: 11/16/2004 5:54:42 AM EDT

[#2]

It's been forever since I've programmed in PL/SQL. What you're looking for is a "cursor". If someone hasn't responded with the answer by the time I get home, I'll post a simple sample.

ETA: Here's a simple sample -- www.fluffycat.com/sql/sql-cursor-oracle.html

google for "pl/sql cursor" for more

Posted: 11/16/2004 5:55:44 AM EDT

[#3]

Quoted:
I have that same PL/SQL written for work that I do. Send me your email and I'll shoot it over.

IM sent

Posted: 11/16/2004 6:07:10 AM EDT

[#4]

email sent

Posted: 11/16/2004 6:08:27 AM EDT

[#5]

Quoted:
I want to take a table I have, read it in a row at a time, and change/update the values in some of the fields in each row using std programming logic (loops, if then, declaring variables, etc).

I haven't used any of the pl/sql crap before, just the normal query stuff (select, create tables, decode, etc.). I could do what I need using the normal query tools, but it's getting to be a pain in the ass.

I want to see if the pl/sql tools can make it easier,

Suppose I have a table with lots of rows and columns A,B,C,D etc.

Is it possible to do somethign like

grab a row,

create a dummy variable
set dummy = 0

do a loop while dummy is less than the value in column B of the row
dummy = dummy + 1
end the loop

column C = dummy

if column D > column C then column D = .223

get the next row

etc.

The result being that after I run the code, the table I started with has been altered with the calculations I do in the code being applied to each row.

The only book I have is "oracle the complete reference" book from oracle. It has a couple of small sections on pl/sql, but it doesn't have many examples of using pl/sql to do things. Are things like what I describe above possible? If so, can you give me some example code?

Posted: 11/16/2004 6:18:19 AM EDT

[#6]

Here is an example of a TSQL
cursor query (you'd have to
port it to PL/SQL):

CREATE PROC dbo.sp_get_pending_tasks
AS
SET NOCOUNT ON

CREATE TABLE #pending_tasks
(
task_schedule_id bigint NOT NULL,
task_id bigint NOT NULL,
task_config_id bigint NOT NULL,
task_run_id bigint NULL,
event_type varchar(32) NOT NULL,
task_object varchar(128) NOT NULL,
max_retry int NOT NULL
)

DECLARE @task_schedule_id bigint
DECLARE @task_id bigint
DECLARE @task_config_id bigint
DECLARE @task_run_id bigint
DECLARE @schedule_type int
DECLARE @schedule_value varchar(64)
DECLARE @event_type varchar(32)
DECLARE @task_object varchar(128)
DECLARE @max_retry int

DECLARE @task_state int

DECLARE ScheduleCursor CURSOR STATIC READ_ONLY FOR
SELECT ts.task_schedule_id,
ts.task_id,
ts.task_config_id,
ts.schedule_type,
ts.schedule_value,
tc.event_type,
tc.task_object,
tc.max_retry
FROM task_schedule ts, task_config tc
WHERE ts.task_config_id = tc.task_config_id
AND ts.enabled = 1
AND (ts.start_date IS NULL OR ts.start_date < getDate())
AND (ts.end_date IS NULL OR ts.end_date > getDate())
OPEN ScheduleCursor
FETCH NEXT
FROM ScheduleCursor
INTO @task_schedule_id,
@task_id,
@task_config_id,
@schedule_type,
@schedule_value,
@event_type,
@task_object,
@max_retry

WHILE @@FETCH_STATUS = 0
BEGIN

-----
-- Interval Schedule
-----
IF @schedule_type = 1
BEGIN
SELECT @task_run_id=task_run_id,
@task_state=state
FROM task_run
WHERE task_id = @task_id
AND task_schedule_id = @task_schedule_id
AND task_run_time > dateadd(mi, -CAST(@schedule_value AS integer), getDate())

-----
-- If no task_run entry exists or if the existing
-- task_run entry is in the 'Retry' state
-----
IF @task_run_id IS NULL OR @task_state = 2
BEGIN
INSERT INTO #pending_tasks (
task_schedule_id,
task_id,
task_config_id,
task_run_id,
event_type,
task_object,
max_retry
) VALUES (
@task_schedule_id,
@task_id,
@task_config_id,
@task_run_id,
@event_type,
@task_object,
@max_retry
)
END
END

FETCH NEXT
FROM ScheduleCursor
INTO @task_schedule_id,
@task_id,
@task_config_id,
@schedule_type,
@schedule_value,
@event_type,
@task_object,
@max_retry
END
CLOSE ScheduleCursor
DEALLOCATE ScheduleCursor

SELECT task_schedule_id,
task_id,
task_config_id,
task_run_id,
event_type,
task_object,
max_retry
FROM #pending_tasks

DROP TABLE #pending_tasks

GO

HTH...

Posted: 11/16/2004 8:00:00 AM EDT

[#7]

Quoted:
Here is an example of a TSQL
cursor query (you'd have to
port it to PL/SQL):

(snip)

HTH...

I avoided doing that myself because T-SQL & PL/SQL are very different syntactically when it comes to cursors. FYI, there's very little that ports *easily* between the two.

Warning

Confirm Action

[ARCHIVED THREAD] - Oracle SQL users (PL/SQL question)

[ARCHIVED THREAD] - Oracle SQL users (PL/SQL question)