oracle函数练习

作业一．案例某数据库有两张表：emp表和dept表，两张表的结构如下：createtableemp1(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4),dept_idnumber(3))；createtabledept1(dept_idnumber(3),dept_namevarchar2(20));要求如下：1、按照上表结构建立相应的表，为每张表写入5组合法数据。insertintoemp1values(1,'zhangsan',3500,001)insertintoemp1values(2,'lisi',3800,002)insertintoemp1values(3,'wanger',4800,003)insertintoemp1values(4,'zhusi',2800,004)insertintoemp1values(5,'gesi',6800,005)insertintodept1values(001,'技术部')insertintodept1values(002,'研发部');insertintodept1values(003,'市场部');insertintodept1values(004,'人事部');insertintodept1values(005,'后勤部');2、操纵相关表，使得“技术部”的员工的薪水上涨20%。updateemp1setemp_salary=(emp_salary+emp_salary*0.2)wheredept_id=(selectdept_idfromdept1wheredept_name='技术部');3、建立日志，追踪薪水变动情况。createtableemp1_logasselect*fromemp1;deletefromemp1_log;createtriggerupd_emp1afterupdateonemp1foreachrowbegininsertintoemp1_logvalues(:old.emp_id,:old.emp_name,:old.emp_salary,:old.dept_id);end;案例的分析与实现要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察触发器的应用；要求1：首先根据前面表的结构可以创建两张表：——创建员工表createtableemp2(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4));——部门表createtabledept2(dept_idnumber(3),dept_namevarchar2(20),emp_idnumber(5));建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。1)给emp表添加记录的存储过程:createorreplaceprocedurep_emp1(emp_idemp1.emp_id%type,emp_nameemp1.emp_salary%type,emp_salaryemp1.emp_salary%type,dept_idemp1.dept_id%type)isbegininsertintoemp1(emp_id,emp_name,emp_salary,dept_id)values(emp_id,emp_name,emp_salary,dept_id);end;executep_emp1(1,'zhangsan',3500,001)executep_emp1(2,'lisi',3800,002)executep_emp1(3,'wanger',4800,003)executeP_emp1(4,'zhusi',2800,004)executep_emp1(5,'gesi',6800,005)2)给dept表添加记录的存储过程:createorreplaceprocedurep_dept1(dept_iddept1.dept_id%type,dept_namedept1.dept_name%type)isbegininsertintodept1(dept_id,dept_name)values(dept_id,dept_name);end;executep_dept1(001,'技术);executep_dept1(002,'研发部');executep_dept1(003,'市场部');executep_dept1(004,'人事部');executep_dept1(005,'后勤部');3)调用相应的存储过程实现记录添加:要求2：给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工选出来，然后对这些员工的薪水进行相应的改动。代码如下：（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）要求3：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。分析：创建一个表salary_change_record（empid,old_salary,new_salary,change_date）,old_salary:用来纪录员工原来的工资，new_salary：用来纪录更新后的工资，change_date：记录更新的系统时间。然后创建一个触发器，名称为change_record，功能：每次更新员工工资之后，将更新纪录保存到表salary_change_record中。createtablesalary_change_record(empidvarchar2(6)notnullold_salarynumber(10)new_salarynumber(10)change_datedate)createtriggerchange_recordafterupdateonempforeachrowbegininsertintosalary_change_record(empid,old_salary,new_salary,change_date)values(:ols.emp_id,:old.emp_salary,:new.emp_salary,sysdate);end;二.案例——创建员工表createtableemp(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4),jobvarchar2(20),dept_idnumber(3));——部门表createtabledept(dept_idnumber(3),dept_namevarchar2(20),locvarchar2(20));1、编写一个数据库触发器，当任何时候某个部门从dept中删除时，该触发器将从emp表中删除该部门的所有雇员。createtriggerdel_deptafterdeleteondeptforeachrowbegindeletefromempwheredeptnonotin(selectdeptnofromdept);end;2、编写两个函数和两个过程以操作emp表。执行的任务为：插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水；显示指定雇员所在部门名称。createorreplacefunctionin_emp(idemp.emp_id%type,nameemp.emp_name%type,salaryemp.emp_salary%type,ejobemp.job%type,didemp.dept_id%type)isbegininserintoempvalues(id,name,salary,ejob,did);end;createorreplacefunctiondel_emp(idemp.emp_id%type,nameemp.emp_name%type,salaryemp.emp_salary%type,ejobemp.job%type,didemp.dept_id%type)isbegindeletefromempwhereemp_id=(id);end;createorreplaceproceduresel_emp_sal(idemp.emp_id%type,nameemp.emp_name%type,salaryemp.emp_salary%type,ejobemp.job%type,didemp.dept_id%type)isbeginselectemp_salaryfromempwhereemp_id=(id);end;createorreplaceproceduresel_emp_dname(idemp.emp_id%type,nameemp.emp_name%type,salaryemp.emp_salary%type,ejobemp.job%type,didemp.dept_id%type)isbeginselectdept_namefromdeptwheredept_id=(selectdept_idfromempwhereemp_id=(id));end;3、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：DesignationRaiseClerk1500-2500Salesman2501-3500Analyst3501-4500Others4501andabove.如果薪水在此范围内，则显示消息SalaryisOK，否则，更新薪水为该范围内的最水值。createorreplacefunctionemp_sal(e_noemp.empno%type)returnvarchar2ase_jobemp.job%type;e_salemp.sal%type;beginselectjob,salintoe_job,e_salfromempwhereempno=e_no;ife_job='clerk'thenif(e_sal=1500ande_sal=2500)thenreturn'salaryisok';endif;elsife_job='salesman'thenif(e_sal=2501ande_sal=3500)thenreturn'salaryisok';endif;elsife_job='analyst'thenif(e_sal=3501ande_sal=4500)thenreturn'salaryisok';elsee_sal:=3501;endif;if(e_sal4501)thenreturn'salaryisok';endif;return'designationraise';endif;updateempsetsal=e_salwhereempno=e_no;end;