Shulou(Shulou.com)06/01 Report--

SQL language is only a language to access and operate the database, not a programming language with flow control, but only a programming language can be used in the development of application software. PL / SQL is a high-level database programming language, which is specially used to access ORACLE database in various environments. Because the language is integrated into the database server, PL/SQL code can process the data quickly and efficiently. In addition, the use of the PL/ SQL language is also a feature of ORACLE database in some client-side tools. The main content of this chapter is to discuss the necessity and main features of introducing PL/SQL language, as well as the importance of understanding PL/SQL language and database version. We will also introduce some of the more detailed high-level concepts throughout the book, and explain some of the conventions of the database tables we use in this book case at the end of this chapter.

1.1 SQL and PL/SQL

1.1.1 what is PL/SQL?

PL/SQL is the abbreviation of Procedure Language & Structured Query Language. ORACLE's SQL is a product that supports ANSI (American national Standards Institute) and ISO92 (International Standards Organization) standards. PL/SQL is an extension of the SQL language stored procedure language. Since ORACLE6, ORACLE's RDBMS has come with PL/SQL. It has now become a process processing language, referred to as PL/SQL. The current PL/SQL consists of two parts, one is the database engine, and the other is an independent engine that can be embedded in many product tools (such as C language, JAVA language, etc.). These two parts can be called: database PL/SQL and tool PL/SQL. The two programs are very similar. All have programming structure, syntax and logic mechanism. The tool PL/SQL also adds syntax to support tools such as ORACLE Forms, such as setting buttons on a form. This chapter mainly introduces the content of database PL/SQL.

1.2 advantages or characteristics of PL/SQL

1.2.1 facilitates the operation of applications in the client / server environment

For the client / server environment, the real bottleneck is on the network. No matter how fast the network is, as long as the client exchanges a lot of data with the server. The efficiency of running applications will naturally be affected. If you use PL/SQL for programming, put this application with a lot of data processing on the server side to execute. Naturally, it saves the time of data transmission on the Internet.

1.2.2 suitable for customer environment

PL/SQL is divided into database PL/SQL part and tool PL/SQL. For the client, the PL/SQL can be nested into the corresponding tool, and the client program can execute the local PL/SQL part, or issue the SQL command to the service or activate the server-side PL/SQL program to run.

1.2.3 process

PL/SQL is a procedural extension of Oracle on the standard SQL. It not only allows embedding SQL statements in PL/SQL programs, but also allows the use of various types of conditional branch statements and loop statements, so that its solutions can be shared among multiple applications.

1.2.4 Modularization

PL/SQL program structure is a highly descriptive, well-defined block structure and nested block structure, which is divided into separate processes, functions and triggers, and can be combined into packages to improve the modularization ability of the program.

1.2.5 handling of run errors

Using the exception handling (EXCEPTION) provided by PL/SQL, developers can focus on a variety of ORACLE and PL/SQL errors, or handle system errors and custom errors to enhance the robustness of the application.

1.2.6 A large number of built-in packages are available

ORACLE provides a large number of built-in packages. Through these packages, some low-level operations and advanced functions of DBS can be realized, which plays an important role for both DBA and application developers.

Of course, there are other advantages such as better performance, portability and compatibility, maintainability, ease of use and rapidity.

1.3 SQL statements available for PL/SQL

PL/SQL is the core language of ORACLE system, and now many parts of ORACLE are written by PL/SQL. The SQL statements that can be used in PL/SQL are:

INSERT,UPDATE,DELETE,SELECT INTO,COMMIT,ROLLBACK,SAVEPOINT .

Tip: you can only use the DML part of the SQL statement in PL/SQL, not the DDL part. If you want to use DDL in PL/SQL (such as CREATE table, etc.), you can only use it in a dynamic way.

When interpreting the PL/SQL program, the PL/SQL component of l ORACLE checks the table name, column name, and data type used in it.

L PL/SQL can be used in SQL*PLUS.

L PL/SQL can be used in high-level languages.

L PL/SQL can be used in ORACLE development tools (such as SQL Developer or Procedure Builder, etc.).

Other development tools can also call procedures and functions written by PL/SQL, such as Power Builder, can call server-side PL/SQL procedures.

1.4 run the PL/SQL program

The PL/SQL program runs through an engine in ORACLE. The engine may be on the server side of ORACLE or on the client side of ORACLE application development. The engine executes the procedural statements in PL/SQL and then sends the SQL statements to the database server for execution. The result is then returned to the executor.

2.1 PL/SQL Block

The PL/SQL program consists of three blocks, namely, the declaration part, the execution part and the exception handling part.

The structure of PL/SQL blocks is as follows:

DECLARE

Declaration section: declare variables, types and cursors used by PL/SQL, as well as local stored procedures and functions

BEGIN

Execution: procedures and SQL statements, that is, the main part of the program

EXCEPTION

-- execute exception section: error handling

END

Among them: the executive part cannot be omitted.

PL/SQL blocks can be divided into three categories:

1. Anonymous block or anonymous block (anonymous): dynamically constructed, can only be executed once, can be called by other programs, but cannot be called by other programs.

two。 Named named: an anonymous block with a name, which is the label.

3. Subprogram: stored procedures, functions, etc., stored in a database. When it is established on the database, you can call them in other programs.

4. Trigger (Trigger): when an operation occurs in the database, some events are triggered and the corresponding program is executed automatically.

5. Package: a set of subroutines and variable definitions stored in a database. Subroutines in packages can be called by other packages or subroutines. However, if a local subroutine is declared, the local subroutine can only be called in the block that defines the local subroutine.

2.2 PL/SQL structure

L PL/SQL blocks can contain child blocks

L subblocks can be located in any part of the PL/SQL

L subblock is also a command in PL/SQL

2.3 Identifier

The requirements for identifier definitions in PL/SQL programming are the same as those for SQL. The requirements and restrictions are:

L identifier name cannot exceed 30 characters

The first character must be a letter

L regardless of case

L cannot use'-'(minus sign)

L cannot be a SQL reserved word.

Tip: generally do not declare the variable name exactly the same as the field name in the table, if this may get incorrect results.

For example, the following example will delete all records instead of 'EricHu'' records

DECLARE

Ename varchar2 (20): = 'EricHu'

BEGIN

DELETE FROM scott.emp WHERE ename=ename

END

Variable naming is particularly particular in PL/SQL. It is suggested that all programmers should be required to comply with certain requirements in the design stage of the system, so that the documentation of the whole system can meet the requirements of the specification. Here are the recommended naming methods:

2.4 PL/SQL variable types

In the previous introduction, there are systematic data types, or you can customize data types. The following table shows a list of legitimate uses of ORACLE types and variable types in PL/SQL:

2.4.1 variable type

Four types of data types

Scalar type (SCALAR, or basic data type): used to hold a single value, such as string, number, date, Boolean

Compound type (COMPOSITE): a compound type can store a variety of values internally, similar to a collection of variables, such as record types, nested tables, index tables, variable arrays, etc.

Reference type (REFERENCE): used to point to a different object, such as REF CURSOR,REF

LOB type: big data type, can store up to 4GB information, mainly used to deal with binary data

Scalar type

Scalar types are also known as basic data types

Common scalar types

Numerical type

NUMBER data type

Using the decimal type, the decimal system needs to be converted to binary for calculation.

Define an integer: NUMBER (n)

Define floating-point data: NUMBER (mfocus n)

Example 1: define NUMBER variable

SQL > set serveroutput on

SQL > DECLARE

2 vault x NUMBER (3);-- up to 3 digits

3 venery NUMBER (5);-- 3 integers, 2 decimal places

4 BEGIN

5 vault x: =-500

6 vs. y: = 999.88

7 DBMS_OUTPUT.put_line ('vSecretx =' | | vSecretx)

8 DBMS_OUTPUT.put_line ('vSecrety =' | | vSecrety)

9 DBMS_OUTPUT.put_line ('addition operation:' | | (vaddix + vclassiy));-- Integer + floating point number = floating point number

10 END

11 /

Vault x =-500

Viciy = 999.88

Addition operation: 499.88

PL/SQL procedure successfully completed.

BINARY_INTEGER and PLS_INTEGER

Description:

Both have the same range length. Smaller footprint than NUMBER

Using binary complement storage, the operation performance is higher than that of NUMBER

The difference between the two:

When the data of BINARY_INTEGER operation is larger than its data range, it will be automatically converted to number and saved.

When the data of PLS_INTEGER operation is larger than the range, exception information is thrown.

Example 1: verify PLS_INTEGER operation

SQL > DECLARE

2 v_pls1 PLS_INTEGER: = 100

3 v_pls2 PLS_INTEGER: = 200

4 v_result PLS_INTEGER

5 BEGIN

6 v_result: = v_pls1 + v_pls2

7 DBMS_OUTPUT.put_line ('calculation result:' | | v_result)

8 END

9 /

Calculation result: 300

PL/SQL procedure successfully completed.

BINARY_FLOAT and BINARY_DOUBLE

Both of them save more space than NUMBER, and at the same time, they have a wider range and use binary to store data.

Example 1: verify BINARY_DOUBLE operation

SQL > DECLARE

2 v_float BINARY_FLOAT: = 8909.51F

3 v_double BINARY_DOUBLE: = 8909.51D

4 BEGIN

5 v_float: = v_float + 1000.16

6 v_double: = v_double + 1000.16

7 DBMS_OUTPUT.put_line ('BINARY_FLOAT variable content:' | | v_float)

8 DBMS_OUTPUT.put_line ('BINARY_DOUBLE variable content:' | | v_double)

9 END

10 /

BINARY_FLOAT variable content: 9.90966992E+003

BINARY_DOUBLE variable content: 9.9096700000000001E+003

PL/SQL procedure successfully completed.

Constants defined by both: these constants can only be used in PL/SQL. These constants represent the data range of BINARY_FLOAT and BINARY_DOUBLE, respectively, and target non-numeric and data tags that exceed the maximum value of their type.

Example 2: observe the constant content of the representation range

SQL > DECLARE

2 BEGIN

3 DBMS_OUTPUT.put_line ('1, BINARY_FLOAT_MIN_NORMAL ='| | BINARY_FLOAT_MIN_NORMAL)

4 DBMS_OUTPUT.put_line ('1, BINARY_FLOAT_MAX_NORMAL ='| | BINARY_FLOAT_MAX_NORMAL)

5 DBMS_OUTPUT.put_line ('1, BINARY_FLOAT_MIN_SUBNORMAL ='| | BINARY_FLOAT_MIN_SUBNORMAL)

6 DBMS_OUTPUT.put_line ('1, BINARY_FLOAT_MAX_SUBNORMAL ='| | BINARY_FLOAT_MAX_SUBNORMAL)

