в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:25Z

1 версия

Новая страница

== An anonymous PL/SQL procedure to demonstrate the use of PL/SQL records==



<source lang="sql">
SQL>
SQL> create table employee (
2 id number not null,
3 employee_type_id number not null,
4 external_id varchar2(30) not null,
5 first_name varchar2(30) not null,
6 middle_name varchar2(30),
7 last_name varchar2(30) not null,
8 name varchar2(100) not null,
9 birth_date date not null,
10 gender_id number not null );
Table created.
SQL>
SQL> declare
2 TYPE name_record is record (
3 first_name employee.first_name%TYPE,
4 middle_name employee.middle_name%TYPE,
5 last_name employee.last_name%TYPE );
6 TYPE name_table is table of name_record index by binary_integer;
7 t_name name_table;
8 begin
9 t_name(1).first_name := "JOHN";
10 t_name(1).last_name := "DOE";
11 t_name(2).first_name := "JANE";
12 t_name(2).last_name := "DOE";
13
14 dbms_output.put_line(t_name(1).last_name||", "||t_name(1).first_name);
15 dbms_output.put_line(t_name(2).last_name||", "||t_name(2).first_name);
16 end;
17 /
DOE, JOHN
DOE, JANE
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL></source>

== Bulk DML with table of records==



<source lang="sql">
SQL>
SQL>
SQL> CREATE TABLE customer_region(
2 region_id NUMBER(4) PRIMARY KEY,
3 region_name VARCHAR2(11) NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,"REGION1");
1 row created.
SQL> INSERT INTO customer_region VALUES (2,"REGION2");
1 row created.
SQL> INSERT INTO customer_region VALUES (3,"REGION3");
1 row created.
SQL> INSERT INTO customer_region VALUES (4,"REGION4");
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 Type regionRecord IS Record(region_id NUMBER(4),region_name VARCHAR2(10));
3 Type region_tbl IS TABLE of regionRecord INDEX BY BINARY_INTEGER;
4 regionRecords region_tbl;
5 returnCode NUMBER;
6 Ret_errorMessage VARCHAR2(1000);
7 Procedure load_regions (regionRecords IN region_tbl,
8 returnCode OUT NUMBER,
9 errorMessage OUT VARCHAR2)
10 Is
11 BEGIN
12
13 DELETE FROM customer_region;
14
15 FOR i in regionRecords.FIRST..regionRecords.LAST LOOP
16 INSERT INTO customer_region
17 values (regionRecords(i).region_id,regionRecords(i).region_name);
18 END LOOP;
19
20 COMMIT;
21
22 EXCEPTION WHEN OTHERS THEN
23
24 returnCode :=SQLCODE;
25
26 errorMessage :=SQLERRM;
27
28 END;
29 BEGIN
30 FOR i IN 1..5 LOOP
31 regionRecords(i).region_id :=i;
32 regionRecords(i).region_name :="REGION"||i;
33 END LOOP;
34 Load_regions(regionRecords,returnCode,ret_errorMessage);
35 EXCEPTION WHEN OTHERS THEN
36 RAISE_APPLICATION_ERROR(-20111,SQLERRM);
37 END;
38 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table customer_region;
Table dropped.
SQL>
SQL></source>

== Defining an Index-by table of records==



<source lang="sql">
SQL> DECLARE
2 TYPE hrc_company_rec IS RECORD
3 (hrc_company_id NUMBER,
4 product_description VARCHAR2(20),
5 company_short_name VARCHAR2(30));
6
7 TYPE hrc_company IS TABLE OF hrc_company_rec INDEX BY BINARY_INTEGER;
8
9 v_example_tab hrc_company;
10 BEGIN
11 /*Do some processing */
12 null;
13 END;
14 /
PL/SQL procedure successfully completed.</source>

== Table collection of records==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PACKAGE process_vacations IS
2 TYPE pv_vacation_rec IS RECORD
3 (pv_vacationing_user_txt VARCHAR2(30),
4 pv_start_date VARCHAR2(10),
5 pv_end_date VARCHAR2(10),
6 pv_redirect_interviews_txt VARCHAR2(30),
7 pv_redirect_reviews_txt VARCHAR2(30));
8 TYPE pv_type_vacation_tab IS TABLE OF pv_vacation_rec
9 INDEX BY BINARY_INTEGER;
10 PROCEDURE process_schedules (p_vac_tab IN pv_type_vacation_tab);
11 END process_vacations;
12 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY process_vacations IS
2
3 PROCEDURE process_schedules (p_vac_tab IN pv_type_vacation_tab) IS
4 BEGIN
5 FOR lv_loop_counter_num IN 1..p_vac_tab.COUNT LOOP
6 NULL;
7 END LOOP;
8 END process_schedules;
9 END process_vacations;
10 /
Package body created.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Table of Record - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия