Oracle Learning Notes (11)

Exception:
The exception is a feature that is provided by the programming language to enhance the robustness and fault tolerance of the program.

Exceptions are divided into:
System exceptions
Custom exceptions

System exceptions are divided into:
No_data_found (no data found),
Too_many_rows (SELECT ... into statement matches multiple rows),
Zero_divide (divide by 0),
Value_error (arithmetic or conversion error, negative numbers can not be opened in the real range of squares, ABC can not be converted to 123),
Timeout_on_resource (timed out while waiting for resources (distributed database))

No_data_found
The group function uses this exception invalid, the default group function will have a default return value if no record is returned, such as Count will return 0,max, MIN, AVG, etc. will return empty, so that the exception will not be triggered

Case one:
--No data found
Set Serveroutput on
Declare
Pename Emp.ename%type;

Begin
--Query employee name is 1234
Select Ename to Pename from EMP where empno=1234;
exception
When No_data_found Then
Dbms_output. Put_Line (' Did not find the employee ');
When others then
Dbms_output. Put_Line (' other exceptions ');
End
/

Case TWO:
--Match multiple rows
Set Serveroutput on
Declare
Pename Emp.ename%type;

Begin
--Check the names of all employees in department 10th
Select Ename to Pename from EMP where deptno=10;
exception
When Too_many_rows Then
Dbms_output. Put_Line (' Select...into matches multiple lines ');
When others then
Dbms_output. Put_Line (' other exceptions ');
End
/
Case THREE:
--by 0 except
Set Serveroutput on
Declare
--Define a basic variable
Pnum number;

Begin
Pnum: =1/0;
exception
When Zero_divide Then
Dbms_output. Put_Line (' 1:0 cannot do divisor ');
Dbms_output. Put_Line (' 2:0 cannot do divisor ');
When others then
Dbms_output. Put_Line (' other exceptions ');
End
/
Exception Four:
-----Value_error arithmetic or conversion error
Set Serveroutput on
Declare
--Define a basic variable
Pnum number;

Begin
Pnum: = ' abc ';
exception
When Value_error Then
Dbms_output. Put_Line (' Arithmetic or conversion error ');
When others then
Dbms_output. Put_Line (' other exceptions ');
End
/

Custom exceptions
(1) Define variable, type is exception
(2) using raise to throw a custom exception

Case:
Set Serveroutput on
Declare

Cursor Cemp is a select ename from emp where deptno=50;
--Define a basic variable
Pename Emp.ename%type;

--Custom exceptions
No_emp_found exception;

Begin
Open cemp;
--Take out a record
Fetch cemp into pename;
If Cemp%notfound Then
--Throw exceptions
Raise No_emp_found;
End If;

--Close cursor
--oralce Auto-Start Process Monitor Pmon (processes monition)
Close Cemp;
exception
When No_emp_found Then
Dbms_output. Put_Line (' No employees found ');
When others then
Dbms_output. Put_Line (' other exceptions ');
End
/

Comprehensive case of PL/SQL programming
Programming methods
Waterfall model
Demand analysis
Design
Overview design
Detailed design
Coding
Test
Deployment
Operation

Thinking: The SQL statements and variables you need
Variable: 1, what is the initial value?
2. How to get the final value

Comprehensive Case 1
Statistics of the number of employees entering the year
Total 1980 1981 1982 1987
======== ======= ========== ======== =========
14 1 10) 1 2

Note: The employee's entry year is known

Method One (function mode):
Select COUNT (*) Total,
SUM (Decode (To_char (hiredate, ' YYYY '), ' 1980 ', 1,0)) "1980",
SUM (Decode (To_char (hiredate, ' YYYY '), ' 1981 ', 1,0)) "1981",
SUM (Decode (To_char (hiredate, ' YYYY '), ' 1982 ', 1,0)) "1982",
SUM (Decode (To_char (hiredate, ' YYYY '), ' 1987 ', 1,0)) "1987"
from EMP;

/*
SQL statements
Select To_char (hiredate, ' yyyy ') from EMP;
---> Cursor---> Loop---> Exit condition: NotFound

Variables: 1. Initial value 2. How to get
Number of employees entering the year:
Count80 number:=0;
Count81 number:=0;
Count82 number:=0;
Count87 number:=0;
*/

PL/SQL Development:

Set Serveroutput on
Declare
--Define Cursor
Cursor Cemp is a select To_char (hiredate, ' yyyy ') from EMP;
Phiredate VARCHAR2 (4);
-Number of employees entering the company each year

