catalogue

Create a process to raise 100 yuan for the designated employee. And output the wages before and after the rise.

Query the name and salary of the specified employee, and use the out mode parameter value to pass it to the caller.

Call by binding variables in the command window, and the related steps

Create a stored procedure, query the name of the superior leader of the employee according to the provided employee number, and return

Define a function to calculate the average salary of a department in the emp table.

Write a function, pass in the employee number and return the name of the Department

Write a function to query the annual income of an employee, including bonus

Define a function, enter a department number, and query the total number of employees in the Department.

Define a function, use the record type as the return type, and return the corresponding department information according to the specified Department number

Create a process to raise 100 yuan for the designated employee. And output the wages before and after the rise.

create or replace procedure my(eno emp.empno%type)
is p_sal emp.sal%type;
begin
select sal into p_sal from emp where empno=eno;
update emp set sal=sal+100 where empno=eno;
dbms_output.put_line('The salary before the change is:'||p_sal||'The revised salary is:'||(p_sal+100));
end;

exec my(7369);

Query the name and salary of the specified employee, and use the out mode parameter value to pass it to the caller.

create or replace procedure my(eno emp.empno%type,p_name out emp.ename%type,p_sal out emp.sal%type)
is
begin
select ename,sal into p_name,p_sal from emp where empno=eno;
exception
when no_data_found then
dbms_output.put_line('No employees');
end;

declare
eno emp.empno%type;
p_name emp.ename%type;
p_sal emp.sal%type;
begin
my(7788,p_name,p_sal);
dbms_output.put_line(p_name||'    '||p_sal);
end;

Call by binding variables in the command window, and the related steps

var v_name varchar2(10);
var v_sal number;
exec my(7369,:v_name,:v_sal); 
print v_name v_sal;
select :v_name,:v_sal from dual;

Create a stored procedure, query the name of the superior leader of the employee according to the provided employee number, and return

 

create or replace procedure mya(eno emp.empno%type,pname  out emp.ename%type)
is 
mgrs emp.mgr%type;
begin
select mgr into mgrs from emp where empno=eno;
select ename into pname from emp where empno=mgrs;
exception
when no_data_found then
dbms_output.put_line('No employees');
end;

declare 
eno emp.empno%type;
 sname emp.ename%type;
begin
 mya(7369,sname);
dbms_output.put_line(sname);
end;

Define a function to calculate the average salary of a department in the emp table.

create or replace function get_avg(dno emp.deptno%type) return number
is 
  getavg emp.sal%type;
begin 
select avg(sal) into getavg from emp where deptno=dno;
return getavg;
end;
select get_avg(10) average value　from dual;

Write a function, pass in the employee number and return the name of the Department

create or replace function getname(eno emp.empno%type) return varchar
is
  d_name dept.dname%type;
begin
  select dname into d_name from dept where deptno=(select deptno from emp where empno=eno);
  return d_name;
end;
select getname(7369)　from dual;

Write a function to query the annual income of an employee, including bonus

create or replace function getsum(eno emp.empno%type) return number
is
  getsum emp.sal%type;
begin
  select sum(nvl(sal+comm,sal))*12 into getsum from emp where empno=eno;
  return getsum;
end;
select getsum(7369)　from dual;

Define a function, enter a department number, and query the total number of employees in the Department.

create or replace function sums(dno emp.deptno%type) return number
is
  counts emp.empno%type;
begin 
select count(*) into counts from emp where deptno=dno;
return counts;
end;
select sums(20) from dual;

Define a function, use the record type as the return type, and return the corresponding department information according to the specified Department number