everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

PL/SQL

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

Chapter # 01

Next >>


 

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 e-book 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 e-book.

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 your directory and login to �sqlplus� as "oracle/learning"

MS-DOS> cd ..

MS-DOS> sqlplus oracle/learning

 

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 OracleLearning" message.

SQL> BEGIN

dbms_output.put_line('Hello OracleLearning');

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 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!

 

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 OracleLearning" 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.

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.