Oracle PL SQL 2

ORACLE PL/SQL
Recognize invalid identifiers
Invalid Identifiers:
• The identifier must not start with a digit or special character (except underscore).
• The identifier must not contain spaces or any special characters other than an underscore.
• The identifier must not exceed 30 characters in length.
• The identifier should not be a reserved PL/SQL keyword or reserved word.
Examples of invalid identifiers:
123employee
department-id
employee name
SELECT
A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL
A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL:
Data types
Data type Description
1. NUMBER This data type is used to store numeric values. It can handle both integer and
floating-point numbers.
2. VARCHAR2 This data type is used to store character strings of varying lengths. The
maximum length allowed is 32767 bytes.
3. CHAR This data type is used to store fixed-length character strings. It is similar to
VARCHAR2, but it pads the string with spaces to the fixed length if the actual
string is shorter.
4. DATE: This data type is used to store date and time values. It can store dates from
January 1, 4712 BC, to December 31, 9999 AD with a precision of up to one
second.
This data type is used to store logical values, such as TRUE or FALSE.
5. BOOLEAN
6. BLOB This data type is used to store binary large objects, such as images, audio files,
or video files.
7. CLOB This data type is used to store character large objects, such as large text
documents.
8. RAW This data type is used to store raw binary data. It can store up to 2000 bytes.
9. LONG This data type is used to store variable-length character strings of up to 2 GB in

size.
10. INTERVAL This data type is used to store intervals of time. It can store the difference
between two dates or timestamps.
11. ROWID This data type is used to store the unique address of a row in a database table.
12. UROWID This data type is used to store the unique address of a row in a database table in
a universal format.
13. XMLType This data type is used to store XML data.
14. RECORD This data type is used to define a structure containing multiple fields. It is
similar to a struct or record in other programming languages.
15. TABLE This data type is used to define collections of values of the same data type. It
can be used to store multiple rows or columns of data.
Declare and initialize variables
In PL/SQL, variables are declared and initialized using the DECLARE statement. The syntax for
declaring and initializing variables is as follows:
DECLARE
variable_name1 data_type := initial_value1;
variable_name2 data_type := initial_value2;
...
BEGIN
-- PL/SQL code
END;
Here's an example of declaring and initializing variables in PL/SQL:
DECLARE
employee_name VARCHAR(50) := 'John Doe';
employee_age NUMBER := 30;
employee_salary NUMBER := 5000.00;
BEGIN
-- PL/SQL code
-- Use the declared variables in your code
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
DBMS_OUTPUT.PUT_LINE('Employee Age: ' || employee_age);
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
END;
/
In the above example, three variables (employee_name, employee_age, and employee_salary) are
declared and initialized with their respective data types and initial values.
Note: The DBMS_OUTPUT.PUT_LINE() statement is used to display the variable values in the output
console. To see the output, you may need to enable the output display in your PL/SQL IDE or use the
SET SERVEROUTPUT ON command before executing the code.
To use the SET SERVEROUTPUT ON command before executing the code in PL/SQL, you need to
follow these steps:
Open your PL/SQL environment (e.g., SQL*Plus, SQL Developer, Toad).
Before running your code, enter the following command:
SET SERVEROUTPUT ON
This command enables the output display for the DBMS_OUTPUT.PUT_LINE() statements in your
PL/SQL code.
Run or execute your PL/SQL code that contains the DBMS_OUTPUT.PUT_LINE() statements.
After executing the code, you should now be able to see the output of the
DBMS_OUTPUT.PUT_LINE() statements in your PL/SQL environment.
An example of how to use the SET SERVEROUTPUT ON command before executing the code in
PL/SQL:
SET SERVEROUTPUT ON
• SET SERVEROUTPUT ON
• DECLARE
• employee_name VARCHAR(50) := 'John Doe';
• employee_age NUMBER := 30;
• employee_salary NUMBER := 5000.00;
• BEGIN
• -- PL/SQL code
• DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
• DBMS_OUTPUT.PUT_LINE('Employee Age: ' || employee_age);
• DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);
• END;
• /

Oracle PL SQL 2

Uploaded by

Copyright:

Available Formats

Oracle PL SQL 2

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle PL SQL 2

Uploaded by

Copyright:

Available Formats

ORACLE PL/SQL

Recognize invalid identifiers

• The identifier must not exceed 30 characters in length.

• The identifier should not be a reserved PL/SQL keyword or reserved word.

Examples of invalid identifiers:

Data type Description

9. LONG This data type is used to store variable-length character strings of up to 2 GB in

13. XMLType This data type is used to store XML data.

Declare and initialize variables

variable_name1 data_type := initial_value1;

variable_name2 data_type := initial_value2;

Here's an example of declaring and initializing variables in PL/SQL:

employee_name VARCHAR(50) := 'John Doe';

employee_age NUMBER := 30;

employee_salary NUMBER := 5000.00;

-- Use the declared variables in your code

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);

DBMS_OUTPUT.PUT_LINE('Employee Age: ' || employee_age);

DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);

Open your PL/SQL environment (e.g., SQL*Plus, SQL Developer, Toad).

Before running your code, enter the following command:

• employee_name VARCHAR(50) := 'John Doe';

• employee_age NUMBER := 30;

• employee_salary NUMBER := 5000.00;

• DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);

• DBMS_OUTPUT.PUT_LINE('Employee Age: ' || employee_age);

• DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_salary);

You might also like