PL/SQL中使用DML、游标、动态SQL

最新推荐文章于 2022-11-02 11:02:57 发布

最新推荐文章于 2022-11-02 11:02:57 发布 · 166 阅读

文章标签：

#SQL

Oracle愚意专栏收录该内容

7 篇文章

订阅专栏

本文介绍PL/SQL的基础用法，包括异常处理、return使用、获取指定员工信息等常见操作，并详细讲解了游标的多种使用方式及字符串操作技巧。

先申明我不是牛X，所有有错的地方，希望广大读友能提醒俺一下！

1.两个常用异常处理

DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
v_empno:=&请输入工号
SELECT ename INTO v_ename
FROM emp e WHERE e.empno!=v_empno;
dbms_output.put_line(v_ename);

EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('没有数据！');
    WHEN too_many_rows THEN
      dbms_output.put_line('行太多！');
END;

2.return使用，隐式游标。自动，用于返回单行结果集

注意：经俺亲手测试，boolean不能打印输出！

DECLARE
v_new_sal emp.sal%TYPE;
BEGIN
UPDATE emp e SET sal=sal+1000
WHERE e.empno=7788
RETURN sal INTO v_new_sal;

IF(v_new_sal>=8888)THEN
    ROLLBACK;
    raise_application_error(-20009,'需要请示！');
END IF;
COMMIT;

IF(SQL%ISOPEN)THEN
    dbms_output.put_line('ok!');
ELSE
    dbms_output.put_line('not ok!');
END IF;
END;

3.获取指定员工信息（是通过先做判断数据是否存在，后在查询）

DECLARE
v_empno emp.empno%TYPE;
v_row emp%ROWTYPE;
v_count NUMBER(1);
BEGIN
v_empno:=&请输入工号;
SELECT COUNT(*) INTO v_count
FROM emp WHERE empno=v_empno;

IF(v_count=1)THEN           --先判断
    SELECT * INTO v_row FROM emp e
    WHERE e.empno=v_empno;
    dbms_output.put_line(v_row.ename||','||v_row.sal);
ELSE
    NULL;
END IF;
END;

4.游标for循环（重点）需要对查询中的每条记录逐一处理时

例子：输出员工不为7566的信息

BEGIN
FOR s IN (SELECT * FROM emp WHERE empno!=7566)
LOOP
dbms_output.put_line(s.empno||','||s.ename||','||s.sal);
END LOOP;
END;

5.显示游标，使用cursor游标对象

技巧：dofw [declare-open-fetch-while] （重点）

1）使用cursor游标对象步骤：

①定义游标 declare cursor游标对象名 is （SQL); •

②定义行变量行变量游标对象名%ROWTYPE;

③打开游标 open游标对象名；

④循环游标 fetch 游标对象名 into 行变量；

while（游标对象名%found）

loop...end loop;

⑤关闭游标 close;

2)示例：打印各部门信息

DECLARE
CURSOR my_cur IS (SELECT * FROM dept);
my_row my_cur%ROWTYPE;
BEGIN
OPEN my_cur;
FETCH my_cur INTO my_row; --先获取数据后游标下移
WHILE(my_cur%FOUND)
LOOP
dbms_output.put_line(my_row.deptno||','||my_row.dname);
FETCH my_cur INTO my_row;
END LOOP;
CLOSE my_cur;
END;

6.在PL/SQL里执行字符串操作

1）execute语句 execute immediate'DDL语句'/ 'select语句'into变量

示例：创建一张表t_53

DECLARE
v_sql varchar2(4000); --在PL/SQL操作列时，数值的长度<=4000byte
BEGIN
v_sql:='create table t_53 (t_id number(2))';
EXECUTE IMMEDIATE v_sql;
END;

2）执行多行查询（open..for）

①游标变量（游标变量也是变量）

②变量需要定义类型

③类型需要自定义

示例：查询工资大于3000的所有员工编号与名字

DECLARE
TYPE my_cur_type IS REF CURSOR;                --类型需要自定义，这里的类型是引用游标
my_cur_var my_cur_type;                                   --变量需要类型
my_row emp%ROWTYPE;
BEGIN
OPEN my_cur_var FOR 'select * from emp where sal>=3000';  --游标变量也是变量
FETCH my_cur_var INTO my_row;                             --后面与显示游标相同
WHILE(my_cur_var%FOUND)
LOOP
    dbms_output.put_line(my_row.empno||','||my_row.ename);
    FETCH my_cur_var INTO my_row;
END LOOP;
CLOSE my_cur_var;
END;