PL/SQL on-machine jobs

Declare   i number;   J number;   Mark number;   cout number:=0; Begin for   i in 1..1000 Loop      mark: =0;     For j in 2..i/2 Loop       if (i mod j = 0) then         mark:=1;         Exit;       End If;      End Loop;            if (Mark = 0) then         cout:=cout+1;         Dbms_output.put (i| | ', ');        If mod (cout,5) =0 then         dbms_output.new_line ();        End If;      End If;    End Loop;    Dbms_output.new_line ();    Dbms_output.put_line (' Total Total ' | | cout| | ' A '); end;/

　　

Declarev_high_sal employees.salary%TYPE; V_high_job Jobs.job_title%TYPE; V_high_count Number; V_low_sal employees.salary%TYPE; V_low_job Jobs.job_title%TYPE; V_low_count Number; begin  Select *   intoV_high_job,v_high_sal,v_high_count from(SelectJob_title,avg(Salary),Count(*) fromEmployees,jobswhereemployees.job_id=jobs.job_idGroup  byJob_titleOrder  by avg(Salary)desc )  whereRowNum=1; Select *   intoV_low_job,v_low_sal,v_low_count from(SelectJob_title,avg(Salary),Count(*) fromEmployees,jobswhereemployees.job_id=jobs.job_idGroup  byJob_titleOrder  by avg(Salary))whereRowNum=1; Dbms_output.put_line ('the job with the highest average wage is'||V_high_job||','||'The average maximum wage is'||V_high_sal||','||'Total'||V_high_count||'person for the position'||';'); Dbms_output.put_line ('the job with the lowest average wage is'||V_low_job||','||'The average maximum wage is'||V_low_sal||','||'Total'||V_low_count||'person for the position'||';'); Dbms_output.put_line ('The difference between the average salary at two levels of the average wage is'||(V_high_sal-V_low_sal)||';');End;/

Declare   cursorCurtest is       SelectJob_title,min_salary,max_salary,Count(*)        fromJobs, Employeeswherejobs.job_id=employees.job_idGroup  byjob_title,min_salary,max_salary; Type Rec_type isrecord (Job_name jobs.job_title%type, min_sal jobs.min_salary%type, max_sal jobs.max_salary%type, cout Number       );    V_jobtitle Rec_type; Times Number(3,1); E_not_similar EXCEPTION;begin   Opencurtest; FetchCurtest intoV_jobtitle; Loopbegin    Exit  whenCurtest%NOTFOUND; Times:=(V_jobtitle.max_sal/v_jobtitle.min_sal); if(Times<=1.5) ThenDbms_output.put_line ('Job Title'||V_jobtitle.job_name||'The minimum wage is'||V_jobtitle.min_sal||','||'The maximum wage is'||V_jobtitle.max_sal||','||'the difference between the two is'||Times||'Times'||','||'the person in this position has'||V_jobtitle.cout||','||'The wage change interval is normal .'); Elseraise E_not_similar; End if; EXCEPTION whenE_not_similar ThenDbms_output.put_line ('Job Title'||V_jobtitle.job_name||'The wage change interval is too large'); End; FetchCurtest intoV_jobtitle; EndLoop; Closecurtest; End;/

 withDept_job as(SelectDname, CaseJob when 'Clerk'  ThenSalEndClerk, CaseJob when 'salesman'  ThenSalEndsalesman, CaseJob when 'President'  ThenSalEndPresident, CaseJob when 'MANAGER'  ThenSalEndMANAGER, CaseJob when 'ANALYST'  ThenSalEndANALYST fromEmpJoinDept onEmp.deptno=Dept.deptno)SelectDNAME,NVL (To_char (avg(clerk)),'No') Clerk,nvl (To_char (avg(salesman)),'No') Salesman,nvl (To_char (avg(President)),'No') president, NVL (To_char (avg(MANAGER)),'No') Manger,nvl (To_char (avg(ANALYST)),'No') ANALYST fromDept_jobGroup  bydname;

SelectDname,clerk,salesman,president,manager,analyst from(SelectDname,sal,job fromEmpJoinDept onEmp.deptno=dept.deptno) Dept_jobpivot (avg(SAL) forJobinch('Clerk'  asClerk,'salesman'  assalesman,'President'  asPresident,'MANAGER'  asMANAGER,'ANALYST'  asANALYST)) P

PL/SQL on-machine jobs