Browse DevX
Sign up for e-mail newsletters from DevX


Oracle Has SQL Server Beat in Stored Procedure Programming : Page 2

Oracle9i and SQL Server 2000 add enhancements to the standard SQL language that enable you to use the database to implement your business rules and processes—SQL Server with Transact-SQL and Oracle with PL/SQL. Find out why the Oracle database engine is better suited for this role.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date