Oracle Stored Procedure usage notes

(1) Pay attention to the following points when writing a stored procedure:
1> Value assignment: =
Logic equal to =
2> comments a single line of code --
Annotate the code block /*---*/
3> string connector |
4> declare variables in the variable declaration area: Between is and begin
5> there are three methods to declare variables:
1) Single Variable
Mo_flag number: = 0;
Content table. Content % type;
2) array
Type data_type is table of varchar2 (10) index by binary_integer; -- reference format: data_type (I)
Type rule_type is record (data_id table. ID % type,
Data_name table. name % Type
); -- Reference format: rule_type (I). data_id
6> a frequently used built-in function Decode:
Decode (ID, 1, 'southlake ',
2, 'san Francisco ',
3, 'new Jersey ',
4, 'seattle ',
'Non-domestic ')
When ID is 1, return southlake; If ID is 2, Return San Francisco; in turn, if none, return non-domestic

(2) PL/SQL Block Structure
1> trigger
Create or replace trigger trigger_name
After | before insert | update | delete on table_name
For each row
Declare
(Declaration part)
Begin
(Program body)
Exception
(Exception Handling)
End;
2> Stored Procedure
Create or replace procedure name (parameter) is
(Declaration part)
Begin
(Program body)
Exception
(Exception Handling)
End;
3> Functions
Create or replace function func_name (parameter) return data_type is
(Declaration part)
Begin
(Program body)
Exception
(Exception Handling)
End;

Note: declare is required when declaring variables in triggers. It is not required when declaring stored procedures and functions.

(3) Pay attention to the following applications during application and debugging:
1> set serveroutput on buffer 20000;
Use dbms_output.put_line ('strin _ content'); you can output information in the stored procedure and debug the stored procedure;
The information can only be displayed on the screen after the serveroutput variable is set to on.
2> execute procedure_name;
Execute a stored procedure
3> select function () from dual;
Execute a function using an SQL statement
4> dbms_job.submit (job, 'test _ warn_proc; ', sysdate, 'sysdate + 30/(24*60 )');
Indicates that the job is executed every 30 minutes, dbms_job.submit (Task Number, stored procedure to be executed, next execution time, interval)
Sysdate + x/24 is executed every x hours.
Sysdate + x/(24*60) is executed every X minutes.
Sysdate + x/(24*60*60) Run Once every X seconds
5> when an error message is reported, the line numbers are described as follows:
The count of the row number starts with the keyword declare. If not, the row number of the begin statement is 1.