Introduction to PL/SQL in Oracle, basic syntax, and data types

Introduction to PL/SQL in Oracle, basic syntax, and data types

A. PL/SQL Introduction.

Oracle PL/SQL language (procedural language/sql) is a powerful language combined with structured queries and Oracle's own process control, and PL/SQL supports more data types, has its own variable declarations, assignment statements, and conditions, Loops and other process control statements. The seamless combination of process Control structures and SQL data processing capabilities creates a powerful programming language that allows you to create procedures and functions as well as packages.

PL/SQL is a block-structured language that puts a set of statements in a block and sends them to the server one at a time, executing code from the server and two actuators of its own engine. Such as:

　　

Advantages:

• Support SQL

SQL is the standard language to access the database, and with SQL commands, the user can manipulate the database data. PL/SQL supports all data manipulation commands, cursor control commands, transaction control commands, SQL functions, operators, and pseudo-columns.

At the same time, PL/SQL is tightly integrated and PL + supports all SQL data types and null values.

• Supports object-oriented programming

PL/SQL supports object-oriented programming in PL/SQL where types can be created, types can be inherited, methods can be overloaded in subroutines, and so on.

• Better performance

SQL is a non-procedural language, only one single execution, and PL/SQL to compile a PL/C unified after the execution, but also can be compiled PL/SQL block for reuse, reduce the application and server communication between the time, so PL/SQL is efficient and fast.

• Portability

Application languages written in PL/SQL, can be ported to Oracle servers on any operating platform, and can also be written in a portable library for use in different environments.

• Security

The application logic between the client and the server can be segmented through the stored procedure, which restricts access to the Oracle database, and the database can also authorize and revoke access rights for other users.

two. PL/SQL basic syntax.

PL/SQL is a block-structured language. A PL/SQL contains one or more logic fast, the logical block can declare variables, write the program body, can also catch exceptions and exception handling. Each logic is quickly divided into three parts, and the syntax structure is as follows:

　　　　The syntactic structure of PL/SQL

[DECLARE

--declaration statements] Variable or constant declaration section. Optional.

BEGIN

--executable Statements Executive section. Begin, end ends (add; No.). Must be written.

[EXCEPTION

--exception statements] Exception handling section. Optional. The role and mechanism of exceptions in Java are the same.

END;

Attention:

• Lp/sql is a programming language that has its own unique data types, variable declarations and assignments, and process control statements.
• Not sensitive to case, but for specification: all uppercase and lowercase for the remainder.
• Each statement ends with a semicolon.

　　　　PL/SQL Special symbol Description:

　　

Three. Variables and constants.

　　Declaring variables:

Variable name data type [: = initial value];

Declarea int; BEGIN Select MGR to a from EMP where empno=7369; END;

Variable name data type default initial value;

Declareb int default 0; BEGIN Select MGR to B from EMP where empno=7369; END;

　　Declare ordinary constants:

Constant name CONSTANT data type: = constant value;

Set serveroutput on; --Set Output to console Declarec CONSTANT int:=666; BEGIN Dbms_output.put_line (c); END;

Four. PL/SQL data type.

• Scalar data types

Prior to building a table, you learned the data types of Oracle SQL, which not only supports these data types, but also their own data types. Such as:

　　

　　

• Property data type

When declaring a variable's value as a row of a database or a column of a database, you can declare it directly using the property type. Oracle has two types of property data:%type and%rowtype

　　　　%rowtype

To reference a row in a database table as a data type, you can use the. "To access the properties in the record.

Instance:

Set serveroutput on; Results:
DECLARE EMPNO  ename  SAL  myrow emp%rowtype; 7369  SMITH 800BEGIN  select * into Myrow from EMP where empno=7369;  Dbms_output.put_line (' EMPNO ename SAL ');  Dbms_output.put_line (myrow.empno| | ' ' | | myrow.ename| | ' ' | | Myrow.sal);  END;

　　　　%TYPE

The type of a column that references a variable or database is used as the data type of a variable.

Instance:

Set serveroutput on;declare Description:  sal Emp.sal%type;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　  MyType Number (4): = 1000; Defines the data type of the SAL as the data type of the SAL in the EMP table.  sumsal Mytype%type;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　BEGIN  Select Sal to Sal from EMP where empno=7499; Defines the data type of the sumsal with the data type MyType.  Sumsal:=sal+mytype;  Dbms_output.put_line (sumsal); END;  

Introduction to PL/SQL in Oracle, basic syntax, and data types