1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Oracle PL/SQL and SQL simplified approach

Oracle PL/SQL and SQL


Declare
    Variables, Cursors, user defined exception
Begin
    Sql statements
    PL/SQL statements
Exception
    Actions to perform when errors, occur
End;


Ex.
declare
    v_variable varchar2(15);
begin
    select column_name
    into v_variable
    from table_name;
exception
    when exception_name then
    ...
end;


procedure procedure_name is
[declare any variables here]
begin

end;


function function_name return datatype is
    [declare any veriables here]
begin
    
    return value;
end;

variable types
    scalar (single value)
    composite (records)
    reference (pointer)
    LOB (large objects)
    Bind variables




Scalar data types
Char, varchar2, long, number, binary_integer, boolean, date, timestamp, timestamp with timezone, timestamp with local time zone
    Ex:
    Declare
        V_job         varchar2(15);
        V_count    binary_integer := 0;
        V_total_sal    number(9,2);
        V_Date    date := sysdate+7;
        C_number    constant number(3,2) := 8.25;
        V_valid    boolean not null default true;
        V_name    employees.last_name%type;

Bind variables
    Variable global_variable number;
    ...
    Print global_variable;

    Procedure dışında tanımlı olan değişkenler bind variable;    
    Procedure içinden bunlara ulaşmak için :variable_name kullan;
    
    DBMS_OUTPUT.PUT_LINE (?The montly salary ? || to_char(v_sal)) ;
//set server output on

Variables in nested blocks
    Declare
        Birthdate date;
    Begin
        Declare
            Birtdate date;
        Begin
            ...
            Outer.birthdate := to_Date(?03-AUG-1976?,?DD-MON-YYYY?);
        end;
    ...
    End;

Ex:
    Declare
        V_deptno    number(4); //departments.department_no%type
        V_location_id number(4); //departments.locations_id%type
    Begin
        Select department_id, location_id into v_deptno, v_location_id
        From departments where department_name = ?Sales?;
    End;

    Set serveroutput on;
    Declare
        V_sum_sal number(10,2);
        V_dept_no number not null := 60;
        V_employee_id employees.employee_id%type;
    Begin
Select sum(salary) into v_sum_sal from employees where depaertment_id = v_dept_no;

DBMS_OUTPUT.PUT_LINE (?The sum salary is? || to_char(v_sum_sal));

Select hire_date from employees where employee_id = v_employee_id;
    End;

SQL%rowcount     number of rows affected by the most recent sql satement
SQL%found        returns true if the most recent sql statement affected any rows
SQL%notfound     returns true if the most recent sql statement affected no rows
SQL%isopen        returns true if the cursor is open, if close then returns false

Ex:
Varible rows_deleted varchar2(30)
Declare
    V_employee_id employees.employee_id%type :=176;
Begin
    Delete from employees where employee_id = v_eployee_id ;
    :rows_deleted := sql%rowcount || ?rows deleted?;
End ;
/
Print rows_deleted

conditions
    if conditions then
        ...
    Elsif conditions then
        ...
    Else
        ...
    End if

    Ex:
        if upper(v_last_name) = ?GETZ? then
            v_mgr := 102;
        end if ;
    
    case selector
        when expression then result
        when expression then result
        ...
        Else result
    End;

    Ex
Set serveroutput on
Declare
    V_grade char(1) := upper(?&p_grade?);
        v_appraisal varchar2(20);
Begin
    V_appraisal :=
    Case v_grade
        When ?A? then ?Excellent?
        When ?B? then ?Very Good?
        When ?C? then ?Good?
        Else ?No grade?
    End;

    DBMS_OUTPUT.PUT_LINE (?grade? || v_grade || ?apraisal ? ||
v_appraisal)
End;
    
Loops
    Basic loop, for loop, while loop
    
    Loop
        ...
        Exit when condition;
    End loop

    While condition loop
        ...
    End loop
    
    For counter in [reverse] lower bound ... upper bound loop

    End for


Composite data types
    Type emp_record_type is record
        (
        Last_name varchar2(25),
        Job_id varchar2(10),
        Salary number(8,2)
)
    Emp_record emp_record_type;

    Veya tüm kolonları seçmek istiyorsak;

    Declare
        Emp_record employees%rowtype;





Ex:
    Define employee_number =124
    Declare
        Emp_Rec employees%rowtype
    Begin
Select * into emp_rec from employees where employee_id = &employee_number;
    
