SQL Records-plsql basic syntax and data types

Source: Internet
Author: User
Tags exception handling month name numeric value reserved scalar

PL/SQL Basic syntax

PL/SQL is a block-structured language, which means that PL/SQL programs are partitioned and written into logical blocks of code. Each block consists of three sub-parts:

S.N. paragraph and Description
1 Statement
This section starts with the keyword declare. It is an optional part and restricts all variables, cursors, subroutines, and other elements used in the program.
2 Executable commands
This section is the closing keyword begin and end, which is a mandatory part. It consists of the PL/SQL statement of the program's executable file. It should have at least one executable line of code, which may simply be an empty command to indicate that nothing is being done.
3 Exception handling
This section starts with the keyword exception. This section is optional and contains an exception in the program handling error.

Each PL/SQL statement ends with a semicolon (;). can be nested in PL/SQL blocks that use the begin and end, and so on. Here is the basic structure of a PL/SQL block:

DECLARE   <declarations section>BEGIN<executable command(s ) ><exception handling>END;         
' Hello World ' example:
DECLARE   message  varchar2: =' Hello, world! ' ; BEGIN dbms_output.  Put_Line(message);  END;  /

End The line signal is the end of a PL/SQL block. To run the SQL command line code, you may need to enter the beginning of the first blank line before the last line of the code. When the above code is executed at the SQL prompt, it produces the following results:

Hello Worldpl/sql procedure successfully completed.
PL/SQL identifiers

PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers are followed by a letter that can be followed by more letters, numbers, dollar signs, underscores, and number symbols, and must not exceed 30 characters.

By default, identifiers are case insensitive. Therefore, you can use an integer or integer to represent a numeric value. You cannot use reserved keywords as identifiers.

PL/SQL Separators

A delimiter is a symbol with a special meaning. The following is a list of the PL/SQL separators:

Separators Description
+, -, *, / addition, subtraction/negation, multiplication, division
% Property Index
String delimiter
. Component Selection
(,) An expression or list separator
: Host variable Indicator
, Item Separator
" Quoted identifier delimiter
= Relational operators
@ Remote Access Indicator
; Declaring a terminating character
:= Assignment operators
= Operator Association
|| Join operators
** Exponentiation operator
<<, >> Label delimiter (start and end)
/*, */ Multiline comment delimiter (start and end)
-- Single-line Comment indicator
.. Range operations
<, <=, >= Relational operators
<>, ' =, ~=, ^= Different versions of unequal
PL/SQL annotations

Program comments are explanatory notes that can include your own writing and help anyone read the PL/SQL code of the source code. All programming languages allow some form of annotation.

Support single-line and multiline annotations in PL/SQL. All characters in any comment will be ignored by the PL/SQL compiler. Use the delimiter-(double hyphen) and multiline comment in the PL/SQL single-line comment by the/* and/*.

DECLARE   --Variable declaration   message  varchar2: =' Hello, world! ' ; BEGIN/* PL/SQL executable statement (s) */ dbms_output.  Put_Line(message);  END;  /

When the above code is executed at the SQL prompt, it produces the following results:

Hello Worldpl/sql procedure successfully completed.
PL/SQL Program Unit

The PL/SQL unit is any one of the following:

    • PL/SQL block

    • Function

    • Package

    • Inclusion

    • Process

    • Trigger

    • Type

    • Type body

PL/SQL data type

PL/SQL variables, constants, and parameters must have a valid data type, specifying a valid range of storage formats, constraints, and values. This tutorial introduces scalar and LOB in PL/SQL, and the data types available for the other two data types will be covered in later chapters.

category Description
Scalar Single value does not have an internal component, such as number, DATE, or BOOLEAN
Large Object (LOB) Store large objects that point to other data items such as text, graphics images, video clips, and sounds
Composite A data item with an internal component that can be accessed separately. For example, collections and records
Reference Point to other data items
PL/SQL Scalar data types and sub-types

PL/SQL Scalar data types and sub-types are subject to the following major categories:

Data Type Description
Digital The numeric value of the arithmetic operation performed on it
Character An alphanumeric string value that represents a single character or character
Boolean Logical value of the logical operation on which to execute
Date Time Date and time

The subtype of the data type provided by PL/SQL. For example, the number of data types has a subtype called integer. You can use subtypes in a PL/SQL program to make data types compatible with data types in other programs, while embedding PL/SQL code in another program, such as a Java program.

PL/SQL numeric data type and subtype

The following is the detailed information for the PL/SQL predefined numeric data types and their subtypes:

Data Type Description
Pls_integer Signed integers in the range 2,147,483,647 to 2147483648, denoted by 32 bits
Binary_integer A signed integer in the range of 2,147,483,647 to 2147483648, represented by 32 bits
Binary_float Floating-point number in single-precision IEEE 754 format
Binary_double Double-precision floating-point number in IEEE 754 format
Number (PREC, scale) Fixed-point or floating-point number in the range 1E-130 to (but not including) the absolute value 1.0E126. The number variable can also represent 0
DEC (PREC, scale) ANSI specific fixed-point type uses 38-bit decimal maximum precision
DECIMAL (PREC, scale) IBM-specific fixed point type with 38 decimal places maximum precision
NUMERIC (pre, Secale) Floating-point types have a maximum precision of 38 decimal places.
DOUBLE PRECISION With 126 binary digits maximum precision ANSI-specific float (approx. 38 decimal digits)
FLOAT With 126 binary digits (approximately 38 decimal digits) Maximum precision ANSI and IBM-specific floating-point type
Int With 38 decimal places maximum precision ANSI specific integer type
INTEGER 38-bit decimal maximum precision specific integer type for ANSI and IBM
SMALLINT 38-bit decimal maximum precision specific integer type for ANSI and IBM
REAL Maximum precision floating point with 63-bit binary digits (approximately 18 decimal places)

