SQL	SQL is a natural language which is very useful for interactive processing. No procedural capabilities like condition testing, looping is offered by SQL. All SQL statements are executed by the database server one at a time, thus it is a time-consuming process. No error handling procedures are there in SQL.
PL/SQL	PL/SQL is a procedural extension of Oracle - SQL. PL/SQL supports procedural capabilities as well as high language features such as conditional statements, looping statements, etc. PL/SQL statements send the entire block of statements to the database server at the same time, thus network traffic is reduced considerably. PL/SQL supports customized error handling.
characteristics of PL/SQL?.	PL/SQL allows access and sharing of the same subprograms by multiple applications. It is known for the portability of code as code can be executed on any operating system provided that Oracle is loaded on it. With PL/SQL users can write their own customized error handling routines. Improved transaction performance with integration to Oracle data dictionary.
What are the data types available in PL/SQL?.	Data types define the ways to identify the type of data and their associated operations.
What are the 4 data types in PL/SQL?.	Scalar Data Types, Composite Data Types, Reference Data Types, Large Object Data Types
What is a Scalar Data Type?	A scalar data type is an atomic data type that does not have any internal components.
Tell us some examples of Scalar Data Type?.	CHAR (fixed-length character value range between 1 and 32,767 characters), VARCHAR2 (variable length character value range between 1 and 32,767 characters), NUMBER ( fixed-decimal, floating-decimal or integer values), BOOLEAN ( logical data type for TRUE FALSE or NULL values), DATE (stores date and time information) LONG (character data of variable length).
What is a Composite Data Types.	A composite data type is made up of other data types and internal components that can be easily used and manipulated.
Tell us some examples of Composite Data Type.	RECORD, TABLE, and VARRAY
What is a Reference Data Types.	A reference data type holds values, called pointers that designate to other program items or data items.
Tell us some examples of Reference Data Types.	REF CURSOR.
What is a Large Object Data Types.	A Large Object datatype holds values, called locators, that defines the location of large objects( such as video clips, graphic image, etc) stored out of line.
Tell us some examples of Large Object Data Types.	BFILE (Binary file), BLOB (Binary large object), CLOB ( Character large object), NCLOB( NCHAR type large object)
Explain the purpose of %TYPE and %ROWTYPE data types?.	PL/SQL uses the %TYPE declaration attribute for anchoring. This attribute provides the datatype of a variable, constant or column. %TYPE attribute is useful while declaring a variable that has the same datatype as a table column.
Tell an example for the purpose of %TYPE	the variable m_empno has the same data type and size as the column empno in table emp. m_empno emp.empno%TYPE;
Explain the purpose of %ROWTYPE data types?.	%ROWTYPE attribute is used to declare a variable to be a record having the same structure as a row in a table. The row is defined as a record and its fields have the same names and data types as the columns in the table or view.
Tell an example for the purpose of %ROWTYPE data types,	dept_rec dept%ROWTYPE; This declares a record that can store an entire row for the DEPT table.
What do you understand by PL/SQL packages?.	PL/SQL packages are schema objects that group functions, stored procedures, cursors and variables at one place. Packages have 2 mandatory parts. Package Specifications and Package body.
What do you understand by PL/SQL cursors?.	PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements. PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a program.
How many types of cursor there are?	There are two types of cursors implicit and explicit.
What is an Explicit Cursor?.	For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed
Declare an explicit cursor.	Syntax. CURSOR <cursor_name> is SELECT statement; Here, <cursor_name> is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.
Open an explicit cursor.	Syntax. OPEN <cursor_nam>; Where, <cursor_name> is the name of the previously defined cursor.
Fetch rows from an explicit cursor.	Syntax. FETCH <cursor_name> INTO <record_list>; Here, <cursor_name> refers to the name of the previously defined cursor from which rows are being fetched. <record_list> represents the list of variables that will receive the data being fetched.
Closing an explicit cursor.	Syntax. CLOSE <cursor_name>; Here, <cursor_name> is the name of the cursor being closed.
What is an Implicit cursor.	When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors. For the following statements, PL/SQL employs implicit cursors. INSERT, UPDATE, DELETE, SELECT ( queries that return exactly one row)
When do we use triggers?.	The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when the database-related event is performed.
Triggers are mainly required for the following purposes.	To maintain complex integrity constraints Auditing table information by recording the changes Signaling other program actions when changes are made to the table Enforcing complex business rules Preventing invalid transactions
Explain the difference in the execution of triggers and stored procedures?.	A stored procedure is executed explicitly by issuing a procedure call statement from another block via a procedure call with arguments. The trigger is executed implicitly whenever any triggering event like the occurrence of DML statements happens.
Triggers	Only affect those rows added after the trigger is enabled. Triggers are used to implement complex business rules which cannot be implemented using integrity constraints.
Constraints	Affect all rows of the table including that already exist when the constraint is enabled. Constraints maintain the integrity of the database.
What is a PL/SQL block?.	In PL/SQL, statements are grouped into units called Blocks. PL/SQL blocks can include constants, variables, SQL statements, loops, conditional statements, exception handling. Blocks can also build a procedure, a function or a package.
PL/SQL blocks are two types.	Anonymous and Named
Anonymous blocks.	PL/SQL blocks without header are known as anonymous blocks. These blocks do not form the body of a procedure, function or triggers.
Anonymous blocks. Example	DECLARE num NUMBER(2); sq NUMBER(3); BEGIN num.= &Number1; sq .= num*num; DBMS_OUTPUT.PUT_LINE(‘Square.’ \|\|sq); END;
Named blocks.	PL/SQL blocks having header or labels are known as Named blocks. Named blocks can either be subprograms (procedures, functions, packages) or Triggers.
Named blocks. Example	FUNCTION sqr (num IN NUMBER) RETURN NUMBER is sq NUMBER(2); BEGIN sq.= num*num; RETURN sq; END;
Syntax errors	are the one which can be easily identified by a PL/SQL compiler. These errors can be a spelling mistake, etc.
Runtime errors	are those errors in PL/SQL block for which an exception handling section is to be included for handling the errors. These errors can be SELECT INTO statement which does not return any rows.
What are COMMIT, ROLLBACK, and SAVEPOINT?.	COMMIT, SAVEPOINT, and ROLLBACK are three transaction specifications available in PL/SQL.
COMMIT statement.	When DML operation is performed, it only manipulates data in database buffer and the database remains unaffected by these changes. To save/store these transaction changes to the database, we need to COMMIT the transaction. COMMIT transaction saves all outstanding changes since the last COMMIT and the following process happens Affected rows locks are released Transaction marked as complete Transaction detail is stored in the data dictionary.
ROLLBACK statement.	When we want to undo or erase all the changes that have occurred in the current transaction so far, we require to be rolled back of the transaction. In other words, ROLLBACK erases all outstanding changes since the last COMMIT or ROLLBACK.
SAVEPOINT statement.	The SAVEPOINT statement gives a name and marks a point in the processing of the current transaction. The changes and locks that have occurred before the SAVEPOINT in the transaction are preserved while those that occur after the SAVEPOINT are released.
What is the mutating table and constraining table?.	A table that is currently being modified by a DML statement like defining triggers in a table is known as a Mutating table. A table that might need to be read from for a referential integrity constraint is known as constraining table.
What are actual parameters and formal parameters?	The variables or an expression referred to as parameters that appear in the procedure call statement is known as Actual parameters.
What is the difference between ROLLBACK and ROLLBACK TO statements?.	The transaction is completely ended after ROLLBACK statement i.e. ROLLBACK command completely undo a transaction and release all locks. On the other hand, a transaction is still active and running after ROLLBACK TO command as it undo only a part of the transaction up till the given SAVEPOINT.
IN parameters.	IN parameters allow you to pass values to the procedure being called and can be initialized to default values. IN parameters acts like a constant and cannot be assigned any value.
OUT parameters.	OUT parameters return value to the caller and they must be specified. OUT parameters act like an uninitialized variable and cannot be used in an expression.
IN OUT parameters.	IN OUT parameters passes initial values to a procedure and return updated values to the caller. IN OUT parameters act like an initialized variable and should be assigned a value.
Why is %ISOPEN always false for an implicit cursor?	An implicit cursor, SQL%ISOPEN attribute is always false because the implicit cursor is opened for a DML statement and is closed immediately after the execution of the DML statement.
When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?	The outcome of the statement is saved in 4 cursor attributes. These are. SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT SQL%ISOPEN
What are the ways of commenting in a PL/SQL code?	Comments are the text which is included with the code to enhance readability and for the understanding of the reader. These codes are never executed. /**/ --
What do you understand by Exception handling in PL/SQL?	When an error occurs in PL/SQL, the exception is raised. In other words, to handle undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section.
3 types of EXCEPTION.	Predefined Exceptions. Undefined Exceptions. User-defined Exceptions.
Predefined Exceptions.	Common errors with predefined names.
Undefined Exceptions.	Less common errors with no predefined names.
User-defined Exceptions.	Do not cause runtime error but violate business rules.
Enlist some predefined exceptions?	NO_DATA_FOUND. TOO_MANY_ROWS. INVALID_CURSOR. ZERO_DIVIDE. A
NO_DATA_FOUND.	Single row SELECT statement where no data is returned.
TOO_MANY_ROWS.	Single row SELECT statement where more than one rows are returned.
INVALID_CURSOR.	Illegal cursor operation occurred.
ZERO_DIVIDE.	Attempted to divide by zero
What are PL/SQL cursor exceptions?	The exceptions related to PL/SQL cursors are. CURSOR_ALREADY_OPEN INVALID_CURSOR
Explain the difference between cursor declared in procedures and cursors declared in the package specification?	The cursor declared in the procedure is treated as local and thus cannot be accessed by other procedures. The cursor declared in the package specification is treated as global and thus can be accessed by other procedures.
What are INSTEAD OF triggers?	The INSTEAD OF triggers are the triggers written especially for modifying views, which cannot be directly modified through SQL DML statements.
What are expressions?	Expressions are represented by a sequence of literals and variables that are separated by operators. In PL/SQL, operations are used to manipulate, compare and calculate some data. An expression is a composition of ‘Operators’ and ‘Operands’.
Operands.	These are an argument to the operators. Operands can be a variable, function call or constant.
Operators.	These specify the actions to be performed on operators. Example. ‘+’, ‘*’, etc.
List different type of expressions with the example.	Numeric or Arithmetic expressions . Boolean expressions. String expressions. Date expressions.
Numeric or Arithmetic expressions .	20* 10+ 15
Boolean expressions.	‘spot’ LIKE ‘sp%t’
String expressions.	LENGTH (‘NEW YORK’\|\| ‘NY’)
Date expressions.	SYSDATE>TO_DATE(’15-NOV-16’, “dd-mm-yy”)
What do you understand by PL/SQL Records?	A PL/SQL record can be referred as a collection of values or say, a group of multiple pieces of information, each of which is of simpler types and can be related to one another as fields.
There are three types of records supported in PL/SQL.	Table based records Programmer based records Cursor based records

Descubre

Crear Tarjetas

Acerca de Nosotros

Explore Tarjetas

Comparta este Set de Tarjetas de Aprendizaje

Tarjetas de Aprendizaje relacionadas

Sql Questions

Cómo estudiar sus tarjetas

Rango de Cartas a estudiar

75 Cartas en este set