Oracle Stored Procedure

 

When learning, you must calm down and learn. I found some online materials and my own understanding, and then practiced them. Because there are many stored procedures, I can only get started here. I hope you can study them in depth.

 

-- This symbol in the text is equal to the meaning of the comment.

 

1. Basic Structure
Create or replace procedure
(
Parameter 1 in data type,
Parameter 2 in Data Type
) Is
Variable 1 data type;
Variable 2 data type;
Begin

* *******-The business of the stored procedure, that is, what you want to do
End stored procedure name

 

The upper-case letters are reserved words, and there are many data types. There are a few basic words. You will learn them by yourself later, such as number, varchar, and varchar2. The equivalent symbol here is: = the unique way to define variables here is that

Variable data type;

Eg

T_name varchar2 (100 );

T_age number;

 

How to assign values to variables

T_name: = 'aaaaaaa ';

T_age: = 10;

 

 

2. Select into statement

Save the SELECT query result to a variable. You can store multiple columns in multiple variables at the same time. One record is required; otherwise, an exception is thrown (if no record exists, no_data_found is thrown)
Example:
Begin
Select col1, col2 into variable 1, variable 2 from table name where xxx;
Exception
When no_data_found then
Xxxx;
End;

3. If judgment

If v_test = 1 then
Begin
Do something-this is your own business.
End;
End if;

4. While Loop

While v_test = 1 Loop
Begin
Xxxx
End;
End loop;

5. Variable assignment

V_test: = 123;

6. Use cursor with for in

Create or replace procedure Test (name in number)
Is
Cursor cur is select * from XXX; -- a cursor is defined here and the query result is placed in the cursor,

-- The cursor is like a pointer. You can understand it like this.

Begin
For cur_result in cur Loop
Begin
V_sum: = cur_result. Column name;
End;
End loop;
End;

 

Take a look at the example below

 

Create or replace procedure Test (name in number)
Is
V_sum varchar2 (200 );
Cursor cur is select * From lad_user;
Begin
For v_result in cur Loop
Begin
V_sum: = v_result.user_name;
End;
End loop;
Dbms_output.put_line (v_sum); -- output command
End test;

 

7. cursor with Parameters

Cursor c_user (c_id number) is select name from user where typeid = c_id;

Open c_user (variable value );

Loop
Fetch c_user into v_name;
Exit fetch c_user % notfound;
Do something
End loop;

Close c_user;

 

8. Use PL/SQL developer debug
Create a test window after connecting to the database
Enter the SP call code in the window, F9 start debug, CTRL + n single-step debugging

 

9. Run

I learned benzene for the first time. Even if the stored procedure was not created, I would execute the call command. Please do not be the same as me.

 

There are two ways to execute

1. First, connect to the Oracle database in the DOS environment

 

Sqlplus username/password @ database address. You can use this command to connect to the database.

 

For example: sqlplus test_zj/111111@192.168.10.10 _ orcl

 

In this way, the database is connected one after another. Copy the stored procedure to a TXT text file and change the text file to a file of the type test. SQL.

Here, I directly put est. SQL in the E:/root directory.

 

After the connection, run @ Test

/

After these two commands are executed, the stored procedure is created.

 

Call test (1) to call the stored procedure );

 

In this way, the creation and calling of DOS are over, and I still feel very troublesome. However, this is the basis and I still understand it well.

 

 

Another method is to use tools, that is, the method described at. The next PLSQL developer, recommended, is very easy to use. After connecting, directly create a stored procedure and execute it, the stored procedure can be written into the database, and the test window can be used for testing. The command directly writes call test (1) and then click execute in the upper left corner to see the result.

 

 

 

 

 

 

 

 

 

 

 

 

1. Command Format

 
A stored procedure is a PL/SQL block that accepts zero or multiple parameters as input or output, or as both input and output (inout ), unlike functions,
Stored procedures do not return values. stored procedures cannot be directly used by SQL statements. They can only be called using the execut command or within the PL/SQL program block. The syntax for defining stored procedures is as follows: Procedure
Name
[
(Parameter [, parameter,
]
)]

Is
|
As


[
Local declarations
]


Begin


Execute
Statements;

[
Exception exception handlers
]


End
 
[
Name
]
;

2. Call

Stored procedures can be called directly using the execut command or within the PL/SQL program block. The format of calling a stored procedure using the execut command is as follows: SQL
>
Excute proc_name (par1, par2 ...); The stored procedure can also be called by another PL/SQL block. The called statement is: Declare
Par1, par2;

Begin

Proc_name (par1, par2 ...);

End
;

3. Release

When a stored procedure is no longer needed, it should be deleted from the memory to release the memory resources it occupies. The statement format of the release process is as follows: SQL
>
Drop
 
Procedure
Proc_name; 4. instance:


Write a stored procedure to display the Department name and location of the specified employee name. Create
 
Or
 
Replace
 
Procedure
Deptmesg (pename EMP. ename
%
Type,
Pdname out Dept. dname
%
Type,
Ploc out Dept. Loc
%
Type)
As


Begin


Select
Dname, Loc

Into
Pdname, ploc

From
EMP, Dept

Where
EMP. deptno
=
Dept. deptno

And
EMP. ename
=
Pename;

End
; Call: Variable vdname
Varchar2
(
14
);
Variable vloc
Varchar2
(
13
);

Execute
Deptmesg (
'
Smith
'
,: Vdname success? : Vloc );

Print
Vdname vloc;

 

 

After reading the demo, I learned to write it myself.

 

 

Create or replace procedure deptmesg (peid out lad_user.user_id % type, pename out lad_user.user_name % type) is
Begin
Select user_id, user_name into peid, pename
From lad_user
Where user_id = 1;
End deptmesg;

 

 

Test code

 

-- Created on 2009-7-22 by Administrator
Declare
-- Local variables here
Id integer;
Names varchar2 (100 );
Begin
-- Test statements here
Dbms_output.put_line ('ssssssssssssss ');
Deptmesg (peid => ID, pename => names );
Dbms_output.put_line ('Id: '| ID );
Dbms_output.put_line ('names: '| names );
End;

 

 

Hope you can understand