自学PL/SQL 第三讲Interacting with the Oracle Server-ylw6006
本讲课程目标1.在PL/SQL块中执行select语句
2.在PL/SQL块中执行DML语句和控制事务
3.使用隐式游标定义PL/SQL块中DML语句的输出
在PL/SQL块中"END；"表示结束PL/SQL块，但并没有结束事务，一个块可以跨越多个事务，一个事务也可以跨越多个块；PL/SQL块中不支持DDL和DCL语句，例如建表，赋予用户权限等.
一：在PL/SQL块中执行select语句
select语句在PL/SQL块中的语法结构如下
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
;
范例：
[*]SQL> set serveroutput on
[*]SQL> DECLARE
[*]2 v_first_name hr.employees.first_name%TYPE;
[*]3 v_last_namehr.employees.last_name%TYPE;
[*]4BEGIN
[*]5 SELECT first_name,last_name INTO
[*]6 v_first_name,v_last_name FROM hr.employees
[*]7 WHERE employee_id=100;
[*]8 DBMS_OUTPUT.PUT_LINE(v_first_name ||' '||v_last_name);
[*]9* END;
[*]
[*]SQL> /
[*]Steven King
[*]PL/SQL procedure successfully completed
二.在PL/SQL块中执行DML语句和控制事务
可以在PL/SQL块中使用SQL的函数，自定义的序列号，字段的默认值插入数据
[*]SQL> create table t14703a (id number(5),constraint pk_t14703a primary key(id),
[*] 2 location varchar2(30) default 'fuzhou',salary number(8,2));
[*]Table created.
[*]
[*]SQL> create sequence user_seq
[*]2increment by 1
[*]3start with 1
[*]4maxvalue 99999
[*]5nocache
[*]6* nocycle
[*]Sequence created.
[*]
[*]SQL> select user_seq.nextval from dual;
[*]
[*] NEXTVAL
[*]----------
[*] 1
[*]
[*]SQL> BEGIN
[*]2 INSERT INTO hr.t14703a
[*]3 (id,location,salary) VALUES
[*]4 (user_seq.nextval,default,'6000');
[*]5COMMIT;
[*]6* END;
[*]SQL> /
[*]PL/SQL procedure successfully completed.
[*]
[*]SQL> select * from hr.t14703a;
[*]
[*] ID LOCATION SALARY
[*]---------- ---------- ----------
[*] 3 fuzhou 6000
[*]
[*]
[*]SQL> DECLARE
[*]2 v_salary_increase hr.t14703a.salary%TYPE := 500;
[*]3 BEGIN
[*]4 UPDATE hr.t14703a SET salarysalary= salary + v_salary_increase
[*]5 WHERE id=3;
[*]6* END;
[*]7/
[*]PL/SQL procedure successfully completed.
[*]
[*]SQL> select * from hr.t14703a;
[*]
[*] ID LOCATION SALARY
[*]---------- ---------- ----------
[*] 3 fuzhou 6500
三.使用隐式游标定义PL/SQL块中DML语句的输出
SQL游标，游标是私有的SQL工作区，有隐式和显式游标两个类型；oracle server使用隐式的游标来执行SQL语句
A cursor is a private SQL work area.
There are two types of cursors:
– Implicit cursors
– Explicit cursors
The Oracle server uses implicit cursors to parse and execute your SQL statements.
Explicit cursors are explicitly declared by the programmer.
4种游标的属性SQL%COUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN
[*]SQL> VAR rownums varchar2(30)
[*]SQL> set serveroutput on
[*]SQL> DECLARE
[*]2 v_ahr.t04310_a.a%TYPE := 50000;
[*]3 BEGIN
[*]4 deletefromhr.t04310_a where a < v_a;
[*]5 :rownums := (SQL%ROWCOUNT ||' ' || 'row deleted');
[*]6 DBMS_OUTPUT.PUT_LINE(:rownums);
[*]7*END;
[*]8/
[*]40000 row deleted
[*]PL/SQL procedure successfully completed.
页:
[1]