Oracle PL/SQL/PL SQL/Begin End Block
Содержание
- 1 A Nested Block Example
- 2 An example of an anonymous block
- 3 Block-Based Development
- 4 Block Nesting
- 5 Block with name
- 6 Building Blocks of PL/SQL
- 7 Five level nested statement
- 8 Nested block
- 9 no executable code
- 10 Outer Block name and inner block name
- 11 Plain SQL and PL/SQL program
- 12 The executable section needs at least one line of code to be valid.
- 13 The PL/SQL Block
- 14 This script demonstrates the structure of a block
- 15 Three sections - declarative, executable, and exception.
A Nested Block Example
<source lang="sql">
SQL> SQL> CREATE TABLE items_tab (item_code varchar2(6) PRIMARY KEY,
2 item_descr varchar2(20) NOT NULL);
Table created. SQL> SQL> DECLARE
2 v_item_code VARCHAR2(6);
3 v_item_descr VARCHAR2(20);
4 v_num NUMBER(1);
5 BEGIN
6 v_item_code := "ITM101";
7 v_item_descr := "Spare parts";
8 BEGIN
9 SELECT 1
10 INTO v_num
11 FROM items_tab
12 WHERE item_code = v_item_code;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 v_num := 0;
16 WHEN OTHERS THEN
17 dbms_output.put_line("Error in SELECT: "||SQLERRM);
18 RETURN;
19 END;
20 IF (v_num = 0) THEN
21 INSERT INTO items_tab VALUES (v_item_code, v_item_descr);
22 END IF;
23 dbms_output.put_line("Successful Completion" ) ;
24 EXCEPTION WHEN OTHERS THEN
25 dbms_output.put_line(SQLERRM);
26 END;
27 /
Successful Completion PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from items_tab; ITEM_C ITEM_DESCR
--------------------
ITM101 Spare parts 1 row selected. SQL> SQL> drop table items_tab; Table dropped. SQL> SQL> --
</source>
An example of an anonymous block
<source lang="sql">
SQL> SQL> -- An example of an anonymous block. SQL> -- Count up by hundreds until we get an error. SQL> SQL> DECLARE
2
3 hundreds_counter NUMBER(1,-2);
4 BEGIN
5 hundreds_counter := 100;
6 LOOP
7 DBMS_OUTPUT.PUT_LINE(hundreds_counter);
8 hundreds_counter := hundreds_counter + 100;
9 END LOOP;
10 EXCEPTION
11 WHEN OTHERS THEN
12 DBMS_OUTPUT.PUT_LINE("That is as high as you can go.");
13 END;
14 /
100 200 300 400 500 600 700 800 900 That is as high as you can go. PL/SQL procedure successfully completed. SQL>
</source>
Block-Based Development
<source lang="sql">
SQL> SQL> set serveroutput on SQL> declare
2 myText varchar2(100);
3 begin
4 myText := "Hello, World!";
5 dbms_output.put_line(myText);
6 exception
7 when others then
8 dbms_output.put_line("We encountered an exception!");
9 raise;
10 end;
11 /
Hello, World! PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
Block Nesting
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> declare
2 myText varchar2(20); 3 begin 4 myText := "First Block"; 5 dbms_output.put_line(myText); 6 declare 7 l_more_text varchar2(20); 8 begin 9 l_more_text := "Second Block"; 10 dbms_output.put_line(l_more_text); 11 end; 12 end; 13 /
First Block Second Block PL/SQL procedure successfully completed. SQL> SQL>
</source>
Block with name
<source lang="sql">
SQL> SQL> <<main_loop>>
2 DECLARE
3 lv_var_num_1 PLS_INTEGER := 5;
4 BEGIN
5
6 DBMS_OUTPUT.PUT_LINE("Step 1: " || lv_var_num_1);
7 <<inner_loop>>
8 DECLARE
9 lv_var_num_1 PLS_INTEGER := 3;
10 lv_var_num_2 PLS_INTEGER := 9;
11 BEGIN
12 DBMS_OUTPUT.PUT_LINE("Step 2: " || lv_var_num_1);
13 DBMS_OUTPUT.PUT_LINE("Step 3: " || main_loop.lv_var_num_1);
14 DBMS_OUTPUT.PUT_LINE("Step 4: " || lv_var_num_2);
15
16 lv_var_num_1 := 6;
17 DBMS_OUTPUT.PUT_LINE("Step 5: " || lv_var_num_1);
18 END;
19 DBMS_OUTPUT.PUT_LINE("Step 7: " || lv_var_num_1);
20 END;
21 /
Step 1: 5 Step 2: 3 Step 3: 5 Step 4: 9 Step 5: 6 Step 7: 5 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Building Blocks of PL/SQL
<source lang="sql">
SQL> SQL> set serveroutput on; SQL> SQL> CREATE TABLE items_tab (item_code varchar2(6) PRIMARY KEY,
2 item_descr varchar2(20) NOT NULL);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 v_item_code VARCHAR2(6); 3 v_item_descr VARCHAR2(20); 4 BEGIN 5 v_item_code := "ITM101"; 6 v_item_descr := "Spare parts"; 7 INSERT INTO items_tab VALUES (v_item_code, v_item_descr); 8 EXCEPTION WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 /
PL/SQL procedure successfully completed. SQL> SQL> select * from items_tab; ITEM_C ITEM_DESCR
--------------------
ITM101 Spare parts 1 row selected. SQL> SQL> drop table items_tab; Table dropped. SQL> SQL> --
</source>
Five level nested statement
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 14-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 11-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 11-JUN-08 PROG 2 20000
1007 Java java01@g.ru 11-JUN-08 PROG 3 20000 1006
1008 Oracle oracle1@g.ru 11-JUN-08 DBA 4 20000 1006
8 rows selected. SQL> SQL> SQL> begin
2 begin
3 begin
4 begin
5 begin
6 declare
7 fname employees.last_name%type;
8 begin
9 select last_name
10 into fname
11 from employees
12 where 1=2;
13 exception
14 when NO_DATA_FOUND then
15 dbms_output.put_line("block #6");
16 end;
17 exception
18 when NO_DATA_FOUND then
19 dbms_output.put_line("block #5");
20 end;
21 exception
22 when NO_DATA_FOUND then
23 dbms_output.put_line("block #4");
24 end;
25 exception
26 when NO_DATA_FOUND then
27 dbms_output.put_line("block #3");
28 end;
29 exception
30 when NO_DATA_FOUND then
31 dbms_output.put_line("block #2");
32 end;
33 exception
34 when NO_DATA_FOUND then
35 dbms_output.put_line("block #1");
36 end;
37 /
block #6 PL/SQL procedure successfully completed. SQL> SQL> drop table employees; Table dropped. SQL> SQL>
</source>
Nested block
<source lang="sql">
SQL> SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE
2 v_emp emp.fname%TYPE;
3 BEGIN
4
5 -- the first nested block
6 BEGIN
7 SELECT fname INTO v_emp FROM emp WHERE UPPER(lname) = "H";
8 EXCEPTION
9 WHEN NO_DATA_FOUND
10 THEN
11 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 1");
12 DBMS_OUTPUT.PUT_LINE(" ");
13 NULL;
14 END;
15
16
17 END;
18 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.
</source>
no executable code
<source lang="sql">
SQL> SQL> declare
2 myText varchar2(100); 3 begin 4 null; 5 end; 6 /
PL/SQL procedure successfully completed. SQL>
</source>
Outer Block name and inner block name
<source lang="sql">
SQL> SQL> SQL> <<BLOCK1>>
2 DECLARE
3 lv_var_num1 NUMBER := 10;
4 BEGIN
5 <<BLOCK2>>
6 DECLARE
7 lv_var_num1 NUMBER := 20;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE("Value for lv_var_num1: " ||
10 lv_var_num1);
11 DBMS_OUTPUT.PUT_LINE("Value for BLOCK1.lv_var_num1: " ||
12 block1.lv_var_num1);
13 DBMS_OUTPUT.PUT_LINE("Value for BLOCK2.lv_var_num1: " ||
14 block2.lv_var_num1);
15 END BLOCK2;
16 END BLOCK1;
17 /
Value for lv_var_num1: 20 Value for BLOCK1.lv_var_num1: 10 Value for BLOCK2.lv_var_num1: 20 PL/SQL procedure successfully completed. SQL>
</source>
Plain SQL and PL/SQL program
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable(
2 e INTEGER, 3 f INTEGER 4 );
Table created. SQL> SQL> SQL> SQL> DELETE FROM myTable; 0 rows deleted. SQL> INSERT INTO myTable VALUES(1, 3); 1 row created. SQL> INSERT INTO myTable VALUES(2, 4); 1 row created. SQL> SQL> /* Above is plain SQL; below is the PL/SQL program. */ SQL> DECLARE
2 a NUMBER; 3 b NUMBER; 4 BEGIN 5 SELECT e,f INTO a,b FROM myTable WHERE e > 1; 6 INSERT INTO myTable VALUES(b,a); 7 END; 8 /
PL/SQL procedure successfully completed. SQL> drop table myTable; Table dropped. SQL>
</source>
The executable section needs at least one line of code to be valid.
<source lang="sql">
SQL> SQL> declare
2 myNumber_variable number := 50; 3 begin 4 -- NULL; means do nothing. 5 null; 6 end; 7 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
The PL/SQL Block
<source lang="sql">
SQL> SQL> set serveroutput on SQL> declare
2 myNumber number := 1; 3 begin 4 myNumber := 1 + 1; 5 dbms_output.put_line( "1 + 1 = " || to_char( myNumber ) || "!" ); 6 exception 7 when others then 8 dbms_output.put_line( "We encountered an exception!" ); 9 end; 10 /
1 + 1 = 2! PL/SQL procedure successfully completed. SQL>
</source>
This script demonstrates the structure of a block
<source lang="sql">
SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime TIMESTAMP; 3 BEGIN 4 SELECT systimestamp INTO myDateTime FROM dual; 5 6 DBMS_OUTPUT.PUT_LINE(myDateTime); 7 EXCEPTION 8 WHEN OTHERS 9 THEN 10 DBMS_OUTPUT.PUT_LINE(sqlerrm); 11 END; 12 /
26-OCT-09 10.40.28.139000 AM PL/SQL procedure successfully completed. SQL>
</source>
Three sections - declarative, executable, and exception.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> select * from lecturer;
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Scott Lawson Computer Science 11.00
- Mar Wells History 4.00
- Jone Bliss Computer Science 8.00
- Man Kyte Economics 8.00
- Pat Poll History 4.00
- Tim Viper History 4.00
- Barbara Blues Economics 7.00
- David Large Music 4.00
- Chris Elegant Nutrition 8.00
- Rose Bond Music 7.00
- Rita Johnson Nutrition 8.00
ID FIRST_NAME LAST_NAME MAJOR CURRENT_CREDITS
-------------------- -------------------- ------------------------------ ---------------
- Sharon Clear Computer Science 3.00
12 rows selected. SQL> SQL> CREATE TABLE myLogTable (
2 code NUMBER, 3 message VARCHAR2(200), 4 info VARCHAR2(100) 5 );
Table created. SQL> SQL> SQL> DECLARE
2 myLecturerID NUMBER(5) := 10000;
3 firstName VARCHAR2(20);
4
5 BEGIN
6 SELECT first_name
7 INTO firstName
8 FROM lecturer
9 WHERE id = myLecturerID;
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 INSERT INTO myLogTable (info)
13 VALUES ("Student 10,000 does not exist!");
14 END;
15 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myLogTable; Table dropped. SQL> SQL> drop table lecturer; Table dropped.
</source>
