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
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> --
An example of an anonymous block
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>
Block-Based Development
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>
Block Nesting
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>
Block with name
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>
Building Blocks of PL/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> --
Five level nested statement
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>
Nested block
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.
no executable code
SQL>
SQL> declare
2 myText varchar2(100);
3 begin
4 null;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
Outer Block name and inner block name
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>
Plain SQL and PL/SQL program
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>
The executable section needs at least one line of code to be valid.
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>
The PL/SQL Block
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>
This script demonstrates the structure of a block
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>
Three sections - declarative, executable, and exception.
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.