Oracle Stored Procedure learning notes

Connection character: |
Value assignment: ": ="
For example, l_name: = 'sky ';
Determine whether the values are equal: "="
For example, if 1 = 1 then...
Variable binding :":"
For example, if: P_NAME = 'sky' then...

Exists
Not exists does not exist

% Type matching Field type
% Rowtype matching record type
% Type when the table type changes, you do not need to manually modify the type called in PL/SQL blocks.
Example: V_GP_ID SC _BALANCE_DAILY.Gp_Id % TYPE;

Sqlcode SQL statement
Sqlerrm SQL Error
No_data_found occurs when select into has no records (DML does not trigger NO_DATA_FOUND exception)

Commit (used in insert, update, and delete)
For example, if you log on to the database using the test account and insert a record into the table without commit,
When other accounts log on to this database, they will not be able to query your insert records. After commit, other accounts will be able to query your insert records.
Rollback (used in insert, update, and delete)
For example, if you use the test account to log on to the delete table of the database, When you query this table,
The delete record does not exist. After rollback, When you query your delete record, the deleted record is returned.

Execute immediate is generally used to execute dynamic SQL statements.
Simply put, you create a table table_a in a stored procedure and then use insert into to insert other data into table_a,
However, because table_a does not exist during the creation process, a compilation error is displayed, because table_a does not exist and the process cannot be executed,
Therefore, compilation fails. If the insert into statement is added to execute immediate, Oracle will not check whether the object exists, so it can be compiled and executed successfully.

Nvl ()
Nvl (field name, 0), that is, when you select this field, although it is empty, it displays 0
For example, nvl (sum (rge_amt_sum), 0) SJCZ_AMT. When sum (rge_amt_sum) is null, the value of SJCZ_AMT is 0.

Decode ()
Decode (column name, judgment condition, output result that meets the judgment condition, and output result that does not meet the judgment condition)
Example: decode (business_type, 'cod _ pay', amt,-amt) cod_def_amt

Sysdate returns the current time yyyy-mm-dd hh24-mi-ss
Example: select sysdate from dual; -- 10:36:30
Sysdate + 1 is the next day, for example, 10:36:30
Trunc () returns the current date yyyy-mm-dd
Example: select trunc (sysdate) from dual; -- trunc (sysdate) 2013-6-7
TIMESTAMP date in millisecond format

-- The first day of the month
Select trunc (sysdate, 'mm') from dual;
-- Last day of the month
Select last_day (trunc (sysdate, 'mm') from dual;