PL/SQL tutorial | Stored Procedure in Oracle Database 11g Complete guide

Learn in depth about stored procedure in oracle,usage of SP in Database. Creating Stored Procedure using in, out ,in/out parameter and handling exception inside with example.

Stored Procedure in Oracle,
Stored Procedure in PL/SQL,
Oracle Stored Procedure,
PL/SQL Stored Procedure,
What is Stored Procedure in pl/sql,
How to use Stored Procedure in pl/sql,
How to write a Stored Procedure in oracle,
How to create Stored Procedure in pl/sql,
Parameterized stored procedure in pl/sql,
Benefit of using stored procedure in oracle,

Advantages of Using Stored Procedures:
Maintainability (because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier)
Can be tested independent of the application
Isolation of Business Rules,(Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application)
Speed / Optimization ( Stored procedures are cached on the server
Execution plans for the process are easily reviewable without having to run the application)
Utilization of Set-based Processing (The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower)
Security ( Limit direct access to tables via defined roles in the database, provide an “interface” to the underlying data structure so that all implementation and even the data itself is shielded.
Securing just the data and the code that accesses it is easier than applying that security within the application code itself ).

Drawbacks to Using Stored Procedures:
Limited Coding Functionality
Stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
Portability issue for complex and large Stored Procedure.
Testing :Any data errors in handling Stored Procedures are not generated until runtime
Cost :Stored Procedure development could potentially require a dedicated database developer. Some businesses will not allow developers access to the database at all, requiring instead a separate DBA.

Alternatives to Stored Procedures:
In-line or Parameterized Queries (These are written within the application code itself)
Object Relational Mapping (ORM) (Provides an abstraction to the database without having to manually write data )


Linkedin: https://www.linkedin.com/in/aditya-kumar-roy-b3673368/
Facebook: https://www.facebook.com/SpecializeAutomation/