Oracle dbms_lock.sleep () stored Procedure usage Tips-Scenario-analysis-examples

the Oracle dbms_lock.sleep () stored Procedure usage Tips " - Scene - Analysis - Example

Summary: today is the Year 3 months , Beijing, fog Haze, the afternoon to organize relevant departments to meet. At the end of the meeting a development engineer found me and asked me how to delay the execution time of the SQL statement in order to be a functional output of the test project. I used the dbms_lock.sleep () stored procedure to complete this implementation in my suggestion. After the meeting, I realized that Oracle 's package application was very important. Often new into the workplace DBA technology is good, there will be a lot of things, is not very familiar with what technology application in what scenario, so that it can play the greatest role to help us. Take this opportunity to share with you today the Dbms_lock.sleep () stored procedure usage tips.

Scene:

1: The stored procedure wants to implement a delay of ten minutes before continuing execution

2: In the application to determine the condition if the "synchronization" will need to wait 5 minutes, if "done" can not wait

3: Test function feedback is normal, we need to delay 3 minutes to get the results of the SQL statement and not delay the results of the resulting SQL statement is consistent

Dbms_lock Package:

1. This package is a lock management Service pack provided by Oracle that has the ability to create a lock, request a lock, release a lock resource, and so on. Generally, the database created with DBCA has this package, and if it is done by hand, it needs to be executed $oracle_home/rdbms/admin/dbmslock.sql This script to create it. The package object cannot be available in the database after it is created.

Note: Only grant execute permission to use

Using the SYS user to grant permissions

Grant execute on Dbms_lock to USERNAME;

2. View Dbms_lock Package Contents

Sql> desc Dbms_lock

Element Type

--------------- ---------

Nl_mode CONSTANT

Ss_mode CONSTANT

Sx_mode CONSTANT

S_mode CONSTANT

Ssx_mode CONSTANT

X_mode CONSTANT

Maxwait CONSTANT

Allocate_unique PROCEDURE assigns a unique identifier to the lock

Request FUNCTION requests a lock mode (gets the state value of the lock)

Convert FUNCTION Converts a lock from one state to another

Release FUNCTION free lock

Sleep PROCEDURE This is a stored procedure that implements the delay time, and you can specify the sleep interval

This article mainly explains Sleepprocedure.

Dbms_lock. SLEEP () stored procedure:

Role: Use this stored procedure to abort the session for a period of time

Syntax: Dbms_lock. SLEEP (seconds in number); unit is "seconds", the smallest increment can be 1% seconds

For example: Dbms_lock. SLEEP (1.95); is a valid value

Example:

(1) because a stored procedure can be used directly

Sql> Executedbms_lock.sleep (60);

PL/SQL Procedure successfullycompleted

Sql> SELECT * Frompb_tradeinfo where rownum<=3;

IID TradeID NAME Certtype

------------------------------------------ ----------------------------------

382 548 Disposable Repayment 0

415 563 Test One-time loan 2

423 569 Test 1

(2) PL/SQL block implementation delay of two seconds

Sql> begin

2 Dbms_output.put_line (To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '));

3 Dbms_lock.sleep (60);

4 Dbms_output.put_line (To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '));

5 end;

6/

2014-03-1020:41:56

2014-03-1020:42:56

PL/SQL Procedure successfullycompleted

(3) print interval 5 seconds time list

Create a test table

Sql> Create tableleonarding (IID number,appendtime date);

Table created

Start testing

Sql> begin

2 for I in 1..10

3 loop

4 INSERT into leonarding values (i,sysdate);

5 commit;

6 Dbms_lock.sleep (5);

7 End Loop;

8 End;

9/

PL/SQL Procedure successfullycompleted

Print List

sql> SELECT * fromleonarding;

IID Appendtime

------------------------------------------------------------

1 2014-3-10 20:51:20

2 2014-3-10 20:51:25

3 2014-3-10 20:51:30

4 2014-3-10 20:51:35

5 2014-3-10 20:51:40

6 2014-3-10 20:51:45

7 2014-3-10 20:51:50

8 2014-3-10 20:51:55

9 2014-3-10 20:52:00

10 2014-3-10 20:52:05

Rows selected

Summary: Dbms_lock.sleep () process when we use, please note that the first is the permission to grant, the second is the use of the scene, the third note unit.

DBMS Sleep delay package leonarding

Oracle dbms_lock.sleep () stored Procedure usage Tips-Scenario-analysis-examples