Topics: Hands-On 03 – Procedure Builder
Procedure Builder
allows you to develop stored procedures, functions, packages, and triggers in
Oracle. Many developers have found the SQL*PLUS and favorite text editor
approach cumbersome. You use Procedure Builder for ease of writing program
development, debugging, and version control.
In this Hands-On,
you will learn how to: Creating PL/SQL Procedure using Procedure Builder, add
a record into the “dept” table using PL/SQL.
In this Hands-On, you will learn the basic fundamentals of PL/SQL block.
The Core Subjects are:
1- Developing a PL/SQL block
2- Types of block
3- Anonymous or unnamed block
4- Components of a PL/SQL block
a. Declaration
b. Body or Execution
c. Exception
5- Cursor
a. Implicit cursor
b. Explicit cursor
6- Parameters in cursor
|
More Resources by
Google: |
|
|
|
|
Go to “MS-DOS.”
Change directory to the iself directory and login to “sqlplus” as
"iself/schooling."
>> cd ..\iself
>>sqlplus iself/schooling
Write a
PL/SQL block to use only the "body" section with PL/SQL statement.
>> begin
null;
end;
/
Use the slash (/) to compile and run the block.
Add the "declaration" section with no variables. Then compile and run the
block.
>> declare
-- no variable
begin
null;
end;
/
Use the “Set serveroutput on” to display the buffer used by dbms_output.
>> set serveroutput on
Write a PL/SQL block, to output the "Hello iselfschooling" message.
>> begin
dbms_output.put_line('Hello
iselfschooling');
end;
/
describe the department table.
>> desc dept
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.
>> declare
v_dname varchar2(14);
begin
v_dname := 'HR';
dbms_output.put_line(v_dname);
end;
/
Save the PL/SQL block as “test_myblock.”
Then go to notepad and open the PL/sql block from the “iself” directory.
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.
Go to “SQLPLUS.”
Get the file.
And run it.
The same output!
>> declare
v_dname dept.dname%type;
begin
v_dname := 'HR';
dbms_output.put_line(v_dname);
end;
/
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.
Output the variable.
>> 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.
The same output!
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.
>> 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.
Back to
“Notepad”
Modify the
PL/SQL block to move the entire record into the variable row.
Then save the file.
Go to “SQLPLUS.”
Get the file.
And run it.
The same result!
Now, you should practice this over and over, until you become a master at it.
Good Luck!
|