Start Discovering Solved Questions and Your Course Assignments
TextBooks Included
Active Tutors
Asked Questions
Answered Questions
explicit cursorsthe set of rows returned by the query can include zero one or multiple rows depending on how many rows meet your search criteria
managing cursorsthe plsql uses 2 types of cursors implicit and explicit the plsql declares a cursor implicitly for all the sql data manipulation
row operatorsthe row operators return or reference the particular rows all retains the duplicate rows in the result of a query or in an aggregate
set operatorsthe set operators combine the results of the two queries into one result the intersect returns all the distinct rows selected by both
sql operatorsthe plsql uses all the sql set comparison and row operators in the sql statements this part briefly describes some of these
rownumthe rownum returns a number representing the order in which a row was selected from the table the first row selected has a rownum of 1 the
rowidthe rowid returns the rowid binary address of a row in the database table you can use the variables of the type urowid to store rowids in a
levelyou use the level with the select connect by statement to categorize rows from a database table into a tree structure the level returns the
currval and nextvalthe series is a schema object which generates the sequential numbers whenever you form a sequence you can specify its primary
sql pseudocolumnsthe plsql recognizes the following sql pseudocolumns that returns the specific data items level nextval currval rowid amp rownum the
sql functionsthe plsql uses all the sql functions involving the following aggregate functions that summarize the whole columns of the oracle data
transaction controlthe oracle is transaction oriented that is oracle uses the transactions to make sure the data integrity the transaction is a
referencing recordsunlike the elements in a collection that are accessed using subscripts the fields in a record are accessed by name to reference an
initializing recordsthe illustration below shows that you can initialize a record in its type definition whenever you declare a record of the type
declaring recordswhenever you define a record type you may declare records of that type as the illustration showsdeclaretype stockitem is record
defining and declaring recordsto create records you have to define a record type and then declare records of that type you may also define record
what is a record a record is a group of related data items that stored in the fields each with its own name and datatype assume that you have
using cursor attributesto process the sql data manipulation statements the sql engine must opens an implicit cursor named sql this cursors attributes
using host arraysthe client-side programs can use anonymous plsql blocks to bulk-bind input and output host arrays however this is the well-organized
using forall and bulk collect togetheryou can unite the bulk collect clause with the forall statement in that case the sql engine bulk-binds column
bulk fetchingthe illustration below shows that you can bulk-fetch from a cursor into one or more collectionsdeclaretype nametab is table of
using the bulk collect clausethe keywords bulk collect specify the sql engine to bulk-bind output collections before returning them to the plsql
rollback behaviorwhen a forall statement fails the database changes are rolled back to an implicit savepoint marked before each of the sql statement
using the forall statementthe keyword forall instruct the plsql engine to bulk-bind input collections before sending them all to the sql engine
how bulk binds improve performancethe assigning of values to the plsql variables in sql statements is known as bindingthe binding of the whole