Oracle 重要知识点
这些是最简单的,理解这些再去看其他的高深一点的吧.......
游标
oracle里面的游标分为四种情况,分别是LOOP循环,FOR循环,删除,更新
1.LOOP循环
检索EMP表。使用LOOP循环语句遍历游标,输出指定部门编号(比如10)的员工信息(empno,ename,sal,deptno)
--定义游标-- DECLARE CURSOR cursor_emp (dept_no NUMBER := 10) IS SELECT * FROM emp WHERE deptno = dept_no; --%ROWTYPE是这个表的行的类型,与%TYPE不同-- row_emp emp%ROWTYPE; BEGIN OPEN cursor_emp(10); LOOP --下面这两行是固定模式,把游标查到的信息插入row_emp里面-- FETCH cursor_emp INTO row_emp; EXIT WHEN cursor_emp%NOTFOUND; --这个是输出语句,||用来分隔-- DBMS_OUTPUT.PUT_LINE('当前检索第'||cursor_emp%ROWCOUNT|| '行:员工号--'||row_emp.empno|| ',姓名--'|| row_emp.ename|| ',工资--'|| row_emp.sal|| ',部门编号--'|| row_emp.deptno); END LOOP; CLOSE cursor_emp; END; --上面我们写完了LOOP类型的游标,里面有输出语句是吧,我们要想执行那语句必须加行代码-- set serverout on
2.For循环
我们还是实现上面的功能,不过这次我们使用For循环。这里不同的地方在于,For循环没有开启游标!也没有关闭游标!也没有那两句固定的语句!
DECLARE CURSOR cursor_emp(dept_no NUMBER := 10) IS SELECT * FROM emp WHERE deptno = dept_no; BEGIN FOR row_emp IN cursor_emp LOOP DBMS_OUTPUT.PUT_LINE('员工号:'||row_emp.empno||',姓名:'||row_emp.ename || ',工资:'|| row_emp.sal|| ',部门编号:'|| row_emp.deptno); END LOOP; END;
3.更新
使用显示游标更新emp表数据,将工资低于4000元的员工工资增加100元 (更新和删除都多一句FOR UPDATE)
DECLARE CURSOR cursor_emp IS SELECT sal FROM emp WHERE sal < 4000 --发现没?多了一个FOR UPDATE!-- FOR UPDATE; --这里我们定义的是%type,这个呢就是某个表行类型的一个列的类型!看emp.sal!!-- v_sal emp.sal%TYPE; --下面还是LOOP循环里面的套路-- BEGIN OPEN cursor_emp; LOOP --LOOP里面的两大固定语句,套路.......-- FETCH cursor_emp INTO v_sal; EXIT WHEN cursor_emp%NOTFOUND; --update语句-- UPDATE emp SET sal=sal+100 WHERE CURRENT OF cursor_emp; END LOOP; CLOSE cursor_emp; END;
4.删除
删除部门号为40的所有员工
DECLARE CURSOR cursor_emp IS SELECT * from emp WHERE deptno =40 FOR UPDATE; row_emp emp%ROWTYPE; BEGIN OPEN cursor_emp; LOOP FETCH cursor_emp INTO row_emp ; EXIT WHEN cursor_emp%NOTFOUND; DELETE from emp WHERE CURRENT OF cursor_emp; END LOOP; CLOSE cursor_emp; END;
函数
定义RESULT()函数计算两个数值相除结果(商和余数),使用IN-OUT参数。运行,返回20/3的结果
--定义函数-- CREATE OR REPLACE FUNCTION result(num1 NUMBER, num2 IN OUT NUMBER) RETURN NUMBER AS v_result NUMBER(6); v_remainder NUMBER; BEGIN v_result := FLOOR(num1 / num2); v_remainder := MOD(num1, num2); num2 := v_remainder; RETURN v_result; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('错误信息:不能除0'); END;
--调用函数-- DECLARE result1 NUMBER; result2 NUMBER; BEGIN result2 := 3; result1 := result(20,result2); DBMS_OUTPUT.PUT_LINE('商是:'||result1); DBMS_OUTPUT.PUT_LINE('余数是:'||result2); END;
触发器
创建BEFORE触发器trig_OutPutScore,更新scores分数时显示分数变化(原来分数##,现在分数##)
CREATE OR REPLACE TRIGGER trig_OutPutScore BEFORE UPDATE ON scores FOR EACH ROW --每更新一行就会触发一次-- DECLARE oldvalue NUMBER; newvalue NUMBER; BEGIN oldvalue := :OLD.sscore; --更新之前的sscore的值-- newvalue := :NEW.sscore; --更新之后的sscore的值-- DBMS_OUTPUT.PUT_LINE('原来分数='||oldvalue|| ',现在分数='||newvalue); END;
调用触发器
update scores set sscore=sscore+5;
存储过程
创建存储过程
CREATE OR REPLACE PROCEDURE proc_GetScoresBySno (no IN NUMBER, result OUT NUMBER) AS BEGIN SELECT SUM(sscore) INTO result FROM scores WHERE sno = no; END;
调用存储过程
--variable是定义全局变量的意思-- VARIABLE AllScores NUMBER; EXEC proc_GetScoresBySno(201102,:AllScores); PRINT AllScores;