PL/SQL Programming

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