SQL stands for Structured Query Language and is used by database developers to retrieve, update and insert data into databases. PL/SQL, or Programming Language/SQL, augments SQL by providing features such as variables and logic. Using PL/SQL, Oracle developers can go beyond the standard set of functions offered by SQL and write entire applications that allow them to take full advantage of the data stored in their Oracle information systems.
Instructions
- 1
Open the SQL *Plus utility. Type the declarative code section at the prompt, followed by a return. Place all variable definitions in the declarative section.
DECLARE
/*declare variables here*/
2Declare a variable that will hold data selected from the database, followed by a return. Variables should be declared using the format "name," "type," "length."
DECLARE
/*declare variables here*/
numVar number (6);
3Type the executable code section. On the next line, enter code that selects a value from a database table and places it in the declared variable. Assume a table named myNums with one record called myNumber.
DECLARE
/*declare variables here*/
numvar number (6);
BEGIN
/*select the value of the record myNumber in table myNums and put it into the numVar variable*/
select myNumber into numVar from myNums;
4Manipulate the data stored in the numVar variable by adding one to the value and adding a return.
DECLARE
/*declare variables here*/
numvar number (6);
BEGIN
/*select the value of the record myNumber in table myNums and write it into the numVar variable*/
select myNumber into numVar from myNums;
numVar := numVar + 1;
5Write the new value into the myNums table, hit return, and execute the code by entering a forward slash on the line immediately following the END; keyword. The table now has two rows, the original row and a new row that contains the variable's value.
DECLARE
/*declare variables here*/
numvar number (6);
BEGIN
/*select the value of the record myNumber in table myNums and put it into the numVar variable*/
select myNumber into numVar from myNums;
numVar := numVar + 1;
insert into myNums values (numVar );
END;
/