7 DBMS_OUTPUT.put_line ('2, BINARY_DOUBLE_MIN_NORMAL ='| | BINARY_DOUBLE_MIN_NORMAL)

8 DBMS_OUTPUT.put_line ('2, BINARY_DOUBLE_MAX_NORMAL ='| | BINARY_DOUBLE_MAX_NORMAL)

9 DBMS_OUTPUT.put_line ('2, BINARY_DOUBLE_MIN_SUBNORMAL ='| | BINARY_DOUBLE_MIN_SUBNORMAL)

10 DBMS_OUTPUT.put_line ('2, BINARY_DOUBLE_MAX_SUBNORMAL ='| | BINARY_DOUBLE_MAX_SUBNORMAL)

11 END

12 /

1. BINARY_FLOAT_MIN_NORMAL = 1.17549435E-038

1. BINARY_FLOAT_MAX_NORMAL = 3.40282347E+038

1. BINARY_FLOAT_MIN_SUBNORMAL = 1.40129846E-045

1. BINARY_FLOAT_MAX_SUBNORMAL = 1.17549421E-038

2. BINARY_DOUBLE_MIN_NORMAL = 2.2250738585072014E-308

2. BINARY_DOUBLE_MAX_NORMAL = 1.7976931348623157E+308

2. BINARY_DOUBLE_MIN_SUBNORMAL = 4.9406564584124654E-324

2. BINARY_DOUBLE_MAX_SUBNORMAL = 2.2250738585072009E-308

PL/SQL procedure successfully completed.

Example 3: calculation out of range

SQL > DECLARE

2 BEGIN

3 DBMS_OUTPUT.put_line ('result of calculation out of range:' | |

4 BINARY_DOUBLE_MAX_NORMAL * BINARY_DOUBLE_MAX_NORMAL)

5 DBMS_OUTPUT.put_line ('result of calculation out of range:' | |

6 BINARY_DOUBLE_MAX_NORMAL / 0)

7 END

8 /

Result of out-of-range calculation: Inf

Result of out-of-range calculation: Inf

PL/SQL procedure successfully completed.

Character type

CHAR and VARCHAR2

Description:

CHAR saves strings in a fixed-length manner. If the content set by the user is less than its defined length, spaces will be automatically added.

VARCHAR2 is a variable string. If the content set is less than its length, it will not be supplemented.

Example 1: observe the difference between CHAR and VARCHAR2

SQL > DECLARE

2 v_info_char CHAR (10)

3 v_info_varchar VARCHAR2 (10)

4 BEGIN

5 v_info_char: = 'MLDN';-less than 10 in length

6 v_info_varchar: = 'java';-less than 10 in length

7 DBMS_OUTPUT.put_line ('v_info_char content length:' | | LENGTH (v_info_char))

8 DBMS_OUTPUT.put_line ('v_info_varchar content length:' | | LENGTH (v_info_varchar))

9 END

10 /

V_info_char content length: 10

V_info_varchar content length: 4

PL/SQL procedure successfully completed.

NCHAR and NVARCHAR2

The features of both are the same as CHAR,VARCHAR2. The difference is that the data they save is UNICODE encoded, and both Chinese and English are saved in hexadecimal code.

Example 1: verify NCHAR and NVARCHAR2

SQL > DECLARE

2 v_info_nchar NCHAR (10)

3 v_info_nvarchar NVARCHAR2 (10)

4 BEGIN

5 v_info_nchar: = 'CSDN';-less than 10 in length

6 v_info_nvarchar: = 'high-end training';-less than 10 in length

7 DBMS_OUTPUT.put_line ('v_info_nchar content length:' | | LENGTH (v_info_nchar))

8 DBMS_OUTPUT.put_line ('v_info_nvarchar content length:' | | LENGTH (v_info_nvarchar))

9 END

10 /

V_info_nchar content length: 10

V_info_nvarchar content length: 8

PL/SQL procedure successfully completed.

LONG and LONG RAW

Both are used for backward compatibility

LONG description:

CLOB or NCLOB is used wherever LONG is used.

LONG is used to store character streams

You can use the "UTL_RAW.cast_to_varchar2 (RAW data)" function to convert RAW to a string

LONG RAW description:

Where you use LONG RAW, replace it with BLOB or BILE

LONG RAW is used to store binary data streams

To set the content for the LONG RAW variable, use "UTL_RAW.cast_to_raw" for conversion

Example 1: using LONG and LONG RAW operations

SQL > set serveroutput on

SQL > DECLARE

2 v_info_long LONG

3 v_info_longraw LONG RAW

4 BEGIN

5 v_info_long: = 'CSDN';-- set the string directly

6 v_info_longraw: = UTL_RAW.cast_to_raw ('high-end training');-- change the string to RAW

7 DBMS_OUTPUT.put_line ('v_info_long content:' | | v_info_long)

8 DBMS_OUTPUT.put_line ('v_info_longraw content:' | | UTL_RAW.cast_to_varchar2 (v_info_longraw))

9 END

10 /

V_info_long content: CSDN

V_info_longraw content: high-end training

PL/SQL procedure successfully completed.

OWID and UROWID

ROWID represents the physical row address of a piece of data, which is composed of 18 characters and has the same function as the pseudo column of ROWID.

UROWID has the function of ROWID and adds a logical line address. In PL/SQL, all ROWID should be managed by UROWID.

Example 1: using ROWID and UROWID

SQL > create synonym emp for scott.emp@CLONEPDB_PLUG

Synonym created.

SQL > DECLARE

