Oracle foundation 5-PL/SQL

1, PLSQL programming language

1. Concept: Procedure Language/SQL,ORACLE's extension to sql;

2. Program structure:

 

declare
--Description part variable description, cursor description, exception description
begin
--Statement sequence DML Sentence
exception
--Exception handling statement
end;
/

3. Description of variables and constants:

 var1 char(15);

 married boolean:=true;

 psal number(7,2);

 myname emp.ename%type;

 emp_rec emp%rowtype;

declare
  --Define variable save name and salary
  --pename varchar2(20);
  --psal   number;
  pename emp.ename%type;
  psal   emp.sal%type;
begin
  --Get 7839's name and salary
  select ename,sal into pename,psal from emp where empno=7839;

end;
/
Record variable

declare
  --Define record variable: represents a row
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7839;
  
  dbms_output.put_line(emp_rec.ename||'The salary is'||emp_rec.sal);
end;
/
4.if statement

-- Determine the number entered by the user from the keyboard

--Accept keyboard input
--variable num: Is an address value on which the entered value is saved
accept num prompt 'Please enter a number';

declare 
  --Define variables to hold the number of inputs
  pnum number := #
begin
  if pnum = 0 then dbms_output.put_line('You entered 0');
     elsif pnum = 1 then dbms_output.put_line('You entered 1');
     elsif pnum = 2 then dbms_output.put_line('You entered 2');
     else dbms_output.put_line('Other numbers');
  end if;
end;
/

5. Circulation

-- Print 1~10
declare 
  -- Defining variables
  pnum number := 1;
begin
  loop
    --conditions for retirement
    exit when pnum > 10;
    --Printing
    dbms_output.put_line(pnum);
    --add one-tenth
    pnum := pnum + 1;
  end loop;
end;
/
6. cursor is used to store multiple rows of data returned by a query

-- Query and print employee's name and salary
/*
Cursor property:% isopen% rowcount (number of rows affected)
             %found    %notfound
*/
declare 
   --Define cursor (cursor)
   cursor cemp is select ename,sal from emp;
   pename emp.ename%type;
   psal   emp.sal%type;
begin
  --open
  open cemp;
  loop
       --Take current record
       fetch cemp into pename,psal;
       --exit when No records were retrieved;
       exit when cemp%notfound; 
       dbms_output.put_line(pename||'The salary is'||psal);
  end loop;
  --Close
  close cemp;
end;
/

-- Pay for employees, President 1000 manager 800 others 400
declare 
  --Define cursor
  cursor cemp is select empno,job from emp;
  pempno emp.empno%type;
  pjob   emp.job%type;
begin
  rollback;  
  --open cursor 
  open cemp;  
  loop
       --Take an employee
       fetch cemp into pempno,pjob;
       exit when cemp%notfound;
       
       --Judge position
       if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
          elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
          else update emp set sal=sal+400 where empno=pempno;
       end if;
  end loop;
  --Close cursor
  close cemp;
  --Submission  ----> why?: affair ACID
  commit;
  dbms_output.put_line('complete');
end;
/
-- Query the name of an employee in a department
declare 
   --Formal parameter
   cursor cemp(dno number) is select ename from emp where deptno=dno;
   pename emp.ename%type;
begin
   --Real reference
   open cemp(20);
   loop
        fetch cemp into pename;
        exit when cemp%notfound;
        
        dbms_output.put_line(pename);

   end loop;
   close cemp;
end;
/
7. Exception (exception)

System defined Exceptions: 5

No data found
 Too_many_rows (select … into statement matches multiple rows)
Zero? Divide
Value error (arithmetic or conversion error)
Timeout on resource

User defined Exceptions

-- Query employees in department 50
declare 
  cursor cemp  is select ename from emp where deptno=50;
  pename emp.ename%type;
  --Custom exception
  no_emp_found exception;
begin
  open cemp;
  --Take the first record
  fetch cemp into pename;
  if cemp%notfound then
    --Throw exception
    raise no_emp_found;
  end if;
  --Process: pmon process(proccesss monitor)
  close cemp;

exception
  when no_emp_found then dbms_output.put_line('Employee not found');
  when others then dbms_output.put_line('Other exceptions');
end;
/

8. Three examples:

 1.

 

/*
1,SQL Sentence
select to_char(hiredate,'yyyy') from emp;
---> Set > cursor > loop > Exit: not found

2,Variable: (*) initial value (*) how to get the final value
 Number of employees per year
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
declare 
   --Define cursor
   cursor cemp is select to_char(hiredate,'yyyy') from emp;
   phiredate varchar2(4);
   
   --Number of employees per year
  count80 number := 0;
  count81 number := 0;
  count82 number := 0;
  count87 number := 0;
begin
  --open cursor 
  open cemp;
  loop
    --Take an employee's year of entry
    fetch cemp into phiredate;
    exit when cemp%notfound;
    
    --Determine the year
    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
  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;
/
2. Raise wages for employees. From the minimum wage, each person is 10% longer, but the total wage cannot exceed 5% of the total wage Please calculate the number and length of the long wage
The total wage after the wage, and output the number of long wage and the total wage.

/*
1,SQL Sentence
selet empno,sal from emp order by sal;
---> Cursor > cycle > Exit: 1. Total amount > 5W 2. Not found

2,Variable: (*) initial value (*) how to get the final value
 The number of wage increase: countemp number: = 0;
Total wage after increase: salTotal number;
(1)select sum(sal) into salTotal from emp;
(2)After increase = before increase + sal *0.1
*/
declare
    cursor cemp is select empno,sal from emp order by sal;
    pempno emp.empno%type;
    psal   emp.sal%type;
    
    --Number of wage hikers: 
    countEmp number := 0;
    --Total wages after increase:
    salTotal number;
begin
    --Get the initial value of payroll
    select sum(sal) into salTotal from emp;
    
    open cemp;
    loop
         -- 1. Total >5w
         exit when salTotal > 50000;
         --Take an employee
         fetch cemp into pempno,psal;
         --2. notfound
         exit when cemp%notfound;
         
         --raise
         update emp set sal=sal*1.1 where empno=pempno;
         --Number+1
         countEmp := countEmp +1;
         --After rising=Before rising + sal *0.1
         salTotal := salTotal + psal * 0.1;

    end loop;
    close cemp;
    
    commit;
    dbms_output.put_line('Number of people:'||countEmp||'    Total:'||salTotal);
end;
/
3. Write a program with PL/SQL language to realize segmentation by department (6000 Above, (6000000), below 3000 yuan)
Count the number of employees in each wage section and the total wage of each department (bonus is not included in the total wage)

/*
1,SQL Sentence
 Department: select deptno from dept; --- > cursor
 Salary of employees in the Department: select Sal from EMP where deptno =?? - > cursor with parameters

2,Variable: (*) initial value (*) how to get the final value
 Number of people per segment
count1 number; count2 number; count3 number;
Department payroll
salTotal number := 0;
(1)select sum(sal) into salTotal  from emp where deptno=??
(2)accumulation
*/
declare
  --department
  cursor cdept is select deptno from dept;
  pdeptno dept.deptno%type;
  
  --Salaries of employees in the Department
  cursor cemp(dno number) is select sal from emp where deptno=dno;
  psal emp.sal%type;
  
  --Number of people per segment
  count1 number; count2 number; count3 number;
  --Department payroll
  salTotal number := 0;
begin
  --department
  open cdept;
  loop
       --Take a department
       fetch cdept into pdeptno;
       exit when cdept%notfound;
       
       --initialization
       count1:=0; count2:=0; count3:=0;
       --Get the total salary of the Department
       select sum(sal) into salTotal  from emp where deptno=pdeptno;
       
       --Take the salary of the middle staff in the Department
       open cemp(pdeptno);
       loop
            --Take an employee's salary
            fetch cemp into psal;
            exit when cemp%notfound;
            
            --judge
            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 cemp;

       --Save results
       insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));

  end loop;
  close cdept;
  
  commit;
  dbms_output.put_line('complete');
  
end;
/



       

Tags: SQL Programming Oracle

Posted on Fri, 01 May 2020 11:09:12 -0700 by jeger003