İnsert into retired_emps (emp_no, ename, job, mng, hire_Date, leaveDate, sql, comm, rept_no) values (emp_rec.employee_id, emprec.lastname,...)

    Commit;
End;

İndex by tables
They are modeled as (but not the same as) database tables. İndex by values/tables use a primary key to provide you with array like access to rows.

    Type ename_Table is table of
        Employees.lastname%type index by binary_integer;
    Ename_table ename_table_type;
    
    Binary_integer     lastname
1    jones
2    smith
3    maduro

Ex:
Declare
        Type ename_table_type is table of employees.last_name%type
index by binary integer;

type hire_Date_typa is table of date index by binary integer;

ename_table ename_table_type ;
hiredate_table hiredate_table_type;
    Begin
        Ename_table(1) := ?cameron?;
        Hiredate_table(8) := sysdate + 7;

        If ename_Table.exists(1) then
            İnsert into ...
        End if;
    End ;

İndex by table methods
    Exists [(n)]
Count
    First
    Last
    Prior [(n)]
    Next [(n)]
    Trim [(n)]        //rename one element from end table
    Delete [(n)]        //remove elements

İndex by table of records
    Set serveroutput on
    Declare
        Type emp_table_type is table of employees%rowtype index by binary_integer;
        My_emp_table emp_table_type;
        V_count number(3) := 104;
    Begin
        For i in 10.. v_count loop
            Select * into my_emp_table(i) from employees where employee_id = i;
        End loop;

        For i in my_emp_table.first .. my_emp_table.last loop
            Dbms_output.put_line (my_emp_table.lastname);
        End loop;
    End;
    /

Cursors:
    Flowchart for cursors
        Declare -> open -> fetch -> if empty then -> close
        Create cursor -> open it ?> load the current row into variables -> test for empty
-> close

    Ex
        Declare
            Cursor emp_cursor is
                Select employee_id , last_name from employees;
            Cursor dept_cursor is
                Select * from departments where location_id = 170;
        Begin
            Open emp_cursor;
            Open dept_cursor;
            Loop
                Fetch emp_cursor into v_id , v_last;
                Exit when emp_cursor%notfound;
            End loop;
        Close emp_cursor;
        Close dept_cursor;
    End;

Cursor_name%isopen
Cursor_name%notfound
Cursor_name%found
Cursor_name%rowcount

Cursor and records
    Declare
        Cursor emp_cursor is select employee_id , last_name from employees;
        Emp_record emp_cursor%rowtype;
    Begin
        Open emp_cursor;
        Loop
            Fetch emp_cursor into emp_record;
            Exit when emp_cursor%notfound;
            İnsert into temp_list (emp_id, emp_name) values
(emp_Record.employee_id, emp_record.last_name)

        End loop;
        Commit;
        Close emp_cursor;
    End ;

/* **
    for emp_record in emp_cursor loop
        if emp_Record.department_id = 80 then
            dbms_output.put_line (?employee? || emp_record.last_name ||
?works in sales dept?);
end if
end loop;    
** */

cursors with parameters
    declare
        cursor emp_cursor (p_deptno number, p_job varchar2) is
            select employee_id, last_name from employees where department_id =
p_deptno and job_id = p_job;
    begin
        open emp_cursor (80,?sales_rep?);
        close emp_cursor;
        open emp_cursor (60,?it_prog?);
        ..
    end;

handling exceptions
    predefined oracle server eroor
    non predefined oracle server error
    user defined error

predefined oracle server errors
    no_data_found        //single row select returned no data
    too_many_rows        //single row select returned more than one row
    invalid_cursor            //illegal cursor operation
    zero_devide            //attempted to devide by zero
    dup_val_on_index        //attempted to insert a duplicate value
    cursor_already_open        
    invalid_number        //convertion of char string to a number
    login_denied

ex:
    begin
        ...
        
    exception
        when no_data_found then
            ...
        when too_many_rows then
            ...
    end;

user definied oracle server errors
    declare
        e_invalid_department exception;
    begin
        update departments set department_name = ?&department_desc?
        where department_id = &p_department_number;

        if sql%notfound then
raise e_invalid_department;
end if;
        
        commit;
    exception
        when e_invalid_department then
            dbms_output.put_line (?no such dept id ?);
    end;

ex:
    delete from employees where manager_id = v_mngr;
    if sql%notfound then
        raise_application_error (-20202,?not valid manager?);
    end if;

    OR

    Exception
        When no_data_found then
            Raise_application_error (?no data found?);
    End;

Creating procedures
    Subprograms
        A procedure that performs an action
        A function that computes a value

