iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 01

“Hope is a waking dream.” Aristotle (384 BC - 322 BC)

 

Read first then play the video:

   PLS-VIDEO -PL/SQL Introduction
   PLS-VIDEO -Basic PL/SQL

  

Basic PL/SQL

 

Introduction

PL/SQL is a language that was provided by Oracle. Stored procedure is a collection of PL/SQL. Stored procedure is like a program module in Oracle. It is available for developers to code stored procedures that easily integrate with database objects via the SQL statements such as INSERT, UPDATE, DELETE, and SELECT. This language offers variable DECLARATION, LOOP, IF-THEN-ELSE-END IF, EXCEPTION an advanced error handling, cursor, and more. This tutorial will provide an overview of PL/SQL syntax, constructs, and usage. This language is not just used by developers. DBAs use this language to perform some of their task such as automatic online or offline backups. You will learn how to write a PL/SQL procedure to perform automatic hot or cold backup in the Backup and Recovery tutorial.

 

PL/SQL is a straightforward language. PL/SQL is very popular with its robust error handling and its interface with the database. It can be stored on the Oracle database (server side) or in a library that was created in the PC (client side). It interfaces with the Oracle database natively. As you can centralize your application modules by storing them in the server side on the database, or decentralize them by storing them on each client side. Remember that when you use decentralize structure, if you change a module then you need to apply your changes to all your clients. But on the other hand, you will increase the Oracle performance.

 

Now, let’s start and do some practical hands-on exercises.

Go to “MS-DOS.”

Change directory to the iself directory and login to “sqlplus” as "iself/schooling."

MS-DOS> cd ..

MS-DOS> sqlplus iself/schooling

 

PL/SQL Body

Write a PL/SQL block to use only the "body" section with PL/SQL statement. A BEGIN and END are keywords. You write the PL/SQL statement to be executed in the body. In this example, you tell PL/SQL do nothing for you (null);

 

SQL> BEGIN

              null;

           END;

/

Use the slash (/) to compile and run the block.

 

PL/SQL Declaration

The DECLARATION statement will be used to define program’s variables, cursor, type, etc.

Add the "declaration" section with no variables. Then compile and run the block. In this declaration, you have no variable or cursor defined.

 

SQL> declare

              -- no variable

           begin

              null;

           end;

/

 

SET SERVEROUTPUT

When you use the DBMS_OUTPUT.PUT_LINE procedure, the procedure will write the passing string into the Oracle buffer. In order to print the content of the Oracle buffer, you should use the SET SERVEROUTPUT command to display the content of the Oracle buffer into your screen. You can also increase the size of the buffer.

 

Use the “Set serveroutput on” to display the buffer used by dbms_output.

SQL> SET SERVEROUTPUT ON

 

DBMS_OUTPUPT.PUT_LINE procedure

Write a PL/SQL block, to output the "Hello iselfschooling" message.

 

SQL> BEGIN

              dbms_output.put_line('Hello iselfschooling');

           END;

/

 

Now, let's see how we define a variable in the DECLARATION section and write PL/SQL statements in the BODY section.

 

First, we take a look at the department columns. So, describe the department table.

SQL> DESC dept

 

Define Variables in PL/SQL

Write a PL/SQL block, to declare a department name variable with the same datatype of the department name. Then assign "HR" to the variable and output the variable.

 

SQL> DECLARE

              v_dname VARCHAR2(14);

           BEGIN

              v_dname := 'HR';

              dbms_output.put_line(v_dname);

           END;

/

Save the PL/SQL block

The following is one of the ways that you can save your stored procedure (PL/SQL program).

 

Save the PL/SQL block as “test_myblock.”

SQL> SAVE c:_myblock.sql

Then go to notepad and open the file from the “iself” directory.

 

%TYPE keyword

If you are using a variable in the stored procedure that deals with a table column, it should have the same datatype and length as the column itself. Instead of look it up, you use the %TYPE keyword. The advantage of doing that is: if a DBA changes the column datatype or length, you don’t have to modify your stored procedure.

The tablename.column%TYPE syntax means use the same datatype of such column in such table.

 

Use the %type keyword, to declare a variable as the same datatype and size of the department name column of the dept table. Then save the file.

(In notepad change)

DECLARE

        v_dname dept.dname%TYPE;

BEGIN

        v_dname := 'HR';

        dbms_output.put_line(v_dname);

END;

/

 

Get a PL/SQL block

By now, you have learned how to save your stored procedure. Now, let’s see how to can get it back.

