PL/SQL basics 1 (notes), plsql

PL/SQL basics 1 (notes), plsql

-- Basic Structure
DECLARE
-- Variable Declaration: Declares the variables, types, cursors, and local stored procedures and functions used by PL/SQL.
BEGIN
-- Execution part: process and SQL statement, that is, the main part of the program
EXCEPTION
-- Execution exception: Exception Handling
END;
-- Basic PL/SQL specifications: All reserved words, roacle built-in functions, packages, and user-defined data types are capitalized.
-- Basic PL/SQL specifications: Only one statement is written per line. spaces should be added after commas and before and after operators.
-- Basic PL/SQL specifications: Use meaningful names:
-- Basic PL/SQL specifications: use the "_" connection method, instead of the case-sensitive hybrid method.
-- PL/SQL basic specification: it is best to add a prefix before a variable to indicate the data type and scope of the variable.
-- 1. when defining a variable, we recommend that you use v _ as the prefix: v_ename
-- 2. When defining constants, we recommend that you use c _ as the prefix: c_rate
-- 3. When defining an exception, we recommend that you use e _ as the prefix: e_error

-- Check the product Inventory Based on the product ID entered by the user
DECLARE
V_ID NUMBER: = & V_ID; -- enter the product ID.
V_STOCKCOUNT NUMBER; --- inventory
BEGIN
Select stockcount into V_STOCKCOUNT FROM ES_PRODUCT where id = V_ID;
DBMS_OUTPUT.PUT_LINE ('inventory: '| V_STOCKCOUNT );

END;

------The updated variable TYPE is the same as the field TYPE in the table. The variable name table. Field name % TYPE is declared.
DECLARE
V_ID ES_PRODUCT.ID % TYPE: = & ID; -- enter the product ID
V_NAME ES_PRODUCT.NAME % TYPE; -- the variable name and the item Table Name field TYPE are the same
V_PRICE ES_PRODUCT.PRICE % TYPE; -- the variable price is of the same TYPE as the price field in the product table.
V_SALEDATE ES_PRODUCT.SALEDATE % TYPE; -- the TYPE of the shelving Date Field of the variable is the same as that of the shelving Date Field of the commodity table.
V_STOCKCOUNT ES_PRODUCT.STOCKCOUNT % TYPE; -- the TYPE of the variable inventory field is the same as that of the item 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 ('item name: '| V_NAME | 'item price:' | V_PRICe | 'item shelving time: '|
V_SALEDATE | 'item stock: '| V_STOCKCOUNT );

END;
--The upgrade uses a variable to represent the variable name % ROWTYPE, a field in a table.

-- Use the variable name and field for calling.

DECLARE
V_es_pro es_product% ROWTYPE; -- declare a variable, indicating the complete row field variable in the table
V_id es_product.id % TYPE: = & ID; ----- enter the product ID
BEGIN
Select name, price, saledate, stockcount
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 ('item name: '| v_es_pro.name | 'item price:' | v_es_pro.price | 'item shelving time: '|
V_es_pro.saledate | 'item inventory: '| v_es_pro.stockcount );
END;

-- Control statement
IF <Boolean expression> THEN
PL/SQL statements
END IF

IF <Boolean expression> THEN
PL/SQL statements
ELSE
Other statements
END IF
IF <Boolean expression> 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 Based on the product ID entered by the user
DECLARE
V_ID ES_PRODUCT.ID % TYPE: = & V_ID; -- enter the product ID.
V_STOCKCOUNT ES_PRODUCT.STOCKCOUNT % TYPE; --- inventory
BEGIN
Select stockcount into V_STOCKCOUNT FROM ES_PRODUCT where id = V_ID;
DBMS_OUTPUT.PUT_LINE ('inventory: '| V_STOCKCOUNT );
-- Determine whether the inventory is normal
IF V_STOCKCOUNT> 0 THEN
-- Update the inventory and delete one at a time
UPDATE ES_PRODUCT set stockcount = STOCKCOUNT-1
-- Update specified, otherwise all updates will be made.
Where id = v_id;
-- Propose data operations
COMMIT;
DBMS_OUTPUT.PUT_LINE ('inventory updated successfully ');
ELSIF V_STOCKCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('inventory is 0, no stocks ');
ELSE
DBMS_OUTPUT.PUT_LINE ('inventory exception ');
End if;
END;

