PL/SQL Step By Step (1)

1. Overview
 
PL/SQL (Procedural Language/Structured Query Language) is a Procedural expansion of Oracle's standard database Language SQL. It associates the database technology with the Procedural programming Language, it is an application development language that processes data using loops and branches, and combines SQL data manipulation with procedural language data processing. The use of PL/SQL makes SQL a high-level programming language that supports block operations, condition judgment, cyclic statements, nesting, and so on in advanced languages. It is integrated with the core data types of the database, it makes SQL programming more efficient. (For more details, Google)
 
PL/SQL programs are mainly divided into two categories: anonymous programs and named programs. Next we will talk about anonymous programs. A typical anonymous block structure consists of the following parts:
 
 
DECLARE -- Optional
-- Declaration of variables, constants, cursors, and user-defined exceptions
BEGIN -- necessary
-- Execution program composed of SQL statements and PL/SQL statements
EXCEPTION -- Optional
-- When an exception occurs in the program, capture the exception and handle the exception
END; -- required. Pay attention to the semicolon after END.
According to the above format, we first write a simple and complete anonymous block:
 
 
DECLARE
V_counter NUMBER (3 );
V_user ALL_USERS.USERNAME % TYPE;
V_today DATE;
BEGIN
Select sysdate, USER
INTO v_today, v_user
From dual;

DBMS_OUTPUT.PUT_LINE (
'Today: '|
TO_CHAR (v_today, 'yyyy-MM-DD '));
DBMS_OUTPUT.PUT_LINE ('schema: '| v_user );

V_counter: = 0;
LOOP
V_counter: = v_counter + 1;
Exit when v_counter> 10;
DBMS_OUTPUT.PUT_LINE ('line: '| v_counter );
End loop;
EXCEPTION
WHEN OTHERS THEN
-- Do log operations
-- COMMIT;
END;
The following are explained one by one:
 
Declaration
Three variables are defined: v_counter, number TYPE; TYPE of the USERNAME field in v_user, ALL_USERS view (XX % TYPE indicates xx type); v_today, date type.
 
Execution part
Assign the values of the current date and the current user to the v_today and v_user variables respectively, and print and output the two variables;
 
Print the 10 numbers from 0 to 9. V_counter acts as a variable for iteration.
 
Exception Handling
How to handle exceptions.
 
End
The end part is to write it in the format. There is nothing to say.
 
In the preceding example, a single line comment is displayed, and a line starting with -- is considered as a comment. In addition, PL/SQL supports multi-row gaze /**/. In addition, for PL/SQL identifiers, it must start with a character and cannot exceed 30 characters, which is slightly different from that in many languages. In particular, PL/SQL strings are enclosed in single quotes.
 
2. constants and variables
 
PL/SQL variables are defined in the following format:
 
 
VARIABLE_NAME DATATYPE;
The variable type must be provided. It can be all PL/SQL-supported types. We will continue to discuss specific types. In addition, when defining a variable, you can give the variable an initial value, as shown below:
 
 
VARIABLE_NAME DATATYPE: = INIT_VALUE;
The value assignment operator of PL/SQL is: =. If no initial value is given, the default initial value of the variable is NULL.
 
The method for defining constants is similar to the method for defining variables. You only need to add the keyword CONSTANT and you must specify the initial value, as shown below:
 
 
VARIABLE_NAME constant datatype: = INIT_VALUE;
A constant value must be given when a constant is defined, and once assigned, it cannot be changed. The following is an example:
 
 
-- Example of variable Constant Value
DECLARE
V_name CONSTANT VARCHAR2 (8): = 'Tom ';
BEGIN
V_name: = 'Jerry ';
DBMS_OUTPUT.put_line (v_name );
END;
/
Oracle Reports an error when running the above program:
 
 
ORA-06550: 5th rows, 3rd columns:
PLS-00363: expression 'v _ name' cannot be used as a value assignment target
ORA-06550: 5th rows, 3rd columns:
PL/SQL: Statement ignored
In PL/SQL, all types supported by constants and variables, as well as detailed explanations, it is recommended that you refer to the official Oracle documentation for the Oracle version you are using. There will be some minor adjustments between the versions. Here are some simple examples:
 
 
CHAR (N): N characters, less than N characters, automatically filled with spaces. N must be specified.
VARCHAR2 (N): a maximum of N characters. N can be left unspecified. The default value is 1. When less than N bits, no space is used to complete N bits. Therefore, it is usually used.
DATE: DATE and Time type, which is displayed in the format required by the database by default.
NUMBER (N, M): NUMBER type. N digits, where M is the decimal digits.
BOOLEAN: This type is not supported in SQL, but PL/SQL supports TRUE and FALSE.
LONG: can receive a LONG string of up to 32760 bytes.
RAW: receives binary data, which can contain up to 32767 characters. PL/SQL cannot parse its content.
Long raw: larger than RAW.
MLSLABEL: security operating system label, which is used in Trusted Oracle.
ROWID: a type that uniquely identifies the physical storage address of a record in a database. Oracle automatically adds this pseudo column for each record.
BLOB: a maximum of 4 GB of binary data.
CLOB: up to 4 GB of text data.
NCLOB: Multi-byte BLOB. For example, store Chinese characters.
BFILE: a pointer to the OS file.
BINARY_INTEGER
INT/INTEGER: INTEGER
SMALLINT: a smaller integer.
POSITIVE: POSITIVE number
NATURAL: NATURAL Number
NUMERIC/DEC/DECIMAL: similar to NUMBER
REAL/FLOAT: Floating Point Number
TABLE: consistent type, similar to array
RECORD: composite type, indicating a RECORD
In the declaration part, not only constants or variables like the above can be defined, but also explicit cursor or custom exceptions. The following articles will discuss the cursor and exception.
 