Go to “SQLPLUS.” Get the file. And run it.

SQL> GET c:_myblock.sql

SQL> /

 

- OR -

Run a PL/SQL block

You can also use the START statement to run or execute a SQL script. Use the START statement to execute your SQL script.

SQL> START c:_myblock.sql

The same output!

 

%ROWTYPE keyword

The %ROWTYPE keyword creates a composite datatype in which all the columns of a row are pieced together into a record. For example: if I have in the test table three columns (c1, c2, and c3) and you need to put them in a variable, you should use %ROWTYPE (v_abc test%ROWTYPE). Now, the v_abc variable contains three columns (c1, c2, and c3). The following shows how this work.

The test.c1 corresponds with v_abc.c1.

The test.c2 corresponds with v_abc.c2.

The test.c3 corresponds with v_abc.c3.

Go back to “notepad,” use the %rowtype keyword to declare a variable row type.

Assign ‘HR’ to the “department name” item of the defined variable row. In this example: we know the dept table contains three columns (deptno, dname, and loc), therefore the v_drec variable contains three columns as well (deptno, dname, and loc). These variable’s datatype and length are the same as the dept table’s columns.

The following PL/SQL block will output the v_drec.dname value.

 

(in notepad change)

DECLARE

     v_drec dept%rowtype;

BEGIN

     v_drec.dname := 'HR';

     dbms_output.put_line(v_drec.dname);

END;

/

 

Then save the file. Go to “SQLPLUS.” Get the file. And run it.

SQL> GET c:_myblock.sql

SQL> /

The same output!

 

Implicit Cursor

If you define your cursor in the PL/SQL body, it will be called an implicit cursor. In the following example, you see how %ROWTYPE works.

 

Go back to “Notepad.” Use the implicit cursor to query the department table information where deptno = 30. Check if no record was found then print “Record was not found.” else print the department name only. The SQL%NOTFOUND reserved word returns the FALSE value if there are records to read from cursor and returns the TRUE value if there are not record exist to read from cursor.

 

(in notepad change)

declare

     v_drec dept%rowtype;

begin

     select deptno, dname, loc into

                v_drec.deptno,v_drec.dname, v_drec.loc

     from dept

     where deptno = 30;

 

     if sql%notfound then

          dbms_output.put_line('Record was not found.');

     else

          dbsm_output.put_line(v_drec.dname);

     end if;

end;

/

Then save the file. Go to “SQLPLUS.” Get the file. And run it.

SQL> GET c:_myblock.sql

SQL> /

 

Modify a PL/SQL block

Back to “Notepad” Modify the PL/SQL block to move the entire record into the variable row.

(in notepad change)

declare

     v_drec dept%rowtype;

begin

     select * into v_drec

          from dept

          where deptno = 30;

     if sql%notfound then

          dbms_output.put_line('Record was not found.');

     else

          dbsm_output.put_line(v_drec.dname);

     end if;

end;

/

 

Then save the file. Go to “SQLPLUS.” Get the file. And run it.

SQL> get c:_myblock.sql

SQL> /

The same result!

 

“A positive attitude may not solve all your problems, but it will annoy enough people to make it worth the effort.” Herm Albright (1876 - 1944)

 

Questions:

Q: What is PL/SQL?

Q: Where can you store a PL/SQL procedure?

Q: What is the PL/SQL body section?

Q: What is the PL/SQL declaration section?

Q: What does the SET SERVEROUTPUT command?

Q: What does the DBMS_OUTPUT.PUT_LINE procedure?

Q: How do you define a variable or variables in the PL/SQL declaration section?

Q: How do you save a PL/SQL block in the client environment?

Q: How do you use the %TYPE keyword?

Q: How do you open a saved PL/SQL block?

Q: How do you run a saved PL/SQL block?

Q: What does the %ROWTYPE keyword in the PL/SQL language?

Q: What is an implicit cursor in the PL/SQL language?

Q: An implicit cursor must have _________ on its SELECT SQL statement?

Q: What does the SQL%NOTFOUND reserved PL/SQL word?

Q: What does the “SET SERVEROUTPUT ON?”

Q: Write a PL/SQL block, to output the "Hello iSelfSchooling" message.

Q: Use the %TYPE keyword, to declare a variable as the same datatype and size of the department name column of the dept table.

Q: Use the implicit cursor to query the department table information where deptno is 30. Check, if no record was found then print “Record was not found.” Else print the department name only.