Oracle series: Triggers, jobs, sequences, connections

• 1. Trigger
• 2. Homework
  • 2.1. Job scheduling function and application
  • 2.2, through the Dbms_job to dispatch the job
• 3. Sequence
  • 3.1. Create a sequence
  • 3.2. Use sequence & Delete sequence
• 4. Connection
  • 4.1. Create a connection
  • 4.2. Use Connection & Delete connection
• 5. Summary

1. Trigger

Triggers can be seen as a special kind of stored procedure. Triggers are also a pattern object that defines a data dictionary, and, like stored procedures, contains PL/SQL blocks that are declared and executed. The biggest difference between a trigger and a stored procedure is the invocation method, which is invoked and executed by the user or application, and the trigger is invoked by the Oracle system, and when the triggering event occurs, the trigger is triggered and then implicitly run.

In general, use triggers sparingly in database projects, as they are used sparingly, because excessive use of triggers can make the project difficult to maintain and can also affect application performance. So this article only briefly introduces triggers, and gives the syntax for creating triggers and an example.

Create a Trigger

Grammar:

CREATE OR REPLACE TRIGGER trigger_name[BEFORE | AFTER] [INSERT | DELETE | UPDATE ] ON table_name[FOR EACH ROW]DECLARE -- 定义变量BEGIN -- 触发器操作代码END;

Example:

CREATE OR REPLACE TRIGGER trg_aiAFTER INSERT ON demo.t_courseFOR EACH ROWDECLARE v_today DATE;BEGIN v_today := SYSDATE; DBMS_OUTPUT.PUT_LINE(‘在 ‘||TO_CHAR(v_today,‘yyyy-mm-dd‘)||‘ 添加了《‘||:NEW.course_name||‘》课程‘);END;

Test:

INSERT INTO t_course(course_id,course_name,course_desc) VALUES(5,‘C/C++‘,‘计算机专业课程‘);

Delete Trigger

DROP TRIGGER trg_ai; -- 删除名为 trg_ai 的触发器

2. Homework

Special note: In the previous version, I named this section as a task, mainly I have seen the information in most of the job scheduling is called a task, at the beginning I also wonder son, SQL Server seems to be called homework Ah, Oracle has created a unique and do not comply with the industry norms of things? Later read the Official Handbook only to know, the original is the domestic netizen with biased!

I thought about the Chinese name carefully, and then I thought it was better to call my homework. Because managing a database is about accomplishing a large number of tasks, deciding when and where to complete a task is a plan, which is scheduled and completed by Oracle Scheduler, the job called by the Scheduler is not a reasonable task or plan because it is confusing, And the job seems to be a more appropriate salutation.

2.1. Job scheduling function and application

The General relational database provides job scheduling capabilities, and Oracle also provides a scheduler that can be used for PL/SQL. The scheduler enables database administrators and application developers to control when and where various tasks occur in the database environment. These tasks can be time-consuming and complex, such as backup or nightly data warehouse loading and extraction, so that limited computing resources can be allocated appropriately in competing jobs.

The most basic function of the job scheduler is the ability to schedule a job to run at a specific date and time or when a particular event occurs. For example, a patch needs to be applied to a database that is being produced, and in order to minimize interference, this task needs to be done during off-peak hours, which can, of course, allow IT staff to perform this task manually during off-peak hours, but can also be done easily using the job scheduler to reduce operational costs.

Oracle has provided DBMS_JOB and DBMS_SCHEDULER two job scheduler packages, which are more powerful, more flexible, and more interactive to the system than the former. Oracle also recommends DBMS_JOB switching from to DBMS_SCHEDULER , but still supported DBMS_JOB for backwards compatibility, and the job scheduler package shares the same job coordinator.

You can enter the show parameter job_queue_processes maximum number of concurrent tasks for a scheduled task in the command window, typically set to 10, and if set to 0, the database timer job will not run. The way to change the concurrency number is alter system set job_queue_processes=10 .

A view can be used USER_JOBS to query the current user's job scheduling situation. As follows:

SELECT t.job jobno,t.what,to_char(t.next_date, ‘yyyy-mm-dd hh24:mi:ss‘) next_date,t.interval FROM USER_JOBS t;

USER_JOBSA description of the meaning of the main fields of the view, as follows:

• job: Number type, unique numbering for the task.
• what: VARCHAR2 (4000), Task action content.
• next_date: Date type, the next time the task is executed.
• interval: VARCHAR2 (200), task execution time interval.
• log_user: The user who submitted the task.
• priv_user: The user who assigned the task permission.
• schema_user: User mode for parsing tasks.
• last_date: The last time the task was successfully run.
• last_sec: hh24:mm:ss last_date hours, minutes, and seconds, such as the date of the format.
• this_date: The start time of the running task, or null if no task is running.
• this_sec: hh24:mm:ss this_date hours, minutes, and seconds, such as the date of the format.

