PL / SQL数据类型
PL/SQL Data Types
Every programming language has built-in data types which are used for declaring variables and many other related tasks. Variable is nothing but the name of the memory space. Every variable has a data type which specifies the storage format, set of specified values, range of the variable and many other such factors.
Variables have to be defined in the declaration section of a PL/SQL program. However, its not mandatory to include a declaration section and variable declaration in a PL/SQL program if its not required.
The Data Types used in PL/SQL are as follows:
|
| Image Source |
1. Scalar: These data types don’t include any internal components. It includes data types such as NUMBER, DATE, BOOLEAN, etc.
2. Large Objects (LOB): This type of data type stores objects that are relatively large in size and stored separately from other data types such as text, graphic images, video clips, sound, etc.
3. Composite: These type of data types have internal components that can be accessed individually. It includes records and collections.
4. Reference: As the name sounds, it includes pointers that refer to the location of the other data items.
Now, I have listed down some of the sub data types frequently used in PL/SQL
1. Numeric: Numeric values on which arithmetic operations are performed. It includes sub types such as number, decimal, real, float, etc.
2. Character: Character values on which character operations such as strings are performed. It includes sub types such as char, varchar, varchar2, nvarchar2, etc.
3. Data and Time: This data type is used to store fixed data type which displays and saves time and date values. The default data format saved into the database might be ‘DD-MM-YY’. However, you can change and alter the position of the terms accordingly.
4. Boolean: These include logical values on which logical operations are performed. The logical values are the Boolean values TRUE and FALSE and the value NULL. But, SQL has no data type equivalent to BOOLEAN. It cannot be used in SQL Statements, built in SQL functions such as To_char, PL/SQL function invoked from DQL commands.
5. Number: Syntax: Number(Precision, Scale). Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.
6. Float: ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits).
7. Integer: ANSI and IBM specific integer type with maximum precision of 38 decimal digits
8. Real: Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits).
9. Varchar2: Variable-length character string with maximum size of 32,767 bytes.
10. Rowid: Physical row identifier, the address of a row in an ordinary table.
11. Bfile: Used to store large binary objects in operating system files outside the database System-dependent. Cannot exceed 4GB.
12. Blob: Used to store large binary objects in the database. Memory Capacity: 8 to 128 TB.
13. Clob: Used to store large blocks of character data in the database. Memory Capacity: 8 to 128 TB.
14. Nclob: Used to store large blocks of NCHAR data in the database. Memory Capacity: 8 to 128 TB.
Note: A variable in PL/SQL program code can be assigned a NULL value i.e., ‘’. However, it cannot be equated with any other variable or even itself.
Variable Declaration
Syntax Variable-Name DataType(Precision/Dimension)
Example Emp_name varchar2(30)
Here, Emp_name is the variable name and varchar2 is the data type with the 30 as the precision or the dimension. It is allowed to assign values during declaration of a variable in the declaration block. Setting the precision as 30 allows us to assign a string value of not more than 30 characters for that particular variable.
There are many more PL/SQL data types used in PL/SQL programming language. Since, this tutorial is especially for beginners, we just want you to first grasp the basic understanding and then jump into deeper details. These mentioned are sufficient for you to get started into PL/SQL programming. However, we shall mention new data types at regular intervals later.
If you find anything incorrect In above tutorial then please mention it by commenting below. You can also ask your queries.
PL / SQL数据类型
每种编程语言都有内置的数据类型，这些数据类型用于声明变量和许多其他相关任务。 变量不过是存储空间的名称。 每个变量都有一个数据类型，该数据类型指定存储格式，一组指定值，变量范围以及许多其他因素。
变量必须在PL / SQL程序的声明部分中定义。 但是，如果不是必需的，则不必在PL / SQL程序中包括声明部分和变量声明。
PL / SQL中使用的数据类型如下：
| 图片来源 |
1.标量：这些数据类型不包含任何内部组件。 它包括诸如NUMBER，DATE，BOOLEAN等数据类型。
2.大对象(LOB)：这种类型的数据类型存储的对象相对较大，并且与其他数据类型(例如文本，图形图像，视频剪辑，声音等)分开存储。
3.组合：这些类型的数据类型具有可单独访问的内部组件。 它包括记录和收藏。
4.参考：听起来很像，它包括指向其他数据项位置的指针。
现在，我列出了PL / SQL中经常使用的一些子数据类型
1.数值：对其执行算术运算的数值。 它包括子类型，例如数字，小数，实数，浮点数等。
2.字符：在其上执行字符操作(例如字符串)的字符值。 它包括子类型，例如char，varchar，varchar2，nvarchar2等。
3.数据和时间：此数据类型用于存储显示和保存时间和日期值的固定数据类型。 保存到数据库中的默认数据格式可能是“ DD-MM-YY”。 但是，您可以相应地更改和更改条款的位置。
4.布尔值：这些值包括对其执行逻辑操作的逻辑值。 逻辑值是布尔值TRUE和FALSE和值NULL。 但是，SQL没有等效于BOOLEAN的数据类型。 它不能在SQL语句中使用，SQL语句内置了SQL函数，例如To_char，从DQL命令调用的PL / SQL函数。
5.数字：语法：数字(精度，小数位数)。 绝对值在1E-130到(但不包括)1.0E126范围内的定点或浮点数。 NUMBER变量也可以表示0。
6.浮点数： ANSI和IBM特定的浮点类型，最大精度为126个二进制数字(约38个十进制数字)。
7.整数： ANSI和IBM特定的整数类型，最大精度为38个十进制数字
8.实数：浮点类型，最大精度为63个二进制数字(约18个十进制数字)。
9. Varchar2：可变长度字符串，最大大小为32,767字节。
10. Rowid：物理行标识符，是普通表中一行的地址。
11. Bfile：用于将大型二进制对象存储在依赖于系统的数据库外部的操作系统文件中。 不能超过4GB。
12. Blob：用于将大型二进制对象存储在数据库中。 内存容量：8到128 TB。
13. Clob：用于在数据库中存储大块字符数据。 内存容量：8到128 TB。
14. Nclob：用于在数据库中存储NCHAR数据的大块。 内存容量：8到128 TB。
注意： PL / SQL程序代码中的变量可以分配一个NULL值，即“''。 但是，它不能等同于任何其他变量甚至本身。
变量声明
语法 变量名称数据类型(精度/尺寸)
示例 Emp_name varchar2(30)
在这里，Emp_name是变量名称，而varchar2是数据类型，其精度或维数为30。 允许在声明块中声明变量期间分配值。 将精度设置为30允许我们为该特定变量分配不超过30个字符的字符串值。
PL / SQL编程语言中使用了更多PL / SQL数据类型。 由于本教程特别适合初学者，因此我们只希望您首先掌握基本的知识，然后再深入了解更多细节。 提到的这些内容足以让您开始使用PL / SQL编程。 但是，稍后我们将定期提及新的数据类型。
如果您发现任何不正确的内容，请在下面的评论中提及。 您也可以询问您的问题。
翻译自: https://www.thecrazyprogrammer.com/2015/05/plsql-data-types.html