iSelfSchooling.com - Copyright © 1999-2007 iSelfSchooling.com  References  Job Openings  |  Secure Login
    Home  | Search more...  |  FREE Online VIDEO Oracle Training  |  Gift Store  |  Bookstore

   Unlimited access!   

    Oracle  Syntax  | Suggestions Your Contribution  |  FREE Legal Forms

 

Email2aFriend Homepage us! |  Bookmark   -  Copyright & User Agreement

Products/Services

 Vision/Mission

 Community Sharing

 Services

  Products

 Biography

 Contact Us

 FAQ

 Current News

 Website Traffic

 Bookstore

 FREE Training

 SQL

 PL/SQL

 Forms 

 Reports

 Other TOOLS

 Fundamentals

 Performance

 OEM

 Application Server

 Grid Control

 Articles

 Prepare for OCP

Oracle SYNTAX

 Oracle Functions

 Oracle Syntax

 Oracle 10g Syntax

  PL/SQL Syntax

UNIX and more...

 UNIX for DBAs

 LINUX for DBAs

 DB using PHP

  A+ Certification

 Basics of JAVA  

 Tips of  SEO

Finance/Jobs

 Financial Aid

 Skilled

 Oracle

 Jobs

  Magazine

More Training

 Q & Answers

 SQL-PL/SQL

 DBA

 Developer

 Important Notes

 Case Studies

 9i New Features

 10g New Features

 10g Qs/As

 Grid Control

 OracleAS # I

 OracleAS # II

  LDAP and OID

  HTTP Server

 Instructor-Led

  Virtual Hosts

 Community Sharing

More to know...

Acknowledgement**

 FREE Legal Forms

 Who is who

 Market Place

 University Directory

 Advisory Articles

 Links...

 

 

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:

 Manuscript

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!

 
 
 
Google
 
Web web site