Reference Website : http://plsql-tutorial.com/plsql-constants.htm
PL/SQL Syntax
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
Placeholders : Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw,
Raw, Blob, Clob, Nclob, Bfile
The General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);
dept varchar2(10) NOT NULL := “HR Dept”;
1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;
2) We can assign values to variables directly from the database columns by using a SELECT..
INTO statement. The General Syntax is:
SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];
Example: The below program will get the salary of an employee with id '1116' and display it on the screen.
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee ' || var_emp_id || ' has salary ' || var_salary);
END;
/
declare
salary number(6,2);
name varchar2(40);
begin
select e.sal, e.ename into salary, name from emp e where e.empno='7934';
dbms_output.put_line('salary is '|| salary || ' name is ' || name);
end;
====================
Scope of Variables
====================
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks.
1 declare
2 salary number(6,2);
3 name varchar2(40);
4 deptname varchar (40);
5 begin
6 select e.sal, e.ename into salary, name from emp e where e.empno='7934';
7 dbms_output.put_line('salary is '|| salary || ' name is ' || name);
8 begin
9 select dname into deptname from dept where deptno='10';
10 dbms_output.put_line('dept is '|| deptname);
11 end;
12* end;
SQL> /
salary is 1300 name is MILLER
dept is ACCOUNTING
PL/SQL procedure successfully completed.
==========
Constant
==========
The General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
DECLARE
salary_increase CONSTANT number (3) := 10;
You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error.
==============
PL/SQL Records
==============
Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.
TYPE record_type_name IS RECORD
(first_col_name column_datatype, second_col_name column_datatype);
col_name table_name.column_name%type;
The datatype of the column is dynamically applied to the field. This method is useful when you are altering the column specification of the table, because you do not need to change the code again.
record_name record_type_name;
can use also %type to declare variables and constants.
Declaring user defined type - employee_type is user defined type
DECLARE
TYPE employee_type IS RECORD
(employee_id number(5),
employee_first_name varchar2(25),
employee_last_name employee.last_name%type,
employee_dept employee.dept%type);
employee_salary employee.salary%type;
employee_rec employee_type;
If all the fields of a record are based on the columns of a table, we can declare the record as follows:
record_name table_name%ROWTYPE;
DECLARE
employee_rec employee%ROWTYPE;
declare
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno='7369';
dbms_output.put_line(emp_rec.empno);
end;
================================
Conditional Statements in PL/SQL
================================
1)
IF condition THEN
statement 1;
ELSE
statement 2;
END IF;
2)
IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF
3)
IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;
4)
IF condition1 THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;
==============================
Iterative Statements in PL/SQL
==============================
1)Simple Loop - A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates
LOOP
statements;
EXIT; //only exit cause the loop to execute only once
{or EXIT WHEN condition;} //loop will end when the condtion will meet
END LOOP;
2)While Loop - The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.
WHILE
LOOP statements;
END LOOP;
3)For Loop - Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer.
declare
begin
for counter in 1..10 loop
dbms_output.put_line(counter);
end loop;
end;
declare
begin
for counter in reverse 1..10 loop
dbms_output.put_line(counter);
end loop;
end;
a)val1 - Start integer value.
b)val2 - End integer value.
c)The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicity.
d)The counter variable is incremented by 1 and does not need to be incremented explicitly.
e)EXIT WHEN statement and EXIT statements can be used in FOR loops but it's not done oftenly.
===========
CURSORS
===========
Cursor is a temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
Types of cursors in PL/SQL:
a)Implicit cursors - They are created when a SELECT statement that returns just one row is executed. They are also created when you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements. Attribute to find status of cursor :
%FOUND
%NOTFOUND
%ROWCOUNT and
%ISOPEN
DECLARE
var_rows number(5);
BEGIN
UPDATE employee SET sal = sal + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
b)Explicit cursors - They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
An explicit cursor is defined in the declaration section of the PL/SQL Block. We can provide a suitable name for the cursor.
There are four steps in using an Explicit Cursor.
1)DECLARE the cursor in the declaration section.
2)OPEN the cursor in the Execution Section.
3)FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
4)CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
1 DECLARE
2 emp_rec employee%rowtype;
3 CURSOR emp_cur IS
4 SELECT *
5 FROM emp
6 WHERE sal > 2500;
7 empname varchar2 (10);
8 BEGIN
9 OPEN emp_cur;
10 loop
11 fetch emp_cur into emp_rec;
12 dbms_output.put_line('emp name is ' || emp_rec.ename);
13 exit when emp_cur%notfound;
14 end loop;
15 close emp_cur;
16* end;
SQL> /
emp name is JONES
emp name is BLAKE
emp name is SCOTT
emp name is KING
emp name is FORD
emp name is FORD
PL/SQL procedure successfully completed.
General Syntax for using FOR LOOP:
FOR record_name IN cusror_name
LOOP
process the row...
END LOOP;
==================
Stored Procedures
==================
A named PL/SQL block which performs one or more specific task. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and
exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
CREATE OR REPLACE PROCEDURE employer_details
IS
CURSOR emp_cur IS
SELECT ename, job, sal FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
dbms_output.put_line('foring');
FOR emp_rec in emp_cur
LOOP
dbms_output.put_line(emp_rec.ename || ' ' ||emp_rec.job || ' ' ||emp_rec.sal);
END LOOP;
END;
Executing :: EXECUTE [or EXEC] procedure_name;
Begin
procedure_name;
end;