Count80 number:=0;
Count81 number:=0;
Count82 number:=0;
Count87 number:=0;

Begin
--Open cursor
Open cemp;
Loop
--Take out an employee's entry year
Fetch cemp into phiredate;
Exit when Cemp%notfound;
--Determine the year of entry
If Phiredate= ' 1980 ' then count80:=count80+1;
elsif phiredate= ' 1981 ' then count81:=count81+1;
elsif phiredate= ' 1982 ' then count82:=count82+1;
else count87:=count87+1;
End If;

End Loop;
--Close cursor
Close Cemp;
--Output results
Dbms_output.put_line (' total: ' | | ' ' | | (count80+count81+count82+count87));
Dbms_output.put_line (' 1980: ' | | ' ' | | COUNT80);
Dbms_output.put_line (' 1981: ' | | ' ' | | COUNT81);
Dbms_output.put_line (' 1982: ' | | ' ' | | COUNT82);
Dbms_output.put_line (' 1987: ' | | ' ' | | COUNT87);
End
/

Comprehensive Case 2
Raise wages for employees. No one has risen by 10% from the minimum wage, but the payroll cannot exceed $50,000, please calculate the number of wage increases and the total wage after the rise, and export the wage increase and payroll
SQL statements
Select Empno,sal from emp order by Sal;
-----loop-----Exit Condition: 1, Payroll >5w 2,%notfound

Variables: 1, initial value 2, how to get
Number of wage increases:
Countemp number: = 0;
Payroll after the wage increase:
Saltotal number;
1, select sum (SAL) into the saltotal from EMP;
2. The gross salary after the rise = total wages before the rise + Sal *0.1

PL/SQL Development:
Set Serveroutput on
Declare
--Define Cursor
Cursor Cemp is a select empno,sal from emp order by Sal;
Pempno Emp.empno%type;
Psal Emp.sal%type;
-The number of people who raise wages
Countemp number:=0;

--The total wage after the rise
Saltotal number;

Begin
--Get the initial value of the payroll
Select sum (SAL) into the saltotal from EMP;

--Open cursor
Open cemp;

Loop
--1, Payroll >5w
Exit when Saltotal > 50000;
--Take an employee to pay a raise
Fetch cemp into pempno,psal;
--2,%notfound
Exit when Cemp%notfound;
if (saltotal + psal*0.1) > 50000
Then Dbms_output.put_line (' excess cannot raise wages ');

--Pay rise
Else update emp set sal=sal*1.1 where Empno=pempno;
--Number +1
countemp:=countemp+1;

--2, the gross salary after rise = The total wage before the rise +sal*0.1
saltotal:=saltotal+psal*0.1;
End If;
End Loop;

--Close cursor
Close Cemp;
Commit
--Print results
Dbms_output.put_line (' Wage Increase: ' | | ' ' | | COUNTEMP);
Dbms_output.put_line (' Payroll After the rise: ' | | ' ' | | Saltotal);
End
/

Comprehensive Case 3
Write a procedure in PL/SQL language to achieve statistics on the number of employees in each wage segment (over 6000, (6000,3000) and under $3000), and the total payroll of each sector (excluding bonuses in payroll)
Create a table
CREATE TABLE MSG (
Deptno number,
Count1 number,
Count2 number,
Count3 number,
Saltotal number
);

Analysis:
SQL statements
1. What are the departments
Select Deptno from dept;
-----loop-----Exit Condition: 1, Payroll >5w 2,%notfound
2. Salary of employees in the department
Select Sal from emp where deptno=? --with one parameter of the cursor--loop--and exit Condition: NotFound (=true)

Variables: 1, initial value 2, how to get
Number of employees per segment:
Count1 number: = 0;
Count2 number: = 0;
Count3 number: = 0;

Payroll for each department:
Saltotal number;
1, select sum (SAL) into the saltotal from EMP where deptno=???;
2. Accumulation (Superior)

PL/SQL Development:
Set Serveroutput on
Declare
--Department's cursor
Cursor Cdept is a select Deptno from dept;
Pdeptno Dept.deptno%type;

--Cursors for employees in the department
Cursor Cemp (DNO number) is a select Sal from EMP where Deptno=dno;
Psal Emp.sal%type;

-Number of employees per segment:
COUNT1 number;
Count2 number;
COUNT3 number;

Payroll for each department:
Saltotal number;

Begin
--Open the department's cursor
Open cdept;

Loop
--Take an employee to pay a raise
Fetch cdept into Pdeptno;
Exit when Cdept%notfound;

--Initialization of the work
count1:=0;
count2:=0;
count3:=0;

--Get the payroll of the Department
--1, select sum (SAL) into Saltotal from EMP where deptno=???
Select sum (SAL) into Saltotal from EMP where Deptno=pdeptno;

--Take the salary of the employees in the department
Open Cemp (PDEPTNO);
Loop
--Take a salary from an employee
Fetch cemp into psal;
Exit when Cemp%notfound;

--judging the range of salary
If psal<3000 Then
count1:=count1+1;
elsif psal>=3000 and psal<6000 Then
count2:=count2+1;
Else
count3:=count3+1;
End If;
End Loop;
--Close the Department cursor
Close Cemp;

--Save the results of the current department
INSERT into MSG values (PDEPTNO,COUNT1,COUNT2,COUNT3,NVL (saltotal,0));
End Loop;
Commit
Dbms_output.put_line (' Statistics complete!! ');
End
/

View results:
SELECT * from MSG;

Comprehensive Case 4
Write a program in PL/SQL language. According to the Department (department name) Sub-statistics (score less than 60 points, 60-85 points, 85 or more) the "University Physics" courses in each of the number of students, and the average student grades.
(Multi-table queries and subqueries)

Script Student.sql
drop table SC;
drop table course;
drop table student;
drop table teacher;
drop table dep;

CREATE TABLE DEP (
DNO number (2),
Dname VARCHAR2 (30),
Director number (4),
Tel varchar (8)
);

CREATE TABLE Teacher (
TNO Number (4),
Tname VARCHAR2 (10),
Title VARCHAR2 (20),
HireDate Date,
Sal Number (7,2),
Bonus number (7,2),
Mgr Number (4),
Deptno Number (2)
);

CREATE TABLE Student (
Sno Number (6),
Sname VARCHAR2 (8),
Sex VARCHAR2 (2),
Birth date,
passwd VARCHAR2 (8),
DNO Number (2)
);

CREATE TABLE Course (
CNO VARCHAR2 (8),
CNAME varchar2 (20),
Credit Number (1),
CTime number (2),
Quota Number (3)
);

CREATE TABLE SC (
Sno Number (6),
CNO VARCHAR2 (8),
Grade Number (3)
);

ALTER TABLE DEP Add (constraint Pk_deptno primary KEY (DNO));
ALTER TABLE DEP Add (constraint Dno_number_check check (dno>=10 and dno<=50));
ALTER TABLE DEP Modify (tel default 62795032);
ALTER TABLE student Add (constraint Pk_sno primary key (SNO));
ALTER TABLE student Add (constraint sex_check check (sex= ' male ' or sex= ' female '));
ALTER TABLE student Modify (birth default sysdate);
ALTER TABLE course Add (Constraint Pk_cno primary KEY (CNO));
ALTER TABLE SC Add (Constraint Pk_key primary KEY (Cno,sno));
ALTER TABLE teacher Add (constraint Pk_tno primary KEY (TNO));
ALTER TABLE SC Add (foreign key (CNO) References course (CNO));
ALTER TABLE SC Add (foreign key (SNO) references student (SNO));
ALTER TABLE Student Add (foreign key (DNO) references dep (DNO));
ALTER TABLE Teacher Add (foreign key (DEPTNO) references dep (DNO));

INSERT into DEP values (10, ' Computer Academy ', 9469, ' 62785234 ');
INSERT into DEP values (20, ' Automation Academy ', 9581, ' 62775234 ');
INSERT into DEP values (30, ' Radio Academy ', 9791, ' 62778932 ');
INSERT into DEP values (40, ' School of Information management ', 9611, ' 62785520 ');
INSERT into DEP values (50, ' Micro-Nano Electronics Academy ', 2031, ' 62797686 ');

Insert into teacher values (9468, ' CHARLES ', ' Professor ', ' 17-DEC-17 ', 8000,1000,null,10);
Insert into teacher values (9469, ' SMITH ', ' Professor ', ' 17-DEC-17 ', 5000,1000,9468,10);
Insert into teacher values (9470, ' ALLEN ', ' ASSOCIATE Professor ', ' 20-feb-16 ', 4200,500,9469,10);
Insert into teacher values (9471, ' WARD ', ' Lecturer ', ' 22-feb-17 ', 3000,300,9469,10);
Insert into teacher values (9581, ' JONES ', ' Professor ', ' 2-apr-2016 ', 6500,1000,9468,20);
Insert into teacher values (9582, ' MARTIN ', ' ASSOCIATE Professor ', ' 28-sep-2018 ', 4000,800,9581,20);
Insert into teacher values (9583, ' BLAKE ', ' Lecturer ', ' 1-may-2019 ', 3000,300,9581,20);
Insert into teacher values (9791, ' Clake ', ' Professor ', ' 9-jun-2016 ', 5500,null,9468,30);
Insert into teacher values (9792, ' SCOTT ', ' ASSOCIATE Professor ', ' 09-DEC-17 ', 4500,null,9791,30);
Insert into teacher values (9793, ' BAGGY ', ' Lecturer ', ' 17-nov-2017 ', 3000,null,9791,30);
Insert into teacher values (9611, ' TURNER ', ' Professor ', ' 8-sep-2018 ', 6000,1000,9468,40);
Insert into teacher values (9612, ' ADAMS ', ' ASSOCIATE Professor ', ' 12-JAN-17 ', 4800,800,9611,40);
Insert into teacher values (9613, ' JAMES ', ' Lecturer ', ' 3-dec-19 ', 2800,200,9611,40);
Insert into teacher values (2031, ' FORD ', ' Professor ', ' 3-dec-18 ', 5500,null,9468,50);
Insert into teacher values (2032, ' MILLER ', ' ASSOCIATE Professor ', ' 23-jan-2018 ', 4300,null,2031,50);
Insert into teacher values (2033, ' migeal ', ' Lecturer ', ' 23-jan-2019 ', 2900,null,2031,50);
Insert into teacher values (2034, ' PEGGY ', ' Lecturer ', ' 23-jan-2020 ', 2800,null,2031,50);

Change format
Alter session set nls_date_language= ' AMERICAN ';
19-jun-01

Table for saving data
CREATE TABLE MSG1 (
Coursename varchar2 (20),
Dname varchar2 (20),
Count1 number,
Count2 number,
Count3 number,
Avggrade number
);

SQL statements
1. What departments are
Select Dno,dname from DEP;
-----loop----Exit Condition: NotFound

2, get the department, elective "College physics" course students ' achievements
Select Grade from SC where cno= (select CNO from Course where cname= ' University Physics ') and Sno in (select Sno from student where dno=? ？ );
--With parameters--loop--and exit condition: NotFound

Variables: 1, initial value 2, how to get
Number of people per fractional segment
COUNT1 number;
Count2 number;
COUNT3 number;

Each department took a "college physics" student's average score
Avggrade number;
1. Arithmetic arithmetic
2. Select AVG (grade) into Avggrade from SC where cno= (select CNO from Course where cname= ' University Physics ') and Sno in (select Sno Fro M student where dno=?? );

PL/SQL Development:
Set Serveroutput on
Declare
--the cursor of the system
Cursor Cdept is select Dno,dname from DEP;
Pdno Dep.dno%type;
Pdname Dep.dname%type;

--Score cursor
Cursor Cgrade (coursename varchar2,depno number) is a select grade from SC where
cno= (select CNO from Course where cname=coursename) and Sno in (select Sno from student where dno=depno);
Pgrade Sc.grade%type;

-Number of people per fractional segment:
Count1 number;count2 number;count3 number;

-Each department has an average of "college physics" Students:
Avggrade number;

--Course Name
Pcoursename varchar2 (20): = ' university physics ';

Begin
--Open the department's cursor
Open cdept;
Loop
--Take a message from a department
Fetch cdept into pdno,pdname;
Exit when Cdept%notfound;

--Initialization of the work
count1:=0;count2:=0;count3:=0;

--The average score of the department
Select AVG (grade) to Avggrade from SC
where cno= (select CNO from course where cname=pcoursename)
and Sno in (select Sno from student where Dno=pdno);

--taking students ' grades in college physics in the system
Open Cgrade (Pcoursename,pdno);
Loop
-Take a student's grade
Fetch cgrade into Pgrade;
Exit when Cgrade%notfound;

--judging the range of grades
If Pgrade<60 Then
count1:=count1+1;
elsif pgrade>=60 and Pgrade<85 Then
count2:=count2+1;
Else
count3:=count3+1;
End If;

End Loop;
Close Cgrade;
--Save the results of the current department
INSERT into MSG1 values (Pcoursename,pdname,count1,count2,count3,avggrade);
End Loop;
Close cdept;

Commit
Dbms_output.put_line (' Statistics complete!! ');
End
/

SELECT * from MSG1;

Oracle Learning Notes (11)