Pl/sql process

To use the Pl/sql program to complete a more complete database task, you need to further learn some advanced design elements of the content. The Pl/sql program that was written earlier, common to the absence of names, can only be stored as files, and then executed through the execution of files, so called nameless blocks. This corresponds to the introduction of some concepts of advanced programming in Pl/sql, the most important of which is the process.
The process is the concept of the module in the Advanced programming language, it is the important content of modular design idea to make some internal contact commands into a process and pass the data between the processes through the parameters.

Create process

1. The grammatical structure of the process
The complete process structure is as follows:
Create or Replace procedure procedure name as
A declaration statement segment;
Begin
Execute the sentence segment;
exception
Exception handling statement segment;
End
2. The characteristics of the process
The process is a named program block, as the keyword replaces the declare of the nameless block.
3. Create a Process instance
Execute the following pl/sql program in "Sqlplus Worksheet", which will create a procedure named Tempprocedure, which is the identifier for the creation process, and replace means that if the process exists with the same name, it will overwrite the original procedure. This procedure defines a variable whose type is the same as the CurrentDate field type in the TestTable datasheet, is a date type, feeds the currentdate field content of the RecordNumber field in the datasheet into the variable, and then outputs the result.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Create or replace procedure tempuser.tempprocedure as
Tempdate Tempuser.testtable.currentdate%type;
Begin
Select CurrentDate
Into Tempdate
From TestTable
where recordnumber=88;
Dbms_output.put_line (To_char (tempdate));
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createprocedure.sql.
The results of the execution are shown in Figure 9.41.


Query process

Log in to Enterprise Manager and select the network/database/myoracle.mynet/scenario/procedure/TEMPUSER option in the Manage target navigation tree to create a good process as shown in Figure 9.42.


Modify Process

(1) in the "Sqlplus Worksheet" menu bar Select the File/Open menu command, the creation of the process of the Createprocedure.sql file to modify, after the modification of the creation process.
(2) In Enterprise Manager, select the process to modify, right-click, and select the View/Edit Details option in the shortcut menu that appears, as shown in Figure 9.43.
(3) A General Information tab appears for the editing process as shown in Figure 9.44. In the text editing area, you can edit the procedure, and clicking the OK button will update the procedure, and clicking the Compile button compiles the Pl/sql source code for the procedure so that it can be stored and executed in the database.


Execution process

To perform the creation process, you must invoke the procedure through the main program.
Perform the following pl/sql programs in "Sqlplus Worksheet", as shown in Figure 9.45.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Begin
Tempprocedure;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Executeprocedure.sql.

In Oracle, the process of creating a good can be invoked by any program.

Process with parameters

The process described earlier has no parameters, the main program and process do not have data transfer, the following describes the process with parameters design and use.
1. Parameter type
There are 3 types of parameters that can be used during the pl/sql process.
In parameter: reads the parameter, the main program passes the parameter value to the procedure.
Out parameter: read out parameter, the procedure passes the parameter value to the main program.
In out parameter: Bidirectional parameter, the process communicates data with the main program bidirectional.
2. Define a process with parameters
In the following Pl/sql program code, three call parameters are created.
Tempdeptno: Type in, consistent with scott.dept.deptno type, numeric.
Tempdname: Type out, consistent with the type of scott.dept.dname, character type.
Temploc: Type in out, consistent with scott.dept.loc type, character type.
Create two process parameters.
Loc1: Consistent with the type of scott.dept.loc, character type.
DNAME1: Consistent with the type of scott.dept.dname, character type.
The function of the process with parameters is to look for the Dname and Loc fields of the Deptno field equal to the Tempdeptno call parameter value from the datasheet scott.dept, and the other character combinations to give two exit parameters.
Log on to "Sqlplus Worksheet" as the system user name, SYSDBA, and perform the following pl/sql programs, as shown in Figure 9.46.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Create or replace procedure Scott.tempprocedure (
Tempdeptno in Scott.dept.deptno%type,
Tempdname out Scott.dept.dname%type,
Temploc in Out Scott.dept.loc%type) as
Loc1 Scott.dept.loc%type;
Dname1 Scott.dept.dname%type;
Begin
Select Loc into Loc1
From Scott.dept
where Deptno=tempdeptno;
Select Dname into Dname1
From Scott.dept
where Deptno=tempdeptno;
temploc:= ' Address: ' | | Loc1;
tempdname:= ' Name ' | | dname1;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createscottprocedure.sql.

The call parameter is split with the "," number.
3. Using a process with parameters
In the main program in the actual parameters and the process of the transfer of formal parameters there are many ways, the reader is recommended to use the one by one corresponding method, the corresponding position to pass parameters. The actual and formal parameters are required to be exactly the same in the data type and position arrangement.
Execute the following pl/sql program in "Sqlplus Worksheet", which calls the procedure with parameters scott.tempprocedure, the actual parameter is (10, ', ')
The results of the execution are shown in Figure 9.47.

"Matching program Location": 9th Chapter \ Executescottprocedure.sql.

The reader can try to change the value of the parameter, and then test the process execution results.