1. What is PL/SQL
PL/SQL is an extended language that combines the Oracle process language with the Structured Query language. is a block-structured language.
The composition of 2.pl/sql?
- Declarations section
- Executive Section
- Exception Handling Section
Advantages of 3.pl/sql
- Support for transaction control and SQL data manipulation commands
- Supports all data types of SQL and expands on this basis new data types, as well as SQL-enabled functions and operators
- PL/SQL can be stored in an Oracle server to improve program performance
- PL/SQL programs on the server can use permissions to control
- Good portability
/*1.PL/SQL Structure Demo */
[DECLARE]
--declaration section, where you declare the variables, types, and cursors used in PL/SQL, and local stored procedures and functions
BEGIN
--Execution part: process and SQL statement, which is the main part of the program
[EXCEPTION]
--Exception Handling section: Error handling
END;
--The execution part must not be omitted
/*2.pl/sql variable and constant syntax demo */
Variable name data type [(range size)][:= value]; --Variable declaration assignment syntax
Constant name CONSTANT data type: = value; --Constant Declaration assignment syntax
--Example:
DECLARE
V_ename VARCHAR2 (20);
V_rate number (7,2);
C_RATE_INCR contrant Number (7,2): = 1.10;
BEGIN
--Assignment method one, via select INFO
SELECT ename,sal* c_rate_incr into V_ename, v_rate
From employee
WHERE empno = ' 7788 ';
--Assignment mode two, via assignment operator ": ="
V_ename: = ' SCOTT ';
END;
/*3.pl/sql Control Statement Demo */
P77-P79: Control syntax
--3.1 condition Control
--3.1.1
BEGIN
IF false Then
Dbms_output. Put_Line (' t142 ');
ELSE
Dbms_output. Put_Line (' aaaaaa ');
END IF;
END;
--3.1.2
DECLARE
V_num number (2,0): = 10;
BEGIN
IF V_num>10 Then
Dbms_output. Put_Line (' 100 ');
Elsif v_num=10 Then
Dbms_output. Put_Line (' 10 ');
ELSE
Dbms_output. Put_Line (' 0 ');
END IF;
END;
--3.2.1 Cycle Control-loop
DECLARE
V_num number (2,0): = 1;
BEGIN
LOOP
Dbms_output. Put_Line (V_num);
V_num+:=1;
EXIT when v_num=11;
END LOOP;
END;
--3.2.2 Cycle Control-for
DECLARE
V_num number (2,0): = 1;
V_NUM2 number (2,0);
BEGIN
For v_num2 in REVERSE 1.. 5 LOOP
Dbms_output. Put_Line (V_NUM2);
END LOOP;
END;
4. Exception Handling
Exceptions: Errors that occur when you run a program are called exceptions
/*
===========================================================
| Pre-defined Exceptions
============================================================
*/
CREATE TABLE employee as SELECT * from Scott.emp;
SELECT * from employee;
--no exception handling
DECLARE
V_ename Employee.ename%type;
BEGIN
SELECT ename into V_ename
From employee
WHERE empno=1234;
Dbms_output.put_line (' Employee Name: ' | | V_ename);
END;
--Exception handling
DECLARE
V_ename employee.ename%type;
BEGIN
SELECT ename to V_ename
from employee
WHERE empno=1234;
Dbms_output.put_line (' Employee Name: ' | | V_ename);
EXCEPTION
When No_data_found and then
Dbms_output.put_line (' Incorrect employee number ');
when Too_many_rows and then
Dbms_output.put_line (' query can only return a single line ');
When OTHERS then
Dbms_output.put_line (' Error number: ' | | sqlcode| | ' Error description: ' | | SQLERRM);
END;
/*
===========================================================
| Benefit Benefits (Comm column) for employees with a query number of 7788.
============================================================
*/
DECLARE
V_comm Employee.comm%type;
E_comm_is_null EXCEPTION; --Defining the exception type variable
BEGIN
SELECT Comm to V_comm from employee WHERE empno=7788;
IF V_comm is NULL then
RAISE E_comm_is_null;
END IF;
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' Employee does not exist! The error is: ' | | sqlcode| | SQLERRM);
When E_comm_is_null Then
Dbms_output.put_line (' The employee is not subsidized ');
When others then
Dbms_output.put_line (' other anomalies ');
END;
5. Cursors:
Tool for handling multi-line records retrieved from the database using the SELECT statement
Classification:
-
Display cursors: When multiple records are returned, use the display cursor to read line by row
Implicit cursors: PL/SQL automatically creates an implicit cursor for a DML statement that contains a return record
Property:
-
- %found: Used to verify the success of a cursor, typically used before a FETCH statement, and returns True when a cursor queries a record by condition
- %isopen: Determines whether the cursor is open, the view opens a cursor that has been opened or has been closed, an error will occur
- %notfound: In contrast to the role of%found, returns True when a record cannot be queried according to the criteria
- %rowcount: Returns the number of rows of the retrieved record data when the cursor reads data
--Show cursor use steps
Declare
V_ename Employee.ename%type;
V_sal Employee.sal%type;
--Declaring cursors
Cursor Cursor_emp is
Select Ename,sal from Employee
for update of Sal;
Begin
--Open cursor
Open cursor_emp;
--Extracting cursors
/*loop
Fetch cursor_emp into v_ename,v_sal;
Exit when Cursor_emp%notfound;
Dbms_output.put_line (' Employee Name: ' | | v_ename| | ' Employee Salary: ' | | V_sal);
End loop;*/
Loop
Fetch cursor_emp into v_ename,v_sal;
Exit when Cursor_emp%notfound;
Update employee Set sal = sal+200
where current of cursor_emp;
End Loop;
Close cursor_emp;
End
6. Stored Procedures
Example code:
CREATE OR REPLACE PROCEDURE add_employee (
Eno Employee.empno%type,--Input parameters, employee number
Name Employee.ename%type,--input parameter, employee name
Salary Employee.sal%type,--input parameters, employee salary
Job Employee.job%type Default ' clerk ',--input parameters, employee jobs defaults ' clerk '
DNO Employee.deptno%type,--Input parameters, employee department number
On_flag out number,--execution status
Os_msg out VARCHAR2--hint message
)
Is
BEGIN
INSERT into employee (EMPNO,ENAME,SAL,JOB,DEPTNO) VALUES (Eno,name,salary,job, DNO);
On_flag:=1;
Os_msg:= ' Add success ';
EXCEPTION
When Dup_val_on_index Then
On_flag:=-1;
Os_msg:= ' The employee already exists. ‘;
When OTHERS Then
On_flag:=sqlcode;
OS_MSG:=SQLERRM;
END;
DECLARE
On_flag number;
Os_msg VARCHAR2 (100);
BEGIN
--Pass parameters by location
Add_employee (2111, ' MARY ', ' a ', ' MANAGER ', 10,on_flag,os_msg);
Dbms_output.put_line (on_flag| | OS_MSG);
END;
PL/SQL Programming