Oracle_pl/sql (1) anonymous block

1. PL/SQL Introduction
PL/SQL is a more complex programming language for accessing Oracle databases from a variety of environments.
Why use PL/SQL?
Orade is a relational database in which the language used to access a relational database is "structured Inquiry language" SQL.
SQL is a fourth-generation language that only describes what to do and does not describe how to do it.
Generation Language: 01
Second generation language: compilation
Three languages: Process (conditional judgment, cycle)
Four-generation languages: SQL DELETE from emp where major= ' MANAGER ';
The fourth generation language is relatively simple, providing fewer commands that isolate the user from the underlying basic data structures and algorithms.
But the third generation language lacks the processing of the process (conditional judgment, loop).
PL/SQL (procedural language/sql), procedural, and efficient by adding procedural control structures
Expanded the functionality of SQL.

2. PL/SQL Basics
2.1 PL/SQL block
The basic single bit in PL/SQL is "block".
All PL/SQL programs are composed of blocks,
These blocks can appear sequentially, or they can be nested with each other.
Classification:
Anonymous BLOCK: No Name, can only be executed once.
Function:
Stored procedures:
Package:
Trigger:

Anonymous block Syntax:
Declare
Statement part;
Begin
Executive section;
exception
Exception handling Section;
End

A first example:
Begin
Null
--dbms_output.put_line (' Hello ');
End

2.2 Lexical units
A lexical unit is a sequence of characters, including:
Uppercase and lowercase letters, a~z and A~z
Digital, 0~9
Non-displayed null characters, spaces, carriage returns, TAB key
Math symbols, +-*/> < =
Interval symbol, () []? % # ! ;
These symbols can be used in Plsql,
Case-insensitive in plsql (except for strings within quotation marks).

2.2.1 Identifier
Identifiers are used to name Plsql objects (variables, cursors, types, functions, stored procedures, packages, triggers, and so on).
Must start with a letter, including letters, numbers, _, #, $, and the rest of the characters are illegal.
The identifier is up to 30 characters long.
Variable name: v_***
Parameter name: p_***
Function Name: func_***
Procedure name: proc_***
Package naming: pkg_***
Trigger naming: tri_***

Reserved words: Identifiers used by Oracle are called reserved words and cannot be used as identifiers.
Tag identifiers: Identifiers in double quotation marks are referred to as index identifiers, and the index identifiers are case-sensitive.
2.2.2 Delimiter
Arithmetic operators: +-*/
Logical operators: = > < >= <=! = <>
Comment:-*/* *
String Connector: | |
Assignment:: =

2.2.3 Text
A literal is a character, number, or Boolean value that cannot be an identifier.
For example: ' ABC ', 23,true,false,null

2.2.4 Notes
Annotations enhance the readability of the program and make the program easier to understand.
Comments are ignored by the compiler at compile time.
Single-line Comment:--
Multiline Comment:/* */

2.3 Variable Declaration
Variables are memory units that can store data in variables.
Variables are defined in the declaration portion of the block.
Each variable has a specific type and length.

2.3.1 Declaration Syntax
Variable name type (length) [NOT NULL] [: = default value];
Example:
Declare
V_number number:=45;
Begin
Dbms_output.put_line (V_number);
End
String length limit in range (1...32767)
Declare
V_char varchar2 (20);
Begin
v_char:= ' ABCDE ';
Dbms_output.put_line (V_char);
End

Declare
V_date date;
Begin
V_date:=sysdate;
Dbms_output.put_line (v_date);
End

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype.
Oracle recommends that's use VARCHAR2 rather than VARCHAR.
In the future, VARCHAR might is defined as a separate datatype used
For variable-length character strings compared with different comparison semantics.

2.3.2 Variable Initialization
If you define a variable without giving the initialization value, the default assignment is null.
Declare
V_char varchar2 (20);
Begin
Dbms_output.put_line (V_char);
End
If not NULL is specified at the time of declaration, then the variable must be initialized.
Declare
V_char varchar2 () not null;
Begin
Dbms_output.put_line (V_char);
End

If constant is specified at the time of declaration, then the variable must be initialized and cannot be modified.
Declare
V_CHAR constant VARCHAR2 (): = ' plsql ';
Begin
Dbms_output.put_line (V_char);
End

Declare
V_CHAR constant VARCHAR2 (): = ' plsql ';
Begin
v_char:= ' ABCDE ';
Dbms_output.put_line (V_char);
End

You can replace an assignment symbol with default in a declaration: =
Declare
V_char varchar2 () default ' ABCD ';
Begin
v_char:= ' ABCDE ';
Dbms_output.put_line (V_char);
End
In a declaration, you can declare only one variable per line.
Declare
V_char,v_char2 varchar2 (20);
Begin
v_char:= ' ABCDE ';
Dbms_output.put_line (V_char);
End

Declare
V_char varchar2 (20);
V_char2 varchar2 (20);
Begin
v_char:= ' ABCDE ';
Dbms_output.put_line (V_char);
End

2.4 PL/SQL type
2.4.1 Using%type
Variables can be used to store data in a table,
The variable should have the same data type as the column in the table.
Declare
V_ename VARCHAR2 (4);
Begin
Select Ename to V_ename from EMP where empno=7788;
Dbms_output.put_line (V_ename);
End

Declare
V_ename Emp.ename%type;
Begin
Select Ename to V_ename from EMP where empno=7788;
Dbms_output.put_line (V_ename);
End

2.4.2 using%rowtype
Declare
V_emp Emp%rowtype;
Begin
SELECT * into V_emp from EMP where empno=7788;
Dbms_output.put_line (V_emp.ename);
End

Scope and visibility of 2.4.3 variables
The scope of a variable is the part of the program that can access the variable.
For a plsql variable, the scope is from the beginning of the variable declaration until the end of the block.
When a variable goes out of scope, the system automatically reclaims the variable's memory space.

2.5 Expressions and operators
2.5.1 Assignment Value
Variable: = expression;
Out Now: = left is called the left value, which appears on the right side of the: = right value.
The left value must be point to the actual storage unit, which is the memory location where the right value is written.
All the left values are variables.
Declare
V_number number;
Begin
v_number:=5*2;
Dbms_output.put_line (V_number);
End
Each line statement can only assign a value to one variable.
Declare
V_number number;
V_number2 number;
Begin
v_number:=v_number2:=5*2;
Dbms_output.put_line (V_number);
End

2.5.2-expression
The plsql expression is a right-hand value.
The expression is not valid for use as a single statement.
An expression can only be part of a statement.
Declare
V_number number;
Begin
5*2;
Dbms_output.put_line (V_number);
End

Oracle_pl/sql (1) anonymous block