2.2, through the Dbms_job to dispatch the job

In the actual project development, most job scheduling will be done through background code, and only in some cases the job scheduling function in the database will be used. In Oracle 10g, it's DBMS_JOB easier to manage jobs, and then we'll talk about how to use them DBMS_JOB .

To create a job:

Grammar:

DBMS_JOB.SUBMIT(  job OUT BINARY_INTEGER,  IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,  [next_date] IN DATE, [interval] IN VARCHAR2 DEFAULT ‘NULL‘, [no_parse] IN BOOLEAN DEFAULT FALSE, [instance] IN BINARY_INTEGER DEFAULT ANY_INSTANCE, [force] IN BOOLEAN DEFAULT FALSE);

Parameter description:

• jobs: Job number, output parameters, the system is automatically assigned, cannot be changed.
• What: the operation to be completed, such as the calling process. Can be DBMS_JOB.WHAT(job, what) modified by.
• next_date: The next run time for the job. Can be DBMS_JOB.NEXT_DATE(job,next_date) modified by.
• interval: The time interval at which the job runs. Can be DBMS_JOB.INTERVAL(job,interval) modified by.

interval parameter setting case :

• Execute once per minute (0 seconds):TRUNC(SYSDATE,‘‘mi‘‘) + 1/(24*60)
• Every hour (0 minutes 0 seconds) is executed once:TRUNC(SYSDATE,‘‘hh24‘‘) + 1/24
• Executes every 7 days:TRUNC(SYSDATE) + 7 + 1/24
• Once daily two o'clock in the morning:TRUNC(SYSDATE) + 1 + 2/24
• Once every Monday 1 o'clock in the morning:TRUNC(NEXT_DAY(SYSDATE,‘‘monday‘‘)) + 1/24
• Every month, 5th, 1 o'clock in the morning, is executed once:TRUNC(LAST_DAY(SYSDATE)) + 5 + 1/24
• Once every quarter, the first day of 1 o'clock in the morning is performed:TRUNC(ADD_MONTHS(SYSDATE,3),‘‘Q‘‘) + 1/24
• Executed once every January 1 1 o'clock in the morning:ADD_MONTHS(TRUNC(SYSDATE,‘‘yyyy‘‘),12) + 1/24
• No longer run the job and delete it:NULL
• Every Saturday, Sunday 6:10 A.M.:TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘‘SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY")))+(6×60+10)/(24×60)

Example:

DECLARE  jobno NUMBER;BEGIN  DBMS_JOB.SUBMIT(jobno, ‘sp_sync_staff90;‘, SYSDATE, ‘TRUNC(SYSDATE) + 1 + 2/24‘); COMMIT;END;

To delete a job:

Grammar:

DBMS_JOB.REMOVE(job);

To enable a job:

Grammar:

DBMS_JOB.RUN(job);

To disable a job:

Grammar:

DBMS_JOB.BROKEN(  IN BINARY_INTEGER, -- 为 true 表示禁用任务，为 false 表示启用任务  broken IN  BOOLEAN, [next_date] IN DATE DEFAULT SYSDATE);

Example:

BEGIN  DBMS_JOB.BROKEN(135,TRUE); -- 禁用 job 为 135 的任务  COMMIT;END;

3. Sequence

You can think of a sequence as a digital factory, because its only function is to produce equal intervals of values. The most typical use of a sequence in Oracle is the value of the production primary key. Anyone with Oracle development experience should know that Oracle does not provide functionality similar to the autogrow column in SQL Server or MySQL. If you need to use an autogrow column, you can implement similar functionality by using a sequence.

