Oracle procedures and functions

The oracle process and function process and function process and function are both stored in the database in the form of compilation. The function can have multiple parameters and return values without any parameters. 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 in 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_sectionBEGINexecutable_section [EXCEPTIONexception_section] END [procedure_name] the syntax of each parameter is as follows: paramter_name mode datatype [(: = | DEFAULT) value] mode can: 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 the function has a RETUREN clause CREATE [or replace] FINCTION [schema.] function_name [parameter_list] RETURN returning_datatype {AS | IS} declaration_sectionBEGINexecutable_section [EXCEPTION] prediction_sectionend [procedure_name] Some functions must have multiple return statements. You can call single-row and group functions 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 is a container that binds processes, functions, and data structures; the package consists of two parts: the external visual packet specification, including the function header, process header, and external visual data structure. The other part is the package body ), 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} release | public_type_declarations | events | function_declarations | procedure_specificationsEND [package_name: 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 inside the package body and are invisible to the called program. Triggers is a program that automatically responds to database changes. It can be set to trigger or execute before or after a trigger event. The following types of events can trigger a trigger event: DML event DDL event database event DML event trigger can be a statement or row-Level Trigger. 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: When a row is inserted to a table or view, trigger the trigger to UPDATE a row in the table or view. Trigger the trigger to DELETE a row from the table or view. Trigger CREATE trigger. Use when the CREATE statement adds an object to a database or project, trigger the trigger ALTER. When the ALTER statement is used to change the object of a database or project, trigger the trigger DROP. When the DROP statement is used to delete the object of a database or project trigger: START: trigger when the database is opened, it is triggered when SHUTDOWN is triggered after the event to shut down the database. before the event, LOGON is triggered when a session is established. before the event, LOGOFF is triggered when the session is closed, the TRIGGER is triggered when a SERVER error occurs before the event. The syntax for triggering the TRIGGER after the event is as follows: CREATE [or replace] TRIGGER trigger_name {before | after | ins Tead of} eventON {table_or_view_name | DATABASE} [for each row [WHEN condition] trigger_body only has DML triggers (INSERT, UPDATE, DELETE) the statement can use the instead of trigger, and only the DML trigger OF the table can be the BEFORE or AFTER trigger. Like a constraint, triggers can be set to disabled or enabled to close or open their EXECUTE bodies (EXECUTE), and set the TRIGGER to disabled or ENABLE the use of alter trigger statement: alter trigger trigger_name ENABLE; alter trigger trigger_name DISABLE; To DISABLE or enable all triggers of 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 trigger_name; Data Dictionary Oracle Data Dictionary contains the metadata of the user 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. The data dictionary view can be roughly divided into three types: the data dictionary view with the prefix of. USER _, which contains information about the objects owned by the USER. 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 contains the table's column permissions, including the grants, grants, and permission EXTENTS data range information, such as data files, Data Segment names (segment_name) and the size of the INDEXES index information, such as the type, uniqueness, and information of the IND_COLUMNS index column of the involved table, such as the OBJECTS object information of the column sorting method on the index, for example, state 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, for example, the sequence cache, cycle, and ast_number SOURCE except for all built-in processes, functions, and package SOURCE code SYNONYMS alias information of the trigger, such as the referenced object and database link db_link SYS_PRIVS system permission, for example, grantee, privilege The column information of the TAB_COLUMNS table and view in the admin option, including the permission of the TAB_PRIVS table of the column data type, such as the authorizer, the authorizer, and the permission TABLES Table information, such as the table space ), TRIGGERS trigger information of storage parms and number of data rows, such as type, event, trigger body, and USERS user information, for example, temporary and default table space VIEWS, including view definitions, and some infrequently used data dictionary tables in Oracle, are not really dictionary families, they are all important single views. View name Description ‑ column permissions granted to others by users USER_COL_PRIVS_RECD the column permissions granted by users USER_TAB_PRIVS_MADE the table permissions granted to others by users USER_TAB_PRIVS_RECD the table permissions granted by users other dictionary views mainly V $ VIEW, 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. In a large system, manually enter each statement for several weeks, manually enter the statement with the username variable, and then enter each username. This takes several hours to write an SQL statement, generate the required alter user statement and execute it. It takes only a few minutes to clearly select the SQL generation method: Example: SELECT 'alter user' | username | 'temporary TABLESPACE temp; 'FROM DBA_USERSWHERE username <> 'sys' AND temporary_tablespace <> 'temp '; the query results will be processed offline to a file, and then executed: alter user system temporary tablespace temp; alter user outln temporary tablespace temp; ALTER USER DBSNMP TEMPORARY TABLE SPACE temp; alter user scott temporary tablespace temp; alter user demo temporary tablespace temp; Process and function processes and functions are stored in the database in compiled form, A 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 in 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_sectionBEGINexecutable_section [EXCEPTIONexception_section] END [procedure_name] the syntax of each parameter is as follows: paramter_name mode datatype [(: = | DEFAULT) value] mode can: 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 the function has a RETUREN clause CREATE [or replace] FINCTION [schema.] function_name [parameter_list] RETURN returning_datatype {AS | IS} declaration_sectionBEGINexecutable_section [EXCEPTION] prediction_sectionend [procedure_name] Some functions must have multiple return statements. You can call single-row and group functions 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 is a container that binds processes, functions, and data structures; the package consists of two parts: the external visual packet specification, including the function header, process header, and external visual data structure. The other part is the package body ), 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} release | public_type_declarations | events | function_declarations | procedure_specificationsEND [package_name: 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 inside the package body and are invisible to the called program. Triggers is a program that automatically responds to database changes. It can be set to trigger or execute before or after a trigger event. The following types of events can trigger a trigger event: DML event DDL event database event DML event trigger can be a statement or row-Level Trigger. 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: When a row is inserted to a table or view, trigger the trigger to UPDATE a row in the table or view. Trigger the trigger to DELETE a row from the table or view. Trigger CREATE trigger. Use when the CREATE statement adds an object to a database or project, trigger the trigger ALTER. When the ALTER statement is used to change the object of a database or project, trigger the trigger DROP. When the DROP statement is used to delete the object of a database or project trigger: START: trigger when the database is opened, it is triggered when SHUTDOWN is triggered after the event to shut down the database. before the event, LOGON is triggered when a session is established. before the event, LOGOFF is triggered when the session is closed, the TRIGGER is triggered when a SERVER error occurs before the event. The syntax for triggering the TRIGGER after the event is as follows: CREATE [or replace] TRIGGER trigger_name {before | after | ins Tead of} eventON {table_or_view_name | DATABASE} [for each row [WHEN condition] trigger_body only has DML triggers (INSERT, UPDATE, DELETE) the statement can use the instead of trigger, and only the DML trigger OF the table can be the BEFORE or AFTER trigger. Like a constraint, triggers can be set to disabled or enabled to close or open their EXECUTE bodies (EXECUTE), and set the TRIGGER to disabled or ENABLE the use of alter trigger statement: alter trigger trigger_name ENABLE; alter trigger trigger_name DISABLE; To DISABLE or enable all triggers of 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 trigger_name; Data Dictionary Oracle Data Dictionary contains the metadata of the user 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. The data dictionary view can be roughly divided into three types: the data dictionary view with the prefix of. USER _, which contains information about the objects owned by the USER. 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 contains the table's column permissions, including the grants, grants, and permission EXTENTS data range information, such as data files, Data Segment names (segment_name) and the size of the INDEXES index information, such as the type, uniqueness, and information of the IND_COLUMNS index column of the involved table, such as the OBJECTS object information of the column sorting method on the index, for example, state 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, for example, the sequence cache, cycle, and ast_number SOURCE except for all built-in processes, functions, and package SOURCE code SYNONYMS alias information of the trigger, such as the referenced object and database link db_link SYS_PRIVS system permission, for example, grantee, privilege The column information of the TAB_COLUMNS table and view in the admin option, including the permission of the TAB_PRIVS table of the column data type, such as the authorizer, the authorizer, and the permission TABLES Table information, such as the table space ), TRIGGERS trigger information of storage parms and number of data rows, such as type, event, trigger body, and USERS user information, for example, temporary and default table space VIEWS, including view definitions, and some infrequently used data dictionary tables in Oracle, are not really dictionary families, they are all important single views. View name Description ‑ column permissions granted to others by users USER_COL_PRIVS_RECD the column permissions granted by users USER_TAB_PRIVS_MADE the table permissions granted to others by users USER_TAB_PRIVS_RECD the table permissions granted by users other dictionary views mainly V $ VIEW, 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. In a large system, manually enter each statement for several weeks, manually enter the statement with the username variable, and then enter each username. This takes several hours to write an SQL statement, generate the required alter user statement and execute it. It takes only a few minutes to clearly select the SQL generation method: Example: SELECT 'alter user' | username | 'temporary TABLESPACE temp; 'FROM DBA_USERSWHERE username <> 'sys' AND temporary_tablespace <> 'temp '; the query results will be 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;