Oracle's Basic Skills for Preluding PL/SQL Programming

1 > Write package specifications and packages, which contain stored procedures and functions.

create or replace package emp_action_pkg is
       v_deptno number(3):=20;
       -----Define a process for adding employees
       procedure newdept(
                 p_deptno   dept.deptno%Type,-----Department number
                 p_dname    dept.dname%type, -----Department name
                 p_loc      dept.loc%type    -----position
         );        
       function getraisedsalary(p_empno emp.empno%type)
         return number;
end;

Summary: 1 >% type defines the same types as deptno,dname,loc columns in the dept table.

2 > The package specification is defined.

create or replace package  body emp_action_pkg is
      ----Disclosure, implementation of package specifications newdept process
        procedure newdept(
                 p_deptno   dept.deptno%Type,-----Department number
                 p_dname    dept.dname%type, -----Department name
                 p_loc      dept.loc%type    -----position
         )
         as
                 v_deptcount number;-----Save whether there is an employee number
         begin
                 select count(*) into v_deptcount from dept where deptno=p_deptno;---Query in dept Is there a department number in the table?
                 if v_deptcount>0 then
                    raise_application_error('-20002','The same employee record appears.');
                 end if;
                 insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_loc);---insert record
         end;
         
      ----Disclosure, implementation of package specifications getraisedsalary function
      function getraisedsalary(p_empno emp.empno%type)
        return number
      is
        v_job emp.job%type;---Job variables
        v_sal emp.sal%type;---Salary variable
        v_salaryratio number(10,2);---Salary adjustment ratio
      begin
        select job,sal into v_job,v_sal from emp where empno=p_empno;
        case v_job
          when 'Staff member' then
               v_salaryratio:=1.09;
          when 'Salesman' then
               v_salaryratio:=1.11;
          when 'manager' then
                v_salaryratio:=1.18; 
          else
                 v_salaryratio:=1; 
        end case;
        if v_salaryratio<>1 then        ----If there is a possibility of a pay increase
          return round(v_sal*v_salaryratio,2);----Return to salary after salary increase
        else
          return v_sal; 
        end if;                         ----Otherwise, no pay will be returned.
      exception
        when no_data_found then
          return 0;         -----If no employee record is found, return 0
      end;

  ---Privately, this function does not exist in the package specification and can only be referenced in the package body
      function checkdeptno(p_deptno dept.deptno%type)
        return number
      as
        v_counter   number(2);
      begin
        select count(*) into v_counter from dept where deptno=p_deptno;
        return v_counter;
      end;   
end;

Summary: 1 > case judgment of the variables after assignment:

case v_job 
when 'Staff member' then
         v_salaryratio:=1.09;
    when 'Salesman' then
         v_salaryratio:=1.11;
    when 'manager' then
         v_salaryratio:=1.18; 
   else
         v_salaryratio:=1; 
end case;

2 > Writing Package Implementation Specification

3 > checkdeptno does not exist in the package specification and can only be referenced within the package.

begin
  emp_action_pkg.v_deptno:=30;
  dbms_output.put_line(emp_action_pkg.getraisedsalary(7369));
end;
begin
  emp_action_pkg.v_deptno:=50;
  emp_action_pkg.newdept(45,'Adoption Department','Foshan');
end;
begin
  dbms_output.put_line(emp_action_pkg.v_deptno);
end;

Summary: 1 > Write three anonymous block call package components.

Use DBMS_JOB to create jobs (for analyzing data tables).

1>Application dbms_job Package creation job,The stored procedure for job execution is analyze_object
DECLARE
   v_jobno   NUMBER;
BEGIN
   DBMS_JOB.submit
        (v_jobno,                             --Job number
          --Stored Procedures for Job Execution
         'DBMS_DDL.analyze_object(''TABLE'',''SCOTT'',''EMP'',''COMPUTE'');',
         --with sysdate As the date of next execution         
         SYSDATE,
         --Interval Property execution interval, representing 24 hours.
         'SYSDATE+1'
        );  
   DBMS_OUTPUT.put_line('The job number obtained is:'||v_jobno);  --Output job number
   COMMIT;
END;

2 > Jobs created by queries

select job,next_date,next_sec,interval,what from user_jobs;

Conclusion:

A > interval parameter is a varchar2 string type, not a date or day or minute number, which can pass the string you want to pass.

'sysdate+1': Represents the current time of the next day

'trunc(sysdate)+1': means the afternoon of the next day, that is, 12 o'clock.

'trunc(sysdate)+17/24': runs at 5 p.m. every day

'null': Represents that the job runs immediately, runs out, and does not run repeatedly