1 PL SQL Concepts
PL/SQL is also a programming language called the procedural SQL language (procedural language/sql).
PL/SQL is an extension of the Oracle database to the statement. In the use of ordinary SQL statements to add the characteristics of the programming language,
So PL/SQL is to organize data operations and query statements in the process unit of PL/SQL code,
Complex functions or computational programming languages are implemented through logical judgments, loops, and other operations.
2 Development Tools
(1) Tools for SQL Plus:oracle Company
(2) PL/SQL Developer: A standalone integrated development environment (IDE)
3 PL/SQL Programming Classification
1 procedures (Stored procedures)
2 functions
3 triggers
4 Packs
5 Other
4 Notes
Single-line Comment:-What to comment on
Multiline Comment:/* What to annotate */
5 Identifier specification (for reference only)
Variable: use V as prefix
Constants: Prefix with C
Cursors: Using the cursor as a suffix
Exception: use e as prefix
6 PL/SQL block structure
Declaration (definition) Section: Defining variables, cursors, and so on using keyword declare
Executive section: Starting with the BEGIN keyword
Exception control section: Capturing errors generated during execution
End section: End With end keyword
Example 1:
sql> Set Serveroutput on--Open output option
Begin
Dbms_output.putline (' Hello PL/sql! ');
End
Example 2
sql> Declare
V_ename VARCHAR2 (5); --Define a string variable
Begin
Select Ename to V_ename from emp where empno = &no;
Dbms_output.put_line (' Name: ' | | v_ename);
End
Description:& represents the variable to be entered from the console
Example 3
sql> DECLARE V_ename VARCHAR2 (5); --Define a string variable
Begin
Select Ename to V_ename from emp where empno = &no;
Dbms_output.put_line (' Name: ' | | v_ename);
--The following is exception handling
exception
When No_data_found Then
Dbms_output.put_line (' An error has occurred! ‘);
End
Note: No_data_found represents an exception where data is not found if the user enters the parameter "&no"
7 Creating a stored procedure
sql> CREATE PROCEDURE sp_proname1 (spname varchar2,newsalary number) is
Begin
Update emp set sal =newsalary where ename = Spname
End
8 Executing stored procedures
Syntax: Exec stored procedure name (parameter);
sql>exec sp_proname1 (' OBAMA ', 10000);
9 Creating a function
Syntax: Create function name (parameter) return value;
10 calling function (SQL Plus)
Syntax: (1) var variable 1 type; Defines the result returned when a variable is used to hold the calling function
(2) Call Function name (parameter) into: variable 1; Use the call keyword to invoke a function and save the result returned by the function in the variable 1
(3) Print variable 1; Show results
11 Creation of packages
Package: A combination of functions and stored procedures
Syntax: Create package name is procedure stored procedure name (parameter);
Function name (parameter) return variable;
End
12 Creating the package body
Syntax: Create package body name is
...//stored procedure creation process
...//Function creation process
End
13 calling the package's functions and stored procedures
Call a stored procedure
Syntax: Call package name. Stored procedure name (parameter);
Calling functions
Syntax: Call Package name. function name (parameter);
Note: The call keyword can also be changed to exec
14 triggers
A trigger is a stored procedure that is implicitly executed. When you define a trigger, you must specify the event that is triggered and the action that is triggered.
To create a trigger by using CREATE trigger
15 Cursors
(temporary)
use of the IF statement
sql> Create or Replace procedure Name1 (spname varchar2) is
V_salary Emp.sal%type;
Begin
Select Sal into V_salary from emp where ename = spname;
If V_salary < Then
Update EMP Set Sal=sal * 1.2 where ename = Spname;
End If;
End
If...then...else Statement
sql> Create or Replace procedure Name1 (spname varchar2) is
V_salary Emp.sal%type;
Begin
Select Sal into V_salary from emp where ename = spname;
If V_salary < Then
Update EMP Set Sal=sal * 1.2 where ename = Spname;
Else
Update EMP Set Sal=sal * 1.05 where ename = Spname;
End If;
End
18 using the loop loop
sql> Create or Replace procedure Name1 (spname varchar2) is
V_num: = 1;
Begin
Loop
Insert into users values (V_num,spname);
Exit Whrn v_num=10;
V_num: = V_num + 1;
End Loop;
End
Note:Here ": =" denotes the meaning of the assignment
19 using the while loop
sql> Create or Replace procedure Name1 (spname varchar2) is
V_num: = 1;
Begin
While V_num <10 loop
Insert into users values (V_num,spname);
V_num: = V_num + 1;
End Loop;
End
