PL/SQL Basic 1 (note)

--Basic structure
DECLARE
--Variable declaration section: this declares the variables, types, cursors, and local stored procedures and functions used in PL/SQL
BEGIN
--Execution part: process and SQL statement, which is the main part of the program
EXCEPTION
--Execution Exception section: Exception Handling
END;
--pl/sql Basic specification: all reserved words, roacle built-in functions, packages, and user-defined data types are capitalized
--pl/sql Basic Specification:: Write only one statement per line, after the comma and before and after the operator should be preceded by a space
--pl/sql Basic Specification:: To be named with a meaningful name:
--pl/sql Basic Specification: How to use "_" instead of mixed-case blending
--pl/sql Basic Specification: It is best to prefix the variables to indicate the data type of the square variable, the scope of action, etc.
--1. When defining variables, we recommend using v_ as a prefix: V_ename
--2. When defining constants, we recommend using C_ as a prefix: c_rate
--3. When defining an exception, it is recommended to prefix it with E_: E_error

--Check the product inventory according to the product ID entered by the user
DECLARE
V_ID Number: = &V_ID; --user Input item ID
V_stockcount number; ---inventory quantity
BEGIN
SELECT Stockcount into V_stockcount from es_product WHERE ID = v_id;
Dbms_output. Put_Line (' Stock: ' | | V_stockcount);

END;

------The type of the upgrade variable and the field type in the table are the same variable name table. field name%type to declare
DECLARE
v_id Es_product.id%type: = &ID;--User input product number
v_name Es_product.name%type;--the variable name is the same as the Name field type of the commodity table
V_price es_product. Price%type; --The variable price is the same as the price field of the commodity table
v_saledate es_product. Saledate%type; --the date of the variable listing is the same as the listing date field of the commodity table
V_stockcount es_product. Stockcount%type; --The variable inventory is the same as the Inventory field type of the commodity table
BEGIN
SELECT NAME, Price, saledate, Stockcount
Into v_name, V_price, V_saledate, V_stockcount
From es_product
WHERE ID = v_id;
Dbms_output. Put_Line (' Product name: ' | | V_name | | ' Commodity price: ' | | V_price | | ' Product shelf time: ' | |
V_saledate | | ' Product stock: ' | | V_stockcount);

END;
-- upgrade uses a variable to represent a row of a table in a field variable name table name%rowtype

--Use the variable name when calling. Field with

DECLARE
V_es_pro es_product%rowtype;--Declares a variable that represents the full row field variable in the table
v_id es_product.id%type:=&id; -----User Input Product number
BEGIN
SELECT Name,price,saledate,stockcount into
V_es_pro.name,v_es_pro.price,v_es_pro.saledate,v_es_pro.stockcount
From Es_product
WHERE id=v_id;
Dbms_output. Put_Line (' Product name: ' | | v_es_pro.name | | ' Commodity price: ' | | V_es_pro.price | | ' Product shelf time: ' | |
v_es_pro.saledate| | ' Product stock: ' | | V_es_pro.stockcount);
END;

--Control statements
IF < Boolean expressions > Then
PL/SQL statements
END IF

IF < Boolean expressions > Then
PL/SQL statements
ELSE
Other Statements
END IF
IF < Boolean expressions > Then
PL/SQL statements
elsif< Boolean expression > Then
Other Statements
elsif< Boolean expression > Then
Other Statements
ELSE
Other Statements
END IF

--Check the product inventory according to the product ID entered by the user
DECLARE
v_id Es_product.id%type: = &V_ID;--user input item ID
V_stockcount es_product. Stockcount%type; ---inventory quantity
BEGIN
SELECT stockcount into V_stockcount from es_product WHERE ID = v_id;
Dbms_output. Put_Line (' Stock: ' | | V_stockcount);
--Determine if the stock is normal
IF v_stockcount > 0 Then
--Update inventory, buy one at a time and lose one
UPDATE es_product SET stockcount = STOCKCOUNT-1
--Update the specified, or it will all be updated
WHERE id=v_id;
--Propose data manipulation
COMMIT;
Dbms_output. Put_Line (' Inventory update Success ');
elsif v_stockcount = 0 Then
Dbms_output. Put_Line (' stock is 0, no inventory ');
ELSE
Dbms_output. Put_Line (' abnormal stock ');
END IF;
END;

--output corresponding status according to the status of the order
SELECT * from Es_order;
DECLARE
v_id Es_order.id%type: = &ID;--The order number of the user input query
v_status Es_order. Status%type; --Order Status
v_stantusname VARCHAR2 ();
BEGIN
SELECT STATUS into V_status from es_order WHERE ID = v_id;
IF v_status = 1 Then
V_stantusname: = ' submitted ';
elsif v_status = 2 Then
v_stantusname: = ' paid ';
elsif v_status = 3 Then
v_stantusname: = ' shipped ';
elsif v_status = 4 Then
v_stantusname: = ' completed ';
ELSE
v_stantusname: = ' unknown state ';
END IF;
Dbms_output. Put_Line (v_stantusname);
END;

