в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:50Z

1 версия

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

==Commit or rollback based on success flag==



<source lang="sql">

SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL> DECLARE
2 success_flag BOOLEAN;
3 BEGIN
4 BEGIN
5 UPDATE emp
6 SET rate = rate * 1.10;
7 success_flag := TRUE;
8 EXCEPTION
9 WHEN OTHERS THEN
10 success_flag := false;
11 END;
12
13 IF success_flag THEN
14 COMMIT;
15 ELSE
16 ROLLBACK;
17 DBMS_OUTPUT.PUT_LINE("The UPDATE failed.");
18 END IF;
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>

</source>

==Effects of nulls on boolean expressions: ===



<source lang="sql">

SQL>
SQL> -- Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 a INTEGER;
3 n INTEGER; -- be our null value.
4 BEGIN
5 -- Assign a value to the variable A, but leave N null.
6 a := 2;
7 -- Note that the test for A=N fails.
8 IF a = n THEN
9 DBMS_OUTPUT.PUT_LINE("a = n is true");
10 ELSE
11 DBMS_OUTPUT.PUT_LINE("a = n is not true");
12 END IF;
13 END;
14 /
a = n is not true
PL/SQL procedure successfully completed.
SQL>

</source>

==Effects of nulls on boolean expressions: >, ==



<source lang="sql">

SQL>
SQL> -- Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 a INTEGER;
3 n INTEGER; -- be our null value.
4 BEGIN
5 -- Assign a value to the variable A, but leave N null.
6 a := 2;
7 -- But also note that the test for a <> n fails.
8 IF a <> n THEN
9 DBMS_OUTPUT.PUT_LINE("a <> n is true");
10 ELSE
11 DBMS_OUTPUT.PUT_LINE("a <> n is not true");
12 END IF;
13
14 END;
15 /
a <> n is not true
PL/SQL procedure successfully completed.
SQL>

</source>

==Use and to link two boolean expressions==



<source lang="sql">

SQL>
SQL> -- Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 a INTEGER;
3 n INTEGER; -- be our null value.
4 BEGIN
5 -- Assign a value to the variable A, but leave N null.
6 a := 2;
7 -- TRUE and NULL = NULL
8 IF (a = 2) AND (a <> n) THEN
9 DBMS_OUTPUT.PUT_LINE("TRUE and NULL = TRUE");
10 ELSE
11 DBMS_OUTPUT.PUT_LINE("TRUE and NULL = NULL");
12 END IF;
13 END;
14 /
TRUE and NULL = NULL
PL/SQL procedure successfully completed.
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия