This article contains information about PL/SQL Data Types and Variables and Literals.
Oracle PL/SQL Data Types
Data types indicate how data is stored in memory.
PL / SQL has several data types to store data.
PL / SQL data types are divided into four sections: Scalar, Large Object, Composite and Refence.
1)Scalar Data Types in Oracle PL/SQL
Scalar data types is a data type that store a single data such as NUMBER, DATE, BOOLEAN.
Numeric Data Types in Oracle
PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, NUMBER(prec, scale), DEC(prec, scale), DECIMAL(prec, scale), NUMERIC(pre, secale), DOUBLE PRECISION, FLOAT, INT, INTEGER, SMALLINT, REAL
1 2 3 4 5 6 7 8 9 10 11 12 | SET SERVEROUTPUT ON; DECLARE v_number_1 INT := 1; v_number_2 INTEGER := 2; v_number_3 PLS_INTEGER := 3; v_number_4 DOUBLE PRECISION := 4; BEGIN DBMS_OUTPUT.put_line(v_number_1); DBMS_OUTPUT.put_line(v_number_2); DBMS_OUTPUT.put_line(v_number_3); DBMS_OUTPUT.put_line(v_number_4); END; |
Character Data Types in Oracle
CHAR, VARCHAR2, RAW, NCHAR, NVARCHAR2, LONG, LONG RAW, ROWID, UROWID
1 2 3 4 5 6 7 8 9 10 11 12 | SET SERVEROUTPUT ON; DECLARE v_myname_1 CHAR(5) := 'YUSUF'; v_myname_2 VARCHAR2(5) := 'YUSUF'; v_myname_3 NCHAR(5) := 'YUSUF'; v_myname_4 NVARCHAR2(5) := 'YUSUF'; BEGIN DBMS_OUTPUT.put_line(v_myname_1); DBMS_OUTPUT.put_line(v_myname_2); DBMS_OUTPUT.put_line(v_myname_3); DBMS_OUTPUT.put_line(v_myname_4); END; |
Boolean Data Types in Oracle
The BOOLEAN data type takes TRUE, FALSE and NULL.
Datetime Data Types in Oracle
DATE, TIMESTAMP, INTERVAL
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON; DECLARE v_date DATE := SYSDATE; v_date_hour TIMESTAMP := SYSTIMESTAMP; v_month INTERVAL YEAR (2) TO MONTH := INTERVAL '15' MONTH; BEGIN DBMS_OUTPUT.put_line(v_date); DBMS_OUTPUT.put_line(v_date_hour); DBMS_OUTPUT.put_line(v_month); END; |
2)Large Object Data Types in Oracle PL/SQL
Large Object data types are data types that store files such as text, images, and videos.
BFILE, BLOB, CLOB, NCLOB
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON; DECLARE v_file_name VARCHAR2(255) := 'elma.jpg'; v_file BFILE := bfilename('BLOB_DIR', v_file_name); BEGIN NULL; END; |
3)Composite Data Types in Oracle PL/SQL
Composite data types are data types such as Collections and Records.
4)Reference Data Types in Oracle PL/SQL
The reference data type is the data type that refers to the previously defined data types.
NOTE: Some data types have the same feature.
User Defined Data Types in Oracle PL/SQL
We can define data types as follows.
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON; DECLARE SUBTYPE name_surname IS VARCHAR2(80); yusuf_sezer name_surname := 'Yusuf Sefa SEZER'; BEGIN DBMS_OUTPUT.put_line(yusuf_sezer); END; |
Unnecessary length of data is prevented by using user-defined data types.
Also, developed PL/SQL programs are provided to be more meaningful.
Variables in Oracle PL/SQL
PL/SQL allows the use of variables to temporarily store data.
We can define a variable as follows.
1 | variable_name [CONSTANT] data_type [NOT NULL] [:= | DEFAULT first_value_of_variable] |
Variables can be up to 30 characters long.
name_surname, v_name_surname
Variables must start with the ASCII character.
For example, a variable name cannot be 1_name_surname.
1 | v_name_surname VARCHAR2(80); |
The keyword := and DEFAULT can be used to give initial values to variables.
1 | v_name_surname VARCHAR2(80) := 'Yusuf SEZER'; |
1 | v_name_surname VARCHAR2(80) DEFAULT 'Yusuf SEZER'; |
The NOT NULL keyword can be used so that variables do not take NULL.
1 2 3 4 5 6 7 8 | SET SERVEROUTPUT ON; DECLARE v_name_surname VARCHAR2(20) NOT NULL := 'Yusuf Sefa SEZER'; BEGIN --v_name_surname := ''; -- it will fail. --v_name_surname := NULL; -- it will fail. DBMS_OUTPUT.put_line(v_name_surname); END; |
The data type of the variable can be the data type of the column in the table.
1 | v_name_surname table.column%TYPE; |
The CONSTANT keyword can be used to make the variable constant.
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON; DECLARE PI CONSTANT NUMBER := 3.141592654; BEGIN --PI := 1; -- it will fail. DBMS_OUTPUT.put_line(PI); END; |
Literals in Oracle PL/SQL
Literal values in PL/SQL are called literal.
‘Hello Oracle’
TRUE, FALSE, NULL
‘A’
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
Also you can find more about data types at docs.oracle.com