Oracle PL/SQL Tutorial/PL SQL Programming/Code Block
Содержание
- 1 A PL/SQL Block
- 2 Demonstrate nested PL/SQL blocks
- 3 Inline procedure
- 4 Inner function
- 5 Inner procedure in an anonymous function
- 6 Select the first names for the Doe family from the Worker table.
- 7 the forward slash on a line by itself says execute this procedure
- 8 This is an anonymous procedure, so it has no name
- 9 Uses a PL/SQL Nested Block
A PL/SQL Block
SQL>
SQL>
SQL> CREATE TABLE myItemTable (
2 item_code varchar2(6) PRIMARY KEY,
3 item_descr varchar2(20) NOT NULL);
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_item_code VARCHAR2(6);
3 v_item_descr VARCHAR2(20);
4 BEGIN
5 v_item_code :="sqle";
6 v_item_descr :="a web site for Oracle";
7 INSERT INTO myItemTable VALUES (v_item_code,v_item_descr);
8 EXCEPTION WHEN OTHERS THEN
9 dbms_output.put_line(SQLERRM);
10 END;
11 /
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myItemTable;
no rows selected
SQL>
SQL> drop table myItemTable;
Table dropped.
SQL>
SQL>
Demonstrate nested PL/SQL blocks
SQL>
SQL> declare
2 v_custno NUMBER := 100;
3 begin
4 dbms_output.put_line(v_custno) ;
5 declare
6 v_state CHAR(2):= "NY" ;
7 begin
8 dbms_output.put_line(v_custno || v_state) ;
9 end ;
10 end ;
11 /
100
100NY
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Inline procedure
SQL>
SQL>
SQL> DECLARE
2 PROCEDURE myProc (dt IN VARCHAR2) IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE (dt || " -> " || ADD_MONTHS (dt, 1));
5 END;
6 BEGIN
7 myProc ("30-JAN-99");
8 myProc ("27-FEB-99");
9 END;
10 /
30-JAN-99 -> 28-FEB-99
27-FEB-99 -> 27-MAR-99
PL/SQL procedure successfully completed.
SQL>
SQL>
Inner function
SQL>
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL>
SQL>
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL> create table employee_type (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,"C","Contractor" );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,"E","Employee" );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,"U","Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2 n_count number := 0;
3
4 FUNCTION add_worker(
5 aiv_first_name employee.first_name%TYPE,
6 aiv_middle_name employee.middle_name%TYPE,
7 aiv_last_name employee.last_name%TYPE,
8 aid_birth_date employee.birth_date%TYPE,
9 aiv_gender_code gender.code%TYPE,
10 aiv_employee_type_code employee_type.code%TYPE)
11 return number is
12
13 v_name employee.name%TYPE;
14
15 begin
16 v_name := rtrim(aiv_last_name||", "||aiv_first_name||" "||aiv_middle_name);
17 begin
18 insert into employee (
19 id,
20 employee_type_id,
21 external_id,
22 first_name,
23 middle_name,
24 last_name,
25 name,
26 birth_date,
27 gender_id )
28 select 1,
29 myCursor.id,
30 lpad(to_char(1), 9, "0"),
31 aiv_first_name,
32 aiv_middle_name,
33 aiv_last_name,
34 v_name,
35 aid_birth_date,
36 c2.id
37 from employee_type myCursor,
38 gender c2
39 where myCursor.code = aiv_employee_type_code
40 and c2.code = aiv_gender_code
41 and not exists (
42 select 1
43 from employee x
44 where x.name = v_name
45 and x.birth_date = aid_birth_date
46 and x.gender_id = c2.id );
47
48 return sql%rowcount;
49 exception
50 when OTHERS then
51 raise_application_error(-20001, SQLERRM||" on insert employee"||" in add_worker");
52 end;
53 end add_worker;
54
55 begin
56 n_count := n_count + add_worker("JOHN", "J.", "DOE", to_date("19800101", "YYYYMMDD"), "M", "C");
57 n_count := n_count + add_worker("JANE", "J.", "DOE", to_date("19800101", "YYYYMMDD"), "F", "E");
58 n_count := n_count + add_worker("JOHNNY", "E.", "DOE", to_date("19980101", "YYYYMMDD"), "M", "E");
59 n_count := n_count + add_worker("JANIE", "E.", "DOE", to_date("19980101", "YYYYMMDD"), "F", "E");
60 DBMS_OUTPUT.PUT_LINE(to_char(n_count)||" row(s) inserted.");
61 end;
62 /
4 row(s) inserted.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>
Inner procedure in an anonymous function
SQL>
SQL> create table t ( x varchar2(30) );
Table created.
SQL>
SQL>
SQL> declare
2 procedure method1( p_data in varchar2 )
3 is
4 begin
5 execute immediate "insert into t(x) values(:x)"
6 using p_data;
7 end method1;
8
9 procedure method2( p_data in varchar2 )
10 is
11 begin
12 execute immediate "insert into t(x) values( """ ||replace( p_data,"""", """""" ) || """ )";
13 end method2;
14 begin
15 for i in 1 .. 10000
16 loop
17 method1( "row " || I );
18 end loop;
19
20 for i in 1 .. 10000
21 loop
22 method2( "row " || I );
23 end loop;
24
25 end;
26 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
Select the first names for the Doe family from the Worker table.
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 v_first_name employee.first_name%TYPE;
4 n_id employee.id%TYPE;
5
6 FUNCTION get_first_name(
7 aion_id in out employee.id%TYPE,
8 aiv_last_name in employee.last_name%TYPE)
9 return employee.first_name%TYPE is
10
11 v_first_name employee.first_name%TYPE;
12
13 begin
14 select id,first_name
15 into aion_id,v_first_name
16 from employee
17 where id > aion_id
18 and last_name like aiv_last_name||"%"
19 and rownum = 1;
20
21 return v_first_name;
22 exception
23 when NO_DATA_FOUND then
24 return v_first_name;
25 when OTHERS then
26 raise_application_error(-20001, SQLERRM||" on select employee"||" in show_worker");
27 end get_first_name;
28
29 begin
30 n_id := 0;
31 loop
32 v_first_name := get_first_name(n_id, "DOE");
33 if v_first_name is NULL then
34 exit;
35 end if;
36 DBMS_OUTPUT.PUT_LINE(v_first_name);
37 end loop;
38 end;
39 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
the forward slash on a line by itself says execute this procedure
SQL>
SQL> declare
2 /*
3 declare local cursors, variables, and methods here,
4 but you don"t need to have a declaration section.
5 */
6 begin
7 -- You write your logic here
8
9 null; -- Ahhh, you"ve got to have at least one command!
10 exception
11 when NO_DATA_FOUND then
12 raise_application_error(-20000, "Hey, No Data Found!");
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
This is an anonymous procedure, so it has no name
SQL> declare
2 /*
3 declare local cursors, variables, and methods here,
4 but you don"t need to have a declaration section.
5 */
6 begin
7 -- You write your logic here
8
9 null; -- Ahhh, you"ve got to have at least one command!
10 exception
11 when NO_DATA_FOUND then
12 raise_application_error(-20000, "Hey, No Data Found!");
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
Uses a PL/SQL Nested Block
SQL>
SQL>
SQL> CREATE TABLE myItemTable (
2 item_code varchar2(6) PRIMARY KEY,
3 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 v_num NUMBER(1);
5 BEGIN
6
7 v_item_code :="sqle";
8
9 v_item_descr :="a website for Oracle";
10
11 BEGIN
12
13 SELECT 1 INTO v_num FROM myItemTable WHERE item_code =v_item_code;
14
15 EXCEPTION
16
17 WHEN NO_DATA_FOUND THEN
18 v_num :=0;
19
20 WHEN OTHERS THEN
21 dbms_output.put_line("Error in SELECT:"||SQLERRM);
22 RETURN;
23 END;
24
25 IF (v_num =0)THEN
26
27 INSERT INTO myItemTable VALUES (v_item_code,v_item_descr);
28
29 END IF;
30 dbms_output.put_line("Successful Completion");
31 EXCEPTION WHEN OTHERS THEN
32 dbms_output.put_line(SQLERRM);
33 END;
34 /
Successful Completion
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from myItemTable;
ITEM_C ITEM_DESCR
------ --------------------
sqle a website for Oracle
1 row selected.
SQL>
SQL> drop table myItemTable;
Table dropped.
SQL>
SQL>