【PL/SQL】处理数据

一、在PL/SQL中查询数据

1.在PL/SQL中使用SELECT语句

·在PL/SQL代码中嵌入SELECT语句可以查询数据

·将查询出的数据保存在变量中，提供后续的输出或处理

·使用INTO子句                   

语法：

SELECT select_list

   INTO {variable_name[,vaviable_name]…| record_name}

   FROM table

 WHERE condition;

①使用标量变量接收数据

SQL> DECLARE

  2    v_ename emp.ename%type;

  3  BEGIN

  4    SELECT ename INTO v_ename FROM emp WHERE empno=7369;

  5    dbms_output.put_line('The name is: '||v_ename);

  6  END;

  7  /

The name is: SMITH

PL/SQL procedure successfully completed

 

②使用复合变量接收数据

SQL> DECLARE

  2    TYPE emp_record_type IS RECORD(

  3      ename emp.ename%TYPE,

  4      sal  emp.sal%TYPE);

  5      emp_record  emp_record_type;

  6  BEGIN

  7    SELECT ename,sal INTO emp_record FROM emp WHERE empno=7369;

  8    dbms_output.put_line('The name is:'||emp_record.ename);

  9    dbms_output.put_line('The sal is:'||emp_record.sal);

 10  END;

 11  /

The name is:SMITH

The sal is:4600

PL/SQL procedure successfully completed

 

注意事项：

①当没有返回任何数据时，会触发NO_DATA_FOUND异常

②当返回的数据位多条时，会触发TOO_MANY_ROWS异常

③WHERE子句后面的变量名不能和列名相同，否则会触发TOO_MANY_ROWS异常

 

二、在PL/SQL中使用DML语句

 

1. INSERT 插入数据

语法：

①INSERT INTO[(column [,column,…])] VALUES

(value[,value,…])

使用VALUES子句，实例：

SQL>

SQL> DECLARE

  2    v_deptno dept.deptno%TYPE;

  3    v_dname dept.dname%TYPE;

  4  BEGIN

  5    v_deptno :=92;

  6    v_dname :='DEV3';

  7    INSERT INTO dept (deptno,dname) VALUES (v_deptno,v_dname);

  8    END;

  9  /

PL/SQL procedure successfully completed

②INSERT INTO

[(column [,column,…])] SubQuery

使用子查询，实例：

SQL> DECLARE

  2    v_deptno dept.deptno%TYPE;

  3  BEGIN

  4    v_deptno :=30;

  5    INSERT INTO emp_bak

  6    SELECT * FROM emp WHERE deptno=30;

  7  END;

  8  /

PL/SQL procedure successfully completed

 

2.UPDATE 更新数据

语法：

UPDATE

  SET=[=]

[WHERE];

实例：

SQL> DECLARE

  2    v_deptno dept.deptno%TYPE :=20;

  3    v_loc dept.loc%TYPE :='BEIJING';

  4  BEGIN

  5    UPDATE dept SET loc=v_loc WHERE deptno=v_deptno;

  6  END;

  7  /

PL/SQL procedure successfully completed

 

3.删除数据

语法：

DELETE FROM [WHERE];

实例：

SQL> DECLARE

  2    v_deptno emp_bak.deptno%TYPE :=20;

  3  BEGIN

  4    DELETE FROM emp_bak WHERE deptno =v_deptno;

  5  END;

  6  /

PL/SQL procedure successfully completed

 

4.隐式游标

一次只能返回一行结果（不需要定义，默认自动建立）

隐式游标的属性：

SQL%ROWCOUNT  统计在游标中处理的记录数

SQL%FOUND      如果在游标中能找到符合条件的一条记录，结果为ture

SQL%NOTFOUND   如果在游标中能找不到符合条件的一条记录，结果为ture

SQL%ISOPEN      判断游标是否打开，在隐式游标中默认游标自动打开

 

①SQL%NOTFOUND,实例：

SQL> DECLARE

  2    v_id t1.id%TYPE;

  3  BEGIN

  4    v_id :=10;

  5    UPDATE t1 SET ID=20 WHERE ID=v_id;

  6    IF SQL%NOTFOUND THEN

  7      INSERT INTO t1(ID) VALUES(v_id);

  8      COMMIT;

  9      END IF;

 10  END;

 11  /

PL/SQL procedure successfully completed

 

SQL> select *from t1;

                                     ID NAME DSC

--------------------------------------- ---------- --------------------

                                     10           

②SQL%FOUND,实例：

SQL> DECLARE

  2    v_id t1.id%TYPE;

  3  BEGIN

  4    v_id :=10;

  5    DELETE FROM t1 WHERE ID=v_id;

  6    IF SQL%FOUND THEN

  7      dbms_output.put_line('T1 recorder is deleted!');

  8      COMMIT;

  9      END IF;

 10  END;

 11  /

T1 recorder is deleted!

PL/SQL procedure successfully completed

③SQL%ROWCOUNT,实例：

SQL> DECLARE

  2    v_id t1.id%TYPE;

  3  BEGIN

  4    v_id :=10;

  5    INSERT INTO t1(ID) VALUES(v_id);

  6    DELETE FROM t1 WHERE ID=v_id;

  7    IF SQL%FOUND THEN

  8      dbms_output.put_line('T1 recorder is deleted!');

  9      dbms_output.put_line('T1 recorder'||SQL%ROWCOUNT||'rows was deleted!');

 10      COMMIT;

 11      END IF;

 12   END;

 13  /

T1 recorder is deleted!

T1 recorder1rows was deleted!

PL/SQL procedure successfully completed

 

三、在PL/SQL中使用事务控制语句

1.使用COMMIT和ROLLBACK

SQL> DECLARE

  2    v_deptno dept.deptno%TYPE :=20;

  3  BEGIN

  4    DELETE FROM dept WHERE deptno=v_deptno;

  5    COMMIT;

  6  EXCEPTION

  7    WHEN OTHERS THEN

  8      ROLLBACK;

  9  END;

 10  /

PL/SQL procedure successfully completed

 

2.使用ROLLBACK和SAVEPOINT

SQL> BEGIN

  2    INSERT INTO dept_bak

  3    SELECT * FROM dept WHERE deptno=10;

  4    SAVEPOINT s1;

  5    INSERT INTO dept_bak

  6    SELECT *FROM dept WHERE deptno=20;

  7    SAVEPOINT s2;

  8    INSERT INTO dept_bak

  9    SELECT * FROM dept WHERE deptno=30;

 10    SAVEPOINT s3;

 11    ROLLBACK TO s2;

 12    COMMIT;

 13  END;

 14  /

PL/SQL procedure successfully completed