PL/SQL

pl/sql is a blocked programming languages.  the declaration block lets you define datatypes, structures and variables. the execution block lets you process data. you can also nest anonymous-block programs inside the execution block. pass-by-reference subroutines define formal  parameters using in and out or out-only modes. the pipelined clause provides improved performance when functions return collections, like nested tables or varrays. procedures act like functions in many ways but can not return a datatype. database trigger are specialized stored programs that are triggered by events in the database.  there are two composite generalized datatypes: records and collections. the next two subsections describe the declaration of records and collection. the associative array is also known as a pl/sql table or an index-by table. elements are of the same type and use a sequential numeric index. the  nested table can grow in size after it is declared. objects requires an explicit construction. the associative array datatyes is the most like a traditional c/c++ linked list. memory is implicitly allocated during assignment to an associative array. you create a wealy types reference cursor by defining it without a return type. a strongly types reference cursor has a defined return type. you can also create implicit cursors whenever you use a select statement with into or bulk collect into clauses,or you embed a select statement inside a cursor for loop statement. every sql statement in a pl/sql block is actual an implicit cursor. you can see how many rows are changed by any statement using the %rowcount attribute after a data manipulation language statement.  the select into statement is present in all implicit cursors that query data. it is a weakness that many developers attemp to exploit by using it to raise exceptions when cursors return more than one row.  the second is to write an embedded query in a cursor for loop rather than defined in a declaration block. the cursor index for a cursor for loop is a pointer to result set in a query work area. a query work area is a memory region in the oracle database process global area. dynamic select statements act like parameterized subroutines. they run different queries each time depending on the actual parameters provided when they are opened. explicit cursors require you to open,fetch and close them whether you are using simple or while loops or cursors for loop statements. you use the opene statement to open cursor,the fetch statement to fetch records from cursors and the close statement to close and release resources of cursors. there are two prototypes for the fetch statement. one assign individual columns to variables and the other assigns a row to a record structure. only the select statement uses variables that change the query behavior.you will find two types of errors in pl/sql in pl/sql: compilation errors and run-time errors. compilation errors occur when you make an error typing the program or defining the program. the typing errors . you publish functions and procedures as standalone units or within packages and object types. packages and objects tyes serves as libarry containers in the database. functions and procedures are named pl/sql blocks. they have headers in place of the declare statement. the header defines the function or procedure name, a list of formal parameters and a return datatype for for function.  pass-by -value functions receive copies of values when they are called. these functions retrun a single output variable upon completion. pass-by-reference functions receive copies of values when they are called unless you override the default behavior by using the nocopy hint. the nocopy hint only works with certain functions that meet restrictive criteria. like functions, procedures can also contain nested named blocks. nested named blocks are local functions and procedures that you define in the declaration block. you can likewise nest anoymous blocks in the execution block or procedures. current-user override the default and set the execution authority to invoker right. invoker right authority means that you call procedures to act on your local data.and it requires that you replicate data objects in any participating schema. the declaration block is between the is and begin phrase. Collections of sql datatype work in both sql and pl/sql environments but collections of pl/sql . collections fall into two categories:array and list. arrays typically have a physical size allocated when you define them while lists have no physical limited imposed. varrays are single -dimensional structures. you can use varrays in table,record and object definitions and you can access them in sql or pl/sql. nested tables are single -dimensional structure of sql or pl/sql datatypes. large objects are powerful data structures let you store text,images,music and video in the database. pakeage specifications publicsh the declaration and package bodies implements the declaration.package are stored libaries in the database. as with functions and procedures, you can declare variables,types and components in your package specification or body. overloading means that you create more than one function or procedure with the same identifier but different signatures. function and procedures are defined by their repective formal parameter lists. an overloaded component differs in either the number of parameters or the datatypes of parameters in the repective positions.formal parameters are only unique by position and datatype. the package specification declares a package as a blackbox to a user's schema. the declaration publishes the avaible functions and procedures. after compiling a package specification, you can use the sql*plus describe command to see the functions and procedures inside the a package.database tiggers are specilazed stored programes . database tiggers differ from stored functions and procedures because you can not call them directly. database tiggers are fired when a triggering event occurs in the database. this makes them very powerful tools in your efforts to manage the database.you are able to limit or redirect actions through triggers. these triggers enables you to stop performance of a dml statement and redirect the dml statement. functions and procedures present different problems because they control all operations on their run-time variables.