Note: use show errors to view compilation errors
Create or replace procedure raise_salary
        (p_id number, p_amount number)
    p_id out employees.employee_id%type;
is / as
    //declare section    
begin
    update employees set salary = salary + (salary * 0.20)
end raise_salary;

execute raise_salary (101,5000)

passing in/out parameters
    create or replace procedure query_emp
        (p_id in employees.employee_id%type,
p_name out employees.last_name%type,
p_salary out employees.salary%type,
p_comm out employees.employee_pct%type)
    is
        //local variables here
    begin
        select last_name , salary , commision_pct
        into p_name, p_salary, p_comm
        from employees
        where employee_id = p_id;
    end query_emp;
    /
    variable g_name varchar2(25)
    variable g_sal    number
    variable g_comm    number

    execute query_emp (171, :g_name, :g_sal, :g_comm)
    print g_name

    ex:
        create or replace procedure p_name
            (arg1 number default 100,
arg2 varchar2 default ?TTTT?)

        p_name (arg1 => 50, arg2 => ?ERGEM?)
        drop procedure procedure_name;




Oracle Supplied Packages

DBMS_SQL
    Open_cursor:    open a cursor and assign an id
    Parse:        parse a ddl or dml statement
            Check the syntax and associate it with the opened cursor
            (DDL statements are immediately executed when parsed)
    bind_variable:    binds the given value to the variable identified by its name in the parsed
statement in the open cursor
    execute:    executes the sql statement and returns the number of rows affected
    fetch_rows:    retrieves a row for the sepicified cursor
    close_cursor:    closes the specified cursor

    ex:
        create or replace procedure delete_all_rows
            (p_tab_name in varchar2,
p_rows_del out number)
        is
            cursor_name integer;
        begin
            cursor_name := dbms_sql.open_cursor;
            dbms_sql.parse (cursor_name, ?Delete from ? || p_rab_name ,
dbms_sql.native);
            p_rows_Del := dbms_Sql.execute(cursor_name);
            dbms_sql.close_cursor(cursor_name) ;
        end;
        /

        variable delete_number number;
        execute delete_all_rows (?employees?, :deleted);
        print delete_number;

        execute immediate ?delete from ? || p_table_name;
        p_rows_deleted := sql%rowcount;

DBMS_JOB
    Submit:    submits a job to the job queue
    Remove:    removes a specified job from the queue
    Change:    alters a specified job that has already been submitted to the queue    
    What:        alters the job description
    Next_date:    alters the next execution time for the specified job
    İnterval:    alters the interval between executions for a job
    Broken:    disable kob execution
    Run:        force a job to run




    Submit parameters     job        out     unique identifiers
                What        in     pl_sql code
                Next_Date    in    next execution date
                İnterval    in     date function to compute the next exec.
                No_parse    in    boolean flag that indicates whether to
parse the job
    ex:
        variable job_no number
        begin
            dbms_job.submit
            (
                job => :job_no,
                what => ?over_pack.add_dept (?education?,2710);?,
                next_Date => trunc(sysdate+1),
                interval => ?trunc(sysdate+1)?
);
            commit;
        end;
        /
        print job_no;

        dbms_job.change (job_no, null, trunc(sysdate+1)+6/24, ?sysdate+4/24?);
        dbms_job.run(job_no)
        dbms_job.remove(job_no)
        dbms_job.broken(job_no)

        select * from user_jobs;


DBMS_OUTPUT
    Put , New_line , Put_line , Get_line , Get_lines , Enable/disable    

UTL_FILE
    Fopen            invalid_path        
    İs_open        invalid_mode
    Get_line        invalid_filehandle
    Put            invalid_operation
    Put_line        read_error
    Putf            write_error
    New_line        internal_error
    Fflush
    Fclose
    Fclose_all







Ex:
    Create or replace procedure sal_status
        (p_filedir in varchar2,
p_filename in varchar2)
        is
            v_filehandle Utl_File.File_Type;
            cursor emp_info is select * from employees order by dept_id;
            v_newdeptno employees.dept_id%type;
            v_olddeptno employees.dept_id%type := 0;
        begin
            v_handle := utl_file.fopen (p_filedir, p_filename, ?w?);
            utl_file.putf(v_filehandle, ?salary report : generated on %s\n?, sysdate);
            utl_file.new_line(v_filehandle);

            for v_emp_rec in emp_info loop
                v_newdeptno := v_emp_rec.department_id;
                if v_newdeptno := v_emp_rec.department_id then
                    utl_file.putf(v_filehandle, v_olddeptno || ?dept?);
                end if;
                utl_file.putf (v_filehandle,?...? || v_emp_no);
            end loop
        end ;

