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 followingunfortunately, commonscenario. 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)
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.