Oracle Applications: PLSQL Queries

1) What is cursor?

A Cursor is a named private sql area from where information can be accessed. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.

2) Types of cursors?

1) Implicit: declared for all DML and pl/sql statements.

By default it selects one row only.

2) Explicit: Declared and named by the programmer.

Use explicit cursor to individually process each row returned by a

Multiple statements, is called ACTIVE SET.

Allows the programmer to manually control explicit cursor in the

Pl/sql block

a) declare: create a named sql area

b)Open: identify the active set.

c) Fetch: load the current row in to variables.

d)Close: release the active set.

IMPLICIT CURSOR ATTRIBUTES

a) Sql%is open

b) Sql%not found

c) Sql%found

d) Sql%row count

EXPLICIT CURSOR ATTRIBUTES

d) %is open: evaluates to true if the cursor is open.

e) %not found: evaluates to true if the most recent fetch does not return a row

f) %found: evaluates to true if the most recent fetch returns a row.

g) %row count: evaluates to the total number of rows returned to far.

Example for cursor:

1) Declare

Vno emp.empno%type;

Vname emp.ename %type;

Cursor emp_cursor is

Select empno,ename

From emp;

Begin

Open cursor;

For I in 1..10 loop

Fetch emp_cursor into vno,vname;

Dbms_output.putline(to_char(vno) ||’ ‘||vname);

E nd;

2) Begin

Open emp_cursor;

Loop

Fetch when emp_cursor % rowcount >10 or

Emp_curor % not found;

Bdms_output_put_line(to_char(vno)||’ ‘|| vname);

End loop;

Close emp_cursor;

End;

CURSOR FOR LOOP

A) cursor for loop is a short cut to process explicit cursors

B) it has higher performance

C) cursor for loop requires only the declaration of the cursor, remaining things like opening, fetching and close are automatically take by the cursor for loop

Example:

1) Declare

Cursor emp_cursor is

Select empno,ename

From emp;

Begin

For emp_record in emp_cursor loop

Dbms_output.putline(emp_record.empno);

Dbms_output.putline(emp_record.ename)

End loop

End;

a) for update clause:

1) use explicit locking to deny access for the duration of a transaction

2) lock the rows before update or delete

Ex: select …….

From…….

For update[ of column ref] [no_wait]

b) Where current of clause?

1) Use cursor to update or delete the current row

Where current of < column ref>

3) Attribute data types?

1) %type 2) %row type.

4) Exception Handilings?

Is a mechanism provided by pl/sql to detect runtime errors and process them with out halting the program abnormally

1) pre-defined

2) user-defined.

PRE-DEFINED:

1) cursor_already_open--------attempted to open an already open cursor.

2) Dup_val_on_index --------attempted to insert a duplicate values.

3) Invalid_cursor -------- illegal cursor operation occurred.

4) Invalid_number -------- conversion of character string to number fails.

5) Login_denied ---------loging on to oracle with an invalid user name

and password.

6) program_error -------- pl/sql has an internal problem.

7) storage_error -------- pl/sql ran out of memory or memory is

corrupted.

8) to_many_row ---------single row select returned more than one row.

9) value_error -------- arithmetic,conversion,truncation or size

constraint error occurred.

10) zero_devided -------- attempted to divided by zero.

USER-DEFINED:

Declare : name the exception

Raise : explicitly raise the exception by using the raise statements

Reference : exception handing section.

The Raise_Application_Error_Procedure:

n You can use this procedure to issue user-defined error messages from stored sub programs.

n You can report errors to your applications and avoid returning unhandled exceptions.

Raise_Application_Error(error_number,message[,{true/false}]

Error number è between -20000 to -20999

pragma exception_init?

It tells the compiler to associate an exception with an oracle error. To get an error message of a specific oracle error.

Ex: pragma exception_init(exception name, oracle error number)

Example for Exceptions?

1) Check the record is exist or not?

Declare

E emp% rowtype

Begin

e.empno := &empno;

select * into e from emp where empno =e.empno;

Dbms_output.putline(‘empno’ || e.empno);

Exception

When no_data_found then

Dbms_output.putline(e.empno ||’doest exist’);

End;

2) User defined exceptions?

Define p_dept_desc =’gvreddy’

Define p_dept_number =1236

Declare

E_invalid_dept exception;

Begin

Update departments

Set dept_name=’&p_dept_desc’

Where dept_id =’&p_dept_number’;

If sql% not found then

Raise e_invalid_departments;

End if;

Commit;

Exception

When e_invalid_departments then

Dbms_output.putline(‘no such dept’);

End;

PRAGMA?

---To restrict the user function in select statement.

5) What is REF Cursor?

