Introduction to PL/SQL programming



Introduction to ①pl/sql Program design

PL/SQL is an advanced database programming language that is specifically designed to access Oracle databases in a variety of environments. Because the language is integrated into the database server,
So PL/SQL code can quickly and efficiently process data

The SQL statements that can be used in PL-SQL are:
Insert,update,delete,select ... Into,commit,rollback,savepoint.
Tip: In PL/SQL, you can only use the DML portion of the statement, not the DDL part, if you want to use DDL in PL/SQL (such as CREATE TABLE, etc.), only in a dynamic way

②PL/SQL block structure and constituent elements
The PL/SQL program consists of three blocks, that is, the declaration part, the execution part, the exception handling part

The structure of PL/SQL blocks is as follows:

DECLARE
/* Declaration section: The variables, types and cursors used in this declaration, and the local stored procedures and functions */
BEGIN
/* Execute part: Process and SQL statement, which is the main part of the program */
EXCEPTION
/* Execute exception section: Error handling */
END;

Where the execution part is required.
PL/SQL blocks can be divided into three categories:
1. Nameless BLOCK: Dynamic construction, can only be executed once.
2. Subroutines: Stored procedures, functions, and packages stored in the database. They can be called in other programs when they are established on the database.
3. Triggers: When a database operation occurs, some events are triggered, which automatically executes the corresponding program.

③ identifier
The identifier definition in PL/SQL programming is the same as the requirement for the definition of an identifier. Requirements and restrictions are:
 identifier name cannot exceed 30 characters;
 the first character must be a letter;
 is not case-sensitive;
 cannot use '-' (minus);
 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 result in incorrect results

Recommended naming methods:
Example of naming rules for identifiers
Program Variable V_name v_name
Program Constants C_name C_company_name
Cursor variable name_cursor emp_cursor
Exception ID E_name e_too_many
Table Type Name_table_type Emp_record_type
Table Name_table EMP
Record Type Name_record Emp_record
Substitution variable P_name p_sal
Binding variable G_name g_year_sal

④ Variable Type
In addition to Char, VARCHAR2, Binary_integer, Number (p,s), LONG, DATE, BOOLEAN, ROWID, ROWID these
There are also composite types
In addition to the various types described earlier in PL/SQL, ORACLE provides a type called a composite type---records and tables

1) record type
A record type is a domain (field) that stores logically related data as a unit, called a PL/SQL record, and is used to store disparate but logically related information.
The definition record type syntax is as follows:
TYPE Record_type is record (
Field1 type1 [Not NULL] [: = Exp1],
Field2 type2 [Not NULL] [: = Exp2],
. . .   . . .
FIELDN Typen [Not NULL] [: = expn]);

Cases:
1 Custom record types

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

--Declaring a variable of a 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 types

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));

--Declaring a variable of a custom record type
V_emp_record Emp_record;
Begin
--by select ... into ... Statement assigns a value to a variable
Select last_name, email, salary, job_id into V_emp_record
From Employees
where employee_id = 186;

--Print the value of the variable
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 variables, dynamically get data declaration types

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);
   
 --Declaring a variable of a custom record type
  V_emp_record Emp_record;
Begin
 -by select ... into ... Statement to assign a value to a variable
 select last_name, email, salary, job_id into V_emp_record
 from employees
 where employee_id = 186;
 
 --The value of the print variable
 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. Using%rowtype

Declare
--Declaring a variable of a record type
V_emp_record Employees%rowtype;
Begin
--by select ... into ... Statement assigns a value to a variable
SELECT * Into V_emp_record
From Employees
where employee_id = 186;

--Print the value of the variable
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)
A PL/SQL program can use a nested table type to create a variable with one or more columns and an infinite row, much like a table in a database. The general syntax for declaring nested table types is as follows:
TYPE Type_name is TABLE of
{datatype | {variable | table.column}% Type | Table%rowtype};
INDEX by Binary_integer;

The syntax is described as follows:
--table_name the name of the table type created.
The--is table indicates that a table type was created.
--datatype can be any of the legitimate PL/SQL data types, such as VARCHAR2.
--index by Binary_integer Specifies that the system creates a primary key index that refers to a specific row in a table-type variable.

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 (' 11111111111111 ');
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.

⑥ character and numeric operation characteristics
Null value plus number is still null: null + < number > = NULL
Null plus (connection) character, the result is character: NULL | | < string > = < string >

⑦ Convertible Type Assignment
CHAR converted to Number:
Use the To_number function to complete the conversion of a character to a number, such as:
V_total: = To_number (' 100.0 ') + sal;

Number is converted to char:
Use the TO_CHAR function to achieve numeric-to-character conversions, such as:
V_comm: = To_char (' 123.45 ') | | ' Yuan ';

Character Conversion to date:
You can use the To_date function to convert a character to a date, such as:
V_date: = to_date (' 2001.07.03 ', ' yyyy.mm.dd ');

Date converted to character
Use the To_char function to implement date-to-character conversions, such as:
V_to_day: = To_char (sysdate, ' yyyy.mm.dd hh24:mi:ss ');

⑧ notes
In PL/SQL, two symbols can be used to write comments, namely:
Use double-'-' (minus) annotation: PL/SQL allows you to write comments with –-, which is scoped to only one row
Use/*/To add one or more lines of comments: It's scoped to multiple lines of validity

Note: the PL/SQL program that is interpreted to be stored in the database automatically removes the comment from the program header.
Only comments after procedure are retained, and empty lines in the program are automatically removed.

Introduction to PL/SQL programming