Oracle create or replace Stored Procedure example basics

An Oracle stored procedure is an Oracle object that you create using PL/SQL. PL/SQL is a procedural language which is used by Oracle developers to process and manipulate data stored in an Oracle database.
The basic form of an Oracle stored procedure is
CREATE OR REPLACE PROCEDURE p_procedure1 IS
BEGIN

null;

END p_procedure1;


The stored procedure above will compile successfully.
Now lets discuss the procedure above.
CREATE OR REPLACE: You must use CREATE command whereas REPLACE is optional. But if you do not put REPLACE then you can not recompile the stored procedure if it already exists. By putting CREATE OR REPLACE you are saying that if it already exists then replace it with the new version.
PROCEDURE: This clause specifies the type of object that you want to create. Here we are saying it to be a procedure.
BEGIN and END: This is the body of the procedure. All code that you want to put in the procedure should live here. In the example above I have put null; It means do nothing. However even if you do nothing and just want to create a stored procedure (as above) you still need to put the Begin and End to complete the frame of the stored procedure. For clarity I have put End p_procedure1 which is not required. Instead you could just put End;
Example of Oracle Store Procedure with parameters:
Most of the time when calling an Oracle procedure you would like to pass on some parameter to the procedure to process it or to do something with the data in the parameter. You can pass parameters to a stored procedure as below:
CREATE OR REPLACE PROCEDURE p_procedure2(p_name varchar2) IS
BEGIN

dbms_output.put_line(p_name);

END p_procedure2;
Here I am passing a parameter called p_name to the stored procedure p_procedure1; Then in the body section I am displaying the value of the parameter.
You can use “dbms_output.put_line()” to display the values of parameters in SQL*Plus window. This is same PRINT/ECHO/PRINTLN used in other programming languages.
Now the parameters that you pass to a stored procedure can be of three types (IN, OUT and IN OUT).
IN - is the default type. So if you do not specify the parameter type then IN is used. This is telling that I am supplying a parameter to the stored procedure.
OUT – is used to get values back from the stored procedure.
IN OUT – is used to pass values to a stored procedure and to get values from the stored procedure using the same parameter.
Unlike Oracle functions you can return multiple values using the out parameter.
For example: if you want to get back the employee name of an employee and you have just the emp_no (employee number) then you can pass the emp_no to a stored procedure using an IN parameter and then get the employee name using an out parameter. This is demonstrated below:
CREATE OR REPLACE PROCEDURE p_procedure3(p_emp_no IN number, p_name OUT varchar2) IS
BEGIN

SELECT ename
INTO p_name
FROM emp
WHERE emp_no = p_emp_no;

END p_procedure3;


Here we are passing the emp_no and using that we are getting ename from table emp. The returned value is put into p_name and the calling program will be able to access this p_name.
Now when we use out parameter it becomes a little bit different while executing a stored procedure.

Source - http://www.oraclecity.com/plsql-tutorial/oracle-create-or-replace-stored-procedure-example-basics/

regards
Rajesh K

In which scenario we use Abstract Classes and Interfaces

Interface: 
–> If your child classes should all implement a certain group of methods/functionalities but each of the child classes is free to provide its own implementation then use interfaces.
For e.g. if you are implementing a class hierarchy for vehicles implement an interface called Vehicle which has properties like Colour MaxSpeed etc. and methods like Drive(). All child classes like Car Scooter AirPlane SolarCar etc. should derive from this base interface but provide a seperate implementation of the methods and properties exposed by Vehicle.
–> If you want your child classes to implement multiple unrelated functionalities in short multiple inheritance use interfaces.
For e.g. if you are implementing a class called SpaceShip that has to have functionalities from a Vehicle as well as that from a UFO then make both Vehicle and UFO as interfaces and then create a class SpaceShip that implements both Vehicle and UFO .
Abstract Classes
–> When you have a requirement where your base class should provide default implementation of certain methods whereas other methods should be open to being overridden by child classes use abstract classes.
For e.g. again take the example of the Vehicle class above. If we want all classes deriving from Vehicle to implement the Drive() method in a fixed way whereas the other methods can be overridden by child classes. In such a scenario we implement the Vehicle class as an abstract class with an implementation of Drive while leave the other methods / properties as abstract so they could be overridden by child classes.
–> The purpose of an abstract class is to provide a common definition of a base class that multiple derived classes can share.
For example a class library may define an abstract class that is used as a parameter to many of its functions and require programmers using that library to provide their own implementation of the class by creating a derived class.

Use an abstract class

• When creating a class library which will be widely distributed or reused—especially to clients, use an abstract class in preference to an interface; because, it simplifies versioning. This is the practice used by the Microsoft team which developed the Base Class Library. ( COM was designed around interfaces.)

• Use an abstract class to define a common base class for a family of types.

• Use an abstract class to provide default behavior.

• Subclass only a base class in a hierarchy to which the class logically belongs.

 Use an interface

• When creating a standalone project which can be changed at will, use an interface in preference to an abstract class; because, it offers more design flexibility.

• Use interfaces to introduce polymorphic behavior without subclassing and to model multiple inheritance—allowing a specific type to support numerous behaviors.

• Use an interface to design a polymorphic hierarchy for value types.

• Use an interface when an immutable contract is really intended.

• A well-designed interface defines a very specific range of functionality. Split up interfaces that contain unrelated functionality.

 Source - http://mycodelines.wordpress.com/2009/09/01/in-which-scenario-we-use-abstract-classes-and-interfaces/

happy coding
Rajesh K

What's the difference between an ABSTRACT CLASS and an INTERFACE

A good way to distinguish between a case for the one or the other for me has always been the following:

1. Are there many classes that can be "grouped together" and described by one noun? If so, have an abstract class by the name of this noun, and inherit the classes from it. (A key decider is that these classes share functionality, and you would never instantiate just an Animal... you would always instantiate a certain kind of Animal: an implementation of your Animal base class)
Example: Cat and Dog can both inherit from abstract class Animal, and this abstract base class will implement a method void Breathe() which all animals will thus do in exactly the same fashion. (I might make this method virtual so that I can override it for certain animals, like Fish, which does not breath the same as most animals).

2. What kinds of verbs can be applied to my class, that might in general also be applied to others? Create an interface for each of these verbs.
Example: All animals can be fed, so I will create an interface called IFeedable and have Animal implement that. Only Dog and Horse are nice enough though to implement ILikeable - I will not implement this on the base class, since this does not apply to Cat.

As said by someone else's reply: the main difference is where you want your implementation. By creating an interface, you can move your implementation to any class that implements your interface.
By creating an abstract class, you can share implementation for all derived classes in one central place, and avoid lots of bad things like code duplication.

Source - http://social.msdn.microsoft.com/forums/en-US/csharplanguage/thread/8ad621b8-a915-4d7e-89c3-5dbbc47202fd/

happy coding
 Rajesh K