Oracle Stored Procedure practice: getting started

1. Basic Structure
Create or replace procedure
(
Parameter 1 in number,
Parameter 2 in number,
Parameter 3 out number
) Is
Variable 1 integer: = default value;
Variable 2 date;
Begin
...
Exception
When exception category then
...
When others then
...
End stored procedure name;

2. Basic operations
Variable value: v_test: = 1;
Dynamic value assignment select col1, col2,... into var1, var2,... from... where ...;
Add 'string1' | 'string2 ';
Equal judgment = rather than =;
Logical judgment and, Or, >,<,<>;
Print the output dbms_output.put_line ();
Whether it is null v_test is (not) NULL;
Submit rollback commit/rollback;
Exception capturing begin... exception when... then... end;

3. If judgment
If (condition) then
Begin
...
End;
Elsif then
...
Else
...
End if;

4. While Loop
While (Judgment condition) loop
Begin
...
End;
End loop;

5. For Loop
For X in (select col1, col2...) loop
Begin
Reference X. col1, X. col2
...
End;
End loop;

6. Common functions
The following lists common usage of some common functions, which are not described in accordance with the Oracle function standards. Therefore, some usage is not comprehensive, but lists the most commonly used cases;
Sysdate: used to obtain the current date of the system;
To_number (STR): converts a string to a number. If the conversion fails, an error is thrown: ORA-01722: Invalid number;
To_char (?) :? Supports many types, such as num, date ......; Date is widely used. Generally, it is used as follows:
To_char (sysdate, 'yyyy-MM-DD hh24: MI: ss'): outputs the complete string of the current date according to the format, where the date format can be customized;
To_date (STR, format): parses a string into the date type according to a certain format;
Nvl (COL, defaultvalue): determines whether col is null. If it is null, defavalue value is returned;
Concat (str1, str2): connects two strings. The function is the same as |;
Length (STR): returns the length of a string. The length of a Chinese character depends on the database encoding. in GBK encoding, the length of a Chinese character is also 1 character;
If the length of a Chinese character is 2 characters, you can use lengthb (STR;
In short, length returns the length of characters; lengthb returns the length of bytes;
Substr (STR, start, count): truncates sub-string, starts from start, and obtains count;
The effect of 'start' is the same as that of '1'. It is intercepted from the first character of 'str;
If Start> = length (STR), null is directly returned and no error is thrown;
Instr (str1, str2, I, j): searches for a specified character in a string and returns the location where the specified character is found;
Str1: string to be searched
Str2: string to be searched
I: Start position of the search. The default value is 1.
J: the position of the first occurrence. The default value is 1.
Lower/Upper (STR): converts all strings to lowercase or uppercase. in GBK encoding, this function is invalid for Chinese characters;
Ltrim/rtrim (STR): Delete the space string on the right of the goods on the left;
Floor (number): an integer for a given number;
MoD (num1, num2): returns the remainder of num1 divided by num2. num1 and num2 can carry decimal places. The returned values may also have decimal places;
Round (Num): returns the rounded value of num;
Trunc (Num): truncates an integer from num, removes decimal places, and does not perform rounding. The function is similar to that of floor. Of course, you can also specify the precision in the parameter;
Trunc (date): truncates a date to a day, removes hours and minutes, And the return type is still date. Similarly, you can specify the date truncation level in the parameter, for example, 'hh ';
Add_months (date, num): Add num months to date. Num can be a negative number, indicating that a few months are subtracted;
Num can have decimal places, but it does not take effect. The effect is equivalent to add_months (date, trunc (Num ));
Chartorowid/rowidtochar: Convert the character data type to the rowid type or vice versa;
AVG/MIN/max/sum/count (distinct | all col): Average, minimum, maximum, sum, and count of a field. The default value is all, use the distinct parameter to perform non-repeated value operations;
Case when... then...
When... then...
Else...
End: get different values based on different conditions, which is similar to case in Java;