Oracle Has SQL Server Beat in Stored Procedure Programming

Oracle Has SQL Server Beat in Stored Procedure Programming

oth Oracle9i and SQL Server 2000 support the use of the database as more than a simple data store for your information. They enable you to use the database to implement your business rules and processes as well with enhancements to the standard SQL language that support the more complicated SQL necessary for this feature. However, each product does so in a different way. This article compares the two approaches and determines which database engine is more suited for this role.

SQL Server and Transact-SQL
SQL Server’s enhanced version of SQL is called Transact-SQL (T-SQL). T-SQL is very much like SQL with a couple of crucial additions: it supports stored procedures and user-defined functions. These two features, as in any programming language, allow you to package your code into modular sections.

T-SQL also supports simple looping and branching constructs such as the 'goto, while' loop and 'if else' statements. In addition, SQL Server provides a library of useful functions in areas such as date and time, math, and data conversion. T-SQL also allows you to declare variables of the various SQL types and retrieve data from the database directly into these variables. You can create temporary tables (or a table variable) for storing intermediate results too.

Because T-SQL is very similar to SQL, programmers who are comfortable with SQL will find it quite easy to learn. For those who need more assistance, SQL Server comes with a very well indexed and searchable help system called Books Online that can quickly provide examples of the necessary syntax or more detailed explanations.

So how well does T-SQL do when programming systems with stored procedures? In my experience, you can support very complex and large systems this way. I currently work on several systems that consist of close to 1,000 stored procedures. To do so successfully, you need to carefully plan how to structure and modularize your code in a standard way. For example, my organization employs a standard way of writing updates, selects, and any other SQL operation to the systems I support. We also have standards for naming variables and stored procedures. Oracle and PL/SQL
Unlike T-SQL, PL/SQL, Oracle’s primary language for writing stored procedures, is a full-fledged programming language that happens to share some syntax with SQL. But as opposed to other general-purpose languages, PL/SQL is very data aware and has many features designed to ease data handling.

To gain an understanding of how PL/SQL can ease your coding, consider the following?unfortunately, common?scenario. Suppose you have a column in a table that is declared as a char (50). In all your stored procedures that reference this column and store values from it in a variable, you also declare your variables to be char (50). Now, after you’ve finished your umpteenth procedure, you find out that the column needs to be extended to a char (100). In T-SQL, you (or in fact, me during a very long afternoon session several weeks ago) are in for a lot of searching and replacing the char (50) declarations with char (100). But in PL/SQL, you can simply use what’s called an anchored declaration and avoid the problem.

An anchored declaration allows you to declare your variable as being of whatever type the particular column in the table is. For example, emp_id emp.empno%TYPE declares a variable called emp_id, which is the same type as the column empno in the emp table. If I change the column type in the table, all I need to do is recompile the dependant code (Oracle also keeps track of the dependencies for you and marks the code as “invalid”). Oracle makes coding even easier by enabling you to declare in one fell swoop all the variables needed to represent a row in a table. The emprow emp%ROWTYPE command declares a structure emprow that has the same attributes as the columns in the emp table.

The syntax for declaring a cursor to loop through each row of a result set again shows how easy PL/SQL makes working with data. The following statement implicitly declares a cursor and loops through each row of the result set:

FOR emp_cur in (select * from emp where deptid = 5)LOOP	Pay_employee(emp_cur);END LOOP;

PL/SQL also gives you additional tools for organizing large volumes of code. Rather than maintaining hundreds of separate stored procedures, you can organize related stored procedures into packages. So for example, I can have a customer package with procedures called add, delete, and update. To use a particular procedure, I simply write code like customer.add(whatever parameters) or customer.delete. This of course makes the code very readable and understandable. Packages also provide for the all-important concept of abstraction by allowing you to declare the signature of the package (the procedures and parameters) separate from the package body. So I can change the body at will without affecting the users who call those procedures, as long as I don’t change the signature. Additionally, I can declare private procedures in the package that can be called only by other procedures in the same package.PL/SQL Versus T-SQL
As you’d expect in a full-fledged programming language, PL/SQL also provides a full set of loop constructs including a for loop, a while loop, repeat until, and of course the simple goto statement. It also includes advanced features such as arrays and collections. I also particularly like its error-handling mechanism in comparison to T-SQL’s. In T-SQL, you have to test a global variable called @@error explicitly after every SQL statement that might cause an error. It also doesn’t provide a way of declaring a common body of code that should handle any errors found. So a typical procedure has numerous lines of code that look like this:

Insert .If @@error <> 0Begin	Set some error message	Goto failed:EndUpdate ..Begin	Set some error message	Goto failed:End

The repeated explicit error handling becomes tedious and clutters up the code. What’s worse is that because of the burden of coding the error-handling logic, many times it is simply left out. In PL/SQL, however, you simply declare an EXCEPTION section in your procedure. When an error occurs, Oracle will transfer control to that section of the code and continue from there. You can also declare your own exceptions based on your business logic. So a typical exception section might look like this.

EXCEPTIONWHEN NO_DATA_FOUND  system exceptionTHEN	..WHEN invalid_emp_id  user-defined exception THEN	.

I hope by now you agree that PL/SQL provides a much better environment for implementing your business rules using stored procedures than T-SQL does. If you plan to build a system that relies heavily on large amounts of stored procedures, you definitely should consider Oracle over SQL Server.

However, all this additional sophistication comes at a cost. PL/SQL definitely has a longer learning curve than T-SQL. Luckily, many good books are available on the subject. I personally have found Learning Oracle PL/SQL by Bill Pribyl with Steven Feuerstein a great introductory book, and Oracle PL/SQL Programming by the same authors offers some more advanced study.

Additionally, if you are serious about PL/SQL you should obtain a better editor than SQL*Plus (the one that comes with Oracle). Many editors are on the market. I personally use OraEdit Pro from DKG Advanced Solutions. It’s relatively cheap (only $99) and it includes Intellisense, which not even Query Analyzer (Microsoft’s rather superb built-in editor) has yet.Oracle Offers the Better Environment
Of course, technology changes and this applies even to something as seemingly mundane and old hat as writing stored procedures. On Oracle’s side, PL/SQL continues to be enhanced with each version. You can also now write stored procedures in Java. Microsoft has also announced that in a future version of SQL Server you will be able to create stored procedures in your .NET language of choice. So in a year or so, perhaps SQL Server will have the better environment. But for now, Oracle is the clear winner.

devx-admin

devx-admin

Share the Post:
Battery Breakthrough

Electric Vehicle Battery Breakthrough

The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years,

Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW)

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Battery Breakthrough

Electric Vehicle Battery Breakthrough

The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years, as reported by energy analytics

Economy Act Soars

Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy

Renewable Storage Innovation

Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will

Renesas Tech Revolution

Revolutionizing India’s Tech Sector with Renesas

Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on

Development Project

Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings.

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the