ORCLE 第十讲
回顾
1) PL/SQL分为哪几部分?
2) 改错
begin varchar2(20) v_name –该语句应该放到声明区,定义变量的时候,【变量名 类型】 v_name = "aaa"; -- 字符串用单引号, 赋值语句用(:=) dbms_output.put_line(v_name); end; |
3) 改错
BEGIN v_name VARCHAR2(20);-- 该语句应该放到声明区 SELECT * INTO v_name FROM EMP WHERE empno = 7394; -- 赋值的个数据一定要匹配 END – 缺少一个分号; |
主要任务
1) 游标
2) 存储过程
3) 函数
游标
概念
在PL/SQL块执行SELECT ,INSERT,UPDATE,DELETE语句时,ORACLE会在内存中为其分配上下文区(Context Area)。游标是指向该区的指针,或是命名一个工作区(Work Area),或是一种结构化数据类型。 它为应用程序提供了一种对具有多行数据的查询结果集中每行数据进行单独处理的方法,是设计交互式应用程序的常用编程接口。
|
游标的类型
1) 隐式游标
2) 显示游标
3) 引用游标(REF)
使用游标的步骤
1) 声明游标
2) 打开游标
3) 提取数据
4) 关闭游标
范例:循环所有的员工信息,如果工资小于1000的,输出[需要加工资],如果在1000和3000之间的,输出[还需要努力],如果大于3000的,输出[基本解决温饱问题]
DECLARE CURSOR cursor_emp IS SELECT * FROM EMP; v_emprow emp%rowtype; BEGIN OPEN cursor_emp; -- 打开游标,游标指针指向第一行数据 LOOP FETCH cursor_emp INTO v_emprow; --取出一行数据放到变量中,取完之后会将指针移动到下一行 IF v_emprow.SAL < 1000 THEN DBMS_OUTPUT.PUT_LINE('工资太少了'); END IF; EXIT WHEN cursor_emp%NOTFOUND; END LOOP; CLOSE cursor_emp; END; |
游标%NOTFOUND : 当游标指针没有下一行的时候
游标%FOUND: 当游标可以移动到下一行,则返回TRUE
游标%ROWCOUNT : 当前游标指针处理到了第几行。
游标%ISOPEN: 判断当前游标是否已经打开, 只有打开的情况下才能操作这个游标数据。
参数游标
DECLARE CURSOR cursor_emp(v_sal NUMBER) IS SELECT * FROM EMP WHERE SAL > v_sal; --参数中的数据类型不能指定精度 v_emprow emp%rowtype; BEGIN OPEN cursor_emp(3000); -- 打开游标, 需要传递参数值 LOOP IF cursor_emp%ISOPEN THEN FETCH cursor_emp INTO v_emprow; EXIT WHEN cursor_emp%NOTFOUND; IF v_emprow.SAL < 1000 THEN --DBMS_OUTPUT.PUT_LINE(cursor_emp%rowcount || ':工资太少了'); UPDATE EMP SET SAL = SAL + SAL * 10 WHERE empno = v_emprow.empno; DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 10); ELSIF v_emprow.SAL > 1000 AND v_emprow.SAL < 3000 THEN UPDATE EMP SET SAL = SAL + SAL * 11 WHERE empno = v_emprow.empno; DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 11); ELSE DBMS_OUTPUT.PUT_LINE(v_emprow.SAL + v_emprow.SAL * 15); UPDATE EMP SET SAL = SAL + SAL * 15 WHERE empno = v_emprow.empno; END IF;
END IF; END LOOP; CLOSE cursor_emp; END; |
隐式游标
Begin update emp set sal = 1200 where empno = 1234; if SQL%NOTFOUND then DBMS_OUTPUT.PUT_LINE('没有找到数据'); end if; End;
BEGIN UPDATE emp SET ename = 'Rob Mathew' ; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END; |
FOR循环游标数据
DECLARE CURSOR cursor_emp IS SELECT * FROM EMP; BEGIN FOR rowone IN cursor_emp LOOP DBMS_OUTPUT.PUT_LINE(rowone.ename); END LOOP; END; |
允许使用游标删除或更新活动集中的行 声明游标时必须使用 SELECT … FOR UPDATE语句 OF 列名
CURSOR <cursor_name> IS SELECT statement FOR UPDATE;
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name>
|
注意:更新完数据之后不要忘记提交数据(COMMIT)
引用游标
REF 游标和游标变量用于处理运行时动态执行的 SQL 查询 创建游标变量需要两个步骤: 声明 REF 游标类型 声明 REF 游标类型的变量 用于声明 REF 游标类型的语法为: TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];
return用于指定游标提取结果集的返回类型。有return表示是强类型ref游标,没有return表示是弱类型的游标。弱类型游标可以提取任何类型的结果集。
|
弱类型游标范例
DECLARE TYPE cursor_emp_type IS REF CURSOR; --弱类型的游标,游标指向的数据是不确定的,可以指向任何的结果集 v_emprow emp%rowtype; cursor_emp cursor_emp_type; BEGIN OPEN cursor_emp FOR SELECT * FROM EMP; LOOP FETCH cursor_emp INTO v_emprow; EXIT WHEN cursor_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emprow.ename); END LOOP; CLOSE cursor_emp; END;
|
强类型游标范例
DECLARE TYPE cursor_emp_type IS REF CURSOR RETURN emp%rowtype;--强类型的游标,要求结果集一定是访问EMP表 v_emprow emp%rowtype; cursor_emp cursor_emp_type; BEGIN OPEN cursor_emp FOR SELECT * FROM EMP; LOOP FETCH cursor_emp INTO v_emprow; EXIT WHEN cursor_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emprow.ename); END LOOP; CLOSE cursor_emp; END; |
存储过程
存储过程的优点:
1) 存储过程是被编译之后放到服务器端的,提高访问效率。
2) 减少网络流量
3) 安全性(是否具有调用该存储过程的权限)
4) 模块化编程
语法
[create [or replace]] procedure procedure_name[(parameter[, parameter]…)] [authid {definer | current_schema}]{is | as} [pragma autonomous_transaction;] [local_declarations] begin executable statements [exception exception handlers] end [procedure_name];
|
范例:使用存储过程,显示员工工资。如果大于或等于2500的员工显示员工编号和工资,如果没有则打印【没找到数据】。
CREATE OR REPLACE PROCEDURE PROC_FIND_EMP AS -- 声明区 CURSOR cursor_emp IS SELECT * FROM EMP; BEGIN FOR v_emprow IN cursor_emp LOOP IF(v_emprow.SAL >= 2500) THEN DBMS_OUTPUT.put_line(v_emprow.empno || ':' || v_emprow.sal); ELSE DBMS_OUTPUT.put_line('工资太低'); END IF; END LOOP; END;
|
参数的类型
IN :只能查看,不能改变这个值
OUT: 是作为输出的值,在调用的地方是可以访问的。
INOUT :既可以作为输入,也可以作为输出。
CREATE OR REPLACE PROCEDURE PROC_FIND_EMPINFO(v_empno IN NUMBER, yearsal OUT NUMBER) AS BEGIN SELECT SAL * 12 + NVL(COMM, 0) INTO yearsal FROM EMP WHERE EMPNO = v_empno; END;
--调用 DECLARE v_yearsal number(7,2); BEGIN PROC_FIND_EMPINFO(7499, v_yearsal); DBMS_OUTPUT.PUT_LINE('年薪是:' || v_yearsal); END; |