2 v_emp_rowid ROWID

3 v_emp_urowid UROWID

4 BEGIN

5 SELECT ROWID INTO v_emp_rowid FROM emp WHERE empno=7369;-- get ROWID

6 SELECT ROWID INTO v_emp_urowid FROM emp WHERE empno=7369;-- get ROWID

7 DBMS_OUTPUT.put_line (ROWID of '7369 employees =' | | v_emp_rowid)

8 DBMS_OUTPUT.put_line (UROWID of '7369 employees =' | | v_emp_urowid)

9 END

10 /

7369 ROWID = AAAR7bAALAAAACTAAA of the employee

7369 UROWID = AAAR7bAALAAAACTAAA of the employee

PL/SQL procedure successfully completed.

Date type

DATE data type

Used to store date and time data

The current date and time can be obtained through two pseudo columns, SYSDATE or SYSTIMESTAMP

Main Field Index:

Example 1: define a date variable

SQL > DECLARE

2 v_date1 date: = SYSDATE

3 v_date2 date: = systimestamp

4 v_date3 date: = TO_DATE ('2015-01-01-01 / 01 / 01 / 01 / 01)

5 BEGIN

6 DBMS_OUTPUT.put_line ('date data:' | | TO_CHAR (hh34:mi:ss' date 1)

7 DBMS_OUTPUT.put_line ('date data:' | | TO_CHAR (vdate2recoveryyMurddd hh34:mi:ss'))

8 DBMS_OUTPUT.put_line ('date data:' | | TO_CHAR (hh34:mi:ss' date 3))

9 END

10 /

Date data: 2017-12-19 15:22:01

Date data: 2017-12-19 15:22:01

Date data: 2015-01-01 00:00:00

PL/SQL procedure successfully completed.

TIMESTAMP data type

This type differs from DATE in that it provides a more accurate time. But use SYSTIMESTAMP pseudo-columns to assign values to them

If you just use SYSDATE, then there is no difference between TIMESTAMP and DATE

Example 1: define timestamp type variables

SQL > DECLARE

2 v_timestamp1 TIMESTAMP: = SYSDATE

3 v_timestamp2 TIMESTAMP: = SYSTIMESTAMP

4 v_timestamp3 TIMESTAMP: = to_timestamp ('2011-12-15 10 yyyy-MM-dd HH24:MI:ss.ff' 40 yyyy-MM-dd HH24:MI:ss.ff' 10.345)

5 BEGIN

6 DBMS_OUTPUT.put_line ('date data:' | | v_timestamp1)

7 DBMS_OUTPUT.put_line ('date data:' | | v_timestamp2)

8 DBMS_OUTPUT.put_line ('date data:' | | v_timestamp3)

9 END

10 /

Date data: 19-DEC-17 03.32.31.000000 PM

Date data: 19-DEC-17 03.32.31.988000 PM

Date data: 15-DEC-11 10.40.10.345000 AM

PL/SQL procedure successfully completed.

Two extended subtypes of TIMESTAMP:

TIMESTAMP WITH TIME ZONE: contains the time zone offset from Greenwich mean time

SQL > DECLARE

2 v_timestamp TIMESTAMP WITH TIME ZONE: = SYSTIMESTAMP

3 BEGIN

4 DBMS_OUTPUT.put_line (v_timestamp)

5 END

6 /

19-DEC-17 03.36.53.909000 PM + 08:00

PL/SQL procedure successfully completed.

TIMESTAMP WITH LOCAL TIME ZONE: no matter what the time zone is, the time zone of the current database is used.

SQL > DECLARE

2 v_timestamp TIMESTAMP WITH LOCAL TIME ZONE: = SYSTIMESTAMP

3 BEGIN

4 DBMS_OUTPUT.put_line (v_timestamp)

5 END

6 /

19-DEC-17 03.37.32.894000 PM

PL/SQL procedure successfully completed.

NTERVAL data type

This type can save the interval between two timestamps, and this type is divided into two subtypes:

INTERVAL YEAR [(precision of the year)] TO MONTHS: the time interval between saved and operating years and months. Users can set the accuracy of the data for the year. Default value is 2

Assignment format: 'year-month'

INTERVAL DAY [(precision of days)] TO SECEND [(precision of seconds)]: the time interval between saving and operating days, hours, minutes, and seconds. Default is 2. The default value for seconds is 6

Assignment format: 'time: minutes: seconds. Millisecond'

When the time interval is obtained, the following formula can be used for calculation:

Example 1: define INTERVAL YEAR TO MONTHS type variables

SQL > DECLARE

2 v_interval INTERVAL YEAR (3) TO MONTH: = INTERVAL'27-09 'YEAR TO MONTH

3 BEGIN

4 DBMS_OUTPUT.put_line ('interval:' | | v_interval)

5 DBMS_OUTPUT.put_line ('current timestamp + interval:' | | (SYSTIMESTAMP + v_interval))

6 DBMS_OUTPUT.put_line ('current date + time interval:' | | (SYSDATE + v_interval))

7 END

8 /

Interval: + 027-09

Current timestamp + interval: 19-SEP-45 03.43.24.742000000 PM + 08:00

Current date + time interval: 2045-09-19 15:43:24

PL/SQL procedure successfully completed.

Example 2: define INTERVAL DAY TO SECOND type variables

SQL > DECLARE

2 v_interval INTERVAL DAY (6) TO SECOND (3): = INTERVAL'8 18 DAY TO SECOND 19 DAY TO SECOND 27.367123909'

3 BEGIN

4 DBMS_OUTPUT.put_line ('interval:' | | v_interval)

5 DBMS_OUTPUT.put_line ('current timestamp + interval:' | | (SYSTIMESTAMP + v_interval))

6 DBMS_OUTPUT.put_line ('current date + time interval:' | | (SYSDATE + v_interval))

7 END

8 /

Time interval: + 000008 18V 19V 27.367

Current timestamp + interval: 28-DEC-17 10.03.46.657000000 AM + 08:00

Current date + time interval: 2017-12-28 10:03:46

PL/SQL procedure successfully completed.

Boolean type

This type saves TRUE,FALSE,NULL

Example 1: define Boolean variables

SQL > DECLARE

2 v_flag BOOLEAN

3 BEGIN

4 v_flag: = true

5 IF v_flag THEN

6 DBMS_OUTPUT.put_line ('conditions are met.')

7 END IF

8 END

9 /

The conditions are met.

PL/SQL procedure successfully completed.

Subtype

Define more constraints on a scalar type to create a new type, which is called a subtype

Create syntax:

Subtype subtype name is parent data type [(constraint)] [not null]

The parent data type must be set when defining the child type. The parent type can be various data types of Oracle

Example 1: define the number subtype

SQL > DECLARE

2 SUBTYPE score_subtype IS NUMBER (5 SUBTYPE score_subtype IS NUMBER 2) NOT NULL

3 v_score score_subtype: = 99.35;-- assign the v_score variable to 99.35 according to the score_subtype type

4 BEGIN

5 DBMS_OUTPUT.put_line ('score:' | | v_score)

6 END

7 /

The score is 99.35

PL/SQL procedure successfully completed.

Example 2: define VARCHAR2 subtype

SQL > DECLARE

2 SUBTYPE string_subtype IS VARCHAR2

3 v_company string_subtype;-- declare a variable v_company with subtype string_subtype

4 BEGIN

5 v_company: = 'test'

6 DBMS_OUTPUT.put_line (v_company)

7 END

8 /

Test

PL/SQL procedure successfully completed.

RETURNING clause

SQL > set serveroutput on

SQL > DECLARE

2 Row_id ROWID

3 info VARCHAR2 (40)

4 BEGIN

5 INSERT INTO dept VALUES (90, 'Finance Office', 'Haikou')

6 RETURNING rowid, dname | |':'| | to_char (deptno) | |':'| | loc

7 INTO row_id, info

8 DBMS_OUTPUT.PUT_LINE ('ROWID:' | | row_id)

9 DBMS_OUTPUT.PUT_LINE (info)

10 END

11 /

ROWID:AAAR7ZAALAAAACFAAA

Financial Office: 90: Haikou

PL/SQL procedure successfully completed.

The RETURNING clause is used to retrieve the number of rows of data affected in the INSERT statement, and when the INSERT statement inserts data using the VALUES clause, the RETURNING clause also returns column expressions, ROWID, and ref values to the output variable. The following restrictions should be noted when using the RETURNING clause:

1. Cannot be used with DML statements and remote objects

2. Cannot retrieve LONG type information

3. When inserting data into a base table through a view, it can only be used with a single base table view.

Example 2. Modify a record and display

SQL > DECLARE

2 Row_id ROWID

3 info VARCHAR2 (40)

4 BEGIN

5 UPDATE dept SET deptno=10 WHERE DNAME='ACCOUNTING'

6 RETURNING rowid, dname | |':'| | to_char (deptno) | |':'| | loc

7 INTO row_id, info

8 DBMS_OUTPUT.PUT_LINE ('ROWID:' | | row_id)

9 DBMS_OUTPUT.PUT_LINE (info)

10 END

11 /

ROWID:AAAR7ZAALAAAACDAAA

ACCOUNTING:10:NEW YORK

PL/SQL procedure successfully completed.

The RETURNING clause is used to retrieve information about the modified row. When the UPDATE statement modifies a single row of data, the RETURNING clause can retrieve the ROWID and ref values of the modified row, as well as the column expressions of the modified columns in the row, and store them in PL/SQL variables or compound variables; when the UPDATE statement modifies multiple rows of data, the RETURNING clause can return the ROWID and ref values of the modified row, as well as column expression values to the compound variable array. The restrictions on using the RETURNING clause in UPDATE are the same as those on the RETURNING clause in the INSERT statement.

Example 3. Delete a record and display

SQL > DECLARE

2 Row_id ROWID

3 info VARCHAR2 (40)

4 BEGIN

5 DELETE dept WHERE DNAME='OPERATIONS'

6 RETURNING rowid, dname | |':'| | to_char (deptno) | |':'| | loc

7 INTO row_id, info

8 DBMS_OUTPUT.PUT_LINE ('ROWID:' | | row_id)

9 DBMS_OUTPUT.PUT_LINE (info)

10 END

11 /

ROWID:AAAR7ZAALAAAACDAAD

OPERATIONS:40:BOSTON

PL/SQL procedure successfully completed.

The RETURNING clause is used to retrieve the information of the deleted row: when the DELETE statement deletes a single row of data, the RETURNING clause can retrieve the ROWID and ref values of the deleted row, as well as the column expression of the deleted column, and store them in PL/SQL variables or composite variables; when the DELETE statement deletes multiple rows of data, the RETURNING clause can return the deleted row ROWID and ref values, as well as column expression values to the composite variable array. The restrictions on using the RETURNING clause in DELETE are the same as those on the RETURNING clause in the INSERT statement.

2.4.2 compound type

In addition to the various types described earlier, ORACLE provides a type called compound type in PL/SQL-records and tables.

2.4.2.1 record type

Record type is similar to structural data type in C language. It stores logically related, separated, basic data type variables as a whole. It must include at least one member of scalar or RECORD data type, called PL/SQL RECORD (FIELD), whose function is to store different but logically related information. When using a record data type variable, you need to first define the composition of the record and the record variable in the declaration section, and then refer to the record variable itself or its members in the execution section.

The syntax for defining record types is as follows:

TYPE record_name IS RECORD (

V1 data_type1 [NOT NULL] [: = default_value]

V2 data_type2 [NOT NULL] [: = default_value]

.

Vn data_typen [NOT NULL] [: = default_value])

Example 1:

SQL > DECLARE

2 TYPE test_rec IS RECORD (

3 Name VARCHAR2 (30) NOT NULL: ='my self-study'

4 Info VARCHAR2)

5 rec_book test_rec

6 BEGIN

7 rec_book.Info: = 'Oracle PL/SQL programming;'

8 DBMS_OUTPUT.PUT_LINE (rec_book.Name | |''| | rec_book.Info)

9 END

10 /

My self-taught Oracle PL/SQL programming

PL/SQL procedure successfully completed.

You can assign values to record variables with SELECT statements, as long as you make sure that the record fields match the fields in the query results list.

Example 2:

SQL > conn hr/hr@pdbtest

Connected.

SQL > set serveroutput on

SQL > DECLARE

2-defines the same record data type as these columns in the employees table

3 TYPE RECORD_TYPE_EMPLOYEES IS RECORD (

4 f_name employees.first_name%TYPE

5 h_date employees.hire_date%TYPE

6 j_id employees.job_id%TYPE)

7-declare a record variable of this record data type

8 v_emp_record RECORD_TYPE_EMPLOYEES

nine

10 BEGIN

11 SELECT first_name, hire_date, job_id INTO v_emp_record

12 FROM employees

13 WHERE employee_id = & emp_id

fourteen

15 DBMS_OUTPUT.PUT_LINE ('employee name:' | | v_emp_record.f_name

16 | | 'date of employment:' | | v_emp_record.h_date

17 | | 'position:' | | v_emp_record.j_id)

18 END

19 /

Enter value for emp_id: 206

Old 13: WHERE employee_id = & emp_id

New 13: WHERE employee_id = 206,

Employee name: William Employment date: 2002-06-07 00:00:00 Job: AC_ACCOUNT

PL/SQL procedure successfully completed.

A variable of record type can only hold one row of records queried from the database. If multiple rows of records are queried, an error will occur.

2.4.2.2 Array type

Data is a collection of members of the same data type. Each member has a unique subscript, depending on the member's position in the array. In PL/SQL, the array data type is VARRAY.

The syntax for defining VARRY data types is as follows:

TYPE varray_name IS VARRAY (size) OF element_type [NOT NULL]

Varray_name is the name of the VARRAY data type, and size is the lower integer, indicating the maximum number of members that can be accommodated, and the data type of each member is element_type. The default member can be null, otherwise you need to use NOT NULL to restrict it. For VARRAY data types, you must go through three steps: definition, declaration, and initialization.

Example 1:

SQL > DECLARE

2-defines a VARRAY data type that holds up to 5 members of the VARCHAR (25) data type

3 TYPE reg_varray_type IS VARRAY (5) OF VARCHAR (25)

4-declare a variable of this VARRAY data type

5 v_reg_varray REG_VARRAY_TYPE

six

7 BEGIN

8-assign initial values with constructor syntax

9 v_reg_varray: = reg_varray_type

10 ('China','US','UK', 'Japan', 'France')

eleven

12 DBMS_OUTPUT.PUT_LINE ('region name:' | | v_reg_varray (1) | |','

13 | | v_reg_varray (2) | |','

14 | | v_reg_varray (3) | |','

15 | | v_reg_varray (4))

16 DBMS_OUTPUT.PUT_LINE ('the value assigned to the fifth member of the initial NULL:' | | v_reg_varray (5))

17-after assigning initial values with constructor syntax, you can assign values to members in this way

18 v_reg_varray (5): = 'France'

19 DBMS_OUTPUT.PUT_LINE ('value of the 5th member:' | | v_reg_varray (5))

20 END

21 /

Region name: China, United States, United Kingdom, Japan

The value assigned to the fifth member of the initial NULL: France

Value of the fifth member: France

PL/SQL procedure successfully completed.

2.4.2.3 using% TYPE

To define a variable whose data type is the same as that of a defined data variable (especially a column of a table), you can use% TYPE.

The advantages of using the% TYPE feature are:

L you don't have to know the data type of the database column referenced

The data type of the referenced database column can be changed in real time, it is easy to be consistent, and there is no need to modify the PL/SQL program.

Example 1:

SQL > conn scott/tiger@clonepdb_plug

Connected.

SQL > set serveroutput on

SQL > DECLARE

2-define fields that match the table with the% TYPE type

3 TYPE T_Record IS RECORD (

4 T_no emp.empno%TYPE

5 T_name emp.ename%TYPE

6 T_sal emp.sal%TYPE)

7-declare variables that receive data

8 v_emp T_Record

9 BEGIN

10 SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7782

11 DBMS_OUTPUT.PUT_LINE

12 (TO_CHAR (v_emp.t_no) | |''| | v_emp.t_name | |''| TO_CHAR (v_emp.t_sal))

13 END

14 /

7782 CLARK 2450

PL/SQL procedure successfully completed.

Example 2:

SQL > DECLARE

2 v_empno emp.empno%TYPE: = & no

3 Type t_record is record (

4 v_name emp.ename%TYPE

5 v_sal emp.sal%TYPE

6 v_date emp.hiredate%TYPE)

7 Rec t_record

8 BEGIN

9 SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno

10 DBMS_OUTPUT.PUT_LINE (Rec.v_name | |'- -'| | Rec.v_sal | |'- -'| | Rec.v_date)

11 END

12 /

Enter value for no: 7782

Old 2: v_empno emp.empno%TYPE: = & no

New 2: v_empno emp.empno%TYPE: = 7782

CLARK---2450--1981-06-09 00:00:00

PL/SQL procedure successfully completed.

2.4.3 using% ROWTYPE

PL/SQL provides the% ROWTYPE operator, which returns a record type whose data type matches the data structure of the database table.

The advantages of using the% ROWTYPE feature are:

L you do not need to know the number and data type of columns in the referenced database

L the number of columns and data types in the referenced database can be changed in real time, easy to be consistent, and there is no need to modify the PL/SQL program.

Example 1:

SQL > DECLARE

2 v_empno emp.empno%TYPE: = & no

3 rec emp%ROWTYPE

4 BEGIN

5 SELECT * INTO rec FROM emp WHERE empno=v_empno

6 DBMS_OUTPUT.PUT_LINE ('name:' | rec.ename | | 'salary:' | rec.sal | | 'working hours:' | | rec.hiredate)

7 END

8 /

Enter value for no: 7782

Old 2: v_empno emp.empno%TYPE: = & no

New 2: v_empno emp.empno%TYPE: = 7782

Name: CLARK salary: 2450 working hours: 1981-06-09 00:00:00

PL/SQL procedure successfully completed.

2.4.4 LOB Typ

ORACLE provides the LOB (Large OBject) type, which is used to store types of large data objects. ORACLE currently mainly supports BFILE, BLOB, CLOB and NCLOB types.

BFILE (Movie)

Store large binary data objects, these data files are not placed in the database, but in a directory of the operating system, the table of the database only stores the directory of the files.

BLOB (Photo)

Stores large binary data types. Variable stores the location of a large binary object. The size of the large binary object-- and then execute

SQL > PRINT result

RESULT 15000

2.4.6 PL/SQL (TABLE)

Defines the record table (or index table) data type. It is similar to the record type, but it is an extension of the record type. It can handle multiple rows of records, similar to a two-dimensional array in high-level, making it possible to mimic tables in a database in PL/SQL.

The syntax for defining the record table type is as follows:

TYPE table_name IS TABLE OF element_type [NOT NULL]

INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2]

The keyword INDEX BY means to create a primary key index to reference a specific row in a record table variable.

Example 1:

SQL > DECLARE

2 TYPE dept_table_type IS TABLE OF

3 dept%ROWTYPE INDEX BY BINARY_INTEGER

4 my_dname_table dept_table_type

5 v_count number (2): = 3

6 BEGIN

7 FOR int IN 1.. V_count LOOP

8 SELECT INTO my_dname_table (int) FROM dept WHERE deptno=int10

9 END LOOP

10 FOR int IN my_dname_table.FIRST.. My_dname_table.LAST LOOP

11 DBMS_OUTPUT.PUT_LINE ('Department number:' | | my_dname_table (int) .deptno)

12 DBMS_OUTPUT.PUT_LINE ('Department name:' | | my_dname_table (int) .dname)

13 END LOOP

14 END

15 /

Department number: 10

Department name: ACCOUNTING

Department number: 20

Department name: RESEARCH

Department number: 30

Department name: SALES

PL/SQL procedure successfully completed.

Example 2: using record tables in an one-dimensional array

SQL > DECLARE

2-define record table data types

3 TYPE reg_table_type IS TABLE OF varchar2 (25)

4 INDEX BY BINARY_INTEGER

5-variables that declare the data type of the record table

6 v_reg_table REG_TABLE_TYPE

seven

8 BEGIN

9 v_reg_table (1): = 'Europe'

10 v_reg_table (2): = 'Americas'

11 v_reg_table (3): = 'Asia'

12 v_reg_table (4): = 'Middle East and Africa'

13 v_reg_table (5): = 'NULL'

fourteen

15 DBMS_OUTPUT.PUT_LINE ('region name:' | | v_reg_table (1) | |','

16 | | v_reg_table (2) | |','

17 | | v_reg_table (3) | |','

18 | | v_reg_table (4))

19 DBMS_OUTPUT.PUT_LINE ('value of the 5th member:' | | v_reg_table (5))

20 END

21 /

Region name: Europe, Americas, Asia, Middle East and Africa

The value of the fifth member: NULL

PL/SQL procedure successfully completed

Example 3: using a record table by a two-dimensional array

SQL > conn hr/hr@pdbtest

Connected.

SQL > set serveroutput on

SQL > DECLARE

2-define record table data types

3 TYPE emp_table_type IS TABLE OF employees%ROWTYPE

4 INDEX BY BINARY_INTEGER

5-variables that declare the data type of the record table

6 v_emp_table EMP_TABLE_TYPE

7 BEGIN

8 SELECT first_name, hire_date, job_id INTO

9 v_emp_table (1). First_name,v_emp_table (1). Hire_date, v_emp_table (1). Job_id

10 FROM employees WHERE employee_id = 177,

11 SELECT first_name, hire_date, job_id INTO

12 v_emp_table (2). First_name,v_emp_table (2). Hire_date, v_emp_table (2). Job_id

13 FROM employees WHERE employee_id = 178

fourteen

15 DBMS_OUTPUT.PUT_LINE ('177employee name:' | | v_emp_table (1) .first_name

16 | | 'date of employment:' | | v_emp_table (1) .hire_date

17 | | 'position:' | | v_emp_table (1) .job_id)

18 DBMS_OUTPUT.PUT_LINE ('178 employee name:' | | v_emp_table (2) .first_name

19 | | 'date of employment:' | | v_emp_table (2) .hire_date

20 | | 'position:' | | v_emp_table (2) .job_id)

21 END

22 /

Jack Employment date: 2006-04-23 00:00:00 Job: SA_REP

Kimberely Employment date: 2007-05-24 00:00:00 Job: SA_REP

PL/SQL procedure successfully completed.

2.5 operators and expressions (data definition)

2.5.1 Relational operator

2.5.2 General operator

2.5.3 logical operator

2.6 variable assignment

In PL/SQL programming, variable assignment is a noteworthy place, and its syntax is as follows:

Variable: = expression

Variable is a PL/SQL variable and expression is an PL/SQL expression.

2.6.1 character and numeric operation characteristics

Null plus number is still null: NULL +

< 数字>

= NULL

Null value plus (concatenation) character, the result is the character: NULL | | =

< 字符串>

2.6.2 BOOLEAN assignment

Boolean values are only TRUE, FALSE and NULL.

2.6.3 Database assignment

Database assignment is done through SELECT statements, which are assigned every time the SELECT statement is executed. Generally, the assigned variables correspond to the column names in SELECT one by one. Such as:

Example 1:

SQL > DECLARE

2 emp_id emp.empno%TYPE: = 7782

3 emp_name emp.ename%TYPE

4 wages emp.sal%TYPE

5 BEGIN

6 SELECT ename, NVL (sal,0) + NVL (comm,0) INTO emp_name, wages

7 FROM emp WHERE empno = emp_id

8 DBMS_OUTPUT.PUT_LINE (emp_name | |'-'| | to_char (wages))

9 END

10 /

CLARK----2450

PL/SQL procedure successfully completed.

Tip: you cannot assign a column in a SELECT statement to a Boolean variable.

2.6.4 convertible type assignment

Convert CHAR to NUMBER:

Use the TO_NUMBER function to complete character-to-number conversion, such as:

V_total: = TO_NUMBER ('100.0') + sal

Convert NUMBER to CHAR:

You can use the TO_CHAR function to convert numbers to characters, such as:

V_comm: = TO_CHAR ('123.45') | |' CNY'

L characters are converted to dates:

Use the TO_DATE function to convert characters to dates, such as:

V_date: = TO_DATE ('2001.07.03)

L date converted to character

Use the TO_CHAR function to achieve date-to-character conversion, such as:

V_to_day: = TO_CHAR (SYSDATE, 'yyyy.mm.dd hh34:mi:ss')

2.7 scope and visibility of variables

In PL/SQL programming, some dangerous errors may be hidden if the definition of variables is not unified, which is mainly due to the scope of variables. The scope of a variable refers to the valid scope of a variable. Similar to other high-level languages, the scope of a variable in PL/SQL is characterized by:

The scope of the l variable is within the program unit (block, subroutine, package) you refer to. That is, from the declaration of the variable to the end of the block.

A variable (identifier) is visible only within the block you refer to.

When a variable is out of scope, the PL/SQL engine frees up the space used to store the variable (because it may not be used).

When the variable is redefined in a subblock, it functions only within that block.

Example 1:

SQL > DECLARE

2 Emess char (80)

3 BEGIN

four

5 DECLARE

6 V1 NUMBER (4)

7 BEGIN

8 SELECT empno INTO v1 FROM emp WHERE LOWER (job) = 'president'

9 DBMS_OUTPUT.PUT_LINE (V1)

10 EXCEPTION

11 When TOO_MANY_ROWS THEN

12 DBMS_OUTPUT.PUT_LINE ('More than one president')

13 END

fourteen

15 DECLARE

16 V1 NUMBER (4)

17 BEGIN

18 SELECT empno INTO v1 FROM emp WHERE LOWER (job) = 'manager'

19 EXCEPTION

20 When TOO_MANY_ROWS THEN

21 DBMS_OUTPUT.PUT_LINE ('More than one manager')

22 END

twenty-three

24 EXCEPTION

25 When others THEN

26 Emess:=substr (SQLERRM,1,80)

27 DBMS_OUTPUT.PUT_LINE (emess)

28 END

29 /

7839

More than one manager

PL/SQL procedure successfully completed.

2.8 comment

In PL/SQL, you can use two symbols to write comments, namely:

L use double'-'(minus sign) to add comments

PL/SQL allows you to write comments with -, and its scope is only valid on one line. Such as:

V_Sal NUMBER (12pr 2);-- salary variable of the staff.

Use / / to add one or more lines of comments, such as:

/ /

/ File name: /

/ author: /

/ time: /

/ /

Tip: for PL/SQL programs that are explained and stored in the database, the general system automatically removes the comments from the header of the program. Only comments after PROCEDURE are retained; in addition, blank lines in the program are automatically removed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.