oracle練習(xí)題.docx

1.通過游標(biāo)操縱數(shù)據(jù)庫PPT39 當(dāng)用戶從一張表或多張表中查詢多條紀(jì)錄時，必須使用一個顯式游標(biāo) 要操縱數(shù)據(jù)庫中數(shù)據(jù)，在定義游標(biāo)的查詢語句時，必須加上FOR UPDATE OF 從句,表示要對表加鎖 表加鎖后，在UPDATE或DELETE語句中，加WHERE CURRENT OF 子句，即可以對鎖定的數(shù)據(jù)進(jìn)行修改 CURSOR 游標(biāo)名 IS SELECT 列1，列2 FORM 表 WHERE 條件 FOR UPDATE OF columnNOWAIT; 帶WHERE CURRENT OF 從句的UPDATE語句和DELETE語句 DELETE FROM 表 WHERE CURRENT OF 游標(biāo)名; UPDATE 表 SET 列1=值1，列2=值2. WHERE CURRENT OF 游標(biāo)名； 查詢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萬以內(nèi)2.存儲過程PPT50CREATE OR REPLACE PROCEDURE 過程名 (參數(shù)名 IN | OUT | IN OUT 數(shù)據(jù)類型,. )IS | AS說明部分BEGIN語句序列EXCEPTION 出錯處理END 過程名; l OR REPLACE是一個可選的關(guān)鍵字，表示替代原有的過程l IS或AS后面是一個完整的PL/SQL塊的三部分（說明部分、執(zhí)行部分、異常處理部分）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類型參數(shù)不能出現(xiàn)在:=的左邊*/ p_outpara:= 7; /* OUT類型參數(shù)不能出現(xiàn)在:=的右邊 */ 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參數(shù)的值在過程中沒有被更改，則它返回到調(diào)用環(huán)境時值不變 如果IN OUT參數(shù)是字符型，不能指定長度。長度由調(diào)用環(huán)境決定 3.觸發(fā)器PPT80創(chuàng)建一個BEFORE型語句級觸發(fā)器。限制一周內(nèi)向emp表插入數(shù)據(jù)的時間，如果是周六、周日，或晚上6點(diǎn)到第二天早上8點(diǎn)之間插入，則中斷操作，并提示用戶不允許在此時間向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. 自治事務(wù)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 (