To execute a multi-row query, oracle opens an unnamed work area that stores processing information, to access the information, an explicit, which names the work area or, a cursor variable, which points to the work area.

Where as a cursor always refers to the same query work area, a cursor variable can refer to a different work areas, cursor variable area like ‘c’ or ‘pascal’ pointers, which hold the memory location (address) of some object instead of the object itself.

So, declaring a cursor variable creates a pointer, not an object.

Uses:

---- Uses pl/sql to fetch data for the report, in REF cursor query you can specity a pl/sql function that returns a cursor value from a cursor variable.

---- When u make Ref cursor query the child in a data link, the link can be only group to group, it can’t be column to column link

---- To increase control and security

Ex:

Declare

Type deptcurTyp is ref cursor return departments % rowtype;

Dept_cv deptcurTyp;

Deptcurtyp: is a pacifier used in subsequent declarations of cursor variables.

Return_type: represents a record or a row in a database table.

These are 2 typs:

1) Strong REF cursor: which have return type

2) Weak REF cursor: which can’t have return type.

Avoid the use of lexical parameters in reports.

A) Similarly we can use % type to provide the data type of a record variable

Ex:

Declare

Dept_rec departments % rowtype --declare record variable.

Type deptcurTyp is ref cursor return dept_Rec % type;

Dept_cv deptcurTyp; -- declare cursor variables.

B) Specify a user-defined record type in the return clause

Ex:

Declare

Type emprecTyp is record (empno number(4),

Ename varchar2(10),

Sal number(7,2));

Type empcurTyp is ref cursor return emprecTyp;

Emp_cv empcurTyp;

6) Stored procedure?

----Stored procedure is a sequence of statements that perform specific task.

----procedures that are stored in the compiled form in the database

----many users can use same procedure at a time

7) What is procedure?

---- is a named pl/sql block to perform a specific task.

---- A procedure may have DML statements.

---- It may or may not return a value.

---- Procedure can return more than one value.

Example for procedure

1) To accept the year as a parameter and list emp belong to the year?

Create or replace

Procedure empy(y number) is

Cursor emp_cursor is

Select * from emp where to_char(hiredate,’yyyy’)=’y’;

Emp_record emp%rowtype;

Begin

For emp_record in emp_cursor loop

Print (emp_record.empno);

Print (emp_record.ename);

Print (emp_record.sal);

End loop;

End;

Output :

var empx number;

Begin

:empx := ‘1234’;

End;

Exec empy(:empx);

Print empy;

8) What is function?

---- is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.

---- A function is called as part of an exception.

---- Every function should return a value

Example for function

Create or replace

Function get_sal(p_id in emp.emp_no% type)

Return number

v_sal emp.sal%type :=0;

Begin

Select salary into v_salary

From emp

Where emp_no = p_id;

Return v_salary

End get_sal;

End;

Output :

var g_sal number;

Exec :g_sal := get_sal(99);

Print g_salary;

9) What is the package?

---- Group logically related pl/sql types, items and subprograms.

1) package specification

2) package body

Advantages of a package:

· Modularity

· Easier Application Design

· Information Hiding

· Overloading

You cannot overload:

•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).

•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)

•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)

•Two functions that differ only in return type, even if the types are in different families.

10) What is FORWARD DECLARATION in Packages?

PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:

• Define subprograms in logical or alphabetical order.

• Define mutually recursive subprograms.(both calling each other).

• Group subprograms in a package

Example of forward Declaration:

CREATE OR REPLACE PACKAGE BODY forward_pack

PROCEDURE calc_rating(. . .); -- forward declaration

PROCEDURE award_bonus(. . .)

IS -- subprograms defined

BEGIN -- in alphabetical order

calc_rating(. . .);

. . .

END;

PROCEDURE calc_rating(. . .)

BEGIN

. . .

END;

END forward_pack;

11) What are triggers?

---- triggers are similar to procedures, in that they are the named pl/sql blocks with declarative, executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.

---- A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.

---- The triggering event is a DML (insert, update, delete) operations on a data base table

----- fires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or database

Trigger timing : 1) before

2) after

3) instead of ( this is used for views)

Triggering events : 1) insert

2) update

3) delete

Trigger type : 1) Statement level

---it execute once for each row in a transaction.

2) Row level

---it execute once for each transaction.

Firing sequence of database triggers

1) before statement trigger

2) before row trigger

3) after row trigger

4) after statement trigger

Ex: 1) Create or replace trigger secure_emp

Before

Insert on emp

Begin

If (to_char(sysdate,’dy’) in(‘sat’,’sun’)) or

To_char(sysdate,’hh24:mi’)

Not between ’08:00’ and ’18:00’)

Then raise_application_error(-20500,’u can insert in the office timings’)

