“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.
|