triggers
    ex:
        create or replace trigger secure_emp
            before insert or delete or update on employees
        begin
            if (to_char(sysdate, ?dy?) in (?sat?,?sun?) or
to_char(sysdate,?hh24?) not between ?08? and ?18? ) then
    if deleting then
        raise_application_error (-20502,? ... ?)
elseif inserting then
raise_application_error (-20503,? ... ?)
elseif updating then     
raise_application_error (-20504,? ... ?)
end if ;
            end if ;
        end secure_emp;


        create or replace trigger secure_emp
            after insert or delete or update on employees for each row
        begin
            insert into audit_emp_table (...) values
(:OLD.lastname, :NEW.lastname)
        end;
        /



        create or replace trigger derive_comission_pct
            before insert or update of salary on employees
            for each row     
            where (new.job_id = ?sa_rep?)
        begin
            if inserting then
                :new.comission_pct := 0;
            elsif :old.comission_pct is null then
                :new.comission_pct := 0;
            else
                :new.comission_pct :=old.comission_pct + 0.05;
            end if;
        end;
        /

        create or replace trigger logon_trig
            after logon on schema
        begin
insert into log_trig_table (user_id, lodate, action) values
(user, sysdate, ?logging on?);
        end;
        /


Oracle SQL

Select lastname || ? is a ? job_id as ?employee details? from employees;
Select lastname from employees where lastname like ?_0%?
Select employee_id , lastname, job_id from employees where job_id like ?%SAL%?ESCAPE?\_?;

Character functions
    Case manupilation
        Lower
        Upper
        initcap
    Character manipulation
        Concat (a,b)
        Substr (a,m,n)        //same as mid function
        Length (a)
        İnstr (a,b,m,n)        //instr(?Hello World? , ?w?)
        Lpad (salary, 10, *)    //(*****24000)
        Rpad
        Trim
        Replace (text, search, replace)

Number functions
    Round        //rounds value to the specified decimal
        Round (45.926, 2) -> 45.93
    Trunc         //truncates to the specified decimal
        Trunc (45.926,2) -> 45.92
    Mod        //return the remainder of the division

Dates
    Date + number = date
    Date ? number =date
    Date ? date = number of days
    
Select sysdate from dual;
    Default format DD-MON-RR

    Months_between (date1, date2)
    Add_months (date,n)
    Next_day (date, ?friday?)        //find the next specified day of the week
    Last_day (date)            //the date of the last day of the month
    Round (date)
    Trunc (date)
        Ex:
            Round (sysdate, ?MONTH?)
            Round (sysdate, ?YEAR?)
            Trunc (sysdate, ?MONTH?)
            Trunc (sysdate, ?YEAR?)


Select employee_id , concat(fisrt_name, last_name) name, length (lastname) ,
    instr(lastname, ?a?) ?contains ?a? ?? from employees where substr(lastname,-1,1) = ?n?;

to_char (number | date, [fmt])
to_number (char, [fmt])
to_Date (char, [fmt])

alter session set nls_date_format = ?DD-MM-YYY HH24:MI:SS?
alter session set time_zone = ?-8:00?

select sessiontimezone, dbtimezone, current_timestamp, current_Date, sysdate from dual;

select extract (month from hire_date) from employees;
select to_timestamp(?2000-12-01 11:00:00? , ?YYYY-MM-DD HH:MI:SS?)

Ex:
    Select employee_id , to_char (hire_date, ?MM/YY?) from employees
where last_name = ?higgins?;
// (YYYY,YEAR, MM, MONTH, MON, DAY, DD, DY, J)
(HH24, MI, SS)

select to_char (hire_Date, ?fmDD Month YYYY?);
select to_char (hire_date, ?fmDdspth ?of? month YYYY fmHH:MI:SS AM?)
select last_name, hire_date from employees
where hire_Date = TO_DATE(?May 24, 1999?, ?fxMonth DD, YYYY?)
select to_char (salary, ?$99,999.00?)
    9 ? represents a number
    0 ? forces a zero to be displayed    
    $ - forces a floating ddler sign
    . ? prints a decimal point
    , - prints a thousand indicator

nvl (expr1, expr2)
nvl2 (expr1, expr2, expr3)
nullif (expr1, expr2)
coalesce (expr1, expr2, ... , exprn)

case expr     when comparison1 then expr1
        when comparison2 then expr2
        else expr3
