本講課程目标
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
[WHERE condition];
範例:
SQL> set serveroutput on
SQL> DECLARE
2 v_first_name hr.employees.first_name%TYPE;
3 v_last_name hr.employees.last_name%TYPE;
4 BEGIN
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
2 increment by 1
3 start with 1
4 maxvalue 99999
5 nocache
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');
5 COMMIT;
6* END;
PL/SQL procedure successfully completed.
SQL> select * from hr.t14703a;
ID LOCATION SALARY
---------- ---------- ----------
3 fuzhou 6000
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 /
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)
2 v_a hr.t04310_a.a%TYPE := 50000;
3 BEGIN
4 delete from hr.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.
本文轉自斬月部落格51CTO部落格,原文連結http://blog.51cto.com/ylw6006/630144如需轉載請自行聯系原作者
ylw6006