3. Execution part
 
This part is actually the most important part of the PL/SQL block. It mainly includes expressions, value assignment statements, condition statements, comparison statements, loops, and cursor control statements:
 
Expression
Expressions are not separate statements, but small code units contained in separate statements. Expressions are generally used in values assignment and condition statements. Expressions are classified into arithmetic expressions and comparison expressions. This is similar to many third-generation languages.
 
Almost all SQL functions can be used as expressions, such as SUBSTR:
 
 
SUBSTR (v_str, m, n );
This is a legal expression. In PL/SQL, the only SQL function that cannot be used as an expression is the DECODE function, because the IF-THEN-END statement already exists in PL/SQL.
 
Expressions can also be connected through logical operators. This is also relatively simple.
 
Assignment Statement
The value assignment statement has been seen above. The value assignment operation is implemented using the value assignment operator: =. Therefore, in PL/SQL, = is used for implementation, which is different from = in C.
 
Condition Statement
A conditional statement is nothing more than an if statement and its variants. The PL/SQL format is slightly different. The simplest format is as follows:
 
 
IF (...) THEN
-- DO something
ELSE
-- DO something ELSE
End if;
This is the simplest form. The C language also has the form of else if, which is also available in PL/SQL, as follows:
 
 
IF () THEN
--
ELSIF () THEN
-- B
ELSE
-- C
End if;
The usage is no different from other languages, but when you are a beginner, pay attention to the writing format.
 
Loop
A loop is a very important structure in programming languages. It is used to execute a group of identical operations on a group of operation objects. There are many different forms of loops. The following describes them one by one:
 
Form 1:
 
 
LOOP
-- Perform several operations
Exit when...; -- WHEN to EXIT the loop
End loop;
This type of loop is used in the first part of the example.
 
Form 2:
 
 
LOOP
-- Perform several operations
IF... -- when to exit the loop
THEN
EXIT;
End if;
End loop;
It is equivalent to Form 1.
 
Form 3: while LOOP
 
 
WHILE (v_ I <10)
LOOP
---- Perform several operations
V_ I: = v_ I + 1;
End loop;
Form 4: Numeric FOR Loop
 
 
FOR v_ I IN 1 .. 10
LOOP
-- Perform some operations
End loop;
Form 5: cursor FOR Loop
 
In this section, we will talk about the cursor later.
 
Below, we first print the numbers 1-10 in four forms, the Code is as follows:
 
 
-- Form 1
DECLARE
V_limit number (2): = 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (v_limit );
V_limit: = v_limit + 1;
Exit when v_limit> 10;
End loop;
END;
/
 
-- Form 2
DECLARE
V_limit number (2): = 1;
BEGIN
LOOP
IF v_limit> 10
THEN
EXIT;
End if;
DBMS_OUTPUT.PUT_LINE (v_limit );
V_limit: = v_limit + 1;
End loop;
END;
/
 
-- Format 3
DECLARE
V_limit number (2): = 1;
BEGIN
WHILE v_limit <= 10
LOOP
DBMS_OUTPUT.PUT_LINE (v_limit );
V_limit: = v_limit + 1;
End loop;
END;
/
 
-- Format 4
DECLARE
V_limit number (2): = 1;
BEGIN
FOR v_limit IN 1... 10
LOOP
DBMS_OUTPUT.PUT_LINE (v_limit );
End loop;
END;
/
PS:
 
1. Run the preceding PL/SQL program and there may be no output. This is because the serveroutput option is not enabled by Oracle by default. You only need to execute it in SQL PLUS:
 
 
Set serveroutput on;
2. The above code snippet is not the content of the Code itself, but an operation for SQLPLUS to run the code immediately.
 
The next blog will discuss about cursor-related content.


The author wawlian said: