Basic Oracle-PL/SQL knowledge

What is PL/SQL?

• PL/SQL is a programming language developed by Oracle and dedicated to Oracle • PL stands for procedurallanguage • SQL stands for structuredquery language • PL/SQL is an Oracle database extension of SQL statements, added programming language features • PL/SQL contains procedural and SQL statements • data operation and query statements are included in PL/SQL code program units (PL/SQL blocks ), complex functions or computation are completed through logical judgment, loop, and other operations.
PL/SQL Block

• PL/SQL is a block structured language (a program can be divided into several logical blocks) • PL/SQL Block Composition-the PL/SQL language is in blocks, blocks can be nested. -A Basic PL/SQL block consists of three parts:

Definition section (declare)

Executable part (BEGIN)

Exception Handling)

Definition of PL/SQL Blocks

Like other languages, the names of variables, constants, cursors, and Exception Handling used in PL/SQL must be defined first and then used. And the part starting with the declare keyword must be defined.

Executable part of PL/SQL Block

This part is the main body of the PL/SQL block and contains the executable statements of the block. This part defines the block function and is required. Start with the keyword begin and end with end.

PL/SQL block Exception Handling Section

This part contains the exception handler (error handler) of the block ). When a statement in the block program body encounters an exception (an error is detected), Oracle transfers the program control to the corresponding exception handler in the exception Section for further processing. This part starts with the keyword exception and ends with the end keyword.

• Declare-optional-variables, constants, cursors, and custom special types • Begin-must-SQL statements-PL/SQL statements • exception-optional-processing actions when an error occurs • end; -required

Declare <br/> v_variable varchar2 (5); <br/> begin <br/> selectcolumn_name <br/> 1_v_variable <br/> fromtable_name; <br/> exception <br/> when exception_name then <br/>... <br/> end; <br/>

Advantages of PL/SQL

• Improved performance

PL/SQL sends the entire statement block to the server. This process is called at a time.

To reduce network congestion. If PL/SQL is not used

SQL statements have independent transmission interaction, which occupies a large amount of resources in the network environment.

Server time, which also causes network congestion.

• Reusability

PL/SQL can run in any Oracle environment (regardless of its operating system

And Platform), and does not need to be modified on the operating systems that other Oracle can run.

Code.

• Modularization

Each PL/SQL unit can contain one or more program blocks, and each block in the program implements a logical operation to separate different tasks, which are implemented by different blocks, blocks can be independent or nested.

PL/SQL block type

-Anonymous block: It is generally described in the application to be run. It is passed to the PL/SQL engine for processing at runtime and can only be executed once. It cannot be stored in the database. -Procedure, Function & package: A named PL/SQL block. It is stored in a database and can be executed multiple times, you can use an external program to display the execution. -Trigger: A named PL/SQL block. It is stored in a database and can be executed multiple times. It is automatically executed when a trigger event occurs.

PL/SQL Variables

• Identifiers can be used in PL/SQL to declare variables, constants, cursors, user-defined exceptions, and so on. They can be used in SQL or procedural statements. • The naming rules for identifiers are the same as those for database objects in Oracle. -It can contain up to 30 characters-it cannot be a reserved word-it must start with a letter-it cannot be the same as the column name of a table in the database-it can contain letters, $, _, numeric symbols • it is best to name the identifier follow relevant naming rules

PL/SQL Variables

Scalar data type:

• Accommodate a single value • No internal component • divided into four categories:-digital

Number, binary_integer, etc.

-Balanced type

Char, varchar2, long, raw, etc.

-Date type

Date, timestamp, etc.

-Boolean

True, false, null

Varchar2 (maximum length): the basic type of variable-length characters. The maximum storage data is 32767 bytes. Varchar2 variables and constants do not have the default length.

Char [(maximum length)]: basic type of fixed-length character data, up to 32767 bytes. If no maximum value is specified, the default value is 1.

The basic type of a long string. The maximum length is 32 KB.

Longraw is a variable-length string that can store up to 32 K bytes of binary data.

Nchar uses the character set specified by the National Character Set

Binary_integer: basic type of integer between-2147483647 and 2147483647.

Pls_integer: the basic type of signed integer between-2147483647 and 2147483647. Compared with number and binary_integer, pls_integer occupies less storage space and runs more efficiently.

RAW stores fixed-length binary data with a maximum length of 32 KB

Long is very similar to varchar2, but the maximum length of long is 32760 bytes, 7 bytes less than varchar2.

Longraw: basic type of binary data and byte string. The maximum value is 32760. PL/SQL cannot interpret longraw data.

Declare PL/SQL Variables

Example

Declare <br/> v_hiredatedate; <br/> v_deptnonumber (2) not null: = 10; <br/> v_locationvarchar2 (13): = 'shenyang '; <br/> c_tax_rateconstant number (3, 2): = 8.25; <br/> v_validboolean not null: = true; <br/>

• The naming rules for variables are basically the same as those for SQL. That is, each identifier must start with a letter and be case-insensitive. • When using a not null constraint condition to define a variable, you must assign a value to the variable • Declare a variable on each line to make the code easier to read and maintain • in constant declaration, the key word constant must be located before the type specified character and must be initialized • Use the value assignment operator (: =) or the default reserved word initializes the variable • the names of variables, constants, cursors, and Exception Handling used in PL/SQL must be first declared and used. • The Declaration part is included between the keyword declare and begin. Each statement ends.

-Variables of different blocks can have the same name. The names of columns in the database must have different names than those of variable names.

Declare <br/> v_jobemp.job % type; <br/> empno EMP. empno % Type: = 7369; <br/> begin <br/> select job <br/> into v_job <br/> from EMP <br/> where empno = empno; <br/> end; <br/>
Variable assignment

Syntax

Identifier: = expr; <br/>

Another way to assign a value to a variable is to assign a value to the variable from the database. Syntax:

Select column into variable

From table

Where condition;

After the SELECT statement is executed, one of the following situations occurs:

• Only one row is retrieved • multiple rows are retrieved • No rows are retrieved

The Select operation is successful only when a row is retrieved.

Attribute of % Type

• Declaring a variable through the % Type attribute means that the variable or the field type in the table is used as the type of the variable and the variable remains synchronized. The variables follow the following type declaration: -declared variable types-Field Types of tables in the database • can be prefixed with % Type-database tables and columns-variable names declared earlier • PL/SQL running program determines the data type and size of the variable.

... <Br/> v_enameemp.ename % type; <br/> v_balancenumber (7,2); <br/> v_min_balancev_balance % Type: = 10; <br/>... <br/>

Benefits of using the % Type attribute:

-During programming, you can choose not to query the Data Type of fields in the database-the data type of fields in the database may be changed-to be consistent with the previous variable type • reference the Bind Variable in PL/SQL, add a colon (:) before the variable name (:). • Example

Variable g_salary number </P> <p> declare <br/> v_salemp.sal % type; <br/> begin <br/> selectsal <br/> rjv_sal <br/> fromemp <br/> whereempno = 7369; <br/>: g_salary: = v_sal; <br/> end; <br/>/<br/>
Dbms_output.put_line

• Use the setserveroutput on command to set the environment variable serveroutput to the open state, so that PL/SQL programs can output results in SQL * Plus.

Declare <br/> v_var varchar2 (10); <br/> begin <br/> select ename into v_var <br/> from EMP <br/> where empno = 7369; <br/> dbms_output.put_line ('employee name = '| v_var); <br/> end; <br/>

• Comments can be multi-line comments between-/* and */-single-line comments, starting --

SQL statements in PL/SQL

• Use the SELECT command to select data from the database • Use the DML command to update the row in the database • Use the commit, rollback, or savepoint command to control transactions. • Use the implicit cursor attribute to determine DML output • The end keyword marks the end of the PL/SQL block, which does not represent the end of the transaction. A block can span multiple transactions, one transaction can span multiple blocks • PL/SQL does not directly support DDL

Select:

Select select_list <br/> into {variable_name [, variable_name]... <br/> | record_name} <br/> from Table <br/> where condition; <br/>

• The into clause must be used. • The query must and only one row can be returned.

InsertBegin <br/> insert into EMP <br/> (empno, ename, job, Mgr, hiredate, Sal) <br/> values (<br/> 1000, 'ljs', 'manager', 1100, sysdate, 4000); <br/> end; <br/>/<br/>

Update:Declare <br/> v_salemp.sal % Type: = 800; <br/> begin <br/> Update EMP <br/> set sal = Sal + v_sal <br/> wherejob = 'analyst'; <br/> end; <br/>/
Delete:Declare <br/> v_deptnoemp.deptno % Type: = 10; <br/> begin <br/> Delete from EMP <br/> where deptno = v_deptno; <br/> end; <br/>/<br/>