-- Outputs the corresponding status based on the order status
SELECT * FROM es_order;
DECLARE
V_ID ES_ORDER.ID % TYPE: = & ID; -- enter the order number you want to query.
V_STATUS ES_ORDER.STATUS % TYPE; -- order status
V_STANTUSNAME VARCHAR2 (20 );
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: = 'deliverable ';
ELSIF V_STATUS = 4 THEN
V_STANTUSNAME: = 'finished ';
ELSE
V_STANTUSNAME: = 'unknown status ';
End if;
DBMS_OUTPUT.PUT_LINE (V_STANTUSNAME );
END;

CASE Syntax:
Format 1:
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 2:
CASE
WHEN condition expression 1 THEN
Statement segment 1
WHEN condition expression 2 THEN
Statement Segment 2
....
[ELSE statement segment]
End case;
-- Use CASE to check the product Inventory Based on the product ID entered by the user
-- Format 1
DECLARE
V_ID ES_ORDER.ID % TYPE: = & ID; -- enter the order number you want to query.
V_STATUS ES_ORDER.STATUS % TYPE; -- order status
V_STANTUSNAME VARCHAR2 (20 );
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: = 'deliverable ';
WHEN 4 THEN
V_STANTUSNAME: = 'finished ';
ELSE
V_STANTUSNAME: = 'unknown status ';
End case;
DBMS_OUTPUT.PUT_LINE (V_STANTUSNAME );
END;

-- Format 2
DECLARE
V_ID ES_ORDER.ID % TYPE: = & ID; -- enter the order number you want to query.
V_STATUS ES_ORDER.STATUS % TYPE; -- order status
V_STANTUSNAME VARCHAR2 (20 );
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: = 'deliverable ';
WHEN V_STATUS = 4 THEN
V_STANTUSNAME: = 'finished ';
ELSE
V_STANTUSNAME: = 'unknown status ';
End case;
DBMS_OUTPUT.PUT_LINE (V_STANTUSNAME );
END;

-- There are three types of loops
LOOP Syntax:
LOOP
The statement to be executed;
Exit when <Condition Statement> --- if the condition is met, EXIT the loop statement.
End loop;

WHILE loop Syntax:
WHILE <Boolean expression> LOOP
The statement to be executed;
End loop;

FOR Loop syntax;
For loop counter IN [REVERSE] lower limit... upper limit LOOP
The statement to be executed;
End loop;
In a FOR loop, the variable is automatically added with 1, the REVERSE is used, the loop variable is automatically reduced with 1, and the exit when can be used to EXIT the loop.


SELECT * FROM es_sort;
ID, sortname, and fatherid Fields
-- Use LOOP to add three records
DECLARE
V_ID ES_SORT.ID % TYPE: = 5; -- assign an initial value to the 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 condition
Exit when V_ID = 9;
End loop;
COMMIT;
Dbms_output.put_line ('successful insert ');
END;
-- Use the FOR loop to complete the process.
DECLARE
V_ID ES_SORT.ID % TYPE; -- assign a TYPE to the 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
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 ('successful insert ');
END;
SELECT * FROM es_sort;
-- Use WHILE to complete the task. If you do not know the number of cycles, use
DECLARE
V_ID ES_SORT.ID % TYPE: = 14; -- assign the TYPE and initial value to the 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
WHILE v_id> = 14 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 ('successful insert ');
END;
SELECT * FROM es_sort;

-- Output the number between 1 and that can be divided by 3

DECLARE
V_ I NUMBER;
BEGIN
FOR V_ I IN 1 .. 100 LOOP
IF (MOD (V_ I, 3) = 0) THEN
DBMS_OUTPUT.PUT_LINE (V_ I );
End if;
End loop;
END;