PL/SQL Control Statements and introduction

----------------------------the composition of PL/SQL------------------------------------------------------------
The PL/SQL block consists of 3 parts: Declaration part, execution part, exception handling part
[DECLARE]
--declaration section: the variables, types and cursors used in this declaration, and local stored procedures and functions
Begin
--Execution part: process and SQL statement, which is the main part of the program, cannot be omitted
[Exception]
--Exception Handling section: Error handling
End
----------------------------Declaration of PL/SQL variables and Constants--------------------------------------------------
Variable: variable_name?data_type[(size)][:=?init_value];
--variable_name represents the variable name
--data_type represents a variable's SQL or/PL data type
--SIZE specifies the range of variables.
--init_value the initial value of the specified variable

--Constant: Variable_name? Constant?data_type?:=?value; (Note constants must be assigned initial values)

----------------------------PL/SQL variable assignment----------------------------------------------------------
Method one: By assigning a value to a variable by Select?into, the query results return only one piece of data and assign a value to the variable to be saved, returning multiple or 0 data to the error.
DECLARE
V_ename VARCHAR2 (20);
V_rate number (7,2);
V_no?number;
C_RATE_INCR CONSTANT Number (7,2): = 1.10;
BEGIN
SELECT ename,sal*c_rate_incr to v_ename,v_rate from EMP
WHERE empno=7369;
--Method Two: Assigning a value to a variable by assigning the operator ": =" myseq.nextval The new feature of index value 11g
v_ename:= ' SCOTT ';
V_no:=myseq.nextval;
END;
---------------------------requirements and restrictions for identifiers defined in PL/sql:-----------------------------------------
1) An identifier name cannot exceed 30 characters.
2) The first character must be a letter.
3) is not case sensitive.
4) You cannot use the "-" minus sign.
5) You cannot use a reserved word for SQL.
Program Variable v_ start
Program Constants C_
Cursor variable Cursor_
Exception Flag E_
--------------------------encoding Rules in PL/SQL----------------------------------------------------------
(1) Use indentation to show the logical structure. The column that appears after the word is reserved begins to indent three spaces, such as declare, which declares a variable row.
(2) Use case-sensitive to enhance readability. reserved word unified capitalization;? The application-specific name or identifier is in uniform lowercase.
(3) format a separate statement. Write at most one statement per line;? The declaration statement keeps the declared data type close to the variable name as much as possible, rather than aligning with the data type.
(4) Format the SQL statement. A reserved word in the clause of the Right-aligned DML statement.

--------------------------Comment-----------------------------------------------------------------------
1) Use a double "-" minus comment, only valid on one line, such as:
V_sal? Number (12,2);? --Salary variables for personnel
2) Use "/*?*/" to add one or more lines of comments, it is recommended to use the following methods
/*
|| The first line of the comment is preceded by a diagonal asterisk, and the flag comment begins
| |, then the comment block each line begins with a double vertical line, highlighting the subsequent comment content. Can not write, but for readability
*/
-------------------------PL/SQL data type--------------------------------------------------------------
1. Scalar data type, containing a single value, without an internal component. Includes numbers, characters, Boolean values, and datetime values.
-For example: Char,varchar2,date,boolean, etc...
2.LOB (Large?) Object) data type, which is used to store large data object types, mainly support bfile, blobs, CLOB, NCLOB types.
3. Property type, which is used to reference the data type of a variable or database column, and the record type that represents a row in a table.
1)%TYPE
--Define a variable whose data type is consistent with the data type of one of the data variables already defined, especially one column of the table.
2)%rowtype
--Returns a record type whose data type is consistent with that of the database table.
Usage:
DECLARE
V_empno emp.empno%type:=7369;
V_rec Emp%rowtype;
BEGIN
SELECT * to V_rec from EMP
WHERE Empno=v_empno;
Dbms_output. Put_Line (' Name: ' | | v_rec.ename| | ' Salary: ' | | V_rec.sal);--Output one line, only in PL/SQL blocks
END;
-----------------------PL/SQL control statements----------------------------------------------------------------
1. Conditional control, used to execute a series of statements based on conditions. Conditional controls include the IF statement and the case statement.
-----------1) If statement syntax:

