Introduction to PL/SQL program design and plsql Program Design

Introduction to PL/SQL program design and plsql Program Design
Zookeeper

① PL/SQL program design overview

PL/SQL is an advanced database programming language used to access ORACLE databases in various environments. Because the language is integrated into the database server,
Therefore, PL/SQL code can process data quickly and efficiently.

The following SQL statements can be used in PL/SQL:
INSERT, UPDATE, DELETE, SELECT... INTO, COMMIT, ROLLBACK, SAVEPOINT.
Tip: only the DML part in the SQL statement can be used in PL/SQL, but not the DDL part. If you want to use DDL (such as CREATE table) in PL/SQL, it can only be used dynamically.

② PL/SQL block structure and composition elements
The PL/SQL program consists of three parts: Declaration, execution, and exception handling.

The structure of PL/SQL blocks is as follows:

DECLARE
/* Declaration part: Declares the variables, types, and cursors used by PL/SQL, as well as local stored procedures and functions */
BEGIN
/* Execution part: process and SQL statement, that is, the main part of the Program */
EXCEPTION
/* Execution exception section: Error Handling */
END;

The execution part is required.
PL/SQL blocks can be divided into three types:
1. Unknown block: it is a dynamic structure and can only be executed once.
2. Subroutine: stored procedures, functions, and packages in the database. After the database is established, you can call them in other programs.
3. Trigger: When a database operation occurs, some events are triggered to automatically execute the corresponding program.

③ Identifier
The identifier definitions in PL/SQL programming are the same as those in SQL. Requirements and restrictions:
The   identifier name cannot exceed 30 characters;
The first character of zookeeper must be a letter;
The condition is case-insensitive;
  Cannot use '-' (minus sign );
Reserved bytes cannot be SQL reserved words.

Tip: Generally, do not declare the variable name exactly the same as the field name in the table. Otherwise, incorrect results may be obtained.

Recommended naming method:
Example of an identifier naming rule
Program variable V_name
Program constant C_Name C_company_name
Cursor variable Name_cursor Emp_cursor
Exception identifier E_name e_too_timeout
Table type Name_table_type Emp_record_type
Table Name_table Emp
Record type Name_record Emp_record
Replace variable P_name P_sal
Bind the variable G_name G_year_sal

④ Variable type
Except CHAR, VARCHAR2, BINARY_INTEGER, NUMBER (p, s), LONG, DATE, BOOLEAN, ROWID, and ROWID
There are also composite types
In addition to the various types described above, ORACLE provides a type called composite-record and table in PL/SQL.

1) record type
The RECORD type stores logical data as a unit, called the PL/SQL RECORD FIELD. It stores different but logical information.
Syntax for defining record types is as follows:
TYPE record_type is record (
Field1 type1 [not null] [: = exp1],
Field2 type2 [not null] [: = exp2],
......
Fieldn typen [not null] [: = expn]);

Example:
1. Custom record type

Declare
-- Define a record type
Type customer_type is record (
V_cust_name varchar2 (20 ),
V_cust_id number (10 ));

-- Declare a variable of the custom record type
V_customer_type customer_type;
Begin
V_customer_type.v_cust_name: = 'andy Lau ';
V_customer_type.v_cust_id: = 1001;

Dbms_output.put_line (v_customer_type.v_cust_name | ',' | v_customer_type.v_cust_id );
End;

2. Custom record type

Declare
-- Define a record type
Type emp_record is record (
V_name varchar2 (25 ),
V_email varchar2 (25 ),
V_salary number (8, 2 ),
V_job_id varchar2 (10 ));

-- Declare a variable of the custom record type
V_emp_record emp_record;
Begin
-- Assign values to variables using the select... into... statement
Select last_name, email, salary, job_id into v_emp_record
From employees
Where employee_id = 186;
 
-- Print the variable value
Dbms_output.put_line (v_emp_record.v_name | ',' | v_emp_record.v_email | ',' |
V_emp_record.v_salary | ',' | v_emp_record.v_job_id );
End;

4. Use % type to define the variable and dynamically obtain the declared Data type