End if;

End;

Ex :- 2) write a program to all transitions with name smith?

Create or replace

Trigger trigger_name

Before insert or update or delete

On emp

For each row

When (old.ename =’smith’ or

New.ename =’smith’)

Begin

Raise_application_error(-20003,’smith’);

End;

12) Difference between triggers and procedures?

Defined with create trigger	Defined with create procedure
The data dictionary contains source code in the user_triggers.	Data dictionary contains source code in user_source
Implicitly invoked	Explicitly invoked
Commit, save point and rollback are not allowed(TCL)	Those are allowed

13) LOCKS?

-- Is to reduce concurrency

1) share lock

---it allows the other users for only reading not to insert or update or delete.

2) exclusive lock

--- only one user can have the privileges of insert or update and delete of particular object

--- others can only read.

3) update lock

----multiple user can read, update delete

Lock levels :

1) table level 2) table space 3) data base level.

14) Composite Datatypes :

– PL/SQL TABLES

– PL/SQL RECORDS

- Nested TABLE

- VARRAY

NESTED TABLES:

--- A nested table is a table with in a table, a nested table is a collection of rows, represented as a column with in the main table. For each record with in main table.

VARYING ARRAY:

--- A varying array is a set of objects, each with the same data types, the size of the array is limited when it is created.

PL/SQL TABLES:

1) Objects of type tables are called pl/sql tables. pl/sql table help you move bulk data.

2) They can store columns or rows and they can be passed as parameters.

3) They make it easy to move collections of data into and out of database tables or between client side applications and stored procedures.

Ex: must be declared in two steps

1) we define a table type

2) we declare a pl/sql table of that type

Declare

step1: Type < name of type> is table of < col-def>

Index by binary-integer

step2: <pl/sql_table name> <Type name?

15) DYNAMIC SQL

Dynamic sql are stored in character string, built in your program at run time. Such string must contain the valid sql statement or pl/sql block and also contain place holders for bind arguments. A place holder is an undeclared identifier.

It is a programming technique that you build sql statements dynamically at runtime

Ex:

1) Dynamic sql you create a procedure that operates on a table whose name is not know until runtime.

2) You create a table, but table name is decided only at runtime then you can use of DBMS-SQL Package.

a) Using the DBMS-SQL package.

---DBMS-SQL package is used to write dynamic sql in stored procedure and to parse DDL statements.

---some of the procedure and function of the package include.

Open_cursor

---opens a new cursor and assign a cursor id number

Parse

---parse the DDL or DML statements

Bind_variables

---binds the given value to the variable identified by its name in the

parsed statements in the given cursor.

Execute

---execute the sql statements and return the number of rows processed.

Fetch_rows

---returns a row for the specified cursor (for multiple rows call in a loop)

Close_cursor

---close the specified cursor.

Ex:

Create or replace procedure delete_all_rows (

p_tab_name in varchar2,

P_rows_del out number)

Cursor_name interger;

Begin

Cursor_name := BDMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_name,

’delete from’ || p_tab_name,

DBMS_SQL.NATIVE);

P_rows_del :=DBMS_SQL.EXECUTE (cursor_name);

DBMS_SQL.CLOSE_CRSOR(cursor_name)

End;

Output:

Variable deleted number

Execute delete_all_rows(‘employee’, :deleted)

Print deleted;

The process of the above program

1) use open_cursor to establish an area in memory to process a sql statement

2) use parse to establish the validity of the sql statements.

3) use the execute function to run the sql statements, this function return the

number of rows processed.

4) use close_cursor to close the cursor.

B) Dynamic sql using EXECUTE IMMIDIATE:

Create or replace procedure delete_rows (

p_tab_name in varchar2,

P_rows_deld out number)

Begin

Execute immediate ‘delete from’ || p_table_name;

P_rows_deld := sql%rowcount;

End;

Output:

Variable deleted number

Execute delete_rows(‘test_employee’, :deleted)

Print deleted;

C) Using the DBMS_DDL package:

---Provides access to some SQL.DDL statements from stored procedures

---includes some procedures

Ex:-

1) Alter_compile(object_type,owner,object_name)

DBMS_DDL.alter_compile(‘procedure’,’a_user’,’query_emp’)

2) analyze_object (object_type, owner, name, method)

DBMS_DDL.analyze_object (‘table’,’a_user’,’jobs’,’compute’);

D) Using DBMS_OUTPUT package:

--- the DBMS_OUTPUT package enables you to output message from pl/sql block, available procedure include.

1) put

--appends text from the procedure to the current line of the line out output buffer

2) newline

--places an end-of-line marker in the output buffer

3) put_line

--combines the action of put and new-line

4) get_line

