Introduction to PL/SQL

Speaker Notes

Slide 1:

This presentation is an introduction to PL/SQL.

Slide 2:

The PL/SQL language allows IF statements, loops, procedures and functions, variables and types as well as the traditional SQL code. This gives tremendous power to the processing capabilities available to the programmer.

Slide 3:

EDIT try_this looks for a procedure called try_this. SInce none exists, the developer will be asked if they want to create a new procedure of that name. I answered yes and then entered the code shown above.

When done I saved and exited. The code was saved as try_this.

I then used @ to execute the code.

Since the code simply stored words in a variable called v_msg, I see no results of the execution. The successful message does indicate that the msg was stored successfully.

Slide 4:

In this example I am going to show the message on the screen so that you can see that it worked. To do this I need to use set serveroutput on and then use the DBMS_OUTPUT.PUT_LINE to show the results. Note that at the end of the routine I set the serveroutput off to return things to normal.

Notice also that the set statements are part of the procedure so set serveroutput on and off is done automatically.

Slide 5:

Now I am going to go back to SQL for a minute and declare the variable at the sql prompt. Then I am going to assign a value to the variable within the PL/SQL code.

Why am I doing this - simply to show you a variety of ways you can do things!

All the work can be done in the procedure OR other processing sequences can be executed such as declaring the variables at the SQL prompt, processing in the procedure and show the results back at the SQL prompt.

The developer can choose what work should be done at the SQL prompt and what work should be done in the PL/SQL procedure. Some of the code such as IF statements and loops has to be done in the PL/SQL procedure because it is only supported by PL/SQL.

Slide 6:

The PL/SQL code in this example does nothing you could not have done using the SQL you have learned instead. However, as we introduce PL/SQL the examples are simple.

Note that if you leave leave a line with a space on it as the last line of code in the editor you avoid the famous input truncated statement. A harmless but annoying statement!

Note that the SELECT has to be set up to retrieve only one record. The logic is very different if multiple records will be retrieved.

Slide 7:

In the previous example, I used the type such as varchar2 (20) and char(2) by checking the desc of the table and using the same code.

In this example I am letting Oracle do the checking and use the type that it finds in the first_pay table. The advantage of this is that it makes easier code for me but more importantly if means that if the type gets changed in the table it will stay compatible with the procedure.

Slide 8:

This shows the code that finds a record based on user input. The user input is prompted at the SQL prompt. The data entered is used to retrieve a record/row. The information from that row is transfered to the variables. Then the results are displayed using the DBMS_OUTPUT.PUT_LINE command.

This example is shown again on the next slide.

Slide 9:

This shows the code from the previous slide.

Slide 10:

In this example, I am accomplishing the same things that I did in the previous two slides, however, this time I am not establishing a variable to hold the user input. I am having the user key it in as part of the SELECT and I am not saving it as a variable in the DECLARE.

Slide 11:

This example uses the ACCEPT to prompt the user for input and then retrieves the record that is requested.

Slide 12:

Again remember that the code in a block performs a logical task. It is possible to nest one block within another. We will revisit this again when we do more advanced code.

Note. v_general is defined in the outer block but it is available in the inner block as well.

Slide 13:

In this example, I changed v_general in the inner block. Note that the change is there when I display the contents of v_general after the inner block is complete.

Slide 14:

In this example, I attempted to show the contents of v_inner_only in the outer block. Note that the variable v_inner_only is unknown to the outer block and therefore an error happens.

Because v_inner_only was defined locally in the inner block, the outer block was unaware of it. Because v_general was defined in the outer, the inner was aware. Things defined in the outer are universally available to the inner and the outer blocks.

Slide 15:

In this example, I defined a field to receive data in the outer block (v_passed_to). In the inner block, I passed the data defined in v_inner_only to v_passed_to. I can then display the contents of v_passed to in both the inner and outer block.

Slide 16:

Good programming always means documenting the code for yourself and others.

To write a comment on a line use -- followed by the comment.

To write multiple lines of comments start with /* and end with */. Both techniques are illustrated in nest4 (explained on the previous slide).