oracle数据库block、tigger、function、package

--1、编写一个程序块从emp表中显示为'SMITH'的雇员的薪水和职位
declare 
v_job emp.job%type;
v_sal emp.sal%type;
begin
select job,sal into v_job,v_sal from emp where ename='SMITH';
dbms_output.put_line('SMITHD的职位是:'||v_job||'薪金:'||v_sal);
end;
--2、编写一个程序块，接受用户输入一个部门号，从dept表中显示该部门的名称与所在地
declare
v_dname dept.dname%type;
v_location dept.loc%type;
begin
select dname,loc into v_dname,v_location from dept
where deptno=&dnamber;
dbms_output.put_line('部门名称:'||v_dname||'部门地址：'||v_location);
end;
--3、编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪金（即：薪水+佣金）
declare
v_empno emp.empno%type;
v_sumsal emp.sal%type;
begin
select 12*(sal + nvl(comm,0)) into v_sumsal from emp where empno=&v_empno;
dbms_output.put_line('该员工的总的薪金：'||v_sumsal);
end;
select * from emp;
--4、编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水（薪水+佣金）
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=&雇员号;
dbms_output.put_line(v_emp.sal+nvl(v_emp.comm,0));
end;
--5、 5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：
             -- Designation    Raise
              -----------------------
             -- Clerk          500
              --Salesman       1000
              --Analyst        1500
              --Otherwise      2000
--编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。
declare 
    v_emp emp%rowtype;
 begin
      select * into v_emp from emp where ename='&name';
      if v_emp.job='CLERK' then
         update emp set sal=sal+500 where empno=v_emp.empno;
      elsif v_emp.job='SALESMAN' then
         update emp set sal=sal+1000 where empno=v_emp.empno; 
      elsif v_emp.job='ANALYST' then
         update emp set sal=sal+1500 where empno=v_emp.empno; 
      else  
         update emp set sal=sal+2000 where empno=v_emp.empno;  
      end if;
      commit;
end;
--6、编写一个程序块，将emp表中雇员名全部显示出来


--解法一
declare
cursor emp_cursor is select ename from emp;
v_ename emp.ename%type;
begin
open emp_cursor;
loop
  fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
end;
--解法二
declare
cursor emp_cursor is select ename from emp;--得到多个cursor
begin
for v_name in emp_cursor
loop
dbms_output.put_line(v_name.ename);--遍历多个cursor
end loop;
end;


--7、编写一个程序块，将emp表中前5人中名字显示出来
--解法一
declare
cursor emp_cursor is select ename from emp where rownum<6;
begin
for v_name in emp_cursor
loop
dbms_output.put_line(v_name.ename);
end loop;
end;
--解法二
declare
   cursor v_cursor is select * from emp;
   v_count number :=1;
   begin
    for v_emp in v_cursor
       loop
        dbms_output.put_line(v_emp.ename);
        v_count := v_count+1;
        exit when v_count>5;
       end loop;   
   end;
----------------------------------异常处理






--8、编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位和薪水，若输入的雇员名不存在
--显示'该雇员不存在'信息
declare 
   v_emp emp%rowtype;
   my_exception Exception;
 begin
   
   select * into v_emp from emp where ename='&name';
   raise my_exception;
   
   exception
         when no_data_found then
              dbms_output.put_line('该雇员不存在!');
         when others then
          dbms_output.put_line(v_emp.job||'---'||v_emp.sal);
 end;


--9、接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”。
declare
  v_dividend float;
  v_divisor float;
  v_result float;
  my_exception Exception;
  begin
        v_dividend:=&被除数;
        v_divisor:=&除数;
        v_result:=v_dividend/v_divisor;
        raise my_exception;
        exception
      when my_exception then
        dbms_output.put_line(v_result);
      when others then
        dbms_output.put_line('除数不能为0');
  end;

--二.声明和使用游标
-- 使用游标属性 
-- 使用游标For循环工作
-- 声明带参数的游标
--（使用FOR UPDATE OF和CURRENT OF子句工作）




--1、通过使用游标来实现dept表中的部门名称
declare
cursor dept_cursor is select dname from dept;
begin
for one_dept_cursor in dept_cursor
loop
dbms_output.put_line(one_dept_cursor.dname);
end loop;
end;


select dname from dept;


--2、使用for循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水
declare
cursor emp_cursor is select ename,job,sal from emp where deptno=&dno;
begin
for one_emp_cursor in emp_cursor
loop 
dbms_output.put_line(one_emp_cursor.ename||one_emp_cursor.job||one_emp_cursor.sal);
end loop;
end;


--3、使用带参数的游标（实现第2题）
declare
cursor emp_cursor(dno number) is select ename,job,sal from emp where deptno=dno;
v_deptno number(10);


