Oracle Database data Object Analysis (next)

oracle| Objects | data | database procedures and functions

Procedures and functions are stored in the database in a compiled form, and functions can have more than one parameter and have a return value without parameters. The procedure has 0 or more parameters and does not have a return value. Both functions and procedures can receive or return 0 or more values through a parameter list, and the main difference between functions and procedures is not in the return value, but in how they are invoked. The procedure is invoked as a standalone execution statement:

Pay_involume (invoice_nbr,30,due_date);

The function is invoked in a legitimate expression:

Order_volumn:=open_orders (sysdate,30);

The syntax for creating the procedure is as follows:

CREATE [OR REPLACE] PROCEDURE [schema.] Procedure_name
[Parameter_lister]
{As|is}
Declaration_section
BEGIN
Executable_section
[EXCEPTION
Exception_section]
End [Procedure_name]
The syntax for each parameter is as follows:

Paramter_name mode datatype [: =| DEFAULT) value]

Mode has three kinds of forms: in, out, INOUT.

In means that when the procedure is invoked, the value of the actual parameter is passed to the process, and the formal parameter is considered read-only, and when the process ends, the control returns to the control environment, and the value of the actual parameter does not change.

Out the actual parameter values are ignored when the procedure is invoked, in which the form parameter can only be assigned, not read from, and the contents of the form parameter are given the actual parameters at the end of the procedure.

InOut This pattern is a combination of in and out; the value of the actual parameter in the process is passed to the form parameter, the value of the situation parameter can be read and written, and the value of the situation parameter will be given the actual parameter after the process is finished.

The syntax for creating a function is essentially the same as the syntax of the procedure, except that the function has a returen clause

CREATE [OR REPLACE] finction [schema.] Function_name
[Parameter_list]
Return Returning_datatype
{As|is}
Declaration_section
BEGIN
Executable_section
[EXCEPTION]
Exception_section
End [Procedure_name]
Part of the function must have yo or multiple return statements.

You can call Single-line functions and group functions in the CREATE function, for example:

CREATE OR REPLACE FUNCTION my_sin (degreesin in number)
return number
Is
Pi Number=acos (-1);
Radiansperdegree number;

BEGIN
radiansperdegree=pi/180;
Return (SIN (Degreesin*radiansperdegree));
End Package

A package is a process, a container in which functions and data structures are bundled; A package consists of two parts: an external visual package specification, including function headers, process headers, and external visual data structures; the other part is the package body (package), which contains the declaration, execution, and implementation of all bundled processes and functions. Exception handling section.

There is a big difference between packaged pl/sql and not packaged, packet data persists throughout the user's session, and when the user obtains the execution authorization for the package, it is equal to the permissions for all programs and data structures in the package specification. However, you cannot authorize only one function or procedure in a package. A package can overload procedures and functions, in which multiple programs can be declared with the same name, and the correct program is invoked at run time based on the number of parameters and the data type.

To create a package, you must first create the package specification, and the syntax for creating the package specification is as follows:

CREATE [OR REPLACE] PACKAGE package_name
{As|is}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
Procedure_specifications
End [Package_name]
Create a package body using the Create PACKAGE bodies 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_specifications
End [Package_name]
Private data structures are those that are inside the package body and are not visible to the invoked program.

Triggers (triggers)

A trigger is a program that automatically performs a response to a database change. Can be set to be triggered or executed before or after a trigger event. The events that trigger the trigger event can include the following:

DML Events
DDL events
Database events

A DML event trigger can be a statement or a row-level trigger. A DML statement trigger triggers a DML row-level trigger before or after a statement is triggered, before or after the row changes affected by the statement. Users can define multiple triggers for a single event and type, but there is no way to enhance a command triggered by multiple triggers. The following table lists the trigger events that users can take advantage of:

