Introduction to PL/SQL Program Design
① PL/SQL program design overview
PL/SQL is an advanced database programming language used to access Oracle databases in various environments. Since the language is integrated into the database server, 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.
Oracle -- plsql Composite data type
-------------------------------------- Split line --------------------------------------
Rlwrap
SQLPLUS spool to dynamic Log File Name
Oracle SQLPLUS prompt settings
Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)
PL/SQL Developer Practical Skills
-------------------------------------- Split line --------------------------------------
② 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 must be a letter;
Case-insensitive;
You cannot use '-' (minus sign );
It cannot be an SQL reserved word.
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;
For more details, please continue to read the highlights on the next page: