Introduction to Oracle's stored procedures

Advantages:

1. Precompiled, optimized, and highly efficient. Avoids the inefficiency of SQL statements being transmitted over the network and then interpreted.
2. If the company has a dedicated DBA, the write stored procedure can be done by the programmer as long as it is invoked by the interface he provides. To do so separately, more clearly.
3. Easy to modify. The SQL statements embedded in the program are cumbersome to modify, and are often not sure whether the changes have been changed. SQL Server stored procedure modification is more convenient, directly change the stored procedure, call its program basically do not move, unless the changes are relatively large (such as the parameters passed, returned data, etc.).
4. It'll be a little safer. There is no SQL statement injection problem.

Of course, there are also shortcomings. In particular, the business logic is more complex, the full use of stored procedures to write, the estimate is also tired.

The SQL stored procedure executes much faster than the SQL command text. When an SQL statement is contained in a stored procedure, the server does not have to parse and compile it every time it executes.

Calling a stored procedure can be considered a three-tier structure. This makes your program easy to maintain. If the program needs to make some changes, you can only change the stored procedure

You can use the power of Transact-SQL in stored procedures. An SQL stored procedure can contain multiple SQL statements. You can use variables and conditions. This means that you can use stored procedures to create very complex queries that update the database in a very complex way.

The ability of the stored procedure greatly enhances the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.
* Ensures data security and integrity.
# Through stored procedures, users without permission can access the database indirectly under control, thus ensuring the security of the data.
# Through stored procedures, the associated actions can be brought together to maintain the integrity of the database.
* The database has been syntactically and syntactically analyzed before running the stored procedure, and the optimal execution scheme has been given. This compiled process can greatly improve the performance of SQL statements. Since most of the work on executing the SQL statement has been completed, the stored procedure can be executed at a very fast rate.
* Can reduce the traffic of the network.
* To place an operator that embodies an enterprise rule into a database server so that:
# Centralized control.
# Change the stored procedure in the server when the enterprise rule changes, without modifying any applications. The characteristic of enterprise rule is to change frequently, if the operation that embodies enterprise rule is put into application, then when enterprise rule changes, need to modify the application workload very much (modify, publish and install application). If you put an operation that embodies an enterprise rule into a stored procedure, you can modify the stored procedure as long as the enterprise rules change, and the application needs no change.

How the stored procedure is written:

---create a table
CREATE TABLE TestTable
(
ID1 VARCHAR2 (12),
Name VARCHAR2 (32)
)
Select T.id1,t.name from TestTable t
Insert into TestTable (ID1, NAME)
VALUES (' 1 ', ' Zhangsan ');


Insert into TestTable (ID1, NAME)
VALUES (' 2 ', ' Lisi ');


Insert into TestTable (ID1, NAME)
VALUES (' 3 ', ' Wangwu ');


Insert into TestTable (ID1, NAME)
VALUES (' 4 ', ' Xiaoliu ');


Insert into TestTable (ID1, NAME)
VALUES (' 5 ', ' Laowu ');
---Create a stored procedure
Create or replace procedure Test_count
As
V_total number (1);
Begin
Select COUNT (*) into the v_total from TestTable;
Dbms_output.put_line (' Total number: ' | | V_total);
End
--Prepare
--line to Scott unlock: Alter user Scott account unlock;
--The stored procedure should be under Scott's user. And give Scott a code.
---alter user Scott identified by Tiger;
---to execute under orders.
EXECUTE Test_count;
----execute in SQL in QL/SPL
Begin
--Call the procedure
Test_count;
End



Create or replace procedure Test_list
As
---is with a cursor
CURSOR test_cursor is select t.id1,t.name from TestTable t;
Begin
For Test_record in Test_cursor loop---traverse the cursor, printing out
Dbms_output.put_line (test_record.id1| | Test_record.name);
End LOOP;
test_count;--simultaneously executes another stored procedure (test_list contains stored procedures test_count)
End
-----Execute Stored Procedures Test_list
Begin
Test_list;
End;
---parameters for stored procedures
---in defines an input parameter variable that is used to pass parameters to the stored procedure
--out defines an output parameter variable that is used to fetch data from a stored procedure
Define an input and OUTPUT parameter variable---in out, with both functions
--These three parameters can only describe the type, do not need to specify length such as VARCHAR2 (a), defaul can not write, but as a programmer it is best to write.

---to create a stored procedure with parameters
Create or Replace procedure Test_param (p_id1 in VARCHAR2 default ' 0 ')
As V_name varchar2 (32);
Begin
Select T.name into V_name from TestTable t where T.id1=p_id1;
Dbms_output.put_line (' Name: ' | | V_name);
End
----Execute Stored Procedures
Begin
Test_param (' 1 ');
End

Default ' 0 '




---to create a stored procedure with parameters
Create or Replace procedure Test_paramout (V_name out VARCHAR2)
As
Begin
Select name into V_name from testtable where id1= ' 1 ';
Dbms_output.put_line (' Name: ' | | V_name);
End
----Execute Stored Procedures
DECLARE
V_name VARCHAR2 (32);
BEGIN
Test_paramout (V_name);
Dbms_output. Put_Line (' Name: ' | | V_name);
End;
-------in Out
---Create a stored procedure
Create or Replace procedure Test_paraminout (P_phonenumber in Out varchar2)
As
Begin
p_phonenumber:= ' 0571-' | | P_phonenumber;
End
 
----
DECLARE
P_phonenumber VARCHAR2 (32);
BEGIN
p_phonenumber:= ' 26731092 ';
Test_paraminout (P_phonenumber);
Dbms_output. Put_Line (' New phone number: ' | | P_phonenumber);
End;
-----The SQL command, query the source code for the current user's stored procedure or function.
-----can be obtained by querying the User_source data dictionary view. The structure of the User_source is as follows:

Sql> DESCRIBE User_source;
Name Type Nullable Default Comments
---- -------------- -------- ------- ------------------------------------------------------------------------------ -------------------------------
Name VARCHAR2 Y name of the object
Type VARCHAR2 Y type of the object: "Type", "Type Body", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE body" or "Java SOURCE"
Line number Y line number of that line of source
TEXT VARCHAR2 (4000) Y Source text
Sql>