Event Trigger Description Insert triggers update when you insert a row into a table or view To update a row in a table or view trigger delete triggers the trigger delete when a row is deleted from a table or view triggering trigger alter triggers when you use the CREATE statement to add an object to a database or project when you use the ALTER statement to trigger drop when you change an object for a database or project using the DRO When the P statement deletes a database or project object, triggers the trigger when the trigger is triggered when the database is opened, triggered when shutdown closes the database after the event, triggering logon before the event triggers when a session is established, triggering logoff when a session is closed, Triggers a trigger when a server server error occurs before an event triggers
The syntax for creating triggers is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name
{Before|after|instead of} event
on {table_or_view_name| DATABASE}
[For each row[when condition]]
Trigger_body
Only DML triggers (INSERT, UPDATE, DELETE) statements can use the instead of trigger and only the table's DML triggers can be before or after triggers.

Triggers, like constraints, can be set to disable or enable to turn off or open their execution (execute), and set the trigger to disable or enable the use of the ALTER TRIGGER statement:

ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
To disable or enable all triggers for a table, use the ALTER TABLE statement

ALTER TRIGGER table_name DISABLE all TRIGGER;
ALTER TRIGGER table_name ENABLE all TRIGGER;
Delete triggers use drop TRIGGER

DROP TRIGGER trigger_name;
Data dictionary

The Oracle data dictionary contains metadata for the user database. The underlined table names are obj$, uet$, source$, which are created by the SQL.BSQ script during the execution of the CREATE DATABASE statement, and are typically rarely accessed by users. Script Catalog.sql (usually located in $oracle_home/rdbms/admin) runs immediately after the CREATE DATABASE statement, creating a data dictionary view.

The data dictionary view can be broadly grouped into three categories:

A data dictionary view with a prefix of user_ that contains information about the objects that the user owns.

A data dictionary view prefixed with All_ that contains information about all objects and permissions that the user currently has access to.

A data dictionary view prefixed with Dba_ that contains information about all objects and permissions that are owned by the database.

There are view families like Dba_tables,all_tables and user_tables in most data dictionary views. There are more than 100 view families in Oracle, so it is tedious and meaningless to introduce these views across the family. The most important and commonly used view families are listed in the following table, and it should be noted that each view family has a dba_, a All_ a user_ view.

The View Family description col_privs contains column permissions for the table, including the grantor, the grantee, and the permissions extents data range information, such as data files, data segment names (segment_name), and size indexes index information, such as type, Uniqueness and table ind_columns indexed column information, such as the sort of columns on the Index objects object information, such as status and DDL Timerole_privs role permissions, such as the grant and admin options segments table and index data segment information , such as tablespace and storagesequecnces sequence information, such as cache, cycle of sequences, and ast_numbersource all built-in procedures, functions, package source code synonyms alias information except triggers. such as referenced objects and database links db_linksys_privs system permissions, such as grantee, privilege, admin options tab_columns table and view column information, including column data type Tab_privs table permissions, such as the grantor, The delegate and Permission tables table information, such as table space (tablespace), storage parameters (storage parms), and number of data rows triggers trigger information, such as type, event, trigger body (trigger) users information, such as temporary and default table space views view information, including view definition
There are also some infrequently used data dictionary tables in Oracle, but these tables are not real dictionary families, they are important single views.

The VIEW name describes the column permissions that the User_col_privs_made user gives to others user_col_privs_recd the column permissions that the user obtains user_tab_privs_made the table permissions that the user grants to others User_tab_privs_ RECD User-Obtained table permissions
The main thing in other dictionary views is the v$ view, which is called because they all start with v$ or gv$. The v$ view is based on the x$ virtual view. The v$ view is owned by the SYS user and, by default, only the SYS user and users with DBA system privileges can see all views, and users without DBA authority can see the user_ and All_ views, but cannot see the Dba_ view. In contrast to database-oriented information in Dba_,all and user_ views, these views give an instance-oriented message.

For a few weeks on a large system, manually enter each statement

It takes several hours to manually enter a statement with a user name variable and then enter each user name.

Write an SQL statement, generate the required ALTER USER statement, and then execute him, it only takes a few minutes

It is clear that we will choose the method for generating SQL:

Cases:

SELECT ' ALTER USER ' | | username| |
' Temporary tablespace temp; '
From Dba_users
WHERE username<> ' SYS '
and Temporary_tablespace
The results of this query are processed offline to 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;