Declare
-- Define a record type
Type emp_record is record (
V_name employees. last_name % type,
V_email employees. email % type,
V_salary employees. salary % type,
V_job_id employees. job_id % type );

-- Declare a variable of the custom record type
V_emp_record emp_record;
Begin
-- Assign values to variables using the select... into... statement
Select last_name, email, salary, job_id into v_emp_record
From employees
Where employee_id = 186;
 
-- Print the variable value
Dbms_output.put_line (v_emp_record.v_name | ',' | v_emp_record.v_email | ',' |
V_emp_record.v_salary | ',' | v_emp_record.v_job_id );
End;

5. Use % rowtype

Declare
-- Declare a record type variable
V_emp_record employees % rowtype;
Begin
-- Assign values to variables using the select... into... statement
Select * into v_emp_record
From employees
Where employee_id = 186;
 
-- Print the variable value
Dbms_output.put_line (v_emp_record.last_name | ',' | v_emp_record.email | ',' |
V_emp_record.salary | ',' | v_emp_record.job_id | ',' |
V_emp_record.hire_date );
End;

2) PL/SQL table (nested table)
PL/SQL programs can use the nested table type to create variables with one or more columns and unlimited rows, which is similar to the tables in the database. The general syntax for declaring the nested table type is as follows:
TYPE type_name IS TABLE
{Datatype | {variable | table. column} % type | table % rowtype };
Index by BINARY_INTEGER;

Syntax description:
-- Name of the table type created by table_name.
-- Is table indicates that the TABLE type IS created.
-- Datatype can be any valid PL/SQL data type, such as varchar2.
-- Index by BINARY_INTEGER specifies that the system creates a primary key INDEX to reference specific rows in Table type variables.

Declare

Type my_record_type is record (
V_id varchar2 (32 ),
V_name varchar2 (100)
);

Type my_table_type is table of my_record_type
Index by BINARY_INTEGER;

Begin

Dbms_output.put_line ('123 ');
End;

Method description
EXISTS (n): Return TRUE if the nth element in a PL/SQL table exists;
COUNT: Returns the number of elements that a PL/SQL table currently contains;
FIRST \ LAST: Return the first and last (smallest and lastest) index numbers in a PL/SQL table. Returns NULL if the PL/SQL table is empty.
PRIOR (n): Returns the index number that precedes index n in a PL/SQL table;
NEXT (N): Returns the index number that succeeds index n in a PL/SQL table;
TRIM: TRIM removes one element from the end of a PL/SQL table.
TRIM (n): removes n element from the end of a PL/SQL table.
DELETE: DELETE removes all elements from a PL/SQL table.
DELETE (n): removes the nth elements from a PL/SQL table.
DELETE (m, n): removes all elements in the range m to n from a PL/SQL table.

⑤ Variable assignment
Variable: = expression;
Variable is a PL/SQL variable, and expression is a PL/SQL expression.

6 character and numeric Operation Features
NULL value plus number is still NULL: NULL + <number> = NULL
NULL value plus (connected) character, result: NULL ||< string >=< string>

7. Convertible type assignment
CHAR to NUMBER:
Use the TO_NUMBER function to convert characters to numbers, for example:
V_total: = TO_NUMBER ('192. 0') + sal;

NUMBER to CHAR:
The TO_CHAR function can be used to convert numbers to characters, for example:
V_comm: = TO_CHAR ('192. 45') | 'meta ';

Character conversion to date:
Use the TO_DATE function to convert characters to dates, for example:
V_date: = TO_DATE ('192. 123', 'yyyy. mm. dd ');

Convert date to character
The TO_CHAR function can be used to convert dates to characters, for example:
V_to_day: = TO_CHAR (SYSDATE, 'yyyy. mm. dd hh24: mi: ss ');

Comment
In PL/SQL, you can use two symbols to write comments:
Double '-' (minus sign) and comments: PL/SQL allows -- To write comments. Its function range is only valid in one row.
Use/**/to add one or more lines of comments: it is valid for multiple lines.

Tip: PL/SQL programs stored in the database are interpreted. Generally, the system automatically removes the comments in the program header.
Annotations are retained only after PROCEDURE, and empty rows in the program are automatically removed.