Oracle Database Data Object analysis (II)

　 Procedures and functions

Both the process and function are stored in the database in the form of compilation. The function can have either no parameter or multiple parameters and return a value. The process has zero or multiple parameters and no return value. Functions and processes can receive or return zero or multiple values through the parameter list. The main difference between a function and a process is not the return value, but the call method. The process is called as an independent execution statement:

Pay_involume (invoice_nbr, 30, due_date );

The function is called using a valid expression:

Order_volumn: = open_orders (sysdate, 30 );

The syntax of the creation process is as follows:

Create [or replace] procedure [schema.] procedure_name [Parameter_lister] {As | is} Declaration_section Begin Executable_section [Exception Prediction_section] End [procedure_name]

The syntax for each parameter is as follows:

Paramter_name mode datatype [(: = | default) value]

There are three modes: In, out, And inout.

In indicates that during the call process, the actual parameter value is passed to the process. The formal parameter is considered read-only. When the process ends, the control will return to the control environment, the actual parameter value does not change.

When an out API is called, the actual parameter values are ignored. In the process, parameters can only be assigned values, but cannot be read from them, after the process ends, the content of the formal parameter is assigned to the actual parameter.

The inout mode is a combination of in and out. The value of the actual parameter inside the process is passed to the form parameter, and the value of the situation parameter can be read and written. After the process ends, the value of the situation parameter is assigned to the actual parameter.

The syntax for creating a function is basically the same as that for a process. The only difference is that a 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] Prediction_section End [procedure_name]

Some functions must have multiple return statements.

You can call single-row and group functions in creating a 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 container that binds processes, functions, and data structures. A package consists of two parts: external visual package specifications, including function headers and process headers, the package body contains the declaration, execution, and exception handling of all bundled processes and functions.

The packaged PL/SQL program is very different from the one not packaged. The package data exists throughout the user's session. When the user obtains the package execution authorization, it is equivalent to obtaining permissions for all programs and data structures in the package specification. However, you cannot only authorize a function or process in the package. The package can reload processes and functions. In the package, multiple programs can be declared with the same name. during runtime, the correct program is called based on the number of parameters and data types.

To create a package, you must first create a package specification. The syntax for creating a 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]

Use the create package body statement to create a package body:

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 inside the package body and are invisible to the called program.

　　Triggers)

A trigger is a program that automatically responds to database changes. It can be set to trigger or execute before or after a trigger event. Events that trigger events include the following:

DML events
DDL events
Database events

DML event triggers can be statements or row-level triggers. The DML statement trigger triggers the DML row-level trigger before or after the statement changes. You can define multiple triggers for a single event and type, but there is no way to enhance the multi-trigger command. The following table lists trigger events that can be used by users:

Event	Trigger description
Insert	Trigger when inserting a row into a table or view
Update	Trigger triggered when updating a row in a table or view
Delete	Trigger triggered when a row is deleted from a table or view
Create	Trigger triggered when an object is added to a database or project using the create statement
Alter	Triggers a trigger when an alter statement is used to change an object of a database or project.
Drop	Trigger triggered when the drop statement is used to delete objects of a database or project
Start	Trigger when the database is opened, and trigger after the event
Shutdown	Triggered when the database is shut down and before the event
Logon	Triggered when a session is established and before the event
Logoff	Triggered when the session is closed and before the event
Server	Trigger when a server error occurs and trigger after the event

The syntax for creating a trigger 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, and delete) can use instead of triggers, and only table DML triggers can be before or after triggers.

Like a constraint, triggers can be set to disabled or enabled to close or open their execute bodies (execute), and the triggers can be set to disabled or enabled using 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 trigger use drop trigger

Drop trigger trigger_name;

　　Data Dictionary

The Oracle Data Dictionary contains the metadata of your database. The names of underlined tables include OBJ $, uet $, and source $. the bsq script is created. Generally, users seldom access these tables. The script catalog. SQL (usually located at $ ORACLE_HOME/rdbms/admin) runs immediately after the CREATE DATABASE statement to create a data dictionary view.

Data Dictionary views can be roughly divided into three types:

The data dictionary view prefixed with "User _" contains information about objects owned by users.

The data dictionary view with the prefix of all _ contains information about all objects and permissions that can be accessed by the user.

The data dictionary view prefixed with DBA _ contains information about all objects and permissions of the database.

In most data dictionary views, there are view families such as dba_tables, all_tables, and user_tables. There are more than 100 view families in Oracle, so it is boring and meaningless to fully introduce these view families. The following table lists the most important and commonly used view families. Note that each view family has a DBA _, an all _, and a user _ view.

View family)	Description
Col_privs	Includes the column permissions of the table, including the grants, grants, and permissions.
Extents	Data range information, such as data file, data segment name (segment_name) and size
Indexes	Index information, such as type, uniqueness, and involved table
Ind_columns	Index column information, such as the column sorting method on the index
Objects	Object information, such as status and DDL time
Role_privs	Role permissions, such as grant and Admin options
Segments	Table and index data segment information, such as tablespace and storage
Sequecnces	Sequence information, such as sequence cache, cycle, and ast_number
Source	Source code of all built-in processes, functions, and packages except triggers
Synonyms	Alias information, such as the referenced object and database link db_link
Sys_privs	System permissions, such as grantee, privilege, and Admin options
Tab_columns	Column information of tables and views, including the column data type
Tab_privs	Table permissions, such as grant, granted, and permissions
Tables	Table information, such as the number of tablespaces, storage parameters, and data rows
Triggers	Trigger information, such as type, event, trigger body)
Users	User information, such as temporary and default tablespace
Views	View information, including view definition

There are also some uncommon data dictionary tables in Oracle, but these tables are not really dictionary families, they are all important single views.

View name	Description
User_col_privs_made	Columns granted by users to others
User_col_privs_recd	Column permissions obtained by the user
User_tab_privs_made	Table permissions granted to others
User_tab_privs_recd	Table permissions obtained by the user

In other dictionary views, the main view is V $. This is because they all start with V $ or GV $. The V $ view is based on the x $ virtual view. The V $ view is owned by sys users. By default, only sys users and users with DBA system permissions can view all views, users without DBA permission can see the user _ and all _ views, but not the DBA _ views. In contrast to database-oriented information in DBA _, all, and user _ views, these views provide instance-oriented information visually.

Manually input each statement for weeks on a large system

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 it. This takes only a few minutes.

Obviously, we will select the method for generating SQL:

Example:

Select 'alter user' | username | 'Temporary tablespace temp ;' From dba_users Where username 'sys' And temporary_tablespace 'temp ';

The query results will be processed offline into 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;