PL/SQL Programming (iii) package and package body, triggers, views, indexes

I. Package and package body

Package: A set of subroutines, variable definitions stored in a database. A subroutine in a package can be called by another package or subroutine. However, if you declare a local subroutine, you can call that local subroutine only in the block that defines the local subroutine.

It has the characteristics of object-oriented programming language and is the encapsulation of these PL/SQL programming elements. Packages are similar to classes in the Java language, where variables are equivalent to member variables in a class, and procedures and functions are equivalent to class methods.

Create or ReplacePackage Stuinfo astype Stucur isRefcursor; procedureShowName (SCLAinch  Number, stus out stucur);EndStuinfo;Create or ReplacePackage Body Stuinfo as  procedureShowName (SCLAinch  Number, Stus out Stucur) as  begin    OpenStus for      Select *  fromStudent SwhereS.class=Scla; End;EndStuinfo;

--Created on 2017/8/16 by ADMINISTRATORDeclare   --Local variables hereIinteger; Type STUC isRefcursor;  STS STUC; Stu Student%RowType; begin  --Test Statements hereStuinfo.showname (95033, STS); LoopFetchSTS intoStu; Exit  whenSts%NotFound;    Dbms_output.put_line (Stu.sname); EndLoop;End;

Two

1. Trigger:

Triggers and stored procedures are similar, triggers can invoke stored procedures, but triggers do not need to be called, and triggers can only be triggered by specific events to which data is available.

Specific triggering events:

The user blocks DML operations in the specified table or view, mainly: Insert,update,delete, etc.

The user does the DDL operation, mainly has: Create,alter,drop and so on.

Database events, mainly including: Logon/logoff user logon or logoff.

The Startup/shutdown database is turned on or off.

Erros specific error messages, and so on.

2. Effects of triggers:

Triggers can be called according to different events, with more granular control, can complete a lot of ordinary statements do not function, the main role:

Automatically generate self-growing fields.

Execute more complex business logic.

Prevent meaningless operations.

Provide audits.

Allow or restrict modification of some tables.

Implement the integrity rules.

Ensure synchronous replication of data.

Type of Trigger:

3. Types OF Triggers

Data manipulation language (DML) triggers:

This trigger is defined on a table, and performing a insert,update,delete operation on a table is a trigger that can fire that type. This trigger can be used to copy, check, and replace some data that matches the specified criteria. The trigger level can be divided into two types, the first row-level trigger, which indicates that each record is fired to the trigger, and the second statement-level trigger, which indicates that the SQL statement execution actually sends the trigger, regardless of how many records are modified. The data Change event is subject to the before and after two types.

Data definition language (DDL) triggers. When the Create,alter,drop mode object is triggered, the related trigger is fired. In Oracle, it is easy to understand that a user has a pattern with the same name, which allows some tables to be modified or deleted.

Composite Trigger:

The new feature of oracle11g, which is equivalent to including four triggers in a trigger, contains the statement level of the before type, the row level of the before type, the statement level after type, and the row level of the after type. This makes the transfer of variables more convenient.

Instead OF triggers:

This type of trigger usually works on the view, and it is generally not allowed for view-block DML operations by multiple source tables, which can be used to resolve the problem with instead OF triggers. It allows you to convert the DML operation of a view to work on multiple source tables.

User and System event triggers:

Triggers that act on database events that are triggered by an upstream database event, such as logon logoff, which can be used to record the database's logon status.

4. Examples of triggers:

--set up a trigger, when the Employee table EMP table is deleted a record, the deleted records are written to the Staff table delete log table. CREATE TABLEEmp_his as SELECT *  fromEmpWHERE 1=2; CREATE OR REPLACE TRIGGERTr_del_emp beforeDELETE --specifies that the trigger time is triggered before the delete operation    onscott.emp forEach ROW--description Creates a row-level triggerBEGIN   --inserts the pre-modified data into the Log record table del_emp for monitoring use.    INSERT  intoemp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)VALUES(: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);END;DELETEEmpWHEREEmpno=7788;DROP TABLEEmp_his;DROP TRIGGERDel_emp;

Iii. views and indexes

A view is a table that is exported from one or more tables or views. A view is a virtual table in which the data is not actually stored, the definition of the view is stored in the database, and when the view data is manipulated, the system operates the base table associated with the view according to its definition.

We can use the view as a table, but it is important to note that there is no limit to the query view, and that inserting/updating/deleting views is limited; All operations on the view will affect the base table of the view; To prevent users from indirectly modifying the data of the base table through the view, you can create the view as a read-only view ( With the Read Only option).

--Build Table:Create TableStudent (IdChar(6), Namevarchar2(8), SexChar(2), class_idChar(4));Create Unique Indexindex_id onStudent (Id);Create  IndexIndex_name onStudent (Name);CreateBitmapIndexIndex_sex onStudent (SEX);

Conn Scott/Tiger; Grant Insert,update,delete on to system;conn system/  orcl1234; Create or Replace VIEW v_emp  as Select   from Scott.emp;

Index

In order to improve the speed of the query, when the user is not satisfied with the query speed and needs to adjust the performance of the database, it is preferred to establish an index.

CREATE INDEX  on DESC);

Proper use of indexes can improve the speed of data retrieval and can create indexes on fields that frequently require queries.

When you add rows to a table or delete rows from a table, you must spend extra time updating the index of the table, so when you need to retrieve a few rows from a large table, you create the index. Generally we think indexes are useful when any single query is to retrieve rows that are less than 10% of the total row count of the entire table.

The table's primary key and unique key will automatically create the index.

PL/SQL Programming (iii) package and package body, triggers, views, indexes