PL/SQL case

Project life cycle:

Waterfall model

Once you get a project, first: Analyze the SQL statements that need to be used;

Second: The analysis of the variables needed to define the initial value of how much, how to get the final value;

Case one:

Count the number of employees and the total number of recruits each year:

SQL statement: Select TO_CHAR (hiredate, ' yyyy ') from Emp;--to_char (hiredate, ' yyyy '): HireDate The original format is YYYY-MM-DD but we only need the year, is cast to char type, and the format is yyyy, CAST syntax: to_ destination type (destination field, format)

Need to traverse the entire table, you need to use the cursor---loop--Exit Condition:%notfound

Variable: phiredate varchar2 (20)

Counter:

Count80 number: = 0;

count82 number: = 0;

count87 number: = 0;

count89 number: = 0;

Set serveroutput on;

Declare

Cursor C is select To_char (hiredate, ' yyyy ') the From emp;--defined cursors

Phiredate varchar2 (20);

Count80 number: = 0;

count82 number: = 0;

count87 number: = 0;

count89 number: = 0;

Begin

Open c;--Cursor

Loop

Fetch c into phiredate;--Take out the employee's entry year

Exit when c%notfound;--turns off the condition of the loop

If Phiredate= ' 1980 ' then count80 number: = count80+ 1;

elsif phiredate= ' 1982 ' then count82 number: = count82+ 1;

elsif phiredate= ' 1987 ' then count87 number: = count87+ 1;

else count87 Number: = count87+ 1;

End If;

End Loop;

Close C;

Dbms_output.put_line (' tatal: ' | | COUNT80+COUNT82+COUNT87+COUNT89)

End

/

Case Two

SQL statement: select no,sal from EMP;

Need to traverse the entire table, you need to use the cursor---loop--Exit Condition:%notfound

Variable: pno emp.no%type;

Psal Emp.sal%type;

Wage rise: countemp number: = 0;

The total wage after rise: saltotal number;

Select sum (SAL) into the saltotal from EMP;

After the rise of the total wages = before the total wage + sal*0.1;

Set Serveroutput on

Declare

Cursor c is select Empno,sal from order by Sal;

Pno Emp.no%type;

Psal Emp.sal%type;

Countemp Number: = 0;--wage Increase

Saltatol number;--Total work after the rise

Begin

Select sum (SAL) into Saltatol the initial value from emp;--before the raise

Open C;

Loop

Exit when Saltotal >50000;

Fetch C into pno,psal;

Exit when C%notfound;

Update emp Set Sal: = sal*1.1 where no = pno;--pay rise

Countemp: = countemp + 1;--people +1

End Loop;

Close C;

Commit

Dbms_output.out_put (' number = ' | | countepm| | ', the total wage after the rise: ' | | Saltatol);

End

/

Case Three

Achieve departmental segment (6000 +, 6000 and 3000 ", 3000 below) statistics on the number of employees in each wage section, and the total payroll (excluding bonuses) of each sector

First you need to create a table that stores the results msg:create table msg (Deptno number;count1 number;count2 number; Count3 number;saltotal number);

Set Serveroutput on

Declare

Cursor Cdept is a select Deptno from dept;--Department

Pdeptno dept.deptno%type;--Department cursor corresponding to the variable

Cursor Cemp (DEPTNO) is a select Sal from emp where no = deptno;--Departmental Payroll cursors

Psal emp.sal%type;--Department salary cursor corresponding variable

Count1 Number;count2 Number;count3 number;--per-stage employee counters

Begin

Open cdept;

Loop

Fetch cdept into pdeptno;--take out a department

Exit when Cdepy%notfound;

Count1: =0;count2: =0;count3: =0;--Initialize counter

Select sum (SAL) into Saltatol from emp where deptno = pdeptno;--Compute Department Payroll

Open Cemp (Pdeptno)

Loop

Fetch cemp into psal;--take out every employee's salary

Exit when Cemp%noufound;

If Psal <3000 then count1: = count1 +1;--Judging wage Range

elsif psal>6000 then Count3: = Count3 +1;

else Count2: = Count2 +1;

End If;

End Loop;

Close Cemp;

INSERT into MSG value (PDEPTNO,COUNT1,COUNT2,COUNT3,SALTATOL);--Save the results of the current department

End Loop;

Close cdept;

End

/

Case Four

(a) The number of students in each fractional section of the University Physics course (scores less than 60, greater than 85, intermediate segment), and the average grade of students in each department;

、

Set Serveroutput on

Declare

Cersor cdept is select DNO, dname from DEP;

Pdno Dep.dno%type;

Cursor Cgrade (coursename varchar2,depno number) is a select grade from SC where cno = (select CNO from course where CNAME = Courname) and Sno in (select Sno from student where DNO = depno)--Score cursor

Pgrade Sc.grade% Type;

Count1 Number;count2 number;count3 number;--per score Segment Count counter

Avggrade number;--Average Score

Pcoursename varchar2: = ' university physics ';

Begin

Open cdept;

Loop

Fetch cdept into pdno,pdname;

Exit when Cdept%notfound;

Count1: =0;count2: =0;count3: =0;--Initialize counter

Select AVG (grade) into Avggrade from SC where cno = (select CNO from course where cname = courname) and Sno in (select SN O from student where dno = Depno)

Open Cgrade (Pcoursename,pdno);

Loop

Fetch cgrade into Pgrade;

Exit when Cgrade%notfound;

If Psal <60 then count1: = count1 +1;--Judging wage Range

elsif psal>85 then Count3: = Count3 +1;

else Count2: = Count2 +1;

End If;

End Loop;

End Loop;

Close Cgrade;

INSERT into MSG value (Pcoursename,count1,count2,count3,avggrade);

End Loop;

Close cdept;

Commit

Dbms_output.put_line (' statistical completion ');

end:/

PL/SQL case