PL/SQL getting started

1.1 PL/SQL Introduction

PL/SQL is a procedural language of ORACLE, including a complete set of data types, condition structures, cyclic structures, and Exception Handling structures. PL/SQL can execute SQL statements, PL/SQL functions can also be used in SQL statements.

1.2 create PL/SQL Blocks

DECLARE
...
BEGIN
...
EXCEPTION
END;
1.3 PL/SQL data type

Name	Type	Description
NUMBER	Number Type	Can store integer and real value, and can define the precision and value range
BINARY_INTEGER	Number Type	Stores signed integers to optimize integer computing performance.
DEC	Number Type	NUMBER Child type, decimal
DOUBLE PRECISION	Number Type	NUMBER Subtype, high-precision real number
INTEGER	Number Type	NUMBER Child type, integer
INT	Number Type	NUMBER Child type, integer
NUMERIC	Number Type	NUMBER Is equivalent to NUMBER.
REAL	Number Type	NUMBER Is equivalent to NUMBER.
SMALLINT	Number Type	NUMBER Sub-type, with a smaller value range than INTEGER
VARCHAR2	Character Type	Stores variable-length strings with the maximum length.
CHAR	Character Type	Fixed Length string
LONG	Character Type	Variable-length string, up to 32,767
DATE	Date type	Store date values in the same database format
BOOLEAN	Boolean	TRUE OR FALSE
ROWID	ROWID	Row number for storing the database

Example:
DECLARE
ORDER_NO NUMBER (3 );
CUST_NAME VARCHAR2 (20 );
ORDER_DATE DATE;
EMP_NO INTEGER: = 25;
Pi constant number: = 3.1416;
BEGIN
NULL;
END;
1.4 handle PL/SQL exceptions

1.4.1 PL/SQL exceptions

For example:
DECLARE
X number;
BEGIN
X: = 'yyyy'; -- Error Here
EXCEPTION WHENVALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('exception handed ');
END;

Implementation Technology:
Exception when first_exception THEN
...
WHEN second_exception THEN
...
WHEN OTHERS THEN
/* The THERS exception processor must be placed at the end, which handles all exceptions not explicitly listed. */
...
END;

1.4.2 pre-defined exception

Exception name	ORACLE Error	Description
CURSOR_ALREADY_OPEN	ORA-6511	Try to open an opened cursor
DUP_VAL_ON_INDEX	ORA-0001	Trying to break a unique restriction
INVALID_CURSOR	ORA-1001	Try to use an invalid cursor
INVALID_NUMBER	ORA-1722	Attempts to perform numeric operations on non-numeric values
LOGIN_DENIED	ORA-1017	Invalid username or password
NO_DATA_FOUND	ORA-1403	No data found in the query
NOT_LOGGED_ON	ORA-1012	Try database operation before connection
PROGRAM_ERROR	ORA-6501	Internal error
ROWTYPE_MISMATCH	ORA-6504	The primary variable is incompatible with the optical indicator type.
STORAGE_ERROR	ORA-6500	Internal error
TIMEOUT_ON_RESOURCE	ORA-0051	Timeout
TOO_MANY_ROWS	ORA-1422	SELECT INTD Multiple lines returned by the command
TRANSACTION_BACKED_OUT	ORA-006	Returned due to deadlock submission
VALUE_ERROR	ORA-6502	Conversion or cropping Error
ZERO_DIVIDE	ORA-1476	Try to be division by zero

　

1.4.3 custom exception handling

DECLARE
BAD_ROWID EXCEPTION;
X rowid;
PRAGMA EXCEPTION_INIT (BAD_ROWID,-01445 );
BEGIN
SELECT ROWID INTO X FROM TAB
Where rownum = 1;
Exception when BAD_ROWID THEN
DBMS_OUTPUT.PUT_LINE ('cannot query rowid from this view ');
END;

Note:-01445 because The PRAGMA EXCEPTION_INIT command connects this variable (-01455)
This ORACLE error. The statement syntax is as follows:
PRAGMA EXCEPTION_INIT (exception_name, error_number );
Error_number is a negative number, because the error number is considered as a negative number. Remember to use a negative number when defining an error.

1.4.4 custom exception

An exception may not necessarily be a system error returned by oracle. You can create
Trigger and handle custom exceptions
DECLARE
SALARY_CODE VARCHAR2 (1 );
INVALID_SALARY_CODE EXCEPTION;
BEGIN
SALARY_CODE: = 'X ';
IF SALARY_CODE not in ('A', 'B', 'C') THEN
RAISE INVALID_SALARY_CODE;
End if;
Exception when INVALID_SALARY_CODE THEN
DBMS_OUTPUT.PUT_LINE ('invalid SALARY Code ');
END;

