oracle练习.docx

查询显示正好为5个字符的员工姓名select ename from emp where length(ename)=5;显示不带有R的员工姓名select ename from emp where ename notlike%R%;查询高于平均工资的全部雇员信息Select * from emp Where sal(select avg(sal) from emp )查询工资比smith高的全部雇员信息Select * from emp where sal(select sal from emp where ename=SMITH)查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的员工号、员工名、工资和部门号。(两种方法实现)1. select empno,ename,sal,deptno from emp where deptno=10 union select empno,ename,sal,deptno from emp where sal200002. select empno,ename,sal,deptno from emp where deptno=10 or sal2000查询30号部门中工资大于2000的员工号、员工名、工资和部门号(两种方法实现)1. select empno,ename,sal,deptno from emp where deptno=30intersectselect empno,ename,sal,deptno from emp where sal2000;2. select empno,ename,sal,deptno from emp where sal2000 and deptno=30;编程输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增加200；否则增加300。declarev_deptno emp.deptno%type;v_increment number(4);v_empno emp.empno%type;beginv_empno:=&x;select deptno into v_deptno from emp where empno=v_empno;if v_deptno=10 then v_increment:=100;elsif v_deptno=20 then v_increment:=200;else v_increment:=300;end if;udate emp set sal=sal+v_increment where empno=v_empno;end;-caseDECLARE v_deptno emp.deptno%type; v_increment NUMBER(4); v_empno emp.empno%type;BEGIN v_empno:=&x; SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno; CASE v_deptno WHEN 10 THEN v_increment:=100; WHEN 20 THEN v_increment:=200; ELSE v_increment:=300; END CASE; UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno; END; 利用简单循环统计并输出各个部门的平均工资。DECLARECURSOR c_dept_stat ISSELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;v_dept c_dept_stat%ROWTYPE;BEGINOPEN c_dept_stat;LOOP FETCH c_dept_stat INTO v_dept; EXIT WHEN c_dept_stat%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept.deptno| | v_dept.avgsal);END LOOP;CLOSE c_dept_stat;END;创建存储过程和函数，以部门号为参数，返回该部门的人数和平均工资。存储过程CREATE OR REPLACE PROCEDURE return_deptinfo(p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE)ASBEGINSELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department dont exists!);END return_deptinfo; 函数CREATE OR REPLACE FUNCTION ret_deptinfo(p_deptno dept.deptno%TYPE,p_num OUT NUMBER,p_avg OUT NUMBER)RETURN dept.dname%TYPEASv_dname dept.dname%TYPE;BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname;END ret_deptinfo; 为emp表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。create or replace trigger empno_triggerbefore insert or update or delete on empfor each rowbeginif inserting thendbms_output.put_line(新员工员工号：|:new.empno|员工名：|:new.ename);elsif updating thendbms_output.put_line(修改前员工工资：|:old.sal|修改后员工工资：|:new.sal);elsedbms_output.put_line(被删员工号：|:old.empno|员工名：|:old.ename);end if;end;为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。create or replace trigger tri_empafter insert or delete or update on empdeclaree_count number;avg_sal emp.sal%type;beginif inserting thenselect count(*) into e_count from emp;dbms_output.put_line(员工总数为：|e_count);end if;if updating thenselect avg(sal) into