Home Database Mysql Tutorial Oracle定时调用存储过程

Oracle定时调用存储过程

Jun 07, 2016 pm 05:06 PM
oracle database

--创建测试表名create table job_table(run_date date);--创建存储过程create or replace procedure job_proc asbegin inser

--创建测试表名
create table job_table(run_date date);
--创建存储过程
create or replace procedure job_proc as
begin
     insert into job_table (run_date) values (sysdate);
end;

--创建job并指定一分钟执行一次
declare
       job number;
begin
     dbms_job.submit(job,'job_proc;',sysdate,'TRUNC(sysdate,''mi'')+1/(24*60)');
end;

commit;

 


--暂停任务

--select job from user_jobs
找出job的id值,,然后使用下面的放语句停止任务。

begin
dbms_job.broken(584,true);
end;

 


--重启任务

begin
dbms_job.run(584);
end;


--删除任务
delete user_jobs where job=584;
drop procedure job_proc;
drop table job_table;

 

 

创建job后。系统即会在指定时间里自动调用该存储过程。

 


关于时间间隔的相关知识:

1.TRUNC(for dates)  

TRUNC函数为指定元素而截去的日期值。  

其具体的语法格式如下:  

TRUNC(date[,fmt])  

其中:  date 一个日期值  fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去  

下面是该函数的使用情况:  

TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am')  ='24-Nov-1999 12:00:00 am'  

TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am'  

trunc(sysdate,'yyyy') --返回当年第一天.  

trunc(sysdate,'mm') --返回当月第一天.  

trunc(sysdate,'d') --返回当前星期的第一天.  

trunc(sysdate,'dd')--返回当前年月日

 


trunc函数后面加的数字的单位是天。  


每天凌晨0点0分:trunc(sysdate+1)

每天早上8点 trunc(sysdate+1)+1/3

每天早上8点30分 trunc(sysdate+1)+(8.5*60)/(24*60)

每天早上8点30分 trunc(sysdate+1)+8.5/24

每天早上8点30分 trunc(sysdate+1)+(8*60+30)/(24*60)

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Nordhold: Fusion System, Explained
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1669
14
PHP Tutorial
1273
29
C# Tutorial
1256
24
How to check which table space a table belongs to in Oracle How to check which table space a table belongs to in Oracle Jul 06, 2023 pm 01:31 PM

How to check which table space a table belongs to in Oracle: 1. Use the "SELECT" statement and specify the table name to find the table space to which the specified table belongs; 2. Use the database management tools provided by Oracle to check the table space to which the table belongs. Tools usually provide a graphical interface, making the operation more intuitive and convenient; 3. In SQL*Plus, you can view the table space to which the table belongs by entering the "DESCRIBEyour_table_name;" command.

How to connect to Oracle database using PDO How to connect to Oracle database using PDO Jul 28, 2023 pm 12:48 PM

Overview of how to use PDO to connect to Oracle database: PDO (PHPDataObjects) is an extension library for operating databases in PHP. It provides a unified API to access multiple types of databases. In this article, we will discuss how to use PDO to connect to an Oracle database and perform some common database operations. Step: Install the Oracle database driver extension. Before using PDO to connect to the Oracle database, we need to install the corresponding Oracle

How to retrieve only one piece of duplicate data in oracle How to retrieve only one piece of duplicate data in oracle Jul 06, 2023 am 11:45 AM

Steps for Oracle to fetch only one piece of duplicate data: 1. Use the SELECT statement combined with the GROUP BY and HAVING clauses to find duplicate data; 2. Use ROWID to delete duplicate data to ensure that accurate duplicate data records are deleted, or use "ROW_NUMBER" ()" function to delete duplicate data, which will delete all records except the first record in each set of duplicate data; 3. Use the "select count(*) from" statement to return the number of deleted records to ensure the result.

How to use php to extend PDO to connect to Oracle database How to use php to extend PDO to connect to Oracle database Jul 29, 2023 pm 07:21 PM

How to use PHP to extend PDO to connect to Oracle database Introduction: PHP is a very popular server-side programming language, and Oracle is a commonly used relational database management system. This article will introduce how to use PHP extension PDO (PHPDataObjects) to connect to Oracle database. 1. Install the PDO_OCI extension. To connect to the Oracle database, you first need to install the PDO_OCI extension. Here are the steps to install the PDO_OCI extension: Make sure

Implement data import into PHP and Oracle databases Implement data import into PHP and Oracle databases Jul 12, 2023 pm 06:46 PM

Implementing data import into PHP and Oracle databases In web development, using PHP as a server-side scripting language can conveniently operate the database. As a common relational database management system, Oracle database has powerful data storage and processing capabilities. This article will introduce how to use PHP to import data into an Oracle database and give corresponding code examples. First, we need to ensure that PHP and Oracle database have been installed, and that PHP has been configured to

How to query table space size in oracle How to query table space size in oracle Jul 06, 2023 pm 01:45 PM

Steps to query the table space size in Oracle: 1. Log in to the Oracle database using a database administrator account; 2. Use the "SELECT" statement to view the space list; 3. There are three methods to query the table space size: use the dbms_utility package to query, and use the dba_segments view Query, use the dba_data_files view query; 4. Use the "DBMS_OUTPUT.PUT_LINE" function or other methods to display the results to display the query results.

Does oracle database require jdk? Does oracle database require jdk? Jun 05, 2023 pm 05:06 PM

The oracle database requires jdk. The reasons are: 1. When using specific software or functions, other software or libraries included in the JDK are required; 2. Java JDK needs to be installed to run Java programs in the Oracle database; 3. JDK provides Develop and compile Java application functions; 4. Meet Oracle's requirements for Java functions to help implement and implement specific functions.

How to use PHP and Oracle database connection pools efficiently How to use PHP and Oracle database connection pools efficiently Jul 12, 2023 am 10:07 AM

How to efficiently use connection pooling in PHP and Oracle databases Introduction: When developing PHP applications, using a database is an essential part. When interacting with Oracle databases, the use of connection pools is crucial to improving application performance and efficiency. This article will introduce how to use Oracle database connection pool efficiently in PHP and provide corresponding code examples. 1. The concept and advantages of connection pooling Connection pooling is a technology for managing database connections. It creates a batch of connections in advance and maintains a

See all articles