绑定变量输出删除的行数
在plsql的命令窗口中执行,默认打印出绑定变量的值
SQL> VARIABLE rows_deleted NUMBER
SQL> DECLARE
2 v_id NUMBER:= 11;
4 BEGIN
5 DELETE FROM building WHERE id = v_id;
6 :rows_deleted := SQL%ROWCOUNT;
7 END;
8 /
PL/SQL procedure successfully completed
rows_deleted
---------
0
正常变量无法通过打印方式输出
SQL> VARIABLE rows_deleted NUMBER
SQL> DECLARE2 v_id NUMBER:= 11;
4 BEGIN
5 DELETE FROM building WHERE id = v_id;
6 rows_deleted := SQL%ROWCOUNT;
7 END;
8 /
PL/SQL procedure successfully completed
只是提示完成过程执行,这个变量要输出的话就必须通过dbms_output过程进行输出才行了。
在sqlplus中执行
SQL> VARIABLE rows_deleted NUMBER
SQL> DECLARE
2 v_id NUMBER := 11;
3 BEGIN
4 DELETE FROM building WHERE id = v_id;
5 :rows_deleted := SQL%ROWCOUNT;
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> print rows_deleted
ROWS_DELETED
------------
0
SQL> DECLARE
2 v_id NUMBER := 11;
3 BEGIN
4 DELETE FROM building WHERE id = v_id;
5 :rows_deleted := SQL%ROWCOUNT;
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> print rows_deleted
ROWS_DELETED
------------
0
如下语句也是在plsql中的命令窗口执行
SQL> VARIABLE rows_deleted NUMBER
SQL> DECLARE
2 TYPE v_id_table_type IS TABLE OF building.id%TYPE;
3 v_id_table v_id_table_type;
4 BEGIN
5 SELECT ID BULK COLLECT INTO v_id_table FROM building WHERE rownum <= 10;
6 :rows_deleted := SQL%ROWCOUNT;
7 END;
8 /
PL/SQL procedure successfully completed
rows_deleted
---------
10
SQL> DECLARE
2 TYPE v_id_table_type IS TABLE OF building.id%TYPE;
3 v_id_table v_id_table_type;
4 BEGIN
5 SELECT ID BULK COLLECT INTO v_id_table FROM building WHERE rownum <= 10;
6 :rows_deleted := SQL%ROWCOUNT;
7 END;
8 /
PL/SQL procedure successfully completed
rows_deleted
---------
10