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/

Comments

Write a comment

*

Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.


Do you like our videos?
Do you want to see more like that?

Please click below to support us on Facebook!

Send this to a friend

▷ Other ReviewsVehicles   Show Cars   Motorbikes   Scooters   Bicycles   Rims & Tires   Luxury BoatsFashion   Sunglasses   Luxury Watches   Luxury Purses   Jeans Wear   High Heels   Kinis Swimwear   Perfumes   Jewellery   Cosmetics   Shaving Helpers   Fashion Hats   Modeling TipsFooding   Chef Club   Fooding Helpers   Coktails & LiquorsSports   Sport Shoes   Fitness & Detox   Golf Gear   Racquets   Hiking & Trek Gear   Diving Equipment   Ski Gear   Snowboards   Surf Boards   Rollers & SkatesEntertainment   DIY Guides   Zik Instruments   Published Books   Music Albums   Cine Movies   Trading Helpers   Make Money   Fishing Equipment   Paintball Supplies   Trading Card Games   Telescopes   Knives   Vapes   GameplaysHigh Tech   Flat Screens   Tech Devices   Camera Lenses   Audio HiFi   Printers   USB Devices   PC Hardware   Network Gear   Cloud Servers   Software Helpers   Programmer Helpers   Mobile Apps   Hearing AidsHome   Home Furniture   Home Appliances   Tools Workshop   Beddings   Floor Layings   Barbecues   Aquarium Gear   Safe Boxes   Office Supplies   Security Locks   Cleaning ProductsKids   Baby Strollers   Child Car Seats   Remote ControlledTravel   Luggages & Bags   Airlines Seats   Hotel Rooms   Fun Trips   Cruise Ships   Mexico Tours