Oracle pl sql trigger updating
In a nonprocedural language such as SQL, the set of data to be operated on is specified, but not the operations to be performed or the manner in which they are to be carried out.
In a procedural language program, most statement execution depends on previous or subsequent statements and on control structures, such as loops or conditional branches, that are not available in SQL.
Also, subprograms increase scalability by centralizing application processing on the server, enabling clients to reuse code.
Because subprogram calls are quick and efficient, a single call can start a compute-intensive stored subprogram, reducing network traffic.
A necessary condition of a raise is that the employee did not receive more than three raises in the last five years.
If a raise is called for, then the application must adjust the salary and email the manager; otherwise, the application must update a report.
CREATE OR REPLACE TRIGGER Department Autonumber Trigger 2 BEFORE INSERT ON Department 3 FOR EACH ROW 4 BEGIN 5 SELECT Department IDSeq. Department ID FROM DUAL; 7 END; 8 / Trigger created.
SQL CREATE or replace PROCEDURE Update Department 2 (Dept ID IN integer, 3 Department Name IN varchar2, 4 Department Description IN varchar2) 5 6 AS 7 BEGIN 8 UPDATE Department 9 SET Name = Department Name, 10 Description = Department Description 11 WHERE Department ID = Dept ID; 12 END; 13 / Procedure created.
The principal benefit of server-side programming is that functionality built into the database can be deployed anywhere.
The database and not the application determines the best way to perform tasks on a given operating system.
The problem is how procedural database applications requiring conditional logic and program flow control can use SQL.
The basic development approaches are as follows: This chapter explains the second approach.
For an illustration of the difference between procedural and nonprocedural languages, suppose that the following SQL statement queries the The preceding statement requests data, but does not apply logic to the data.
However, suppose you want an application to determine whether each employee in the data set deserves a raise based on salary and department performance.