Oracle PL/SQL/PL SQL/Begin End Block

Материал из SQL эксперт
Версия от 09:59, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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.