Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the stored procedures of named blocks in Oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Anonymous blocks and named blocks

◆ PL/SQL blocks are divided into good totals: named blocks and anonymous blocks.

◆ anonymous block: starting with declare or begin, each execution of the anonymous block is sent to Oracle through a client tool, parsed, compiled, and executed.

◆ named blocks: PL/SQL blocks with names, which are stored in Oracle, compiled once, and then executed multiple times only when called. Such as: stored procedures, stored functions, packages, triggers, etc.,

Stored procedure: no return value

Storage function: return value

Package: a container that can hold multiple procedures or functions (better managed, similar to packages in java)

Trigger: automatically executed in the appropriate practice. (no need to call, automatically execute when the requirements are met; triggers are also called named blocks for implicit execution!)

The difference between ◆ anonymous blocks and named blocks:

Anonymous block: a PL/SQL structure that can dynamically create and execute process code. Each time an anonymous block is executed, the block data is automatically sent to Oracle in the form of text, and then executed and fed back to the user. Each execution needs to be sent and compiled!

Named block: the persistence way to store the code as a database attribute in the system directory, compile once in Oracle, the next time you can directly call the execution, do not need to compile again, very efficient.

Second, stored procedures:

A stored procedure is a PL/SQL block with a name, a complete specific function and no return value. You only need to send a call command to execute the procedure, which can reuse the code and cannot be called in the SQL statement!

◆ creates a stored procedure:

Format: create [or replace] procedure proc_name [(para1 [int | out | in out] type,...)] {is | AS} prdc_body

Proc_name represents the stored procedure name

Para1 represents the parameter name; type is the data type of the parameter para1

Proc_body represents the process body. The body of the procedure follows the structure of the PL/SQL block, but the declare keyword cannot be used. End can be followed by the name of the procedure.

Note: the parameter list is optional

◆ calls stored procedures: (the stored procedure is automatically saved in Oracle when it is created, and the PL/SQL block can be called when the stored procedure is executed!)

(1) if called in the PL/SQL block, use proc_name (...) directly.

(2) if you use sql plus environment, you need to use EXEC proc_name (...). The form of

(3) if the stored procedure has no parameters, it can be called with or without parentheses!

[example] create a stored procedure with no parameters (display information for all employees)

Create or replace procedure pr_show_employee is-- enters the declaration part of the block (without writing the declare keyword)-- defines the cursor cursor v_emp_cursor is select * from employees;-- defines the record-type row variable v_emp_record v_emp_cursor%rowtype of the cursor type. Begin for v_emp_record in v_emp_cursor loop dbms_output.put_line (v_emp_record.employee_id | |','| | v_emp_record.first_name | |','| | v_emp_record.last_name |','| | v_emp_record.salary); end loop;end pr_show_employee

-- calling stored procedures (executed in an anonymous block)

Begin pr_show_employee (); end

[example] the parameters of the stored procedure accept the product category number and output the information of all products in this class

Create or replace procedure pr_show_product (product_type products.product_type_id%type) as-- defines the cursor cursor v_prd_cursor is select * from products p where product_type = p.productcursor type attribute;-- defines the row type variable v_prd_record v_prd_cursor%rowtype of the cursor Begin for v_prd_record in v_prd_cursor loop dbms_output.put_line (v_prd_record.product_id | |','| | v_prd_record | |','| | product_type_id | |','| | v_prd_record.description | |','| | v_prd_record.name | |','| | v_prd_record.price); end loop;end pr_show_product -- call stored procedure (execution) declare-- define the type of numbered variable v_prd_type_id products.product_type_id%type: = 1 position begin pr_show_product (v_prd_type_id); end

The delivery mode of the ◆ parameter:

There are three modes of parameter passing: in, out, in out

In: when a procedure is called, the value of the argument is passed to the parameter. Parameters are treated as constants and cannot be modified within the process.

Out: when a procedure is called, the value of the argument is ignored, the parameter is null, and the parameter can be read and written inside the procedure. At the end of the process, the value of the parameter is assigned to the argument!

In out: when a procedure is called, the value of the argument is passed to the parameter, and the parameter inside the procedure is readable and writable. At the end of the procedure, the value of the parameter is assigned to the parameter.

Note: if the parameter passing mode is not specified when defining the stored procedure, the default is in mode

[example] create a default stored procedure with parameters passed

Create or replace procedure pr_test (pair1 in out varchar2) is begin dbms_output.put_line ('pair1 in out varchar2' | | pair1); pair1: = 'abc'; dbms_output.put_line (' pawl in out varchar2'| | paw1); end pr_test

-- calling stored procedures

Declare vault 1 varchar2 (5): = 'wwww';begin pr_test (vroom1); dbms_output.put_line (' vici1 varchar2'| | vroom1); end

Note: (1) when creating a stored procedure, the parameter type cannot specify a specific length. The character type is varchar2, and the numeric type is number;, which is equal to the length of the default and argument type.

(2) using in mode, the parameter accepts the value of the argument, and the default parameter is constant, which cannot be modified in the stored procedure, otherwise there will be an error!

(3) using out mode, the value of the argument is ignored, but it must be a variable to receive the value of the formal parameter executed by the stored procedure, while the parameter is readable and writable.

(4) using in out mode, the value of the argument is passed to the parameter, which must also be a variable, and the parameter can be read and written. Finally, the value of the parameter of the stored procedure is passed to the parameter!

(5) when the parameter is passed, the value and length or precision constraint of the parameter will be passed to the parameter, so the parameter cannot have the constraint of length or precision. When assigning a value to a parameter in the process, you should pay attention to the limitation of its length or precision!

This is the end of this article on "sample analysis of stored procedures for named blocks in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.