Q: What is PL/SQL?
Ans: PL/SQL stands for Procedural Language extension of SQL.
Q: Where do we store a PL/SQL code?
Ans: In database and in client system as well.
Q: Is it possible to have two procedures in a same package with same name?
Ans: Yes, until you have these procedures accept different variables with different datatypes.
Q: A new procedure is added to a package body and it is not added to spedification. On combilation will this package be valid or invalid?
Ans: Valid. A procedure if not placed in the package specification doesnt lead the package to invalid state. It makes the procedure as private, ie this particular procedure cannot be called from any other package.
Q: How can we declare a procedure as private?
Ans: As explained above.
Q. What is a collection?
Ans:
Ordered group of elements, all of the same type, similar to lists and arrays.
Q: What is Record?
Ans:
A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns.
Q: What are different types of Collections in PL/SQL?
Ans:
Nested Tables
Associative Arrays; similar to hash table
Variable-size arrays.
Q: What is a Subprogram?
Ans:
A named pl/sql block.
Q: What are different types of subprograms available in PL/SQL?
Ans:
Procedures and Functions.
Q: What is the difference between a procedure and function?
Ans:
Procedure does an action wheras function computes and return a value.
Q: What are the different blocks in PL/SQL?
Ans:
Declarative
Execution
Exception
Q: What all are the things that you can declare in Declaration section?
Ans:
Types
Cursors
Constants
Exceptions
Nested Subprograms
Variables
Q: How does nested table differ from arrays?
Ans:
1. Nested tables does not have declared number of elements whereas arrays has. Size of nested tables will increase dynamically.
2. Nested tables might not have consecutive subscripts whereas arrays always has.
You can delete elements from a nested table using the built-in procedure DELETE.
The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.
Q: What are the PL/SQL datatypes used to define collections?
Ans:
TABLE and VARRAY.
Q: How many types of tables are available in Oracle?
Ans:
Three Types:
Relational table (eg: employee table to hold employee data.)
Object tables
XMLType tables
Q: Different type of table level constraints?
Ans:
-Primary Key
-Unique Key
-Check
-Foriegn
Q: When do you use compressed tables?
Ans:
For some applications, particularly data warehousing, with large tables that are frequently queried but very rarely updated, you may create compressed tables. These require less disk storage than uncompressed tables (which are the default).
Q: What is the syntax for IF THEN ELSE statement in PL/SQL?
IF department=1 THEN
result:= 'Accounts'
ELSIF department='2' THEN
result:='Finance'
ELSE
result:='Miscellaneous'
END IF;
Q: What is the syntax for CASE statement in PL/SQL?
Ans: CASE perfoms the same function of IF-THEN-ELSE statement.
The sysntax is:
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Ans: PL/SQL stands for Procedural Language extension of SQL.
Q: Where do we store a PL/SQL code?
Ans: In database and in client system as well.
Q: Is it possible to have two procedures in a same package with same name?
Ans: Yes, until you have these procedures accept different variables with different datatypes.
Q: A new procedure is added to a package body and it is not added to spedification. On combilation will this package be valid or invalid?
Ans: Valid. A procedure if not placed in the package specification doesnt lead the package to invalid state. It makes the procedure as private, ie this particular procedure cannot be called from any other package.
Q: How can we declare a procedure as private?
Ans: As explained above.
Q. What is a collection?
Ans:
Ordered group of elements, all of the same type, similar to lists and arrays.
Q: What is Record?
Ans:
A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns.
Q: What are different types of Collections in PL/SQL?
Ans:
Nested Tables
Associative Arrays; similar to hash table
Variable-size arrays.
Q: What is a Subprogram?
Ans:
A named pl/sql block.
Q: What are different types of subprograms available in PL/SQL?
Ans:
Procedures and Functions.
Q: What is the difference between a procedure and function?
Ans:
Procedure does an action wheras function computes and return a value.
Q: What are the different blocks in PL/SQL?
Ans:
Declarative
Execution
Exception
Q: What all are the things that you can declare in Declaration section?
Ans:
Types
Cursors
Constants
Exceptions
Nested Subprograms
Variables
Q: How does nested table differ from arrays?
Ans:
1. Nested tables does not have declared number of elements whereas arrays has. Size of nested tables will increase dynamically.
2. Nested tables might not have consecutive subscripts whereas arrays always has.
You can delete elements from a nested table using the built-in procedure DELETE.
The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.
Q: What are the PL/SQL datatypes used to define collections?
Ans:
TABLE and VARRAY.
Q: How many types of tables are available in Oracle?
Ans:
Three Types:
Relational table (eg: employee table to hold employee data.)
Object tables
XMLType tables
Q: Different type of table level constraints?
Ans:
-Primary Key
-Unique Key
-Check
-Foriegn
Q: When do you use compressed tables?
Ans:
For some applications, particularly data warehousing, with large tables that are frequently queried but very rarely updated, you may create compressed tables. These require less disk storage than uncompressed tables (which are the default).
Q: What is the syntax for IF THEN ELSE statement in PL/SQL?
IF department=1 THEN
result:= 'Accounts'
ELSIF department='2' THEN
result:='Finance'
ELSE
result:='Miscellaneous'
END IF;
Q: What is the syntax for CASE statement in PL/SQL?
Ans: CASE perfoms the same function of IF-THEN-ELSE statement.
The sysntax is:
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Q: What are all the advantages of PLSQL?
Ans:
1. Tightly integrated with SQL
a) You can perform all actions done by SQL with PLSQL
b) PLSQL fully support SQL datatypes.
2. High Performance
a) You can send a block of sql statements to the database, thereby reducing traffic.
3. Productivity
3. Portability & Scalability & Manageability
4. Support for Object-Oriented-Programming, Developing Web Applications, Developing Server Pages
Q: What is the difference between Static and Dynamic SQL?
Ans:
Static SQL is SQL whose full text is known at compilation time.
Dynamic SQL is SQL whose full text is known only at run time.
Ans:
1. Tightly integrated with SQL
a) You can perform all actions done by SQL with PLSQL
b) PLSQL fully support SQL datatypes.
2. High Performance
a) You can send a block of sql statements to the database, thereby reducing traffic.
3. Productivity
3. Portability & Scalability & Manageability
4. Support for Object-Oriented-Programming, Developing Web Applications, Developing Server Pages
Q: What is the difference between Static and Dynamic SQL?
Ans:
Static SQL is SQL whose full text is known at compilation time.
Dynamic SQL is SQL whose full text is known only at run time.
No comments:
Post a Comment