The following is a valid declaration:

DECLARE   num1 INTEGER;   Num2 REAL;   num3 DOUBLE PRECISION; BEGIN   null; end;/

Let's compile and run the above program, which will produce the following results:

PL/SQL procedure successfully completed
PL/SQL character data type and subtype

The following is a detailed information about the predefined character data types and their subtypes of PL/sql:

Data Type Description
CHAR A fixed-length string with a maximum size of 32,767 bytes
VARCHAR2 Maximum size variable length string with 32,767 bytes
RAW The maximum size of a variable-length binary or byte string of 32,767 bytes, rather than PL/SQL interpretation
NCHAR A fixed-length country string with a maximum size of 32,767 bytes
NVARCHAR2 Maximum size variable length country string with 32,767 bytes
LONG Has a 32,760-byte maximum size variable length string
LONG RAW The maximum size of a variable-length binary or byte string of 32,760 bytes, rather than PL/SQL interpretation
ROWID Physical row identifier, one row of addresses in a normal table
Urowid Physical Universal Row Identifier (physical, logical, or foreign row identifier)
PL/SQL Boolean data type

A logical value stored using a Boolean data type in a logical operation. The logical values are Boolean values that are true and false, and NULL values.

However, SQL does not have a data type equal to Boolean. Therefore, the Boolean value cannot be used for:

    • SQL statements

    • Built-in SQL functions (such as TO_CHAR)

    • To invoke a PL/SQL function from an SQL statement

PL/SQL Date time and interval type

The date data type stores a fixed-length datetime, which includes the time of day from midnight in a few seconds. Valid dates range from January 1, 4712 to 999912 months before 31st.

The default date format is set by the Oracle initialization parameter Nls_date_format. For example, the default can be "Dd-mon-yy", which includes a date in a two-digit month, an abbreviation for the month name, and the last two digits of the year, for example, 01-oct-12.

Each date, including century, year, month, day, time, minute, second. The following table shows the valid values for each field:

Field name valid datetime value valid interval value
Year -4712 to 9999 (excluding year 0) Any non-0 integer
MONTH 12 0 to 11
Day 31 (subject to the values of the year and month, as set by the locale of the calendar) Any non-0 integer
HOUR XX to 23 0 to 23
MINUTE XX to 59 0 to 59
SECOND XX to 59.9 (n), where 9 (n) is the time fractional seconds precision 0 to 59.9 (N), where 9 (n) is the interval fractional seconds precision
Timezone_hour -12 to 14 (range accommodates daylight saving time changes) Not applicable
Timezone_minute XX to 59 Not applicable
Timezone_region Find V$timezone_names in Dynamic performance view Not applicable
Timezone_abbr Find V$timezone_names in Dynamic performance view Not applicable
PL/SQL Large object (LOB) data type

Large object (LOB) data types are large to data items such as text, graphic images, video clips, and sound waveforms. LOB data types allow for efficient, random, segmented access to these data. The following are the predefined PL/SQL LOB data types:

Data Type Description size
BFILE System files for storing large binary objects outside the operational database dependent on system, cannot exceed 4,000 gigabytes (GB)
Blob Used to store large binary objects in the database 8 to 128 terabytes (TB) of
Clob Used to store characters in large chunks of data in a database 8-128 TB
NCLOB Used to store large chunks of nchar data in a database 8-128 TB
PL/SQL user-defined sub-types

Subtypes are another type of data, which is a subset of the so-called basic types. Subtypes have the same operations as their basic types, but they are only a subset of valid values.

PL/SQL Pre-defines the encapsulation criteria for some subtypes. For example, PL/SQL pre-defined subtypes character and integers are as follows:

Subtype CHARACTER is CHAR; Subtype INTEGER is number (38,0);

You can define and use your own subtypes. The following program description defines and uses a user-defined subtype:

DECLARE   subtype name is char ();   Subtype message is VARCHAR2 (+);   Salutation name;   Greetings message; BEGIN   Salutation: = ' Reader ';   Greetings: = ' Welcome to the world of PL/SQL ';   Dbms_output.put_line (' Hello ' | | salutation | | greetings); end;/

When the above code is executed at the SQL prompt, it produces the following results:

Hello Reader Welcome to the world of Pl/sqlpl/sql procedure successfully completed.
PL/SQL NULL

PL/SQL Null value indicates missing or unknown data, clam It is not an integer, character, or any other specific data type. It is important to note that NULL is not the same as an empty data string or a null character value ' \ s '. Null can be assigned, but it cannot be equated with anything, including itself.

SQL Records-plsql basic syntax and data types

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.