PL/SQL Block
Declare begin
-- SQL statement
-- SQL statement directly written (DML/TCL)
-- Indirectly write execute immediate <DDL/DCL command string>
-- Select statement
<1> the into clause must be included.
Select empno into eno from emp
Where e-mapreduce = 7369;
<2> only one row can be found **********
<3> the number of fields must be the same as the number of variables.
Exception
-- Exception
When <abnormal name> then -- specific exception
<Processing Statement>
When others then -- all exceptions can be caught
<Processing Statement>
End; <example>
Write a program to insert a record into the DEPT table,
Input data from the keyboard, if
Data Type input error prompt
A prompt is required if the record cannot be inserted.
Only positive numbers are allowed. If a negative number is displayed
Declare
N number;
No dept. deptno % type;
Nm dept. dname % type;
Lc dept. loc % type;
Exp exception; -- Abnormal variable
Exp1 exception;
Num number: = 0; -- Counter
Pragma exception_init (exp,-1); -- pre-defined statement
-- (-1 error and exception variable Association)
Pragma exception_init (exp1,-1476 );
E1 exception; -- custom exception variable
Begin
-- Input value
No: = '& no ';
Num: = num + 1;
If no <0 then
Raise e1; -- custom exception thrown
End if; nm: = '& name ';
Num: = num + 1; lc: = '& address ';
Num: = num + 1; n: = 10/0; insert into dept values (no, nm, lc );
Num: = num + 1;
Commit; exception
-- Custom exception
When e1 then
Dbms_output.put_line ('Number cannot be negative number ');
-- The data type is incorrect.
When value_error then
If num = 0 then
Dbms_output.put_line ('wrong numbered data type ');
Elsif num = 1 then
Dbms_output.put_line ('name data type incorrect ');
Elsif num = 2 then
Dbms_output.put_line ('address data type incorrect ');
End if;
Rollback;
-- Primary key conflict
When exp then
-- Sqlcode global variable Exception error code
-- Sqlerrm text information of a global variable exception
-- Dbms_output.put_line ('exception ID: '| sqlcode );
-- Dbms_output.put_line ('Abnormal content: '| sqlerrm );
-- Dbms_output.put_line ('Number already exists ');
Rollback;
-- Non-predefined exception (associated error number)
When exp1 then
-- Dbms_output.put_line ('0 divisor ');
Raise_application_error (-20001, '0's divisor); -- caused a custom error.
-- Pre-retain-20001 to-29999 numbers
Rollback;
-- Other exceptions
When others then
Dbms_output.put_line ('exception ID: '| sqlcode );
Dbms_output.put_line ('Abnormal content: '| sqlerrm );
-- Dbms_output.put_line ('error prompted ');
Rollback;
End; -- insert into dept values (40, 'asdf ', 'asdf ');
<Simple practice>
-- Stores abnormal
Create table save_exp (
Bh number,
Wz varchar2 (1000)
); Declare
No dept. deptno % type;
Nm dept. dname % type;
Lc dept. loc % type;
Errno number;
Errtext varchar2 (1000 );
Begin
No: = '& no ';
Nm: = '& name ';
Lc: = '& address ';
Insert into dept values (no, nm, lc );
Commit;
Exception
When others then
Rollback;
Errno: = sqlcode;
Errtext: = sqlerrm;
Insert into save_exp values (errno, errtext );
Commit;
End; <cursor> an area in memory that stores the select result
1. Implicit cursor
Result set generated by a single SQL statement
Use keyword SQL to indicate implicit cursor
Integer number of rows of records affected by four attributes % rowcount
% Found affects record true
% Notfound does not affect record true
% Isopen whether to enable Boolean value is always false Multiple SQL statements implicit cursor SQL always refers to the result of the last SQL statement
It is mainly used in the update and delete statements. Explicit cursor
Use an explicit cursor on the select statement
Explicitly accessible result set
For Loop cursor
Parameter cursor
Solve the Problem of querying multi-row records
Fetch Cursor Display cursor
Need to be clearly defined
(1) FOR Loop cursor (a common type of cursor) -- <1> define a cursor
-- <2> define the cursor variable
-- <3> use the for loop to use this cursor -- the forward cursor can only go in one direction.
-- High efficiency declare
-- Type Definition
Cursor cc is select empno, ename, job, sal
From emp where job = 'manager ';
-- Define a cursor variable
Ccrec cc % rowtype; begin
-- For Loop
For ccrec in cc loop
Dbms_output.put_line (ccrec. empno | '-' | ccrec. ename | '-' | ccrec. job | '-' | ccrec. sal); end loop;
End;
(2) fetch cursor
-- Declare must be enabled and disabled explicitly.
-- Type Definition
Cursor cc is select empno, ename, job, sal
From emp where job = 'manager ';
-- Define a cursor variable
Ccrec cc % rowtype; begin
-- Open the cursor
Open cc;
-- Loop
Loop
-- Extract a row of data to ccrec
Fetch cc into ccrec;
-- Determine whether to extract the value. Exit if the value is not obtained.
-- The value cc % notfound is false.
-- The cc % notfound value cannot be obtained. The value of cc % notfound is true.
Exit when cc % notfound;
Dbms_output.put_line (ccrec. empno | '-' | ccrec. ename | '-' | ccrec. job | '-' | ccrec. sal); end loop;
-- Disable
Close cc;
End;
Four types of cursor attributes
% Notfound fetch indicates whether to mention data. If not, true indicates false.
% Found fetch indicates whether data is true or not. false is not mentioned.
% Rowcount number of records retrieved
% Isopen Boolean value whether the cursor opens declare
-- Type Definition
Cursor cc is select empno, ename, job, sal
From emp where job = 'manager ';
-- Define a cursor variable
Ccrec cc % rowtype;
Begin
-- Open the cursor
Open cc;
-- Loop
Loop
-- Extract a row of data to ccrec
Fetch cc into ccrec;
-- Determine whether to extract the value. Exit if the value is not obtained.
-- The value cc % notfound is false.
-- The cc % notfound value cannot be obtained. The value of cc % notfound is true.
Exit when (cc % notfound or cc % rowcount = 3); dbms_output.put_line (cc % rowcount | '-' | ccrec. empno | '-' | ccrec. ename | '-' | ccrec. job | '-' | ccrec. sal); end loop;
-- Disable
Close cc;
End; <example>
Declare
Cursor cc is select dept. dname,
Emp. ename, emp. sal from
Dept, emp where dept. deptno = emp. deptno;
Ccrec cc % rowtype;
Begin
For ccrec in cc loop
Dbms_output.put_line (ccrec. dname | '-' | ccrec. ename | '-' | ccrec. sal );
End loop;
End;
(3) parameter cursor
Name of the department manager in the order of Department numbers
Declare
-- Department
Cursor c1 is select deptno from dept;
-- Parameter cursor c2: when defining a parameter
-- Only the type can be specified, and the length cannot be specified.
-- The parameter can only appear on the Right of select statement =
Cursor c2 (no number, pjob varchar2) is select emp. * from emp
Where deptno = no and job = pjob ;/*
No = 10 pjob = 'manager'
Select * from emp where deptno = 10 and job = 'manager ';
*/
C1rec c1 % rowtype;
C2rec c2 % rowtype;
-- Specify the length when defining variables.
V_job varchar2 (20 );
Begin
-- Department
For c1rec in c1 loop
-- The parameter is used in the cursor
For c2rec in c2 (c1rec. deptno, 'manager') loop
Dbms_output.put_line (c1rec. deptno | '-' | c2rec. ename); end loop;
End loop;
End;
<Comprehensive example>
The purchased products include the customers (names) of the products purchased by the customer "Dennis );**************
Ideas:
Dennis (A, B) other customers (A, B, C) (A, C) (B, C) C
Declare
-- Items purchased by Dennis
Cursor cdennis is select productid
From purcase where customerid = (
Select customerid from
Customer where name = 'Dennis ');
-- Each customer except Dennis
Cursor ccust is select mermerid
From customer where name <> 'Dennis ';
-- Items purchased by each customer
Cursor cprod (id varchar2) is
Select productid from purcase
Where customerid = id; j number;
I number;
C1rec cdennis % rowtype;
C2rec ccust % rowtype;
C3rec cprod % rowtype;
Cname varchar2 (10 );
Begin
-- Customer cycle
For c2rec in ccust loop
I: = 0;
J: = 0;
For c1rec in cdennis loop
I: = I + 1;
-- What each customer buys
For c3rec in cprod (c2rec. customerid) loop if (c3rec. productid = c1rec. productid) then
J: = j + 1;
End if; end loop;
End loop;
If (I = j) then
Select name into cname from
Customer where customerid = c2rec. customerid;
DBMS_output.put_line (cname );
End if; end loop; end;
(4) Reference cursor/Dynamic Cursor
The select statement is dynamic.
Declare
-- Define a weak type (ref cursor)
Type cur is ref cursor;
-- Strong type (the returned result set is required)
Type cur1 is ref cursor return emp % rowtype;
-- Define a variable of the ref cursor type
Cura cur;
--
C1rec emp % rowtype;
C2rec dept % rowtype;
Begin
DBMS_output.put_line ('output employee ');
Open cura for select * from emp;
Loop
Fetch cura into c1rec;
Exit when cura % notfound;
DBMS_output.put_line (c1rec. ename );
End loop;
DBMS_output.put_line ('output departments ');
Open cura for select * from dept;
Loop
Fetch cura into c2rec;
Exit when cura % notfound;
DBMS_output.put_line (c2rec. dname); end loop;
Close cura;
End; >>>>> stored procedures and functions
PL/SQL block with no name (anonymous)
PL/SQL blocks with names (subprograms-stored procedures and functions)
Stored Procedure
Create or replace procedure p1
As
Begin
Exception
End; <simplest stored procedure>
Create or replace procedure p_jd
As
Hello varchar2 (20 );
Begin
Select 'Hello world' into Hello from dual;
Dbms_output.put_line (hello );
End;
How to execute a stored procedure
<1> execute p_jd; (SQL in SQL * PLUS>)
<2> begin
P_jd;
End;
Stored Procedures with Parameters
-- Input parameter in
-- All input parameters without writing in
-- Query employee names based on department numbers
Create or replace procedure p_getemp (no number)
As
Cursor c1 is select * from emp
Where deptno = no;
C1rec c1 % rowtype;
Begin
-- No: = 20; input parameters cannot be assigned values.
For c1rec in c1 loop
Dbms_output.put_line (c1rec. ename );
End loop;
End;
-- Output parameter out
-- Find the average salary of the department according to the department number and return the average salary value
-- In input (values cannot be assigned in procedure)
-- Out output (which can be assigned a value in procedure)
-- The length cannot be specified for defining parameters.
-- To define a variable, you must specify the length of create or replace procedure p_getavgsal (no number, avgsal out number)
-- No input parameter
-- Avgsal output parameters
As
Aa varchar2 (10); -- Variable
Begin
Select avg (sal) into avgsal
From emp where deptno = no;
End;
You can only call PL/SQL blocks.
Declare
Av number;
Begin
P_getavgsal (10, av );
Dbms_output.put_line ('average salary: '| round (av, 2 ));
End;
-- A parameter can be input or output at the same time.
-- Input and Output Parameters
Create or replace procedure
P_getavgsal (n in out number)
As begin
Select avg (sal) into n
From emp where deptno = n;
End;
Declare
Av number;
Begin
Av: = 10;
P_getavgsal (av );
Dbms_output.put_line ('average salary: '| round (av, 2 ));
End;
-- Stored procedure with multiple parameters
Create or replace procedure
P_getM (no number, pjob varchar2)
-- Parameter cursor c2: when defining a parameter
-- Only the type can be specified, and the length cannot be specified.
-- The parameter can only appear on the Right of select statement =
Cursor c2 (no1 number, pjob1 varchar2) is select * from emp
Where deptno = no1 and job = pjob1; c2rec c2 % rowtype;
-- Specify the length when defining variables.
V_job varchar2 (20 );
Begin -- use the parameter in the cursor
For c2rec in c2 (no, pjob) loop
Dbms_output.put_line (c2rec. deptno | '-' | c2rec. ename); end loop; end;
Call method: execute p_getm (10, 'manager'); -- by location
-- No = 10, pjob = 'manager'
Execute p_getm (pjob => 'manager', no => 10 );
-- Pass the value function by parameter name:
A return value is required.
Only one value can be returned.
-- Find the average salary of the department according to the department number and return the average salary value (using the function)
Create or replace function
F_getavgsal (no number)
Return number
As
Avgsal number (7,2 );
Begin
Select avg (sal) into avgsal
From emp where deptno = no;
-- Return Value
Return avgsal;
End;
-- With output parameters
-- Average salary and total salary of each department
-- Two values are returned for a function.
Create or replace function
F_getavgsal (no number, sumsal out number)
Return number
As
Avgsal number (7,2 );
Begin
-- Average salary
Select avg (sal) into avgsal
From emp where deptno = no;
-- Total salary
Select sum (sal) into sumsal
From emp where deptno = no;
-- Return Value
Return avgsal;
End; -- call Method
<1> PL/SQL block call
Declare
Aa number;
Begin
Aa: = f_getavgsal (10 );
Dbms_output.put_line (to_char (aa ));
End;
<2> SQL statement to call (DML)
Select f_getavgsal (10) from dual; select deptno, f_getavgsal (deptno) from dept; <3>
Create or replace function f1
Return number
As
Update emp set comm = 1000
Where job = 'cler ';
Return SQL % rowcount;
End;
-- The select statement cannot be called because it contains a modified statement.