Case Syntax:
format one:
Case Conditional Expression
When conditional expression result 1 Then
Statement Segment 1
When conditional expression result 2 Then
Statement Segment 2
....
[ELSE statement segment]
END case;
format two:
Case
When conditional expression 1 Then
Statement Segment 1
When conditional expression 2 Then
Statement Segment 2
....
[ELSE statement segment]
END case;
--Check the product inventory with case to complete according to the product ID entered by the user
--Format one
DECLARE
v_id Es_order.id%type: = &ID;--The order number of the user input query
v_status Es_order. Status%type; --Order Status
v_stantusname VARCHAR2 ();
BEGIN
SELECT STATUS into V_status from es_order WHERE ID = v_id;
Case V_status
When 1 Then
V_stantusname: = ' submitted ';
When 2 then
v_stantusname: = ' paid ';
When 3 then
v_stantusname: = ' shipped ';
When 4 Then
v_stantusname: = ' completed ';
ELSE
v_stantusname: = ' unknown state ';
END case;
Dbms_output. Put_Line (v_stantusname);
END;

--Format two
DECLARE
v_id Es_order.id%type: = &ID;--The order number of the user input query
v_status Es_order. Status%type; --Order Status
v_stantusname VARCHAR2 ();
BEGIN
SELECT STATUS into V_status from es_order WHERE ID = v_id;
Case
When V_status=1 then
V_stantusname: = ' submitted ';
When v_status=2 then
v_stantusname: = ' paid ';
When v_status=3 then
v_stantusname: = ' shipped ';
When v_status=4 then
v_stantusname: = ' completed ';
ELSE
v_stantusname: = ' unknown state ';
END case;
Dbms_output. Put_Line (v_stantusname);
END;

-There are 3 types of loops
Loop Syntax:
LOOP
the statement to execute;
Exit when < conditional statement >---Condition is met, exits the loop statement
END LOOP;

While loop syntax:
while< Boolean expression >loop
the statement to execute;
END LOOP;

for loop syntax;
For loop counter in [REVERSE] lower limit ... Upper Loop
the statement to execute;
END LOOP;
in the For loop once, the variable is automatically added 1, using reverse, the loop variable automatically minus 1, you can also use exit when to exit the loop


SELECT * from Es_sort;
Id,sortname,fatherid of three fields
--Use loop to add 3 records
DECLARE
v_id Es_sort.id%type: = 5;--Assign an initial value to a variable
v_sortname Es_sort. Sortname%type: = ' category '; --Specify the variable type and assign the initial value
V_fatherid Es_sort. Fatherid%type: = 0;
BEGIN
LOOP
INSERT into Es_sort VALUES (v_id, V_sortname | | v_id, V_fatherid);
v_id: = v_id + 1;
--Exit conditions
EXIT when v_id = 9;
END LOOP;
COMMIT;
Dbms_output.put_line (' insert Success ');
END;
--use for loop to complete, know the number of cycles with the for
DECLARE
v_id Es_sort.id%type;--assigning types to variables
v_sortname Es_sort. Sortname%type: = ' category '; --Specify the variable type and assign the initial value
V_fatherid Es_sort. Fatherid%type: = 0;
BEGIN
For v_id in 9..13 LOOP
INSERT into Es_sort VALUES (v_id, V_sortname | | v_id, V_fatherid);
END LOOP;
COMMIT;
Dbms_output.put_line (' insert Success ');
END;
SELECT * from Es_sort;
--use while to complete, do not know the number of times to use
DECLARE
v_id es_sort.id%type:=14;--assigning types and initial values to variables
v_sortname Es_sort. Sortname%type: = ' category '; --Specify the variable type and assign the initial value
V_fatherid Es_sort. Fatherid%type: = 0;
BEGIN
While v_id>= and v_id<=20 LOOP
INSERT into Es_sort VALUES (v_id, V_sortname | | v_id, V_fatherid);
--Variable increase
v_id:=v_id+1;
END LOOP;
COMMIT;
Dbms_output.put_line (' insert Success ');
END;
SELECT * from Es_sort;

--numbers divisible by 3 between outputs 1-100

DECLARE
v_i number;
BEGIN
For v_i in 1:-Loop
IF (MOD (v_i, 3) = 0) Then
Dbms_output. Put_Line (v_i);
END IF;
END LOOP;
END;

PL/SQL Basic 1 (note)