PLSQL Day1

--1.编写一个程序块，从emp表中显示名为‘SMITH’的雇员的薪水和职位：
-------------------------------------------------------------------------

begin
  select 列名1,列名2,..,列名n
into 变量1,变量2,..,变量n|record变量|rowtype变量
from emp where ename='SMITH';
end;
-------------------------------------------------------------------------

declare
     v_emp scott.emp%rowtype;
begin
  
  select * into v_emp from scott.emp where ename='SMITH';
  dbms_output.put_line(v_emp.ename||','||v_emp.sal||','||v_emp.job);
end;

-------------------------------------------------------------------------
declare 
  e_sal scott.emp.sal%type;
  e_job scott.emp.job%type;
begin
  select sal,job into e_sal,e_job from scott.emp where ename = 'SMITH';
  dbms_output.put_line(e_sal||','||e_job);
end;

-------------------------------------------------------------------------

declare
  e emp%rowtype;
begin
  e.empno:=&empno;
  e.ename:='&ename';
  e.job:='&job';
  e.mgr:=&mgr;
  e.hiredate:=to_date('&hiredate','YYYY-MM-DD');
  e.sal:=&sal;
  e.comm:=&comm;
  e.deptno:=&deptno;
  insert into emp values(
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);
end;
-------------------------------------------------------------------------

--1.
declare 
  e_sal scott.emp.sal%type;
  e_job scott.emp.job%type;
begin
  select sal,job into e_sal,e_job from scott.emp where ename = 'SMITH';
  dbms_output.put_line(e_job||' '||e_sal);
end;

--2.
declare
  e_emp scott.emp%rowtype;
begin
  select * into e_emp from scott.emp where ename = 'SMITH';
  dbms_output.put_line(e_emp.job||' '||e_emp.sal);
end;

--3.
declare
  type emp_type is record(
       sal scott.emp.sal%type,
       job scott.emp.job%type
  );
  e emp_type;
begin
  select sal,job into e.sal,e.job from scott.emp where ename = 'SMITH';
  dbms_output.put_line(e.job||' '||e.sal);
end;

--4.
create or replace type myobj is object(
       sal number ,
       job varchar2(10) 
);
declare
   e_sj myobj := myobj(0,'无'); -- 使用之前必须初始化
begin
  select sal,job 
  into e_sj.sal,e_sj.job 
  from scott.emp 
  where ename = 'SMITH';
  dbms_output.put_line(e_sj.job||' '||e_sj.sal);
end;

-------------------------------------------------------------------------
--2.编写一个程序块，输入一个部门号，
--从dept表中显示该部门的名称与所在位置：（四种声明方式）

--1.
declare
  dno number := &部门号;
  ddname scott.dept.dname%type;
  dloc scott.dept.loc%type;
begin
  select dname,loc
  into ddname,dloc 
  from scott.dept
  where deptno = dno;
  dbms_output.put_line(ddname||'--'||dloc);
end;

--2.

declare
  dno number := &部门号;
  d_dept scott.dept%rowtype;
begin
  select * 
  into d_dept
  from scott.dept
  where deptno = dno;
  dbms_output.put_line(d_dept.dname||'--'||d_dept.loc);
end;

--3.
declare
  type dtype is record(
       dno number := &部门号,
       ddname varchar2(20),
       dloc varchar2(20)
  );
  d_dept dtype;
begin
  select dname,loc 
  into d_dept.ddname,d_dept.dloc
  from dept 
  where deptno = d_dept.dno;
  dbms_output.put_line(d_dept.ddname||'--'||d_dept.dloc);
end;

--4.
grant create any type to scott;
create or replace type d_obj is object(
       ddname varchar2(20),
       dloc varchar2(20) -- 不允许用%type,%rowtype
);
declare
  dno number := &部门号;
  d_dept d_obj := d_obj('无','无');
begin
  select dname,loc
  into d_dept.ddname,d_dept.dloc
  from scott.dept
  where deptno = dno;
  dbms_output.put_line(d_dept.ddname||'--'||d_dept.dloc);
end;
----------------------------------------------------------------------
--3.编写一个程序块，接受一个雇员号，
--从emp表中显示该雇员的整体薪水（薪水加佣金）：（四种声明方式）

-- 1)
select * from emp;
declare
  e_eno scott.emp.empno%type := &雇员号;
  e_all scott.emp.sal%type;
begin
  select sal+nvl(comm,0) 
  into e_all
  from scott.emp
  where empno = e_eno;
  dbms_output.put_line('整体薪水:'||e_all);
end;
-- 2)
declare
  e_eno number := &雇员号;
  e_emp scott.emp%rowtype;
begin
  select *
  into e_emp
  from scott.emp
  where empno = e_eno;
  dbms_output.put_line('整体薪水:'||to_char(e_emp.sal+nvl(e_emp.comm,0)));
end;
-- 3)
declare
  type e_type is record(
    e_eno number := &雇员号,
    e_all scott.emp.sal%type
  );
  e e_type; 
begin
  select sal+nvl(comm,0) 
  into e.e_all
  from scott.emp
  where empno = e.e_eno;
  dbms_output.put_line('整体薪水:'||e.e_all);
end;

-- 4)
create or replace type e_obj is object(
    e_all number   
);

declare
  eno number := &雇员号;
  e e_obj := e_obj(0) ;
begin
  select sal+nvl(comm,0) 
  into e.e_all
  from scott.emp
  where empno = eno;
  dbms_output.put_line('整体薪水:'||e.e_all);
end;

-------------------------------------------------------------------------

--4.编写一个程序块，键盘输入学生编号，从表中显示该学生的姓名、性别与年龄：
declare
  no varchar2(10):='&学生编号';
  name varchar2(20);
  sex varchar2(10);
  age number;
begin
  select sname,ssex,sage
  into name,sex,age
  from scott.student
  where sno = no;
  dbms_output.put_line(name||':'||sex||','||age);
end;

--5.编写一个程序，键盘输入学号，查询出学生姓名和所学课程的总分：
declare
  no varchar2(10):='&学生编号';
  name varchar2(20);
  score number;
begin
  select s.sname,sum(score)
  into name,score
  from scott.student s left join scott.sc sc 
  on s.sno = sc.sno
  where s.sno = no
  group by s.sname;
  dbms_output.put_line(name||':'||nvl(score,0));
end;

--6.编写一个程序，键盘输入课程编号，查询出对应课程名称和最高分数：
declare
  no varchar2(10):='&课程编号';
  name varchar2(20);
  score number;
begin
  select c.cname,max(sc.score)
  into name,score
  from scott.course c left join 
  scott.sc
  on c.cno = sc.cno
  where c.cno = no
  group by c.cname;
  dbms_output.put_line(name||':'||nvl(score,0));
end;

--7.编写一个程序，键盘输入老师编号，
--查询出对应教师姓名和授课科目数量：

declare
  no varchar2(10):='&教师编号';
  name varchar2(20);
  cnt number;
begin
  select t.tname,count(c.cno)
  into name,cnt
  from scott.teacher t left join 
  scott.course c
  on c.tno = t.tno
  where t.tno = no
  group by t.tname;
  dbms_output.put_line(name||':'||nvl(cnt,0));
end;