--retrieve the current line from the output buffer into the procedure

5) get_lines

--retrieves any array of lines from the output buffer in to the procedure

6) enable/disable

--enable/disable calls to the DBMS_OUTPUT procedure.

16) Can u define exceptions twice in same block?

17) Can you have two functions with the same name in a pl/sql block?

Yes

18) Can you have two stored functions with in the same name?

Yes

19) Can function be overload?

Yes

20)What is the maximum number of statements that can be specified in a trigger statement?

One.

Some queries

1) Display particular record using row id?

Select *

From emp

Where row id in (select max (row id)

From emp

Group by empno;

1) To remove duplicate records from table?

Delete from emp

Where row id not in (select max (row id)

from emp

group by empno;

2) To display the alter native records?

Select *

from emp

where row id in (select decode (mod( row num,2),0,row id)

from emp;

3) Display the last N records?

Select *

From emp x

Where x > (select count(*)

from emp

where y.row id < x.row id);

5) write a query which return a day of a week for any date enter by user?

Select to_char(sysdate,’day’) from dual;

4) To see the description of tables and views?

Select * from dictionary;

5) To display all priviligers for an user?

Select * from session_priviliages;

6) To display the max salary?

Select sal

from emp

where sal =(select max(sal)

from emp

where level =1 connect byprior sal>sal);

7) to display the particular record?

Select emp.*,row num from emp where row num <=11)

Minus

Select emp.*,row num from emp where row num <=10)

8) To get 2^nd highest sal?

Select * from emp e1

where 1 = (select count (*)

from emp e2

where e2.sal > e1.sal);

9) To get 3^nd highest sal?

Select * from emp e1

where 2> = (select count (*)

from emp e2

where e2.sal > e1.sal);

BULK COLLECTS:

BULK COLLECT is used to fetch large volumes of data. Bulk Bind reduces the number of context switches between sql and plsql server.

eg: 1

PROCEDURE process_all_rows
IS
TYPE employees_aat
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;

FOR indx IN 1 .. l_employees.COUNT
LOOP
analyze_compensation
(l_employees(indx));
END LOOP;
END process_all_rows;

eg2:

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
CURSOR employees_cur
IS
SELECT * FROM employees;

TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;

l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT limit_in;

FOR indx IN 1 .. l_employees.COUNT
LOOP
analyze_compensation (l_employees(indx));
END LOOP;

EXIT WHEN l_employees.COUNT < limit_in;

END LOOP;

CLOSE employees_cur;
END process_all_rows;

eg3: SQL%BULK_ROWCOUNT

DECLARE
TYPE salarios IS TABLE OF NUMBER;
lv_sal SALARIOS := salarios(2100, 2200, 2500, 2600);
BEGIN
FORALL i IN lv_sal.FIRST..lv_sal.LAST
UPDATE employees
SET salary = salary*2
WHERE salary = lv_sal(i);

dbms_output.put_line('Quantidade total de linhas modificadas: '||SQL%ROWCOUNT);
FOR i IN lv_sal.FIRST..lv_sal.LAST LOOP
dbms_output.put('Quantidade de linhas afetadas com salário de ');
dbms_output.put(lv_sal(i));
dbms_output.put_line(' foram: '||SQL%BULK_ROWCOUNT(i));
END LOOP;
END;

eg4: Save Exceptions- Bulk Exceptions

DECLARE
TYPE tabela IS TABLE OF NUMBER;
lv_numeros TABELA := tabela(1,2,3,3,5,6,6);
lv_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(lv_exception, -24381);
BEGIN
FORALL i IN lv_numeros.FIRST..lv_numeros.LAST SAVE EXCEPTIONS
INSERT INTO bulk_ex VALUES(lv_numeros(i));

EXCEPTION
WHEN lv_exception THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
dbms_output.put('Erro número '||i);
dbms_output.put(' no index: '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(' com a seguinte mensagem: '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type.

EXISTS(n) - Returns TRUE if the specified element exists.
COUNT - Returns the number of elements in the collection.
LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
FIRST - Returns the index of the first element in the collection.
LAST - Returns the index of the last element in the collection.
PRIOR(n) - Returns the index of the element prior to the specified element.
NEXT(n) - Returns the index of the next element after the specified element.
EXTEND - Appends a single null element to the collection.
EXTEND(n) - Appends n null elements to the collection.
EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
TRIM - Removes a single element from the end of the collection.
TRIM(n) - Removes n elements from the end of the collection.
DELETE - Removes all elements from the collection.
DELETE(n) - Removes element n from the collection.
DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.d

Oracle Applications

Pages

Sunday, May 28, 2017

PLSQL Queries

PL/SQL TABLES:

No comments:

Post a Comment