Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.?Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller
Dynamic SQL is an enhanced form of Structured Query Language (SQL) that, unlike standard (or static) SQL, facilitates the automatic generation and execution of program statements. This can be helpful when it is necessary to write code that can adjust to varying databases, conditions, or servers. It also makes it easier to automate tasks that are repeated many times.
Dynamic SQL statements are stored as strings of characters that are entered when the program runs. They can be entered by the programmer or generated by the program itself, but unlike static SQL statements, they are not embedded in the source program. Also in contrast to static SQL statements, dynamic SQL statements can change from one execution to the next.
Dynamic SQL statements can be written by people with comparatively little programming experience, because the program does most of the actual generation of the code. A potential problem is reduced performance (increased processing time) if there is too much dynamic SQL running at any given time.
1.Normalization is the process of efficiency organizing data in database. 2.A composite key can be defined on a combination of columns. We can define composite keys on entity integrity and referential integrity constraints. Composite key can be defined in table and alter levels only. 3.without group by we don't have having clause,This will work as where clause which can be used only with group by because of absence of where clause in group by.
Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
ReplyDelete2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.?Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller
Difference between Anonymous Block and PL /SQL Block.
ReplyDeleteAnonymous Block:
1. It is an unnamed block.
PL/SQL Block
1. It is a named PL/SQL sub program.
Anonymous Block:
2. It is not stored in the data base.
PL/SQL Block:
2. It is stored in the data base.
Anonymous Block:
3. It is not a schema object.
PL/SQL Block:
3. It is a schema object.
Anonymous Block:
4. It is a one time only procedure.
PL/SQL Block:
4. It is complied and stored in the data base for repeated execution.
Anonymous Block:
5. It can not accept arguments.
PL/SQL Block:
5. It can accept arguments.
Anonymous Block:
6. It can not return values.
PL/SQL Block:
6. It can return values.
Anonymous Block:
7. We can execute it directly.
PL/SQL Block:
7. We need to execute it separately with one more begin and end.
Anonymous Block:
8. It can be used temporarily.
PL/SQL Block:
8. It can be accessed permanently until block exists.
what is dynamic sql
ReplyDeleteDynamic SQL is an enhanced form of Structured Query Language (SQL) that, unlike standard (or static) SQL, facilitates the automatic generation and execution of program statements. This can be helpful when it is necessary to write code that can adjust to varying databases, conditions, or servers. It also makes it easier to automate tasks that are repeated many times.
DeleteDynamic SQL statements are stored as strings of characters that are entered when the program runs. They can be entered by the programmer or generated by the program itself, but unlike static SQL statements, they are not embedded in the source program. Also in contrast to static SQL statements, dynamic SQL statements can change from one execution to the next.
Dynamic SQL statements can be written by people with comparatively little programming experience, because the program does most of the actual generation of the code. A potential problem is reduced performance (increased processing time) if there is too much dynamic SQL running at any given time.
Pls give example of using synonyms in index.
ReplyDeletewe din't have create synonym on index
Delete1) what is meant by normalization?
ReplyDelete2) what is meant by composite primary key ?
3) without ' group by ' can we have 'having' ?
4) what is meant by pl/sql collections?
5) what is oracle supplied packages?
6) explain execute immediate :-
7)will the base table gets affected in a view ?
8) how to search character in a string with out using ' like ' keyword ?
9) how many ' long ' data type allowed for a table ?
10) can we set primary key constraint for a null value column? reason?
11) how can we select only duplicates from a table ?
12) what is the difference between in and exists?
13) what is flash back database ?
14) what are the keyword and function you know in orale to handle null values ?
1.Normalization is the process of efficiency organizing data in database.
Delete2.A composite key can be defined on a combination of columns.
We can define composite keys on entity integrity and referential integrity constraints.
Composite key can be defined in table and alter levels only.
3.without group by we don't have having clause,This will work as where clause which can be used only with group by because of absence of where clause in group by.
we can use commit in trigger ?
ReplyDeletewe can use commit by declaring pragma autonomous_transaction
needs more points