This example describes Oracle's pl/sql. Share to everyone for your reference, specific as follows:
First, what is Pl/sql?
Pl/sql (procedural language/sql) is an extension of Oracle's standard SQL language.
Pl/sql not only allows you to embed SQL languages, you can also define variables and constants, allow you to use conditional statements and circular statements, and allow exceptions to handle various errors, making it more powerful.
Second, why to learn Pl/sql
1. Improve the running performance of the application
2. Modular design idea (pagination process, order process, transfer process). )
3. Reduce network traffic
4. Improve security (SQL will include the table name, and sometimes the password may be, the transmission will leak.) Pl/sql will not)
Third, why Oracle in Pl/sql Developer execution quickly, with C # OracleClient execution is slow
Because the Pl/sql language is specifically designed to access Oracle databases in a variety of environments. Because the language is integrated into the database server, Pl/sql code can quickly and efficiently handle the data.
While the C # language is Microsoft's product, it is stored in the "Connection pool" when connecting to Oracle, so it slows down for the first time, but when your Web program is not back up, the speed will not be slow.
Iv. Disadvantages of using Pl/sql
Bad portability (no use for database Exchange)
Five, Pl/sql understanding
1, stored procedures, functions, triggers are written by pl/sql
2, stored procedures, functions, triggers exist in Oracle
3), Pl/sql is a very powerful database process language
4, stored procedures, functions can be called in Java
Write a stored procedure that can add records to a table.
1. Create a simple table
CREATE TABLE mytest (
username VARCHAR2 (),
pwd VARCHAR2 ()
);
2, the creation process (replace: means if there is a insert_proc, replace)
CREATE OR REPLACE PROCEDURE insert_proc
is BEGIN
inserts into MyTest VALUES (' Lin Yi-chin ', ' 123456 ');
End;
/
3, how to view the error message: Show error;
Note to execute in the command window
4, how to call the process: Exec process Name (parameter value 1, parameter value 2 ...);
eg, exec Insert_proc;
Note to execute in the command window
I hope this article will help you with your Oracle database program design.