Introduction to PL/SQL Program Design

Source: Internet
Author: User

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:

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.