1.5 query a single record in PL/SQL

In PL/SQL, when no explicit cursor is defined, You need to query a single record and assign the record data to the variable.
DECLARE
Ln_dno NUMBER;
Lvs_dname VARCHAR2 (40 );
BEGIN
SELECT DEPT_NO, DEPT_NAME
INTO ln_dno, lvs_dname
FROM dept
WHERE DEPT_NO = 1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (ln_dno) | '.' | lvs_dname );
Exception when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('no DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('too _ MANY_ROWS ');
END;

1.6 query multiple records with the cursor

CURSOR is a pointer to an area called a context-related area. This area is located in the global area of the server's processing process (PGA). When a query is executed on the server, the record set returned by the query is stored in the context-related area. You can retrieve these records to the client application through cursor operations.

1.6.1 how to use the cursor

DECLARE
CURSOR C1 is select VIEW_NAME FROM ALL_VIEWS
Where rownum <= 10
Order by VIEW_NAME;
VNAME VARCHAR2 (40 );
BEGIN
OPEN C1;
FETCH C1 into vname;
WHILE C1 % FOUND LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR (C1% ROWCOUNT) | ''| VNAME );
FETCH C1 into vname;
End loop;
END;

Attribute	Content
% FOUND	Boolean attribute. If this record is returned successfully the most recently, the value is TRUE.
% NOTFOUND	Boolean attribute. Its value is always opposite to the value of % FOUND.
% ISOPEN	Boolean attribute, returns TRUE when the cursor is open
% ROWCOUNT	Number attribute, returns the number of records read from the cursor

　

1.6.2 Use cursor FOR Loop

DECLARE
CURSOR C1 IS
SELECT VIEW_NAME
FROM ALL_VIEWS
Where rownum <= 10
Order by VIEW_NAME;
BEGIN
For I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE (I. VIEW_NAME );
End loop;
End loop;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

1.6.3 parameter-based cursor

DECLARE
CURSOR C1 (VIEW_PATTERN VARCHAR2) IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE VIEW_PATTERN | '%' AND
ROWNUM <= 10
Order by VIEW_NAME;
VNAME VARCHAR2 (40 );
BEGIN
For I IN C1 ('user _ AR ') LOOP
DBMS_OUTPUT.PUT_LINE (I. VIEW_NAME );
End loop;
DBMS_OUTPUT.PUT_LINE ();
For I IN C1 ('user') LOOP
DBMS_OUTPUT.PUT_LINE (I. VIEW_NAME );
End loop;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('aaa ');
END;

1.7 create variables that represent database records and columns

Variable name base table name. Column name % TYPE
DECLARE
D_NO DEPT. DEPT_NO % TYPE;
D_NAME DEPT. DEPT_NAME % TYPE;
BEGIN
SELECT DEPT_NO, DEPT_NAME INTO D_NO, D_NAME
From dept;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (D_NO ));
Exception when NO_DATA_FOUND THEN
NULL;
END;

Variable name base table name % ROWTYPE
DECLARE
D VEQU12 % ROWTYPE;
BEGIN
SELECT ASSET12ID, ASSET12NAME
Into d. ASSET12ID, D. ASSET12NAME
FROM VEQU12;
DBMS_OUTPUT.PUT_LINE (D. ASSET12ID );
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('too _ MANY_ROWS ');
END;
Note:
When you want to create a variable to represent a base table column or to create multiple variables to represent a whole record, you can actually use the % TYPE attribute and % ROWTYPE attribute, the % TYPE and % ROWTYPE attributes ensure that your PL/SQL code works normally when the structure of the Base table or the data TYPE of a column changes.

1.9 how to use PL/SQL tables to implement array Functions

PL/SQL tables are similar to one-dimensional arrays of other procedural languages (such as C. To implement a PL/SQL table, you must create a data type and describe variables separately.
Type <Type Name> Is
Table Of <data type>
Index by Binary_Integer;
The following is an example:
Declare
Type Array_type is
Table Of Number
Index by Binary_Integer;
My_Array Array_type;
Begin
For I In 1 .. 10 Loop
My_Array (I): = I * 2;
End Loop;
For I In 1 .. 10 Loop
Dbms_Output.Put_line (To_char (My_Array (I )));
End Loop;
End