Introduction to Database _ Stored Procedures (Oracle Edition)

At the request of a friend, write a stored procedure description, this article is relatively simple, suitable for new contact with the stored procedures of the classmate

Let's start with a simple one.

begin   dbms_output.put_line ('my first execute'); End;

If you are using PL/SQL, and you print my first execute in output after execution, then there is a second feature

Dbms_output.put_line, print results in output, plus dbms_output.put, this is usually not used, put does not include a carriage return, and Put_Line is a carriage return

Below is a description of how to write a complete stored procedure, a complete stored procedure, as follows

Create or Replace procedureMy_first_pro (user_inputvarchar2) as N Number;beginN:= 1; Dbms_output.put_line ('user input Values is'||user_input); Dbms_output.put_line ('n ='||n);End;

A complete stored procedure consists of at least two parts

1. Creating a stored procedure called My_first_pro CREATE PROCEDURE My_first_pro as

2. Begin

End

In the middle of begin and end is the function that the stored procedure needs to implement

Here is a description of the above code

Creating a stored procedure does not explain, or replace means overwriting an existing stored procedure with a newly modified stored procedure, you can try not to add this, and if not added, it will indicate that the stored procedure already exists, that is, it cannot be overwritten

Inside the parentheses after the stored procedure name, User_input varchar2 This is the input parameter, which is the in parameter by default, and if it is an output parameter, you can enter user_output out VARCHAR2

N number this is a declaration variable, the variable must be declared before use, and indicate the type, this and C very much like, the specific type can Baidu, commonly used have number, VARCHAR2, char (1)

N: = 1 assigns a value to N, in Oracle in order to distinguish between use = and assignment use = difference, when the assignment is not directly used =, need to use: =

Dbms_output.put_line, it's worth noting that Oracle's SQL functions are supported in the stored procedure, and are used in the same way, such as the connectors | |

So the result is the assignment of values and n that show the parameters of the entry.

Write these first, sleep, and tomorrow. Write for and cursors

Introduction to Database _ Stored Procedures (Oracle Edition)