begin
v_deptno:= &部门号;
for one_emp_cursor in emp_cursor(v_deptno)
loop
dbms_output.put_line(one_emp_cursor.ename||one_emp_cursor.job||one_emp_cursor.sal);
end loop;
end; 
--4、编写一个pl/sql程序块，从emp表中名字以'A'或'S'开始的所有库员按他们基本薪水的 10%100给他们加薪
declare
cursor emp_cursor is select * from emp where ename like 'A%' or ename like 'S%';
begin
   for one_cursor in emp_cursor
loop
update emp set sal=sal*1.1 where ename=one_cursor.ename;
-- dbms_output.put_line(one_cursor.ename);
end loop;
end;


select * from emp where ename like 'A%' or ename like 'S%';
select * from emp;




--5、emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果增加后的薪水大于5000，则取消加薪


declare
cursor emp_cursor is select * from emp;
begin
    for one_emp in emp_cursor
loop
if one_emp.sal*1.1<5000
then update emp set sal=sal*1.1 where empno=one_emp.empno;
end if;
end loop;
end;


select * from emp where sal*1.1<5000;


--三，创建PL/SQL记录和PL/SQL表
--   创建过程
--   创建函数


--3、创建一个过程，能像dept表中添加一个新纪录（in参数）
create or replace procedure insert_dept(dept_no in number,dept_name in varchar2,dept_loc in varchar2)
is
begin
insert into dept values(dept_no,dept_name,dept_loc);
end;
--调用存储过程：
declare
begin
insert_dept(50,'人事部','南京');
end;
select * from emp;
select * from dept;
--4、创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值(out参数)，然后调用过程
create or replace procedure
 find_emp3(emp_name in varchar2,emp_sal out number)
 is
     v_sal number(5);
 begin
      select sal into v_sal from emp where ename = emp_name;
      emp_sal:=v_sal;
 end;
 
declare
mysal number;
begin
  find_emp3('SMITH',mysal);
dbms_output.put_line(mysal);
end;
--5、编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比。


create or replace procedure
   update_sal(emp_no in number,parsent in float)
 is
   begin
     update emp set sal=sal+sal*parsent where empno=emp_no;
   end;


begin
  update_sal(7937,0.5);
 end;


select * from emp;


--7、创建一个函数，他以部门号作为参数传递并且使用函数显示那个部门名称与位置，然后调用此函数


create or replace function
  find_dept(dept_no number)
  return dept%rowtype
  is
   v_dept dept%rowtype;
   begin
    select * into v_dept from dept where deptno=dept_no;
    return v_dept;
  end;

--调用
declare
  v_dept dept%rowtype;
  begin
         v_dept:=find_dept(50);
         dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);
 end;




--四，创建程序包
   -- 创建程序件
   -- 创建触发器
 
 
--6、创建一个语句级别触发器，不允许用户在"Sundays"使用emp表
create or replace trigger control_emp
before update or delete or insert on emp


begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SUM') 
then raise_application_error(-20001,'不允许在星期天操作emp表');
end if;
end;

select to_char(sysdate,'day','nls_date_language=AMERICAN') from dual;--英文星期
select to_char(sysdate,'day') from dual;--中文星期
select to_char(sysdate,'hh24:mi') from dual;
 if to_char(sysdate,'day') in ('星期六','星期日') or 
 to_char(sysdate,'hh24:mi') not between '08:30' and '18:00'




--5.创建一个行级别触发器，停止用户删除雇员名为"SMITH"的记录。
   create or replace trigger delete_smith
     before delete on emp 
     for each row
     when (old.ename='SMITH')
     begin
     raise_application_error(-20001,'不能删除该条信息!');
     end;
 
 --4.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。
create or replace trigger delete_emp
      after delete on emp 
     for each row
      begin
       insert into ret_emp values(:old.empno,:old.ename,:old.job,
         :old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
       end;
 


--1.创建在dept表中插入和删除一个记录的数据包，它且有一个函数(返回插入或删除的部门名称)和两个过程。
然后调用包。
create or replace package pack_1
 is
  procedure find_emp(emp_no in number,emp_name out varchar2);
  procedure find_emp1(emp_name in varchar2,emp_no out number);
  function find_dname(dept_no number)
  return varchar2;
  end pack_1;
    
create or replace package body pack_1
is
 function find_dname(dept_no number)
 return varchar2
is 
 v_dname varchar2(20);
 begin 
  select dname into v_dname from dept where deptno=dept_no;
  retrun v_dname;
  end;
end pack_1;


--调用包：
declare
  v_dname varchar2(20);
  begin
   v_dname:=pack_1.find_dname(50);
   dbms_output.put_line(v_dname);
   end;