PL/SQL : Procedure Language for SQL
nice step by step guide for running procedure from sql developer is here
PL/SQL code blocks comprise of statements. Each statement ends with a semi colon. PL/SQL code blocks are followed by a forward slash / in the first position of the following line. This causes the code block statements to be executed. The End keyword is the only PL/SQL code block keyword that is followed by a semi colon.
PL/SQL procedures are wrapped in Packages for reusability.
Anonymous block: PL/SQL block that is contained in an external file are called anonymous block procedures and can be executed from the SQL prompt by using START keyword, which causes SQL*Plus to load, compile and execute the code block in the file.
e.g.
START c:\plsql\code.sql
Note: to start sqlplus type sqlplus from command prompt.
PL/SQL Code Block has following sections:
0. Header: This is optional first block. It is used to identify the type of code block and its name. The code block types are:
a. Anonymous procedure
b. Named procedure
c. function
1. Declaration(optional)- This is an optional section and contains the names of the local objects that will be used in the code block. These include variables, cursor definitions and exceptions. This section begins with the keyword DECLARE.
2. Executable(mandatory): This section starts with keyword BEGIN and is terminated by keyword END. This is the only mandatory section and contains the statements that will be executed i.e. SQL statements, DML statements, procedures(PL/SQL code blocks returning a value) , and build in subprograms. This section starts with the word BEGIN.
3. Exception(optional): An optional section to handle any errors that occur during the execution of the statements and commands in the executable section.
Code block is terminated by the END keyword, the only construct keyword that is followed by a semicolon. Executable is the only required section, which means the code block must have BEGIN and END. The code block is executed by /
declare
local_variable varchar2(30);
begin
select 'Num of employees'|| to_char(count(last_name), '999')
into local_variable
from employee;
dbms_output.put_line(local_variable);
exception
when others then dbms_output.put_line('ERROR OCCURED');
end;
/
The select command is used to compute the number of employees in the database, concatenate this value with another string and assign the value to variable defined in declare section. The final section is the output section. It contains a catchall error handler, the statement which is executed when a database error occurs and when an error handler is not available.
Variable Definition:
Variables are defined as :
Variable_Name Datatype(precision) ;
in Declaration section. Oracle treats variable definition as any other statement so a semi colon is mandatory at the end. Value may be assigned to a variable during definition as well. The variable may also be constrained.
Character Definitions:
Variables that contain alphanumeric values are normally defined as CHAR or VARCHAR2.
VARCHAR2 : contains null spaces in each position that a character does not occupy. Defining length is mandatory. Maximum length can be 32767.
CHAR: contain white space in the unoccupied positions. Whitespace and null are not the same and are not
comparable. Length when not specified is 1. maximum can be 32767.
Note: PL/SQL length of CHAR and VARCHAR2 datatypes is larger than the length allowed in the Oracle database.
Numeric Definitions:
Numeric data definitions can include two parameters: precision and scale. Precision defines overall length of the value. Scale determines the number of digits to the left or the right of the decimal point. The range of the scale is -84 to 127. When a scale is specified, rounding will occur at the end. Following rules apply:
a. Positive scale definitions cause rounding to the right of the decimal point.
b. Negative scale definitions cause rounding to the left of the decimal point.
c. Zero scale definitions cause rounding to the nearest whole number.
The default precision of a number is 38.
e.g. age integer(3); : age must be a whole number less than 999
galloons number(3); the scale is not specified, the value can contain upto three places to the left of decimal or upto three positions. The overall length of the value can't be more than three positions.
salary number(8, 2) ; The overall length of value can't be more than 8 positions including decimal values. At max the variable will have two positions to the left of decimal.
Other datatypes: Several other datatypes including Boolean, Date, Exception can be used.
Constrained Definitions:
Constraints can be applied on the variables defined in the code block. Two common constraints are :
CONSTANT: ensures that the value is not changed after a value is initially assigned to the variable. If a statement tries to change the variable value, an error will occur.
NOT NULL: ensures that the variable will always contain a not null value. If a statement tries to assign a null value to the variable, an error will occur.
e.g
febonaci constant number(9,8): =12.333223;
resonate not null date:='08-APR-53'
Aggregate and PL/SQL Record Definitions
Aggregate variable definition is based on a database of PL/SQL object. It has following advantages:
a. The developer can define a variable with the same data specifications as a table column or cursor variable,
without actually knowing the specifications.
b. The developers can set up an array of variables for a cursor or table record with one statement. The
variables will have the same specifications as the table or cursor variables.
The first aggregate definition tool is %TYPE which allows developer to define a variable with the same specifications as the indicated table or cursor variable. Syntax:
VARIABLE_NAME table_cursor_name.columns_name%type;
e.g.
lname employee.last_name%type;
the second keyword is used to establish an array of variables based on the columns in a cursor or table. This aggregate is called a PL/SQL record. The keyword will create a variable for each column in the identified table/cursor. Each variable in the array will have the same name and specification as its counterpart in the table/cursor.
syntax: ARRAY_NAME table/cursor_name%rowtype;
Here is an e.g. for aggregate array definition:
DEPT_VAR department%rowtype;
variables are acccessed as :
DEPT_VAR.department
DEPT_VAR.department_name
Assigning values to Variables
PL/SQL has two ways to assign values to variables:
:= assignment operator assigns the argument of the left of the operator to the argument of variable on
the right of the sign.
INTO keyword is used in a SELECT or FETCH statement. When used in a
SELECT statement, it assigns the values in the SELECT clause to the variables
following the INTO keyword. When used with the FETCH statement, it
assigns the cursor values to the variables that follow the INTO keyword.
When using the INTO keyword in a SELECT clause, be sure that only one record is retrieved. If more than one record is retrieved an, error will occur.
nice step by step guide for running procedure from sql developer is here
PL/SQL code blocks comprise of statements. Each statement ends with a semi colon. PL/SQL code blocks are followed by a forward slash / in the first position of the following line. This causes the code block statements to be executed. The End keyword is the only PL/SQL code block keyword that is followed by a semi colon.
PL/SQL procedures are wrapped in Packages for reusability.
Anonymous block: PL/SQL block that is contained in an external file are called anonymous block procedures and can be executed from the SQL prompt by using START keyword, which causes SQL*Plus to load, compile and execute the code block in the file.
e.g.
START c:\plsql\code.sql
Note: to start sqlplus type sqlplus from command prompt.
PL/SQL Code Block has following sections:
0. Header: This is optional first block. It is used to identify the type of code block and its name. The code block types are:
a. Anonymous procedure
b. Named procedure
c. function
1. Declaration(optional)- This is an optional section and contains the names of the local objects that will be used in the code block. These include variables, cursor definitions and exceptions. This section begins with the keyword DECLARE.
2. Executable(mandatory): This section starts with keyword BEGIN and is terminated by keyword END. This is the only mandatory section and contains the statements that will be executed i.e. SQL statements, DML statements, procedures(PL/SQL code blocks returning a value) , and build in subprograms. This section starts with the word BEGIN.
3. Exception(optional): An optional section to handle any errors that occur during the execution of the statements and commands in the executable section.
Code block is terminated by the END keyword, the only construct keyword that is followed by a semicolon. Executable is the only required section, which means the code block must have BEGIN and END. The code block is executed by /
declare
local_variable varchar2(30);
begin
select 'Num of employees'|| to_char(count(last_name), '999')
into local_variable
from employee;
dbms_output.put_line(local_variable);
exception
when others then dbms_output.put_line('ERROR OCCURED');
end;
/
The select command is used to compute the number of employees in the database, concatenate this value with another string and assign the value to variable defined in declare section. The final section is the output section. It contains a catchall error handler, the statement which is executed when a database error occurs and when an error handler is not available.
Variable Definition:
Variables are defined as :
Variable_Name Datatype(precision) ;
in Declaration section. Oracle treats variable definition as any other statement so a semi colon is mandatory at the end. Value may be assigned to a variable during definition as well. The variable may also be constrained.
Character Definitions:
Variables that contain alphanumeric values are normally defined as CHAR or VARCHAR2.
VARCHAR2 : contains null spaces in each position that a character does not occupy. Defining length is mandatory. Maximum length can be 32767.
CHAR: contain white space in the unoccupied positions. Whitespace and null are not the same and are not
comparable. Length when not specified is 1. maximum can be 32767.
Note: PL/SQL length of CHAR and VARCHAR2 datatypes is larger than the length allowed in the Oracle database.
Numeric Definitions:
Numeric data definitions can include two parameters: precision and scale. Precision defines overall length of the value. Scale determines the number of digits to the left or the right of the decimal point. The range of the scale is -84 to 127. When a scale is specified, rounding will occur at the end. Following rules apply:
a. Positive scale definitions cause rounding to the right of the decimal point.
b. Negative scale definitions cause rounding to the left of the decimal point.
c. Zero scale definitions cause rounding to the nearest whole number.
The default precision of a number is 38.
e.g. age integer(3); : age must be a whole number less than 999
galloons number(3); the scale is not specified, the value can contain upto three places to the left of decimal or upto three positions. The overall length of the value can't be more than three positions.
salary number(8, 2) ; The overall length of value can't be more than 8 positions including decimal values. At max the variable will have two positions to the left of decimal.
Other datatypes: Several other datatypes including Boolean, Date, Exception can be used.
Constrained Definitions:
Constraints can be applied on the variables defined in the code block. Two common constraints are :
CONSTANT: ensures that the value is not changed after a value is initially assigned to the variable. If a statement tries to change the variable value, an error will occur.
NOT NULL: ensures that the variable will always contain a not null value. If a statement tries to assign a null value to the variable, an error will occur.
e.g
febonaci constant number(9,8): =12.333223;
resonate not null date:='08-APR-53'
Aggregate and PL/SQL Record Definitions
Aggregate variable definition is based on a database of PL/SQL object. It has following advantages:
a. The developer can define a variable with the same data specifications as a table column or cursor variable,
without actually knowing the specifications.
b. The developers can set up an array of variables for a cursor or table record with one statement. The
variables will have the same specifications as the table or cursor variables.
The first aggregate definition tool is %TYPE which allows developer to define a variable with the same specifications as the indicated table or cursor variable. Syntax:
VARIABLE_NAME table_cursor_name.columns_name%type;
e.g.
lname employee.last_name%type;
the second keyword is used to establish an array of variables based on the columns in a cursor or table. This aggregate is called a PL/SQL record. The keyword will create a variable for each column in the identified table/cursor. Each variable in the array will have the same name and specification as its counterpart in the table/cursor.
syntax: ARRAY_NAME table/cursor_name%rowtype;
Here is an e.g. for aggregate array definition:
DEPT_VAR department%rowtype;
variables are acccessed as :
DEPT_VAR.department
DEPT_VAR.department_name
Assigning values to Variables
PL/SQL has two ways to assign values to variables:
:= assignment operator assigns the argument of the left of the operator to the argument of variable on
the right of the sign.
INTO keyword is used in a SELECT or FETCH statement. When used in a
SELECT statement, it assigns the values in the SELECT clause to the variables
following the INTO keyword. When used with the FETCH statement, it
assigns the cursor values to the variables that follow the INTO keyword.
When using the INTO keyword in a SELECT clause, be sure that only one record is retrieved. If more than one record is retrieved an, error will occur.
