Oracle PL/SQL subprograms-process and function learning notes

It is true that the same content is different for the second time. Some of them were not carefully understood during the first time, some of them may be skipped in a rush for the first time. Of course, some of them may be forgotten after they are remembered. Today, I saw the subprogram for the second time and found some new content. Here I will write some basic content and easy to forget, so that I will not forget it again. The content may not be comprehensive. It's a bit specific to me!

 

1. Create subprogram syntax

Creation process

Create [or replace] Procedure procedure_name </P> <p> [(argument [{In | Out | in out}] type, </P> <p>... </P> <p> argument [{In | Out | in out}] type)] </P> <p> {is | as} -- there is no difference between is and as </P> <p> procedure_body <br/>

 

Create a function

 

Create [or replace] function function_name </P> <p> [(argument [{In | Out | in out}] type, </P> <p>... </P> <p> argument [{In | Out | in out}] type)] </P> <p> return return_type {is | as} -- there is no difference between is and as </P> <p> procedure_body </P> <p>

 

2. Parameter Mode

In

During the call process, the actual parameter value is passed to this process. In a process, a formal parameter is like a constant in PL/SQL -- it is regarded as read-only and cannot be modified. When the process is completed and the control of the program is returned to the calling environment, the actual parameter value will not change.

 

Out

During the call process, all actual parameter values are ignored. In the process, a formal parameter also has a null value, just like an uninitialized PL/SQL variable. The value of a formal parameter can be read or written. After the process is completed, the control will return to the calling environment, and the value of the formal parameter will be assigned to the actual parameter.

 

In out

This is the compound mode of in and out. During the call process, the actual parameter value is passed to the process. In the process, formal parameters are like variables after initialization, which can be read and written. After the process ends, the control will return to the calling environment, and the content of the formal parameters will be allocated to the actual parameters.

 

3. PASS Parameters by passing values and references

There are two ways to pass subroutine parameters: pass value and pass reference. When a parameter is passed as a reference, a pointer pointing to the actual parameter is passed to the corresponding formal parameter. On the other hand, when a parameter is passed by passing a value, the actual parameter value is copied to the corresponding formal parameter. Passing a parameter in reference mode is usually faster because it avoids copying. This is more obvious for parameters of the set type, because the data of the set type is usually very large.

By default, PL/SQL uses the pass reference method for in parameters, while in out and out parameters both use the pass value method.

 

1) usage of nocopy

 

Parameter_name [mode] nocopy datatype

 

Parameter_name is the parameter name, mode is the parameter mode, and datatype is the parameter type. If nocopy exists, the PL/SQL Compiler will try to pass the parameter by passing the reference instead of passing the parameter by passing the value. Note that nocpy is only a compiler prompt, not a compiler command. Therefore, such prompts are not always accepted.

 

-- This procedure demonstrates the syntax of the nocopy compiler <br/> -- hint. <br/> Create or replace procedure nocopytest (<br/> p_inparameter in number, <br/> p_outparameter out nocopy varchar2, <br/> p_inoutparameter in out nocopy char) is <br/> begin <br/> NULL; <br/> end nocopytest; <br/>/

 

When nocopy is used on the in parameter, a compilation error is thrown, because the in parameter always transmits parameters in reference mode, so nocopy is not allowed to be prompted by the compiler.

 

2) abnormal semantics with nocopy

When a parameter is passed as a reference, any changes made to the form parameter will be reflected to the actual parameter at the same time, because the two point to the same location. This also means that if the process ends with an unhandled exception after the value of the formal parameter changes, the original value of the actual parameter will also be lost.

 

Create or replace procedure raiseerrornocopy (<br/> p_raise in Boolean, <br/> p_parame1_out nocopy number) as <br/> begin <br/> p_parametings: = 7; <br/> If p_raise then <br/> raise dup_val_on_index; <br/> else <br/> return; <br/> end if; <br/> end raiseerrornocopy; <br/>/</P> <p> declare <br/> v_num number: = 1; <br/> begin <br/> dbms_output.put_line ('value before first call: '| v_num); <br/> raiseerrornocopy (false, v_num); <br/> dbms_output.put_line ('value after successful call:' | v_num ); <br/> dbms_output.put_line (''); </P> <p> v_num: = 2; <br/> dbms_output.put_line ('value before second call: '| v_num ); <br/> raiseerrornocopy (true, v_num); <br/> exception <br/> when others then <br/> dbms_output.put_line ('value after unsuccessful call: '| v_num); <br/> end; <br/>/</P> <p> -- The result is as follows <br/> value before first call: 1 <br/> value after successful call: 7 </P> <p> value before second call: 2 <br/> value after unsuccessful call: 7 <br/>

 

We can see that even if an exception occurs, the actual parameter value is modified twice.

 

3) nocopy restrictions

In some cases, the compiler ignores the existence of nocopy, and the parameter is still passed as a value without any errors. Remember, nocopy is just a kind of pragma, And the compiler is not responsible for fully following this prompt. In the following situations, the existence of nocopy is ignored:

• The actual parameter is a member of the Union array. However, if the actual parameter is the entire array, It is not restricted by this constraint.
• Use actual parameters with length, precision, or not null constraints.
• The actual parameters and formal parameters are record, and they are either implicitly declared as a cyclic variable or declared using % rowtype, and the constraints on the corresponding fields are different.
• The actual parameters passed must undergo implicit data type conversion.
• Subroutines are included in remote procedures Call (RPC.

4. subprogram call

If a process or function has no parameters, no parentheses are required during process declaration or call.

Now, let's talk about the difference between exec and call.

A.

 

Exec is a command in sqlplus and can only be executed in sqlplus.

B.

Call is an SQL command. It can be used by any tool and must be enclosed in brackets.

 

Location Identification and name-based identification

 

Create or replace procedure callme (<br/> p_paramew.varchar2, <br/> p_parameterb number, <br/> p_parameterc Boolean, <br/> p_parameterd date) as <br/> begin <br/> NULL; <br/> end callme; <br/>/</P> <p> declare <br/> v_variable1 varchar2 (10); <br/> v_variable2 number (7, 6); <br/> v_variable3 Boolean; <br/> v_variable4 date; <br/> begin <br/> callme (v_variable1, v_variable2, v_variable3, v_variable4); <br/> end; <br/>/</P> <p> declare <br/> v_variable1 varchar2 (10); <br/> v_variable2 number (7, 6); <br/> v_variable3 Boolean; <br/> v_variable4 date; <br/> begin <br/> callme (p_parametera => v_variable1, <br/> p_parameterb => v_variable2, <br/> p_parameterc => v_variable3, <br/> p_parameterd => v_variable4); <br/> end; <br/>/</P> <p> declare <br/> v_variable1 varchar2 (10); <br/> v_variable2 number (7, 6); <br/> v_variable3 Boolean; <br/> v_variable4 date; <br/> begin <br/> callme (p_parameterb => v_variable2, <br/> p_parameterc => v_variable3, <br/> p_parameterd => v_variable4, <br/> p_parame1_=> v_variable1); <br/> end; <br/>/</P> <p> declare <br/> v_variable1 varchar2 (10); <br/> v_variable2 number (7, 6); <br/> v_variable3 Boolean; <br/> v_variable4 date; <br/> begin <br/> -- first 2 parameters passed by position, the second 2 are <br/> -- passed by name. <br/> callme (v_variable1, v_variable2, <br/> p_parameterc => v_variable3, <br/> p_parameterd => v_variable4); <br/> end; <br/>/<br/>

 

 

5. Position of the subroutine

1) built-in subroutine (stored subprogram)

When you create a subroutine using the create or replace command, it is stored in the database. This subroutine is stored in compiled form, which is P-code.

 

2) Local subroutine

 

Let's take a look at an example. This example shows the local subroutine declared in the declaration part of PL/SQL blocks.

 

Declare <br/> cursor c_allstudents is <br/> select first_name, last_name <br/> from students; </P> <p> v_formattedname varchar2 (50 ); </P> <p>/* function which will return the first and last name <br/> concatenated together, separated by a space. */<br/> function formatname (p_firstname in varchar2, <br/> p_lastname in varchar2) <br/> return varchar2 is <br/> begin <br/> return p_firstname | ''| p_lastname; <br/> end formatname; </P> <p> -- begin main block. <br/> begin <br/> for v_studentrecord in c_allstudents loop <br/> v_formattedname: = <br/> formatname (v_studentrecord.first_name, <br/> v_studentrecord.last_name ); <br/> insert into temp_table (char_col) <br/> values (v_formattedname); <br/> end loop; </P> <p> commit; <br/> end; <br/>/

 

