For Loop in PL/SQL

One of the statement  in oracle I use most is for loop in pl/sql. This for loop is very handy to use and the basic statement comes like this:


for variable in 1..x 
loop
   statement;
end loop;


The reverse loop statement is:

for variable in reverse 1..x 

loop

   statement;

end loop;

The example code comes like this

for v_cnt in 1 .. 100

       

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

    for v_cnt in reverse 1 .. 100

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

For in pl/sql could also work with cursor, this make handy as I said before. I like to use for loop in pl/sql with cursor so I don't have open and close the cursor, the for loop will do it automatically. Here is how for loop in pl/sql works:


for c_cur in (select statement)
loop
  statement;
end loop;

for c_cur in c_name 
loop
  statement;
end loop;


Where c_name is the declared cursor and c_cur is the variable (you don't need to declare it).

For example:


for c_cur in (select a, b from tab_x) 
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;



for c_cur in declared_cursor
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;

Read More

PL/SQL Best Practices

PL/SQL Best Practices with Steven Feuerstein

Renowned Oracle PL/SQL expert, Steven Feuerstein, presents "PL/SQL Best Practices" -- offering high-level principles to guide our work.

Here are some example of the PL/SQL best practices derived from PL_SQL_Best_Practices2.pdf:


DEV-01: Set Standards and Guidelines for Your Application Before Anyone Starts Writing
DEV-02: Ask for Help if you find Yourself Spending More Than 30 Minutes to Solve a problem
DEV-03: Make Code Review a Regular Part of Your Development Process
DEV-04: Validate Standards by using SQL to Analyze Source Code Stored in the Database
DEV-05: Generate Code Whenever Possible and Appropriate
DEV-06: Set Up —and Use! —A Formal Unit Testing Procedure
DEV-07: Get Someone Else to Perform Functional Tests on your Code

Read More

Common Mistakes in Oracle PL/SQL Programming

I just came a cross this video about oracle pl/sql programming, which provide good technique to write pl/sql code.
Very good video to take a look for pl/sql programmer.



You wrote an explicit cursor? You declared a variable using the VARCHAR2 datatype? You raised an exception to skip over unnecessary lines of code?

If slip-ups like these sound familiar (or if you don't know why these are mistakes), join Quest for a one-hour Webcast featuring guest speaker, Steven Feuerstein, as he examines common mistakes in Oracle PL/SQL programming. Learn how to correct mistakes to improve the maintainability and performance of your application.

This Webcast will also demonstrate CodeXpert — a powerful feature available within Toad® that will help you avoid common mistakes and ensure you implement PL/SQL best practices in your daily activities.

About the Guest Speaker:
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written nine published books on PL/SQL (all from O'Reilly & Associates). Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and serves as a Senior Technology Advisor to Quest Software.

Read More

PL/SQL features by release

A Mini-History of Oracle and PL/SQL

This chapter answers two questions: where did PL/SQL come from and why is it the best database development language ever developed?

In the late 70s, around the time Ingres was getting started at UC Berkeley, three guys working on a contract for the CIA got together and started a company called Relational Software, Inc.

Their first product was a relational database called Oracle. The founders decided to use the C language for development. This would later become important when they decided to start porting to different platforms.

They also decided to support SQL as the internal data access language. This would also become a very important factor to its success. In 1979, Relational Software was the only company making an SQL compliant database. If anyone ever asks you who wrote the first SQL database, you now know the answer: Oracle.

To access the database, to write an application for example, you had to use an external language and compiler. In the early days of Oracle, that was C but, in time, several other languages were added: COBOL, ADA, Fortran, PL/1, and others.

In the early 1980s, the company was renamed Oracle Corporation. That would just be the beginning of Oracle’s desire to rename its products. In my time using the Oracle database, I think every tool I have used has been renamed at least once. In the case of CDE/Developer 2000/Developer Suite, it has been renamed enough to be confusing.

Oracle did not have an embedded language for many years. Having come from a government background, when they chose a language for the database, they modeled it on ADA.

I programmed in ADA for a few years in the 1980s while I was working as a consultant for the US Department of Defense. It is a very powerful, but very wordy, object oriented language. ADA, and by extension PL/SQL, are descendants of Pascal.

Oracle named this new language PL/SQL; the Procedural Language extension to SQL. I pronounce it pee ell sequel but many others pronounce it pee ell ess que ell. Feel free to pronounce it however you like though.

>> More Information <<

Source: oracle_tips@topica.email-publisher.com

Read More