I have been involved in two Oracle-based development projects, but none of the Oracle sequences (one with C # code to generate serial numbers and the other directly with GUIDs), so far I have not used the sequence of actual combat experience. The following is just a brief description of the sequence I know.

3.1. Create a sequence

The standard syntax for creating a sequence is as follows:

CREATE SEQUENCE seq_name [INCREMENT BY n] [START WITH n] [{MAXVALUE n/NOMAXVALUE}] [{MINVALUE n/NOMINVALUE}] [{CYCLE/NOCYCLE}] [{CACHE n/NOCACHE}];

Syntax Options Description:

• INCREMENT by N: n represents the interval between two consecutive values in a sequence, also known as the step size. If n is a positive number, the increment is incremented, and if n is negative, it is decremented, which defaults to 1.
• start with n: n denotes the starting value of the sequence, that is, the first value of the sequence, the default is 1, increment n is MinValue, and N is maxValue when descending.
• MAXVALUE n: n indicates the maximum value of the sequence, or Nomaxvalue, which is not set to the maximum value, is 999999999999999999999999999 by default.
• MINVALUE n: n indicates the minimum value of the sequence, or nominvalue, that is, the minimum value is not set and the default is 1.
• cycle and nocycle indicate that when the value of the sequence reaches the limit value, the cycle value and the value are not looped.
• CACHE N: N defines the size of the memory block in which the sequence is stored, with a default of 20 (digits). NOCACHE indicates that the sequence is not buffered in memory. The sequence CACHE (which is pre-generated as part of the serial number and put into memory) can be used to speed up access to the sequence.

3.2. Use sequence & Delete sequence

Using sequences

SELECT seq_name.CURRVAL FROM DUAL; -- 获得指定序列的当前值SELECT seq_name.NEXTVAL FROM DUAL; -- 获得指定序列的下一个值

Note (the sequence cannot be used for one of the following situations):

• 1. In the subquery of DELETE, SELECT, UPDATE.
• 2. In the query of views or materialized objects.
• 3. The DISTINCT operator is used in the SELECT query.
• 4. The SELECT query has GROUP by or ORDER by.

If you want to query the exact sequence in the data, the syntax is as follows:

SELECT * FROM USER_SEQUENCES; -- 当前用户所有序列

To delete a sequence , example:

DROP SEQUENCE seq_name;

4. Connection

Oracle has a "database link" East, translated into Chinese should be a data connection, in order to address the convenience, the following unified called the connection. When I was talking to a technical expert in the company, the other side mentioned "database connection", so I looked up the following: The connection is an object that defines the path of a database to another database, and the connection allows you to query remote tables and execute remote programs.

There are two types of connections, public and private, respectively. A public database link is available to all users in the database, and a private link is available only to the user who created it. It is not possible for a user to grant a private database link to another user, a database link is either public or private. In any distributed environment, connectivity is necessary. It is also important to note that the connection is a one-way connection.

4.1. Create a connection

Before you create a connection, you need to determine three things, the first local database and the network between the remote database is normal connection, the second creation of the connection account must have the CREATE DATABASE link or create public link permissions, The third account used to log on to the remote database must have the CREATE SESSION permission.

The practice tells me that the correct syntax for creating a connection is as follows:

CREATE [PUBLIC] DATABASE LINK dblink_name CONNECT TO user_name IDENTIFIED BY user_pwd USING ‘connect_string‘;

connect_stringThere are two types of writing, examples:

Example (one):

CREATE PUBLIC DATABASE LINK dblink168 CONNECT TO office IDENTIFIED BY 123456 USING ‘192.168.1.168:1521/orcl‘;

Example (b):

CREATE PUBLIC DATABASE LINK dblink168 CONNECT TO office IDENTIFIED BY 123456 USING ‘(DESCRIPTION = ( ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.168)(PORT = 1521)) )( CONNECT_DATA = (SERVICE_NAME = orcl) ))‘;

Many articles on the web are given in the creation of the syntax does not include the specified password, and said if not specified, then use the current user name and password to log in to the remote database, I repeated experiments many times, are directly reported syntax errors. But I did find a feature, after the password is created successfully, then to see the connection definition of SQL, you will find that the password directive is missing. I guess Oracle did a special encryption, so if you're going to create a connection, you don't have to worry about showing the specified password after someone sees it, causing a security leak.

4.2. Use Connection & Delete connection

Use connection

I think the connection is one of the real charm is easy to use, regardless of the deletion and change the statement, only need to follow the table name @dblink_name can operate the remote database. To query the number of female employees in the user table on 168, example:

SELECT COUNT(1) FROM [email protected] t WHERE t.gender=0;

I also found a small problem in the test, if I want to query the server time on 168, it should be said that the following:

SELECT SYSDATE FROM [email protected]; -- 结果显示出来的时间仍是本地数据库的服务器时间

If you want to be more unified for naming, or if you don't wish to let the other person know the name of Dblink, you can also wrap it through a view or a synonym, example:

CREATE VIEW v_name AS SELECT * FROM [email protected]_name;CREATE SYNONYM table_name FOR [email protected]_name;

Several views related to the connection are described below:

SELECT * FROM DBA_DB_LINKS; -- 查询当前数据库实例中所有 dblinkSELECT * FROM V$DBLINK; -- 查询当前数据库示例中正在打开状态的 dblinkSELECT * FROM USER_SYS_PRIVS t WHERE t.privilege LIKE ‘%LINK%‘; -- 查询跟 dblink 有关的系统权限

Delete Connection

For non-public types of connections, only owner can delete them, and non-public types of connections do not have this requirement. Example of deleting a connection:

DROP [PUBLIC] DATABASE LINK dblink168;

5. Summary

This article mainly introduces some of the relatively less commonly used in Oracle SQL operations, but in the actual project process, sometimes they are very practical, so it is necessary to summarize, so as not to use the time do not remember the grammar, but also to check the information.

Oracle series: Triggers, jobs, sequences, connections