в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:40Z

1 версия

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

==Declare variable as a loop counter==



<source lang="sql">
SQL>
SQL>
SQL> -- unconstrained loop: exit when
SQL>
SQL>
SQL> declare
2 l_loops number := 0;
3 begin
4 dbms_output.put_line("Before my loop");
5
6 loop
7 exit when l_loops > 4;
8 dbms_output.put_line("Looped " || l_loops || " times");
9 l_loops := l_loops + 1;
10 end loop;
11
12 dbms_output.put_line("After my loop");
13 end;
14 /
Before my loop
Looped 0 times
Looped 1 times
Looped 2 times
Looped 3 times
Looped 4 times
After my loop
PL/SQL procedure successfully completed.
SQL>

</source>

==Declare variables which will be used in SQL statements==



<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>
SQL>
SQL> DECLARE
2 v_NewMajor VARCHAR2(10) := "History";
3 myFirstName VARCHAR2(10) := "Scott";
4 v_LastName VARCHAR2(10) := "Urman";
5 BEGIN
6 UPDATE lecturer
7 SET major = v_NewMajor
8 WHERE first_name = myFirstName
9 AND last_name = v_LastName;
10 IF SQL%NOTFOUND THEN
11 INSERT INTO lecturer (ID, first_name, last_name, major)
12 VALUES (10020, myFirstName, v_LastName, v_NewMajor);
13 END IF;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
</source>

==Defining the Indefinable: NULL==



<source lang="sql">

SQL>
SQL>
SQL> declare
2 value1 varchar2(100);
3 value2 varchar2(100) := "";
4 value3 varchar2(100) := null;
5 value4 varchar2(100) default null;
6 begin
7 null;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
</source>

==Raise the "no data found" exception==



<source lang="sql">

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>
SQL> DECLARE
2 v_TempVar NUMBER(2);
3 BEGIN
4 SELECT id
5 INTO v_TempVar
6 FROM lecturer
7 WHERE last_name = "Smith";
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
</source>

==Raise the VALUE_ERROR exception==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_TempVar VARCHAR2(3);
3 BEGIN
4 v_TempVar := "ABCD";
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL>
SQL>
SQL>
</source>

==Variables and Constants==



<source lang="sql">

SQL>
SQL> declare
2 myNumber_variable number;
3 begin
4 myNumber_variable := 50;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
</source>

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

Oracle PL/SQL/PL SQL/Variable Declare - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия