Oracle stored procedure and job

What is the first stored procedure? What does the job do?

Stored procedures: the equivalent of a complex SQL, used to perform custom complex functions, created and then stored in a table, you can execute the stored procedures through the job, to achieve the functions we need

Job: In fact, the database built-in timer task, you can set the stored procedure what time to perform such a function, is the database comes with,

========================== stored procedures ========================== stored procedures can be written in the form of complex SQL to implement custom special functions, written by their own definition, defined and stored in the built-in table define the format:

Create or Replace procedure name (parameter (optional))
as .....
Begin
...........
End  

Example:

1: Create a non-parametric stored procedure named Test_job, which acts as a table to add system time

Create or replace procedure Test_job
As
Begin
INSERT into t_job values (sysdate);---Add data to the table
END;2: Create a stored procedure named Test_user, under the figure, the Scarlet Letter is interpreted

Create or Replace procedure Test_user (username1 in varchar2) --The parameter is username1,in represents the input parameter, the data type and length of the parameter
As Vs_username VARCHAR2 (4000); --as You can define variables later, defined variables can be referenced in the following custom SQL;
Begin --Custom SQL functions start
Select username into Vs_username from T_user where username=username1; --Pay the value of the Username field identified in the table to the custom variable vs_username,into is a keyword of Oracle
Dbms_output. Put_Line (Vs_username);--print output of the result
End --End of custom SQL function

To view the stored procedures defined:

SELECT * from user_objects where object_type= ' PROCEDURE '; --You can view your custom stored procedures

=============================job===================================

The job is a feature of Oracle itself that allows you to customize the job, specify when to execute the stored procedure, and then store it in the built-in table to execute the stored procedure at the defined time

The format defined:
Declare
JOB1 number; --Specify the name
Begin
Dbms_job.submit (Job1, ' test_job; ', sysdate, ' sysdate+5/1440 ');--Set the name, the stored procedure to be executed, the time format, the specific time setting, where the stored procedure is executed every 5 minutes
End to view a defined job:

SELECT * from dba_jobs;--all jobs, query under Admin user
SELECT * from user_jobs;--queries under individual users
SELECT * from dba_jobs_running;--Query the running job, this remains to be confirmed

Once the job is defined, the stored procedures we define are executed on a regular basis according to the defined job.

==================== Small example begins ===============================

CREATE TABLE T_job (a date);--Creating tables

--Create a stored procedure
Create or replace procedure Test_job
As
Begin
INSERT into t_job values (sysdate);---Add data to the table
End

--Define Job
Declare
JOB1 number;
Begin
Dbms_job.submit (Job1, ' test_job; ', sysdate, ' sysdate+5/1440 ');--executes every 5 minutes, and the job is run after the definition is complete.
End

SELECT * from T_job; ---can check the table every 5 minutes to see if there is any additional data.

==================== Small Example End ===============================

Here are some operations on the job
--============= Stop job==============
Begin
Dbms_job.broken (25,false);--true = = y = Stop, false ==n = Execute
End
/
--=============== Delete job=============
Begin
Dbms_job.remove (24);
End
/
--================ Change Job Time ========

Begin
Dbms_job. Interval (' sysdate+5/1440 ');--Change the time setting for the specified job
End
/

Here are only three ways to write broken/remove/Interval

For more methods and job table structure see: http://www.cnblogs.com/xueershewang/p/7354936.html

Time settings See: http://www.cnblogs.com/xueershewang/p/7355286.html

Oracle stored procedure and job