end; alias

decode (col | expr,     search1, result1,
serach2, result2,
default) alias;

decode (job_id ,     ?IT_PROG? , 1.10*SAL
?SL_CLECK?, 1.15*SAL
SAL) ?REVISED_SAL?;

Joins
    Select table1.column, table2.column from table1 , table2
where table1.column(+)=table2.column;
    
    select table1.column, table2.column from table1
        [CROSS join table2]
        [NATURAL join table2]
        [JOIN table2 USING column_name]
    [JOIN table2 ON tbl1.column=tbl2.column]
    [LEFT | RIGHT | FULL OUTER join table2 ON tbl1.column ) tbl2.column]

define employee_id = 200;

set verify on
set echo on
show echo
set heading off | on
show heading
show _variable_name
set arraysize 20
set feedback on | off
set ttitle on | off | text
set btitle on | off | text
set break on | off | text | column

column lastname justify left format $99,990.00
column lastname clear

col colname format a30

break on job_id
noprint
break [on report element]
clear
script file
print
null text
column colname option

Ex:
set feedback off
ttitle ?employee | report?     //top title [text | on | off]
btitle ?confidential?        //bottom title
break on job_id
column job_id heading ?job / category?
column lat_name heading ?employee?
column salary heading ?salary? format $99,999.99
REM ** insert select statement

Select job_id , last_name salary from employees where salary < 15000
    Order by job_id, last_name;
/

REM ** clear all formatting commands

Set feedback on
Column job_id clear
Column last_name clear
Column salary clear
Clear break


Merge into table_name table_alias
    Using (table) alias
    On (join condition)
    When matched then update set col1 = val1 , col2 = val2
    When not matched then insert (col_list) values (val_list)

    Ex:
        Merge into copy_emp c using employee e on (c.employee_id = e.employee_id)
        When matched then
            Update set c.firstname = e.firstname
            ...
        when not matched then
            Insert values (e.emplyee_id ...)

Commit , rollback , savepoint

Update ..
Savepoint update_done
&#304;nsert
Rollback to update_done

Create table dept80 as select * from employees where department_id = 80;

Alter table table_name add (column_name datatype)
Alter table set unused column col_name
Alter table table_name modify (col_name datatype)

Alter table table_name drop (col_name)

Rename dept to dept_detail;
Truncate table detail_dept;
Select row_num as rank;

Contraints
    Not null
    Unique
    Primary key
    Foreign key
    Check
    
    Create table employees (    employee_id number(6) ,
First_name varchar2(20),
Job_id varchar2(10) not null,
Constraint emp_emp_id_pk primary key (employee_id))
    Constraint emp_dep_fk foreign key (department_id) references
        Departments (department_id) on delete cascade | set null

    Alter table table_name add constraint c_name typce (col);
    Constraint emp_Sal_min check (salary > 0)

Alter table employees drop | disable | enable constraint const_name;

Create view v$name as select * from tbl_name;
Create or replace view empv10 as
    ...
    with check option name
    read only;

select rownum as rank , last_name , salary from ... where rownum <= 3;

objects
    table , view, sequence, index, synonym
    
    create sequence seq_name
        increment by n
        start with n
        maxvalue n
        minvalue n
        cycle | no cycle
        cache | no cache

    seq_name.currval
    seq_name.nextval

    alter sequence seq_name ...
    describe user_sequences


    create index index on table (column)
    create index index on table (upper(column));

    create public synonym synonym_name for object

    create user scott identified by tiger
    grant privilige ro user_name | role
        create session
        create table
        create sequence
        create view

    create role manager
    grant priv to manager
    grant manager to user

    

alter user scott identified by lion;
    grant object_priv on object to user | role [with grant option]
        alter
        delete
        execute
        index
        insert
        references    
        select
        update

Ex:
    Grant update (department_name , location_id) on departments to scott, manager

Set operators
    Union
    Union all    //includes duplicate rows
    &#304;ntersect
    minus
        
    select department_id, job_id, sum(salary) from employees where department_id < 60
        group by rollup | cube (department_id, job_id)

    select department_id , job_id manager_id, avg(salary) from employees
        group by grouping sets ((department_id, job_id) , (job_id, manager_id));

select department_id , job_id , sum(salary) from employees where department_id < 60 group by [rollup | cube] (department_id, job_id)

group by grouping sets ((, , , ) , (, , ,))

exists , not exists

alter table employees add (department_name varchar2(14));

update employees e set department_name = (select department_name from departments.d where e.department_id = d.department_id);

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy