Basic Grammar

Hello World of plsql

set serveroutput on
--Print Hello World
declare
    --Explanation section(Variable, cursor or exception description)
begin
    --Program body ( DML Statement)
    dbms_output.put_line('Hello World');
end;

Use of basic variable types

-- Use basic variable types
declare
    --Define basic variable types
    --Basic data types
    pnumber number(7,2);
    --String variable
    pname varchar(20);
    --Date variable
    pdate date;
begin
    pnumber := 1;
    DBMS_OUTPUT.PUT_LINE(pnumber);
    pname := 'Tom';
    DBMS_OUTPUT.PUT_LINE(pname);
    pdate := sysdate;
    DBMS_OUTPUT.PUT_LINE(sysdate);
    --Calculate tomorrow's date
    DBMS_OUTPUT.PUT_LINE(pdate + 1);
end;

Referential and Recorded Variables

• Referential variable
  Referencing the type of a column in a table as the variable type I defined

-- Referential variable
set serveroutput on
declare
    --Define Reference Variables:Query Print 7839 Name and Salary
    --pename varchar2(20)
    --psal number;
    pename emp.ename%type;
    psal emp.sal%type;
begin
    --Get 7839 names and salaries
    select ename,sal into pename,psal from emp where empno = 7839;
    --Print name and salary
    dbms_output.put_line(pename||'Salary is'||psal);
end;

• Recorded variable
  An array that stores the data type of a row

set serveroutput on
 --Define Record Variables:Query Print 7839 Name and Salary
 set serveroutput on
 declare 
    --Define a record variable: note that it represents a row
    emp_rec emp%rowtype;
begin
    --Get information for line 7839
    select * into emp_rec from emp where empno = 7839;
    --Print name and salary
    dbms_output.put_line(emp_rec.ename||'Salary is'||emp_rec.sal);
end;

Use of If statements

/*
Determine the number the user enters from the keyboard
1.How to use if statement
2.Receive a keyboard input (string)
*/
set serveroutput on
--Receive a keyboard input
--num:Address value, meaning:The value entered is saved on this address
accept num prompt 'Please enter a number';
declare
    --Define variables to hold numbers entered from the keyboard
    pnum number := #
begin
    --implement if Conditional judgment by statement
 if pnum = 0 then dbms_output.put_line('The number you entered is 0');
    elsif pnum = 1 then dbms_output.put_line('The number you entered is 1');
    elsif pnum = 2 then dbms_output.put_line('The number you entered is 2');
    else dbms_output.put_line('The number you entered is something else');
 end if;
end;

Loop statement use

• while

--Use while Circular Printing Numbers 1-10
set serveroutput on
declare
    --Define Circular Variables
    pnum number := 1;
begin
    while pnum <= 10 loop
        --Circulatory body
        dbms_output.put_line(pnum);
        --Make this variable+1
        pnum := pnum + 1;
    end loop;
end;

• loop

--Use loop Circular Printing Numbers 1-10
set serveroutput on
declare
    --Define Circular Variables
    pnum number := 1;
begin
   loop
        --Exit condition: Loop variable greater than 10
        exit when pnum > 10;
        --Print the value of the variable
        dbms_output.put_line(pnum);
        --Circular variable+1
        pnum := pnum+1;
   end loop;
end;

cursor

• The cursor is a result set
• Example 1

--Examples of cursor use:Query Print Salaries for All Employees
set serveroutput on
/**
    %found %notfound
*/
declare
    --Define a cursor
    cursor cemp is select ename,sal from emp;
    --Define the corresponding variable for the cursor
    pename emp.ename%type;
    psal emp.sal%type;
begin
    --open cursor
    open cemp;
    
    loop
        --Take a record
        fetch cemp into pename,psal;
        exit when cemp%notfound;
        dbms_output.put_line(pename||'Salary is'||psal);
    end loop;
    
    --Close cursor
    close cemp;
end;   

• Example 2

--Examples of cursor use:Raise wages for employees,President 1000,Manager 800,Other 400
set serveroutput on
/**
    %found %notfound
*/
declare
    --Define which employees are paid a raise by a cursor
    cursor cemp is select empno,empjob from emp;
    --Define the corresponding variable for the cursor
    pempno emp.empno%type;
    pjob emp.empjob%type;
begin
    --open cursor
    open cemp;
    
    loop
        fetch cemp into pempno,pjob;
        exit when cemp%notfound;
        --Judging an employee's position
        if pjob = 'president' then update emp set sal=sal+1000 where empno = pempno;
            elsif pjob = 'manger' 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;
    --about oracle，The default transaction isolation level is read committed
    commit;
    dbms_output.put_line('Wage raise complete');
end;

• Example 3: Cursor definition with parameters

--Query the name of an employee in a department
set serveroutput on
declare
    --Define a cursor with parameters
    cursor cemp(dno number) is select ename from emp where deptno = dno;
    pename emp.ename%type;
begin
    --open cursor
    open cemp(20);
    loop
        --Take out each employee's name
        fetch cemp into pename;
        exit when cemp%notfound;
        dbms_output.put_line(pename);
    end loop;
end;

Cursor Properties

/**
Cursor Properties
    %found %notfound
    %isopen:Determine if the cursor is open
    %rowcount:Influencing the number of rows
*/
set serveroutput on
declare
    --Define Cursor
    cursor cemp is select empno,empjob from emp;
    pempno emp.empno%type;
    pjob emp.empjob%type;
begin
    open cemp;
    if cemp%isopen then
        dbms_output.put_line('The cursor is open');
    else
        dbms_output.put_line('The cursor is not open');
    end if;
    
    loop 
        --Take out a record
        fetch cemp into pempno,pjob;
        exit when cemp%notfound;
        --Print rowcount Value of
        dbms_output.put_line('rowcount:' || cemp%rowcount);
    end loop;
    
    close cemp;
end;

Cursor number limit

By default, oracle allows only 300 cursors to be opened in the same call.

• Query under Administrator User how many cursors are currently allowed to open

SQL>show parameter cursor

NAME	TYPE	VALUE
cursor_bind_capture_destination	string	memory+disk
cursor_invalidation	string	IMMEDIATE
cursor_sharing	string	EXACT
cursor_space_for_time	boolean	FALSE
open_cursors	integer	300
session_cached_cursors	integer	50

• Modify cursor limit

SQL>alert system set open_cursors = 400 scope = both;

scope value: both, memory (change current instance only, do not change configuration file), SPFILE (database needs to be restarted)

Exceptions (exceptions)

• Exceptions are functions provided by programming languages to enhance the robustness and fault tolerance of programs.

• System exceptions

--System exceptions:no_data_found
set serveroutput on
declare 
    pename emp.ename%type;
begin
    --Query the name of an employee who is 1234
    select ename into pename from emp where empno = 1234;
exception
    when no_data_found then dbms_output.put_line('The employee was not found');
    when others then dbms_output.put_line('Other exceptions');
end;

--System exceptions:too_many_rows
set serveroutput on
declare 
    pename emp.ename%type;
begin
    --Query the name of an employee who is 1234
    select ename into pename from emp where deptno = 20;
exception
    when too_many_rows then dbms_output.put_line('select into Match multiple lines');
    when others then dbms_output.put_line('Other exceptions');
end;

--System exceptions:zero_divide
set serveroutput on
declare 
    pnum number;
begin
    pnum := 1/0;
exception
    when zero_divide then dbms_output.put_line('0 Cannot be a divisor');
                           dbms_output.put_line('erro x/0');
    when others then dbms_output.put_line('Other exceptions');
end;

--System exceptions:value_error
set serveroutput on
declare 
    pnum number;
begin
    pnum := 'abc';
exception
    when value_error then dbms_output.put_line('Arithmetic or conversion exception');
    when others then dbms_output.put_line('Other exceptions');
end;

• Custom Exceptions

--System exceptions:value_error
set serveroutput on
declare 
    --Define Cursor,Name of employee representing Department 50
    cursor cemp is select ename from emp where deptno = 50;
    pename emp.ename%type;
    --Custom Exceptions
    no_emp_found exception;
begin
    --open cursor
    open cemp;
    fetch cemp into pename;
    if cemp%notfound then 
        --Throw an exception
        raise no_emp_found;
    end if;
    dbms_output.put_line('Exception thrown, not here');
    
    --oracle Autostart pmon(process monitor)
    --Close cursor
    close cempp;
exception
    when value_error then dbms_output.put_line('Arithmetic or conversion exception');
    when others then dbms_output.put_line('Other exceptions');
end;

case

/*
Needs Statistics Number of Employees in Year 2021-2022
SQL Sentence
select to_char(hiredate,'yyyy') from emp;
->Close->Loop->Exit Conditions:notfound
 Variable: 1. Initial Value 2. How to get it
 Number of employees employed per year:
count21 number := 0;
count22 number := 0;
*/
set serverout on;
declare
   cursor cemp is select to_char(hiredate,'yyyy') from emp;
   phiredate varchar2(4);
   count21 number := 0;
   count22 number := 0;
begin
    open cemp;
    loop
        fetch cemp into phiredate;
        exit when cemp%notfound;
        if phiredate = '2022' then count22 := count22+1;
            elsif phiredate = '2021' then count21 := count21+1;
        end if;
    end loop;
    dbms_output.put_line('2022 Annual enrollments are:'||count22);
    dbms_output.put_line('2021 Annual enrollments are:'||count21);
    close cemp;
end;

/*
Requirements: Increase salaries for employees. Everyone gets a 10% increase from the minimum wage, but the total wage for everyone can't exceed 5w.
Please calculate the number of people who have been raised and the total amount of wages after they have been raised, and output the number of people who have been raised and the total amount of wages.
*/
set serveroutput on
declare
cursor cemp is select empno,sal from emp order by sal asc;
pempno emp.empno%type;
psal emp.sal%type;
--Number of people who get a raise
countEmp number := 0;
--Total wage after wage increase
salTotal number;
begin
   --Get the initial value of the total wage
   select sum(sal) into salTotal from emp;
   open cemp;
   loop
        --Exit Circulation Conditions 1, Total Wages greater than 5 w
        exit when salTotal > 50000;
        --Get a raise for an employee
        fetch cemp into pempno,psal;
        --Exit Loop Condition 2 %notfound
        exit when cemp%notfound;
        --Total wage increase=Total wages before increase + sal*0.1
        salTotal := salTotal + psal*0.1;
        if salTotal >= 50000 then
            --hold salTotal reduction
            salTotal := salTotal - psal*0.1;
        elsif salTotal < 50000 then
            --Raise
            update emp set sal = sal*1.1 where empno = pempno;
            --Number of people + 1
            countEmp := countEmp + 1;
        end if;
   end loop;
   close cemp;
   commit;
   dbms_output.put_line('Number of wage increases:'||countEmp||' Total wage increase:'||salTotal);
end;

/*
*To achieve the statistics of the number of employees with each wage by Department (above 6000, (6000, 3000), and below 3000), and
*Total wages per department (excluding bonuses)
*Create table msg to store statistics
* create table msg(deptno number,count1 number,count2 number,count3 number,saltotal number)
*/
set serveroutput on
truncate table msg;
declare
    --Department cursor
    cursor cdept is select deptno from dept;
    pdeptno dept.deptno%type;
    --Salary of employees in the Department
    cursor cemp(dno number) is select sal from emp where deptno = dno;
    psal emp.sal%type;
    -- Number of employees per segment
    count1 number;
    count2 number;
    count3 number;
    saltotal number;
begin
    open cdept;
    loop
        --Remove a department
        fetch cdept into pdeptno;
        exit when cdept%notfound;
        --Initialization
        count1 := 0;
        count2 := 0;
        count3 := 0;
        --Get total Department wages
        select sum(sal) into saltotal from emp where deptno = pdeptno;
        --Take salary from department staff
        open cemp(pdeptno);
        loop
            --Take the salary of an employee
            fetch cemp into psal;
            exit when cemp%notfound;
            --Judge the salary range
            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 from current department
        insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
    end loop;
    close cdept;
    commit;
    dbms_output.put_line('Statistics complete');
end;

Write a program in plsql language. Same by Department (department name) (score less than 60, 60-80, 85 or more)
The number of students in each fraction of the College Physics course, the number of students in each department, and the average scores of students in each department.
[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-iuTnYMe5-1645290725099)(img/1.png)]

Create tables to store statistical results

create table msg1(
    coursename varchar2(20),
    dname varchar2(20),
    count1 number,
    count2 number,
    count3 number,
    avggrade number
);

Analysis:
1. What systems were obtained
select dno,dname from dep; -> Cursor -> Loop -> Exit Conditions: notfound
2. Achievement of students who have taken the course "University Physics" in their department
select grade from sc where cno=(select cno from where cname=??)
and sno in (select sno from student where dno=??)
Cursor with parameters - > Loop - > Exit condition: notfound
Variable: 1. Initial Value 2. How to get it
Number of people per fraction
count number;count2 number; count3 number;
Average scores of college physics students in each department
avggrade number;
1. Arithmetic operations
select avg(grade) into avggrade from sc where cno = (select cno from course where cname=??)
and sno in (select sno from student where dno=??);

set serveroutput on
declare 
    --Cursor of system
    cursor cdept is select dno,dname from dep;
    pdno dep.dno%type;
    pdname dep.dname%type;
    
    --Achievement Cursor
    cursor cgrade(coursename varchar2,depno number)
        is 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 fraction
    count1 number; count2 number; count3 number;
    --Each department is selected"University Physics" Average student performance
    avggrade number;
    
    --Course Name
    pcourseName varchar2(20):='University Physics';
    
begin
    --Open System Cursor
    open cdept;
    loop
        --Get information about a department
        fetch cdept into pdno,pdname;
        exit when cdept%notfound;
        --Initialization Work
        count1:=0;count2:=0;count3:=0;
        --Average Department Performance
        select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName) 
            and sno in (select sno from student where dno=pdno);
        --Withdrawal,Achievements of students taking college physics
        open cgrade(pcourseName,pdno);
        loop
            --Take out a student's grade
            fetch cgrade into pgrade;
            exit when cgrade%notfound;
            --Judge the range of results
            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 current structure
        insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade);
    end loop;
    --Close system cursor
    close cdept;
    commit;
    dbms_output.put_line('Statistics complete');
end;