PLSQL programming basics, control statements, exception details, plsql programming Basics

PLSQL programming basics, control statements, exception details, plsql programming Basics
Basic PL/SQL programmingI,PL/SQL Overview1.PL/SQL Introduction

PL/SQL (Procedural Language) is a programming Language that combines with Structured Query Language (SQL). It is an extension of SQL. It supports multiple data types, such as large objects and Collection types. It can use control structures such as conditions and loops to create stored procedures, triggers, and packages, adds program logic to SQL statement execution, which is closely integrated with Oracle servers and Oracle tools, providing portability, flexibility, and security.

To put it simply, PL/SQL is a combination of PL + SQL (process language + Structured Query Language). It is an extension of SQL and can be done by SQL. PL/SQL can be done by the vast majority ..

2. Advantages of PL/SQL

L supports SQL: Data Control commands, transaction control commands, cursor control, SQL functions, and SQL operators;

L supports object-oriented programming;

L portability and running on any operating system;

L compiled and executed, with excellent performance;

L tightly integrated with SQL to simplify data processing, support SQL data types, support NULL values, support for % type and % rowtype attribute types (the most interesting in oracle );

L Security

3. PL/SQL ArchitectureII, PL/SQL programming structure

PL/SQL blocks are the basic units of PL/SQL programs. They combine logic-related statements and statements.

PL/SQL blocks are divided into three parts: Declaration, executable, and exception handling.

[DECLARE

Declarations] -- Declaration part: Definition variables, cursors, and custom exceptions

BEGIN

Executable statements -- executable part: Contains SQL and PL/SQL statements

[EXCEPTION

Handlers] -- Exception Handling part: Specifies the operation to be performed when an error occurs.

END;

Example:

/* Declaration part */

DECLARE

Qty_on_hand NUMBER (5 );

BEGIN

/* Executable part */

SELECT quantity INTO qty_on_hand FROM Products

WHERE product = 'Barbie doll'

For update of quantity;

IF qty_on_hand> 0 THEN

UPDATE Products SET quantity = quantity + 1

WHERE product = 'Barbie doll ';

Insert into purchase_record

VALUES ('purchased Barbie dolls ', SYSDATE );

End if;

COMMIT;

/* Exception Handling statement */

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('error: '| SQLERRM );

END;

Tip: In BEGIN ...... Use the SELECT statement in the END. SELECT… must be used... INTO.

III, Basic PL/SQL programming1. Variables and constants

Declare variables:

Variable name type;

Icode VARCHAR2 (6 );

Example:

DECLARE

/* Declare the variable. Note that it ends with a; sign */

Icode VARCHAR2 (6 );

P_catg VARCHAR2 (20 );

P_rate NUMBER;

C_rate constant number: = 0.10;

BEGIN

...

Icode: = 'i205 '; -- assign a value to the variable

SELECT p_category, itemrate * c_rate

INTO p_catg, p_rate FROM itemfile

WHERE itemcode = icode;

...

END;

2. Assignment

(1) Use: = value assignment

C_rate NUMBER: = 0.10;

(2) assign values using the select into statement

SELECT column 1, column 2 ...... INTO variable 1, variable 2 ...... FROM table WHERE Condition

3. Input and Output

Input:

L & (usually used for testing, rarely used)

DECLARE

Sid NUMBER (5 );

BEGIN

Sid: = & enter;

DBMS_OUTPUT.PUT_LINE (sid );

END;

Tip:

Sid: = & enter; -- indicates that the input is a NUMBER integer.

Sid: = '& enter'; -- indicates that the input is of the VARCHAR2 string type.

Output:

L SELECT output

SELECT 'learn' FROM dual;

L DBMS_OUTPUT.PUT_LINE output statement

DBMS_OUTPUT.PUT_LINE ('Study hard, daily upgrade ');

4. Splicing

Splicing Symbol: |

DBMS_OUTPUT.PUT_LINE ('Study hard '| ''| 'daily upgrade ');

5. Note

L -- line comment

L/**/multi-line comment

IV, Data Type1. Common scalar type

Character	CHAR, VARCHAR2, NVARCHAR2, LONG
Number	NUMBER, DECIAML, FLOAT, INTEGER
Date and Time	DATE, TIMESTAMP
Boolean	BOOLEAN (used to store TRUE, FLASE, and NULL)

2. LOB type

It is used to store unstructured data such as big text, images, videos, and sounds. Up to 4 GB data can be stored.

BLOB	Store large binary objects
CLOB	Store large character data
NCLOB	Large UNICODE characters
BFILE	Store large binary objects

3. Attribute typeL % TYPE column TYPE

Provides the data type of a variable or column.

Example 1:

Calculate the onboarding Date of 7369 In the emp table (I don't know what data type this column is)

DECLARE

Sid NUMBER: = 7369;

Shiredate emp. hiredate % TYPE; -- declares variables of the same TYPE as hiredate columns in the emp table.

BEGIN

SELECT hiredate INTO shiredate FROM emp WHERE empno = sid;

DBMS_OUTPUT.PUT_LINE (shiredate );

END;

Example 2:

Calculate the name and work information of 7369 (when the column type is unknown)

DECLARE

Sid NUMBER;

Sname emp. ename % TYPE;

Sjob emp. job % TYPE;

BEGIN

Sid: = 7369;

SELECT ename, job INTO sname, sjob FROM emp WHERE empno = sid;

DBMS_OUTPUT.PUT_LINE (sname | ''| sjob );

END;

L % ROWTYPE Row Type

If the table has too many columns that do not know the TYPE, use % TYPE to write a lot of code. This is simple. We use % ROWTYPE.

That is, the type of a variable is the type of a table row (including multiple columns), that is, the object name.

Syntax:

Object variable table name % ROWTYPE;

Example:

Query all information of 7369 (when all column types are unknown)

DECLARE

Sid number;

Er emp % ROWTYPE; -- er can be regarded as the object name

BEGIN

Sid: = 7369;

SELECT * INTO er FROM emp WHERE empno = sid;

DBMS_OUTPUT.PUT_LINE (er. ename | ''| er. job); -- output the ename in the er object

END;

V, PL/SQL control statements

Flow Control statements supported by PL/SQL:

Conditional Control: IF statement and CASE statement

LOOP Control: LOOP, while loop, FOR LOOP

Sequential Control: GOTO statements and NULL statements (statements that do not do anything)

1. IF statement

L IF-THEN ...... ENF IF

L IF-THEN-ELSE ...... ENF IF

L IF-THEN-ELSIF ...... ENF IF

Example:

Enter the salary. If you pay more than 3500, = 3500, <3500 effort

DECLARE

Sal NUMBER;

BEGIN

Sal: = & enter the salary;

IF sal> 3500 THEN

DBMS_OUTPUT.PUT_LINE ('tax payby ');

ELSIF sal = 3500 THEN

DBMS_OUTPUT.PUT_LINE (' ');

ELSE

DBMS_OUTPUT.PUT_LINE ('hardworking ');

End if;

END;

2. CASE statement

The CASE statement is used to compare a single variable or expression with multiple values.

Example:

Enter the score based on the score.

BEGIN

CASE '& enter'

WHEN 'a 'then DBMS_OUTPUT.PUT_LINE ('excellent ');

WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('excellent ');

WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('good ');

WHEN 'd 'then DBMS_OUTPUT.PUT_LINE ('General ');

WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('poorer ');

ELSE DBMS_OUTPUT.PUT_LINE ('no such score ');

End case;

END;

3. LOOP statement

LOOP is an unconditional LOOP (that is, an unconditional LOOP), used with EXIT or exit when.

EXIT: indicates to EXIT the LOOP.

Exit when: EXIT the loop when a condition is met.

Example 1:

Print Output 1 ~ 100.

DECLARE

I NUMBER (3 );

BEGIN

I: = 1;

LOOP

DBMS_OUTPUT.PUT_LINE (I );

I: = I + 1;

Exit when I = 100; -- exit loop when I = 100

End loop;

END;

Example 2:

Control loop jump.

DECLARE

I NUMBER (3 );

BEGIN

I: = 1;

LOOP

LOOP

DBMS_OUTPUT.PUT_LINE (I );

I: = I + 1;

EXIT a_loop WHEN I = 100; -- return to the <> position WHEN I = 100

End loop;

EXIT B _loop whenI = 100;

End loop;

<> -- <Name>

DBMS_OUTPUT.PUT_LINE ('first position ');

< > -- Loop name

DBMS_OUTPUT.PUT_LINE ('second position ');

END;

4. WHILE statement

WHILE is a conditional LOOP statement used with LOOP.

Example:

1 ~ Sum of 100.

DECLARE

I NUMBER (3 );

MySum NUMBER (5 );

BEGIN

I: = 0;

MySum: = 0;

WHILE I <= 100 LOOP

MySum: = mySum + I;

I: = I + 1;

End loop;

DBMS_OUTPUT.PUT_LINE ('sum: '| mySum );

END;

5. FOR statement

FOR is a conditional loop statement, also known as a FOR-IN loop. Use with LOOP.

Example 1:

1 ~ Sum of 100.

DECLARE

Mysum NUMBER (5 );

BEGIN

Mysum: = 0;

FOR I IN 1 .. 100 LOOP -- 1 .. 100 indicates from 1 to 100

Mysum: = mysum + I;

End loop;

DBMS_OUTPUT.PUT_LINE (mysum );

END;

Example 2:

1 ~ 100 the sum of even numbers.

DECLARE

Mysum NUMBER (5 );

BEGIN

Mysum: = 0;

FOR I IN 1 .. 100 LOOP -- 1 .. 100 indicates from 1 to 100

IF mod (I, 2) THEN

Mysum: = mysum + I;

End if;

End loop;

DBMS_OUTPUT.PUT_LINE (mysum );

END;

VI, PL/SQL Exception Handling

When an exception occurs, the statement stops execution and the control is transferred to the exception handling section of the PL/SQL block.

There are two types of exception handling:

1. Pre-defined exception

This is implicitly triggered when PL/SQL programs violate ORACLE rules or exceed system restrictions.

Example:

DECLARE

Ordernum VARCHAR2 (5 );

BEGIN

SELECT empno INTO ordernum FROM emp;

EXCEPTION

WHEN TOO_MANY_ROWS THEN -- pre-defined exception, that is, WHEN there are too many exceptions

DBMS_OUTPUT.PUT_LINE ('Return multiple rows'); -- execute the output statement.

END;

Summary of predefined exceptions provided by PL/SQL:

Access_assist_null	Appears when the object is not initially written
CASE_NOT_FOUND	When the options in the CASE statement do not match the data entered by the user
COLLECTION_IS_NULL	It appears when assigning values to tables or arrays that have not been initialized.
DUP_VAL_ON_INDEX	When a user tries to store duplicate values in a database column with a unique index
INVALID_CURSOR	When an illegal cursor operation is executed (such as opening an unopened cursor ),
INVALID_NUMBER	When you convert a string to a number
LOGIN_DENIED	When the entered user name or password is invalid
NO_DATA_FOUND	When a request does not exist in the table
STORAGE_ERROR	Occurs when memory is damaged or PL/SQL runs out of memory
TOO_MANY_ROWS	Multiple rows are returned after the select into statement is executed.
VALUE_ERROR	An error occurs when a size limit error occurs.
ZERO_DIVIDE	When Division by zero
OTHERS	For all exceptions
CURSOR_ALREADY_OPEN	When a user tries to open an opened cursor

2. Custom exception

You can define exceptions in the Declaration section of PL/SQL blocks. Custom exceptions are explicitly thrown by RAISE statements.

Example:

The user-defined input is incorrect.

DECLARE

InvalidCATEGORY EXCEPTION; -- declares the EXCEPTION object

Category VARCHAR2 (10 );

BEGIN

Category: = '& Category ';

IF category not in ('Attach ', 'topic', 'spare parts ') THEN -- IF the input is NOT an attachment, top cover, or spare parts

RAISE invalidCATEGORY; -- throw an invalidCATEGORY exception

ELSE

DBMS_OUTPUT.PUT_LINE ('the type you entered is '| category );

End if;

EXCEPTION

WHEN invalidCATEGORY THEN -- WHEN an invalidCATEGORY exception is thrown, the output is executed.

DBMS_OUTPUT.PUT_LINE ('unable to recognize this category ');

END;

With multiple exception bodies

EXCEPTION

WHEN invalidCATEGORY1 THEN

DBMS_OUTPUT.PUT_LINE ('error message 1 ');

WHEN too_many_rows THEN

DBMS_OUTPUT.PUT_LINE ('Too many row ');

......

3. RAISE_APPLICATION_ERROR () function

Purpose: return the exception information to the called client.

Basic Syntax:

RAISE_APPLICATION_ERROR (error_number_in NUMBER, error_msg_in VARCHAR2 );

Example:

RAISE_APPLICATION_ERROR (-20001, 'error message ');

Explanation:

-20001 indicates the error code. The error code assigned to the user is in the range of-20000 ~ -20999, a total of one thousand.

The 'error message 'is of the VARCHAR2 type and consists of a maximum of 2000 bytes.