Stored Procedure View trigger sequence

The stored procedure (Stored Procedure) is in a large database system, a set of SQL statements to complete a particular function, compiled and stored in a database, the user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).
Advantages of stored procedures :

1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution.
2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database.
3. stored procedures can be reused to reduce the workload of database developers
4. High security, can be set only a certain user has the right to use the specified stored procedure

Types of stored procedures :

1. System Stored procedure : Start with sp_, used to make the system settings. Get information. Related management work, such as sp_help, is to obtain information about the specified object.

2. Extended stored procedures start with XP_ to invoke the functionality provided by the operating system exec master. xp_cmdshell ' ping 10.8.16.1 '

3. User-defined stored procedures, which we refer to as stored procedures

Grammar

CREATE OR REPLACE procedure IOCS. Sp_d_materialinfobykey
(
p_materialid NVARCHAR2,
p_flag out integer
)
is
begin
Delete from materialinfo where materialid = P_materialid;
if Sql%found then
P_flag: = 1;
Else
P_flag: = 0;
End If;
commit;
Exception
When No_data_found
Then
null;
When others
Then
raise;
end Sp_d_materialinfobykey;

Second, trigger (Trigger)
A trigger (trigger) is a special stored procedure whose execution is not invoked by the program or manually, but is triggered by an event, such as when an operation on a table (Insert,delete, update) activates it for execution. Triggers are often used to enforce data integrity constraints, business rules, and so on. Triggers can be found in the dba_triggers, user_triggers data dictionary.
Advantages of the trigger:

triggers can implement cascading changes through related tables in the database, but these changes can be performed more efficiently by cascading referential integrity constraints. Triggers can enforce more complex constraints than constraints defined with a CHECK constraint. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can compare inserted or updated data with a SELECT from another table, and perform other actions, such as modifying data or displaying user-defined error information. Triggers can also evaluate the state of the tables before and after data modification and take countermeasures based on their differences. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different actions to be taken in response to the same modification statement.

Trigger syntax (Oracle)
CREATE OR REPLACE Trigger iocs. Before_insert
before Insert
on IOCS. baseinfo 
referencing new as new old as old
for each ROW
DECLARE
Tmpvar number;
BEGIN
   Tmpvar: = 0;

   SELECT myseq.nextval into Tmpvar from dual;
  : NEW. Sequencecolumn: = Tmpvar;
  : NEW. CreatedDate: = sysdate;
  : NEW. Createduser: = USER;

   EXCEPTION
     when OTHERS and then
       -Consider logging the error and then re-raise
       raise;
END;

Three, view:
View introduction:  

a view is a logical table that is based on a table or multiple tables or views , which itself does not contain data that can be queried and modified by the data in the table. The table that the view is based on is called the base table. A view is a SELECT statement that is stored in a data dictionary.  You can extract the logical collection or combination of data by creating a view .

Advantages of the view :

1. Access to the database because the view can be selected as part of a selective database.
2. Users can get results from complex queries through simple queries.
3. Maintain the independence of data and attempt to retrieve data from multiple tables.
4. Different views can be generated for the same data.

Syntax for the view:

1. CREATE [OR REPLACE] [force| Noforce] VIEW view_name
3. [(alias[, alias] ...)]
5. As subquery
6. [With CHECK OPTION [CONSTRAINT CONSTRAINT]]
7. [With READ only]

which
OR REPLACE:ORACLE rebuilds the view automatically if an attempt to create it already exists;
Force: The View is automatically created by ORACLE regardless of the presence of the base table;
Noforce: Only the base table exists for ORACLE to create the view :
Alias: The aliases defined for the columns generated by the view ;
Subquery: A complete SELECT statement in which the alias can be defined;
With CHECK OPTION:
The data rows inserted or modified must satisfy the constraints of the view definition;
With READ only:
No DML operations can be performed on this view .

For example:

1. CREATE OR REPLACE VIEW DEPT_SUM_VW
2. (name,minsal,maxsal,avgsal)
3. As SELECT d.dname,min (e.sal), Max (e.sal), avg (e.sal)
4. From EMP e,dept D
5. WHERE E.deptno=d.deptno
6. GROUP by D.dname;

Iv. sequence (Sequence):

Sequence is a sequence of numbers automatically added by a database system in accordance with certain rules. This sequence is generally used as the surrogate primary key (because it is not duplicated) and has no other meaning. Sequence is the characteristic of database system, some databases implement sequence, others do not. For example, Oracle, DB2, PostgreSQL database implementation Sequence,mysql, SQL Server, Sybase and other databases do not sequence.

Sequence Syntax:
Defines a seq_test with a minimum value of 10000, a maximum value of 99999999999999999, starting at 20000, an incremental step of 1, and a cyclic sort sequence with a cache of 20. How Oracle is defined:

Create sequence Seq_testminvalue 10000maxvalue 99999999999999999start with 20000increment by 1cache 20cycleorder;

Stored Procedure View trigger sequence