Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

Oracle Procedures, Functions and looping constructs

Procedures and functions in PL/SQL are types of blocks that are stored in the database in a compiled form. This is among the major differences between ordinary queries and procedures and functions (otherwise known as subprograms). Creating a function or procedure requires the use of the Phrases "Create function" and "Create procedure" respectively. For example the code bellow is used to create a procedure:



CREATE OR REPLACE PROCEDURE add_low_earner(

p_jobtitle IN varchar(50),

p_minsalary IN number) is 

begin

   insert into low_earners(job_title,min_salary) values(p_jobtitle,p_minsalary);

end;

To execute this procedure you use the following piece of code in SQL Plus:



 SQL> begin

   2  add_low_earner('sleeper',6777);

   3  end;

   4  /

This will insert the record into the table low_earners with the values 'Sleeper' and 6777 into the fields job_title and min_salary respectively.

PL/SQL looping constructs

Looping constructs are used to perform a certain tasks repeatedly. This can be for a number of pre-determined times or repeat the task until a certain condition is met. For example:

begin 

     ...

     

     FOR p_counter IN 1..20 LOOP

         insert into employee_counter(num_employees) values(p_counter);

     END LOOP;

    END;

The above block of code counts from one to 20 and inserts each number into employee_counter table. When the number reaches 20 the routine kicks it out and execution stops.

PL/SQL cursors

Cursors are used to process multiple rows returned from a database. Basically you are going through returned rows, one at a time performing whatever task you want on them. We will go through the details of how cursors are defined in the next posting.

Functions Notation

Array related PHP Functions
The PHP language has a lot of functions that are used to populate, retrieve and manipulate array variables. Bellow are some of those functions, we will be using some of them as we continue to learn. Functi

Functions in PHP
Just like we have learned in mathematics, the basic way to think of and understand functions is to think of it as a machine, you feed it with raw materials and the machine goes through processing these materials into finished and possibly usable product. An easy way to look at functions is to consid

MySQL String functions
MySQL relational Database system has a lot of built-in functions, some of which are used to retrieve and manipulate data, while others are simply used for server administrative purposes. In this posting we will explore how String functions are used in MySQL. String length function

PHP Flow control functions

PHP String functions
So far we have only displayed strings directly to the browser without changing the format. We have successfully done that using the PHP echo () command. In addition to the echo () command, PHP also has two other print functions that allow users to format the output. The formatting may come in the fo

Trigonometry functions
Trigonometry is a branch of mathematics that deals with triangles. It is more like geometry, but it deals particularly with right angle triangles. The concept of trigonometry deals with the relationship between angles and sides of the triangle. To accurately descri

PHP Flow control functions
Most scripts we have looked at so far are linear and flow only in one direction. For example we have looked at scripts that would take two numbers add them together and display the answer. In real world things hardly work that way. In most cases you are faced with different possibilities and

php functions

Introduction to Oracle PL/SQL
PL/SQL stands for Procedural Language/ SQL, and is a pretty sophisticated language used to access Oracle databases. It is integrated within the server environment to ensure smooth and fast execution of PL/SQL code or queries. The bigger part of this language is the SQL componen

Udjuni.com

Oracle Procedures, Functions and looping constructs

PL/SQL looping constructs

PL/SQL cursors

RELATED ARTICLES: