Dani's IT Blog

…moving from Oracle to Software Development

PL/SQL Basics for the DBA. Part III (… or how to start having fun working as a DBA)

leave a comment »

In the last two posts I showed you some basics of SQL and PL/SQL. We can now combine this knowhow to get a powerful tool for DBAs. Maybe you want to change multiple objects in a scripts, without copy&paste output nor writing files. To achieve this, we need CURSOR, LOOP and EXECUTE IMMEDIATE altogether combined in PL/SQL. Again three new things to learn, seems like we will never finish this task.

The simplest way to build a loop and read a cursor is by using an implicit cursor. This could look like this.

BEGIN
FOR object IN (SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10) LOOP dbms_output.put_line('object '||object.owner||'.'||object.object_name||' is of type '||object.object_type); END LOOP; END; / [/sourcecode] This loop works quite like an iterator in Java, Perl and other languages. But what happend to our cursor, where is it? Oracle opens an implicit cursor for you, when you use this FOR - LOOP notation. With an explicit cursor the, the same code would look like this. [sourcecode language='sql'] DECLARE CURSOR cObject IS SELECT object_name, owner, object_type FROM dba_objects WHERE rownum <= 10; vOwner dba_objects.owner%TYPE; vObjectName dba_objects.object_name%TYPE; vObjectType dba_objects.object_type%TYPE; BEGIN OPEN cObject; LOOP FETCH cObject INTO vObjectName, vOwner, vObjectType; EXIT WHEN cObject%NOTFOUND; dbms_output.put_line('object '||vOwner||'.'||vObjectName||' is of type '||vObjectType); END LOOP; CLOSE cObject; END; / [/sourcecode] As we see, with an explicit cursor we have additional stuff to code. In the end this means, use implicit cursor where ever possible. I will show you some examples where you need explicit cursors, and even examples where you need a more detailed and complex notation, with which you are able to explicitly parse, fetch and so forth.

Advertisements

Written by danirey

June 16, 2009 at 18:55

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: