The PL/SQL language has all of the conditional (IF... THEN) looping (WHILE), assignment, variable declaration and other language constructs of a complete programming language. SQL statements may be freely mixed in with the other programming statements. The major change to SQL is the syntax of the SELECT statement. All SELECT statements in PL/SQL must use the INTO clause to redirect the rows returned by the SELECT into variables. The syntax of the SELECT statement is:
SELECT <column1, column2, >
INTO <var1, var2, >
FROM <table1, table2, >
WHERE <where clause>
GROUP BY <column1, column2, >
HAVING <having clause>
ORDER BY <column1, column2, >
Variables named in the INTO clause correspond to the order of columns selected in the SELECT clause. For example:
DECLARE
empsalary NUMBER;
empdepartment NUMBER;
BEGIN
SELECT employee.salary, employee.dno
INTO empsalary, empdepartment
FROM employee
WHERE employee.lname = 'SMITH';
IF (empdepartment = 1) THEN
UPDATE employee
SET salary = empsalary * 1.03
WHERE employee.lname = 'SMITH';
END IF;
END;
The above PL/SQL block declares two variables and then executes a SELECT statement returning the salary in PL/SQL variable empsalary and the department number in PL/SQL variable empdepartment for employee SMITH. If the empdepartment is equal to 1 then an SQL UPDATE statement is executed.
wwww.czimple.com
You can find more expert in Database
|