if?< Boolean expression;? Then--if
??? --pl/sql and SQL statements
END? IF;
--Example:
BEGIN
IF 1=1 Then
Dbms_output. Put_Line (' pig ');
END IF;
END;
-------------
if?< Boolean expression;? Then--if-else
--pl/sql and SQL statements
ELSE
--Other statements
END? IF;
--Example:
BEGIN
IF 1=2 Then
Dbms_output. Put_Line (' pig ');
ELSE
Dbms_output. Put_Line (' dog ');
END IF;
END;
-------------
if?< Boolean expression;? Then--if-else?if-else
--pl/sql and SQL statements
elsif?< other expressions;? Then--? this is elsif? Instead of ElseIf, notice!!!
--Other statements
elsif?< other expressions;? Then
--Other statements
ELSE
--Other statements
END? IF;
--Example:
BEGIN
IF 1=2 Then
Dbms_output. Put_Line (' King Coco ');
Elsif 1=3 Then
Dbms_output. Put_Line (' yes ');
ELSE
Dbms_output. Put_Line (' Green Pond ');
END IF;
END;

-----------2) Case statement syntax:

--Format One
Case? Conditional expression
When? conditional expression result 1? Then? statement Segment 1
When? conditional expression result 2? Then? Statement Segment 2
.....
When? conditional expression result n? Then? statement Segment N
[ELSE statement Segment]
END? case;
--Example:
DECLARE
V_char VARCHAR2 (20);
BEGIN
Case ' l '
When ' A '
Then v_char:= ' excellent ';
When ' B '
Then v_char:= ' good ';
When ' C '
Then v_char:= ' poor ';
ELSE
v_char:= ' no words ';
END case;
Dbms_output. Put_Line (V_char);
END;
--Format Two
Case
When? conditional expression 1? Then? statement Segment 1
When? conditional expression 2? Then? Statement Segment 2
.....
When? conditional expression n? Then? statement Segment N
[ELSE statement Segment]
END? case;
--Example:
DECLARE
V_char VARCHAR2 (20);
BEGIN
Case
When 1>2
Then v_char:= ' excellent ';
When 1<2
Then v_char:= ' good ';
When 1>8
Then v_char:= ' poor ';
ELSE
v_char:= ' no words ';
END case;
Dbms_output. Put_Line (V_char);
END;

2. Loop control, a series of statements for repeated execution. Including the loop and exit statements, you can exit the loop immediately by using the Exit statement; Use Exit? When
A statement can end a loop based on a condition. There are 3 types of loops, including loop loops, while loops, and for loops.

----------1) Loop syntax:
LOOP
The statement to execute;
EXIT? when?< conditional statement;?? --Condition satisfied, exit Loop statement
END? LOOP;
--Example:
DECLARE
V_count number:=0;
BEGIN
LOOP
v_count:=v_count+1;
Dbms_output. Put_Line (V_count);
EXIT when v_count=5;
END LOOP;
END;

----------2) While loop syntax:
while?< Boolean expression;? LOOP
The statement to execute;
END? LOOP;
--Example:
DECLARE
V_count number:=0;
BEGIN
While V_count<5 LOOP
v_count:=v_count+1;
Dbms_output. Put_Line (V_count);
END LOOP;
END;

----------3) for loop syntax:
For? Loop counter? In? [REVERSE]? Lower limit?....? Ceiling? LOOP
The statement to execute;
END? LOOP;?
Example:
DECLARE
V_num number:=1;
BEGIN
For V_num in REVERSE 1..10 LOOP--reverse from big to small
Dbms_output. Put_Line (V_num);
END LOOP;
END;

3. Sequential control to execute statements sequentially. The order control includes a null statement and a goto statement. The goto statement is not recommended.
Null statement: An executable statement that is equivalent to a placeholder or an empty statement that does not perform any action, which makes sense for some statements, improves the process
To ensure the integrity and correctness of other statement structures.
--Example:
DECLARE
V_count Number: = 5;
BEGIN
Dbms_output. Put_Line (' Current value is: ' | | V_count);
IF V_count >=
NULL;
ELSE
V_count: = V_count +10;
Dbms_output. Put_Line (' Change value: ' | | V_count);
END IF;
END;

PL/SQL Control Statements and introduction