Menu
what all can be done in PL SQL can also be done by embedding sql statements in an application langauage say PhP. Why do people still use PL SQL , Are there any major advantages.?
I want to avoid learning a new language and see if PHP can suffice.
350
In your code example CURSOR is declared as part of procedure (not as part of package), which means, cursor is closed automatically when procedure executioin is complete. However it is best coding practice to CLOSE cursor statement if you have OPEN cursor statement in code.
The answer is that PL/SQL is not growing, but not going away either. Because it is used in the Oracle database, and the Oracle database is a fixture of enterprise systems world-wide, it will outlive you. High-performance batch processing has to happen close to the data, so PL/SQL will continue to rule in this area.
PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database. PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types. Applications written in PL/SQL are fully portable.
From my point of view SQL and PL-SQL is necessary for Data Scientist. Everyone is busy learning Python and R which is necessary but knowledge of SQL is a must to become a Data Scientist. We must know how the database works and how to pull data from databases.
Closing a cursor instructs Oracle to release allocated memory at an appropriate time. If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end. However, you must explicitly close package-based cursors.
No, PLSQL is the most efficient and high performing language, hence, it will be very active for another decade at least.
A PL/SQL developer can extend his skills to become a professional database developer or a DBA in addition to performing assigned PL/SQL tasks . This includes creating and maintaining database users, roles, permissions etc. This will increase their value in the job market and fetch higher salaries.
We have data on 65,597 companies that use PL/SQL. The companies using PL/SQL are most often found in United States and in the Information Technology and Services industry. PL/SQL is most often used by companies with 50-200 employees and 1M-10M dollars in revenue.
PL/SQL is useful when you have the opportunity to process large chunks of data on the database side with out having to load all that data in your application.
Lets say you are running complex reports on millions of rows of data. You can simply implement the logic in pl/sql and not have to load all that data to your application and then write the results back to the DB - saves bandwidth, mem and time.
It's a matter of being the right tool for the right job.
It's up to the developer to decide when is a best time to use PL/SQL.
113
In addition to performing bulk operations on the DB end, certain IT setups have stringent security measures.
Instead of allowing applications to have direct access to tables, they control access through PL/SQL stored procedures. This way they know exactly how the data is being accessed instead of applications maintained by developers which may be subject to security attacks.
40
I suppose advantages would include:
Tight integration with the database - Performance.
Security
Reduced network traffic
Pre-compiled (and natively compiled) code
Ability to create table triggers
Integration with SQL (less datatype conversion etc)
In the end though every approach and language will have its own advantages and disadvantages. Not learning PL/SQL just because you already know PHP would be a loss to yourself both personally and possibly career-wise. If you learn PL/SQL than you will understand where it has advantages over PHP and where PHP has advantages over PL/SQL but you will be in a better position to make the judgement.
Best of luck.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
