1) OUTER JOINS EXAMPLE:-
2) Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
3) Table Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
4) and we want to find out the sales amount for all of the stores. If we do a regular join, we
will not be able to get what we want because we will have missed "New York," since it
does not appear in the Store_Information table. Therefore, we need to perform an outer
join on the two tables above:
5) SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
6) Note that in this case, we are using the Oracle syntax for outer join.
7) Result:
store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
8) Note: NULL is returned when there is no match on the second table. In this case, "New
York" does not appear in the table Store_Information, thus its corresponding "SALES"
column is NULL.
2) NATURAL JOIN:- SELECT dname, ename FROM dept NATURAL JOIN emp
EQUI JOIN :- on (emp.deptno = dept.deptno)
3) CODE TO OPEN/CLOSE CURSOR:-
OPEN [CURSOR NAME]
LOOP
FETCH [CURSOR NAME] INTO [VARAIABLE NAMES]
EXIT WHEN [CURSOR NAME]%NOTFOUND
PL/SQL CODE
END LOOP;
CLOSE [CURSOR NAME]
4) A TIME WE CAN ONLY RUN ONE QUERY USING EXECUTE IMMEDIATE.
5) v$ prefix in oracle:- They are just normal characters. But Oracle (and other vendors) will frequently
use characters like $ that normal developers don't normally use in order to create conventions around
table names.
For example, Oracle provides a number of data dictionary tables that use x$, v$, and gv$ as prefixes.
There is nothing inherently special about those prefixes. But they convey meaningful information to the
users of those systems (x$ views are undocumented views, v$ views provide information about activity
on the local instance, gv$ views provide information about activity on all the instances of a RAC cluster).
And using those sorts of prefixes prevents confusion between application tables and Oracle tables. Lots
of applications, for example, have a SESSION table. Having the V$ prefix for the Oracle-delivered
V$SESSION table makes it easy to avoid confusion from having two identically named tables.
6) tns names ora
7) to see errors after procedure compilation:- SHOW ERRORS.
8) query to see procedure text:-
SELECT line||' '||text PROCEDURE
FROM user_source
WHERE name = 'COMPILE_ERROR';
9)
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
DECLARE
V_NAME VARCHAR2(30) := 'NESTED';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME );
END;
END;
IT IS POSSIBLE TO USE DECLARE IN MAIN PL/SQL BLOCK.
10 )
IF VARIABLE IN OUTER AS WELL AS INNER BLOCK HAVE SAME NAME THEN NAME IN INNER BLOCK
REFERES TO INNER BLOCK VARIABLE.
11)
ACCESS OUTER BLOCK VARIALBE IN INNER BLOCK
<<OUTER>>
DECLARE
V_NAME VARCHAR2(30) := 'NESTEDO';
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
DECLARE
V_NAME VARCHAR2(30) := 'NESTEDI';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME );
DBMS_OUTPUT.PUT_LINE(OUTER.V_NAME );
END;
DBMS_OUTPUT.PUT_LINE(V_NAME );
END;
12 INNER BLOCK VARIABLES CANNOT BE ACCESSED BY OUTER BLOCKS.
13 DIFFERENCE BETWEEN CASE AND SERCHED CASE:-
NO EXPRESSION GIVEN IN SEARCHED CASE AS GIVEN BELOW:-
CASE:-
SET SERVEROUTPUT ON
DECLARE
v_category books.category%TYPE;
v_discount NUMBER(10,2);
v_isbn books.isbn%TYPE := '72230665';
BEGIN
SELECT category
INTO v_category
FROM books
WHERE isbn = v_isbn;
-- Determine discount based on category
CASE v_category
WHEN 'Oracle Basics'
THEN v_discount := .15;
WHEN 'Oracle Server'
THEN v_discount := .10;
END CASE;
DBMS_OUTPUT.PUT_LINE('The discount is '
||v_discount*100
||' percent');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
SERCHED CASE:-
CASE -- Notice that there is no expression here...
WHEN v_price < 40 THEN
DBMS_OUTPUT.PUT_LINE('This book is already discounted');
108 Oracle Database 10g PL/SQL Programming
Chapter 3: PL/SQL Basics 109
WHEN v_price BETWEEN 40 AND 50 THEN
v_price := v_price - (v_price * .10);
UPDATE books
SET price = v_price
WHERE isbn = v_isbn;
WHEN v_price > 50 THEN
v_price := v_price - (v_price * .10);
UPDATE books
SET price = v_price
WHERE isbn = v_isbn;
ELSE
DBMS_OUTPUT.PUT_LINE('Price not found');
END CASE;
14) GOT O RULES:-
GOTO cannot reference a label in a nested block.
■ GOTO cannot be executed outside an IF clause to a label inside the IF clause.
■ GOTO cannot be executed from inside an IF clause to a label inside
another IF clause.
■ GOTO cannot navigate from the EXCEPTION section to any other section
of the block
15) THE BEGINNING OF A TRANSACTION:-
Instead, a transaction begins whenever a DML command
locks an object.
16)IT IS POSSIBLE TO USE * IN CURSOR EXAMPLE:-
DECLARE
V_REC MASTER_USER%ROWTYPE;
CURSOR CUR IS
SELECT *
FROM MASTER_USER;
BEGIN
FOR REC IN CUR
LOOP
DBMS_OUTPUT.PUT_LINE(REC.USERNAME||' D ' ||REC.C_PASSWORD) ;
END LOOP;
END;
17) WHILE RUNNING A SCRIPT LIKE:-
INSERT INTO MASTER_COURSE VALUES('1','ORACLE/PLSQL');
INSERT INTO MASTER_COURSE VALUES('1','ORACLE FORMS');
INSERT INTO MASTER_COURSE VALUES('1','ORACLE REPORTS');
INSERT INTO MASTER_COURSE VALUES('1','JAVA');
With primary key on the first value it will still give error even though the script is not committed.
