Basic Analysis of PL/SQL

Basic Analysis of PL/SQL SQLSQL (Structured Query Language) is the basic operating Language of relational databases. It is an interface for applications to interact with databases. It integrates Data Query, Data Manipulation, Data Definition, and Data Control functions, this allows us to access the database through the SQL language to perform the corresponding operations. Www.2cto.com ● Data Query Language (SELECT statement): used to retrieve database data. ● Data manipulation language (DML): Used to change database data, including INSERT, UPDATE, and DELETE statements. ● Transaction control language (TCL): used to maintain data consistency, including three statements: COMMIT, ROLLBACK, and SAVEPOINT. ● Data Definition Language (DDL): used to create, modify, and delete database objects. Such as create table, DDL statements automatically commit transactions. ● Data Control Language (DCL): Used to GRANT and REVOKE permissions, including GRANT and REVOKE commands. The DCL statement automatically submits the transaction. SQL keywords, object names, and column names are case-insensitive. character values and date values are case-sensitive. SelEct ename, sal from emp where ename = 'Smith '; here, SMITH must be uppercase and single quotation marks. Www.2cto.com PL/SQL is a unique programming language of Oracle. It not only has the basic features of the process programming language (loops, condition branches, etc ), it also has advanced features of the object programming language (heavy load, inheritance, etc ). PL/SQL (Procedural Language/SQL) is a Procedural extension of Oracle in the standard SQL Language. PL/SQL not only allows embedding SQL statements, but also allows defining variables and constants, allows the use of conditional statements and loop statements, allows the use of exceptions to handle various errors, thus providing more powerful functions. PL/SQL Block is the basic program unit of PL/SQL. Compiling PL/SQL programs is actually compiling PL/SQL blocks. To complete relatively simple functions, you only need to write one PL/SQL block. To implement complex application functions, other PL/SQL blocks may need to be nested in one PL/SQL block. ● PL/SQL block structure PL/SQL block consists of three parts: definition part, execution part, and Exception Handling part. The basic structure of PL/SQL blocks is as follows: DECLARE/** definition section-defining constants, variables, cursors, exceptions, and complex data types, this part is optional */www.2cto.com BEGIN/** execution part-PL/SQL statements and SQL statements, this part is required */EXCEPTION/** EXCEPTION Handling part-processing running errors, this part is optional */END; /* block end mark */1. PL/SQL blocks that only contain the execution part:
Dbms_output is the system package provided by Oracle. put_line is the process contained in the package and is used to output string information.

Note: when using the dbms_output package to output messages, you must set the serveroutput environment variable of SQL * Plus to on to enable the server output display. 2. PL/SQL block variables, constants, exceptions, and explicit cursors that contain the definition part and execution part must be defined in the definition part. Www.2cto.com

Display the employee name according to the entered employee number, and define the variable v_ename to temporarily store the employee name. & no is an alternative variable of SQL * Plus, which only plays a role in the current statement, and enter data for it. If the substitution variable provides data for the numeric column, it can be referenced directly. If the substitution variable provides data for the character type column or the date type column, it must be enclosed by single quotation marks. & The label is similar to a single & label, but note that & the Defined variables will always take effect in the current SQL * Plus environment.
3. PL/SQL blocks that contain definition, execution, and Exception Handling. To avoid running errors of PL/SQL programs and improve the robustness of PL/SQL programs, the PL/SQL program running errors should be properly handled.
● PL/SQL block classification based on the functions of the required application module, PL/SQL blocks can be divided into four types: anonymous blocks, naming blocks, subprograms, and triggers. 1. Anonymous block: a PL/SQL block that starts with DECLARE and has no name. 2. A nameblock refers to a PL/SQL block with a specific name. It is very similar to an anonymous block, except that it is marked before a PL/SQL block <>. When nested blocks are used, nameblocks can be used to differentiate multi-level nested hierarchies.

The external PL/SQL block <outer> depends on the deptno queried by the embedded PL/SQL block <inner> to query the dname. 3. subprograms include processes, functions, and packages. Procedure is used to perform specific operations. In SQL * Plus, you can use the CREATE PROCEDURE command to CREATE a process. You can specify either the input parameter (IN) or the output parameter (OUT) during the creation process ). Www.2cto.com

The update_sal process is used to update the employee's salary. execute or call can be used to call the process. A Function is used to return specific data. You can use the create function command to CREATE a Function. When creating a function, the RETURN clause must be included in the function header, and the RETURN statement must be included in the function body to RETURN data.
A Package is used to logically combine related processes and functions. It consists of the Package specification and Package body. The PACKAGE specification is used to define common constants, variables, procedures, and functions. The create package command is used to establish the PACKAGE specification. Package specification: only process and function descriptions are included, and no code is implemented.
Package body: used to implement the procedures and functions in the PACKAGE specification. Use the create package body command.
When calling a package's process and function, the package name must be prefixed before the process and function name (package name. subprogram name), and if you want to access the packages of other schemes, you must add the scheme name as the prefix (scheme name. package name. subroutine name ).

4. The trigger www.2cto.com refers to the stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger event and trigger operation. Common trigger events include INSERT, UPDATE, and DELETE statements, while the trigger operation is often a PL/SQL block. Use the create trigger command to CREATE a TRIGGER. When we update the deptno column of the dept table, the integrity constraints are violated. Because some records in the emp Table reference deptno = 10.
Create a cascade update trigger
Update the deptno = 10 column in the dept table. We can see that the reference to deptno = 10 in the emp table is also cascade updated.
 
Author: Nevo