oracle练习题.docx

1.通过游标操纵数据库PPT39 当用户从一张表或多张表中查询多条纪录时，必须使用一个显式游标 要操纵数据库中数据，在定义游标的查询语句时，必须加上FOR UPDATE OF 从句,表示要对表加锁 表加锁后，在UPDATE或DELETE语句中，加WHERE CURRENT OF 子句，即可以对锁定的数据进行修改 CURSOR 游标名 IS SELECT 列1，列2 FORM 表 WHERE 条件 FOR UPDATE OF columnNOWAIT; 带WHERE CURRENT OF 从句的UPDATE语句和DELETE语句 DELETE FROM 表 WHERE CURRENT OF 游标名; UPDATE 表 SET 列1=值1，列2=值2. WHERE CURRENT OF 游标名； 查询emp表某部门的雇员情况，如果某雇员的工资小于800，则将其工资改成800 DECLARE v_deptno emp.deptno%type :=&p_deptno; v_empno emp.empno%type; v_job emp.job%type; v_sal emp.sal%type; CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE deptno = v_deptno FOR UPDATE OF sal;BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.sal 800 THEN UPDATE emp SET sal=800 WHERE CURRENT OF emp_cursor; END IF; dbms_output.put_line(emp_record.empno|emp_record.sal); END LOOP; COMMIT;END;DECLARE emp_num number :=0; s_sal emp.sal%type; e_sal emp.sal%type; e_empno emp.empno%type; CURSOR c1 IS SELECT empno,sal FROM emp ORDER BY sal FOR UPDATE OF sal;BEGIN OPEN c1; SELECT sum(sal) INTO s_sal FROM emp; WHILE s_sal 500000 LOOP FETCH c1 INTO e_empno,e_sal; EXIT WHEN c1%NOTFOUND; UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF c1; s_sal := s_sal + e_sal*0.1; emp_num := emp_num + 1; END LOOP; CLOSE c1; INSERT INTO msg VALUES(emp_num,s_sal); COMMIT;END;为职工增加10的工资，从最低工资开始长，增加后工资总额限制在50万以内2.存储过程PPT50CREATE OR REPLACE PROCEDURE 过程名 (参数名 IN | OUT | IN OUT 数据类型,. )IS | AS说明部分BEGIN语句序列EXCEPTION 出错处理END 过程名; l OR REPLACE是一个可选的关键字，表示替代原有的过程l IS或AS后面是一个完整的PL/SQL块的三部分（说明部分、执行部分、异常处理部分）l IN表示输入变量，OUT表示输出变量，IN OUT表示输入输出变量,缺省表示IN CREATE OR REPLACE PROCEDURE modetest( p_inpara INnumber, p_outpare OUT number, p_inoutpara IN OUT number)IS v_local number;BEGIN v_local:= p_inpara; /*IN类型参数不能出现在:=的左边*/ p_outpara:= 7; /* OUT类型参数不能出现在:=的右边 */ v_local:= p_inoutpara; p_inoutpara:= 7;END;CREATE PROCEDURE raise_salary(emp_id interger,v_increase integer)IS /* 缺省IN */ BEGIN UPDATE emp SET sal = sal + v_increase WHERE empno = emp_id; COMMIT;END;CREATE OR REPLACE PROCEDURE query_emp( v_emp_no IN emp.empno%TYPE, v_emp_name OUT emp.ename%TYPE, v_emp_sal OUT emp.sal%TYPE, v_emp_comm OUT m%TYPE)ISBEGIN SELECT ename,sal,comm INTO v_emp_name,v_emp_sal,v_emp_comm FROM emp WHERE empno = v_emp_no ;END query_emp; 如果IN OUT参数的值在过程中没有被更改，则它返回到调用环境时值不变 如果IN OUT参数是字符型，不能指定长度。长度由调用环境决定 3.触发器PPT80创建一个BEFORE型语句级触发器。限制一周内向emp表插入数据的时间，如果是周六、周日，或晚上6点到第二天早上8点之间插入，则中断操作，并提示用户不允许在此时间向emp表插入 CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp BEGIN IF (TO_CHAR(sysdate,DY) IN( SAT,SUN) OR (TO_CHAR(sysdate,HH24) NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR(-20500,you may only insert emp during normal hours.); END IF;END; 4. 自治事务PPT117create or replace trigger EMP_AUDITbefore update on emp for each rowdeclarepragma autonomous_transaction;l_cnt number;beginselect count(*) into l_cnt from dualwhere EXISTS ( select null from emp where empno = :new.empno start with mgr = ( select empno from emp where ename = USER )connect by prior empno = mgr );if (