Process and functions
The processes and functions are stored in the database in the database, and the function can have multiple parameters and there is a return value without parameters. The process has zero or more parameters, no return value. Functions and processes can receive or return zero or more values through the parameter list, and the main difference between functions, and processes is not to return values, but their call mode. The process is called as a standalone executive statement:
Pay_Involume (Invoice_nbr, 30, Due_Date);
The function is called in a legal expression:
Order_volumn: = Open_ORDERS (Sysdate, 30);
The syntax of the creation process is as follows:
Create [or report] procedure_name [parameter_lister] {as | is} declaration_sectionBeginexecutable_section [exceptionException_section] End [Procedure_name]
The syntax of each parameter is as follows:
Paramter_name mode datatype [(: = | default) value]
Mode has three forms: IN, OUT, INOUT.
In indicates that the value of the actual parameter is passed to the process when the calling process is passed, and the form parameter is considered to be read-only. When the process ends, the control will return to the control environment, and the value of the actual parameter will not change.
The value of the actual parameter will be ignored when the process is called, and the internal form parameters can only be assigned, and the data cannot be read from the process, and the content of the formal parameters after the process will be given the actual parameters.
This mode is a combination of IN and OUT; the value of the actual parameters within the process will pass to the form parameters, the value of the situation parameters can be written, and the value of the situation parameters will be given the actual parameters after the process is over.
The syntax of the creation function is basically the same, the only difference is that the function has a Returen clause.
CREATE [OR REPLACE] FINCTION [schema.] Function_name [parameter_list] RETURN returning_datatype {AS | IS} declaration_sectionBEGINexecutable_section [EXCEPTION] exception_sectionEND [procedure_name]
There must be or more Return statements in the execution part of the function.
You can call a single line function and group function in the creation function, for example:
Create or replace function my_sin (Degreesin in number) Return Numberis Pi Number = ACOS (-1); Radiansperdegree Number;
Beginradiansperdegree = pi / 180; return (sin (Degreesin * radiansperdegree); end package
Package is a container that is bundled with process, function, and data structure; package consists of two parts: external contributor specifications, including function heads, process heads, and external visual data structures; another part is the main body ( Package Body), the cover body contains all the bundled processes and functions, execution, and exception handling parts.
Packaged PL / SQL programs and a big difference without packaging, the package data has been existing during the entire session of the user, and when the user gets the execution authorization, it is equal to all programs and data structures in the package specification. Permission. But you can't just authorize only a function or process in the package. The package can be overloaded from the process and function, and multiple programs can be declared in the package, and the correct program is called according to the number of parameters and the data type of the data.
Create package must first create the package specification, the package specification created syntax: CREATE [OR REPLACE] PACKAGE package_name {AS | IS} public_variable_declarations | public_type_declarations | public_exception_declarations | public_cursor_declarations | function_declarations | procedure_specificationsEND [package_name]
Create a package body Using the CREATE PACKAGE BODY statement:
CREATE [OR REPLACE] PACKAGE BODY package_name {AS | IS} private_variable_declarations | private_type_declarations | private_exception_declarations | private_cursor_declarations | function_declarations | procedure_specificationsEND [package_name]
Private data structures are those who are internally in the main body, which is invisible to the called program.
Trigger
The trigger is a program that automatically performs a response database. Can be set to trigger or execute before or after the trigger event. Events that can trigger trigger events include the following:
DML event DDL event database event
The DML event trigger can be a statement or a row trigger. The DML statement trigger triggers the DML row-level trigger before or after the speech statement is triggered before or after the row changes affecting the statement. Users can define multiple triggers to a single event and type, but there is no way to enhance the command of the multi-trigger trigger. The following table lists the trigger events that users can take advantage of:
Event Trigger Description INSERT Trigger Trigger DELETE Trigger Trigger DELETE Trigger Trigger CREATE When you remove a row from a table or view when you use a CREATE statement to use the CREATE statement to use the CREATE statement when you are inserting a row. Item Increases an object When the trigger ALTER triggers the trigger DROP when using the ALTER statement to change a database or item, trigger the trigger Start when the trigger Start is triggered when using the DROP statement to remove the trigger Start to turn on the trigger, in After the event triggers shutdown to turn off the database, triggering the logon before the event is triggered when a session is established, and the event triggers logoff when the session is turned off. Trigger the trigger when the server is triggered, and the event is triggered.
The syntax for creating a trigger is as follows:
Create [or report] Trigger_name {before | after | instead of} Eventon {Table_OR_VIEW_NAME | DATABASE} [for Each Row [hen condition]] TRIGGER_BODY
Only the DML trigger (INSERT, UPDATE, DELETE) statement can use the INSTEAD OF trip and only table DML triggers can be a Before or an AFTER trigger.
Like constraints can be set to disable or enable to turn off or open their actuat (Execute), set the trigger to disable or enable the use of ALTER TRIGGER statements:
Alter Trigger trigger_name enable; alter trigger trigger_name disable;
Use the ALTER TABLE statement to disable or enable the table
Alter Trigger Table_name Disable All Trigger; ALTER TRIGGER TABLE_NAME ENABLE All Trigger;
Delete trigger uses DROP TRIGGER
DROP TRIGGER TRIGGER_NAME; Data Dictionary
Oracle Data Dictionary contains metadata of the user database. With Underline Table Name, with Obj $, UET $, Source $, which is created by SQL.BSQ scripts during the execution of the CREATE DATABASE statement. Under normal circumstances, users rarely access these tables. Script Catalog.sql (usually at $ ORACLE_HOME / RDBMS / Admin) runs immediately after the CREATE DATABASE statement, create a data dictionary view.
Data dictionary views can be roughly divided into three categories:
. Prefix the data dictionary view of the user_ contains information about the object owned by the user.
The data dictionary view of the ALL_ contains information that the user is currently accessible.
The prefix is a DBA_ 's data dictionary view, which contains information about all objects and permissions owned by the database.
There are view families like DBA_Tables, All_Tables, and User_Tables, all of the data dictionary views. There are more than 100 view families in Oracle, so we must fully introduce these views of these views that are monotonous and not much. The most important and most common view families are listed in the table below, and you need to pay attention to each view family has a DBA_, an all_ a USER_ view.
View Family Description Col_privs contains column permissions for tables, including grantors, granted people, and permissions EXTENTS data range information, such as data files, data segment names (segment_name), and size indexes index information, such as type, uniqueness And the table IND_COLUMNS index column information, such as the column of the index Objects object information, such as status and DDL Time Role_privs role permissions, such as the GRANT and Admin Options Segments table and index data segment information, such as TableSpace and Storage Sequecnces Sequence information, such as Cache, Cycle, and AST_NUMBER SOURCE in addition to all built-in processes, functions, functions of the trigger, such as referenced objects and database links DB_LINK SYS_PRIVS system permissions, such as Grantee, Privilege, Admin Option Tab_columns tables and view column information, including column data type Tab_Privs table permissions, such as grantors, granted people, and permissions tables table information, such as tablespace, storage parameters, quantity Triggers Trigger information, such as type, event, trigger, User information, such as temporary and default table space Views view information, including view definitions
There are also some unused data dictionary tables in Oracle, but these tables are not a real dictionary family, they are all important single views.
View Name Description User_col_privs_made User granted the listed permission user_col_privs_recd User Joller USER_TAB_PRIVS_MADE User Grants Other Personal_Tab_Privs_Recd User Get Terms Limits
The main thing in the other dictionary is V $ view, which is called because they all start with V $ or GV $. V $ view is based on X $ virtual view. V $ view is owned by the SYS user. Under the default situation, only SYS users and users with DBA system privileges can see all views, users without DBA privileges can see User_ and all_ view, but not See the DBA_ view. In contrast to DBA_, ALL, and User_ view, these views are visually given information. Uppening for a few weeks from a large system for several weeks, each statement
Manually enter the statement with a username variable, then enter each user name, this takes a few hours of time
Write a SQL statement to generate the needs of the Alter User statement, then execute him, this takes only a few minutes.
Obviously we will choose to generate SQL:
example:
Select 'alter user' || username || 'Temporary TableSpace Temp;' from dba_userswhere username <> 'sys' and temporary_tablespace <> 'Temp';
The result of this query will be processed from a file and then executed:
ALTER USER SYSTEM TEMPORARY TABLESPACE temp; ALTER USER OUTLN TEMPORARY TABLESPACE temp; ALTER USER DBSNMP TEMPORARY TABLESPACE temp; ALTER USER SCOTT TEMPORARY TABLESPACE temp; ALTER USER DEMO TEMPORARY TABLESPACE temp;