In the preceding example, the formatname function is declared in the declaration part of the anonymous block. The function name is a PL/SQL identifier, because it follows the same scope and Visibility rules as other PL/SQL identifiers.

In particular, it is only visible in the block where the declaration is located. Its scope starts from the position where the declaration is located until the end of the block. Other blocks cannot call formatname because it is invisible to other blocks.

All local subroutines must be declared at the end of the declaration part.

 

A concept cannot be mentioned here:

Forward Declaration (Forward Declaration)

 

Because the local PL/SQL subprograms are named identifiers, they must be declared before being referenced. Generally, this is not a problem. However, this may cause a problem for subprograms that are cross-referenced. Consider the following example:

 

Declare <br/> v_tempval binary_integer: = 5; </P> <p> -- local procedure. note that the code of a callprocedure B. <br/> procedure A (p_counter in out binary_integer) is <br/> begin <br/> If p_counter> 0 then <br/> B (p_counter ); <br/> p_counter: = p_counter-1; <br/> end if; <br/> end a; </P> <p> -- local procedure B. note that the code of B callprocedure. <br/> procedure B (p_counter in out binary_integer) is <br/> begin <br/> p_counter: = p_counter-1; <br/> A (p_counter ); <br/> end B; <br/> begin <br/> B (v_tempval); <br/> end; <br/>/<br/>

 

This example cannot be compiled. Because process a and process B call each other. To solve this problem, we can use the Forward Declaration. This is just a process name and its formal parameter, so that you can use the cross-reference process in the program. As shown below

 

Declare <br/> v_tempval binary_integer: = 5; </P> <p> -- Forward Declaration of procedure B. <br/> procedure B (p_counter in out binary_integer); </P> <p> procedure A (p_counter in out binary_integer) is <br/> begin <br/> If p_counter> 0 then <br/> B (p_counter); <br/> p_counter: = p_counter-1; <br/> end if; <br/> end a; </P> <p> procedure B (p_counter in out binary_integer) is <br/> begin <br/> p_counter: = p_counter-1; <br/> A (p_counter); <br/> end B; <br/> begin <br/> B (v_tempval); <br/> end; <br/>/<br/>

 

6. subprogram authorization Problems

 

Let's take a look at the following code:

Create or replace procedure recordfullclasses as <br/> cursor c_classes is <br/> select Department, course <br/> from classes; <br/> begin <br/> for v_classrecord in c_classes loop <br/> -- Record all classes which don't have very much room left <br/> -- In temp_table. <br/> If almostfull (v_classrecord.department, v_classrecord.course) Then <br/> insert into temp_table (char_col) values <br/> (v_linoleic Ssrecord. Department | ''| v_classrecord.course | <br/> 'is almost full! '); <Br/> end if; <br/> end loop; <br/> end recordfullclasses; <br/>/

 

Assume that recordfullclasses and the dependent objects (functions almostfull, table classes, and temp_table) are owned by the database user usera. If we grant the execute permission on recordfullclasses to the userb database user using the following command

 

Grant execute on recordfullclasses to userb

 

Then userb can execute recordfullclasses through the following blocks.

 

Begin

Usera. recordfullclasses;

End;

 

Assume that userb has another table, also called temp_table. If userb calls usera. recordfullclasses, which table will be changed? The answer is that the table in usera is changed. This concept can be expressed as follows:

The subroutine is executed under the control of its owner's permissions.

Since userb calls recordfullclasses, recordfullclasses is owned by usera. In this way, the identifier temp_table must evaluate the table that belongs to usera rather than userb.

 

It is worth noting that subprograms cannot be performed by roles during authorization.

 

Grant select on clasees to userb;

Grant execute on almost full to userb;

 

Not through indirect role authorization

Create role usera_role;

 

 

 

Grant execute on almost full to usera_role;

Grant usera_role to userb;

Grant select on clasees to usera_role;