Monday, May 28, 2012

VLSI DESIGN QUESTIONS


VLSI DESIGN NOV/DEC 2007

VLSI DESIGN (2marks & 16 marks with key)
Prepared by
NOORUL ISLAM COLLEGE OF ENGINEERING

VLSI DESIGN (2marks & 16 marks)
Based on units
Prepared by KINGS COLLEGE OF ENGINEERING

Take Over the Screen Saver

Take Over the Screen Saver


Take Over the Screen Saver
To activate and deactivate the screen saver whenever you want, goto the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\ScreenSavers
Now add a new string value and name it Mouse Corners.

click her to expand the post 

Disabling Display of Drives in My Computer


Disabling Display of Drives in My Computer
This is yet another trick you can play on your geek friend. To disable the display of local or networked drives when you click My Computer go to :
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
Now in the right pane create a new DWORD item and name it NoDrives. Now modify it's value and set it to 3FFFFFF (Hexadecimal) Now press F5 to refresh.

click here to expand the post

Ban Shutdowns : A trick to Play on Lamers



Ban Shutdowns : A trick to Play on Lamers

This is a neat trick you can play on that lamer that has a huge ego, in this section I teach you, how to disable the Shut Down option in the Shut Down Dialog Box. This trick involves editing the registry, so please make backups. Launch regedit.exe and go to :
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
In the right pane look for the NoClose Key. If it is not already there then create it by right clicking in the right pane and selecting New > String Value.(Name it NoCloseKey ) Now once you see the NoCloseKey in the right pane, right click on it and select Modify. Then Type 1 in the Value Data Box.
Doing the above on a Win98 system disables the Shut Down option in the Shut Down Dialog Box. But on a Win95 machine if the value of NoCloseKey is set to 1 then click on the Start > Shut Down button displays the following error message:

Click here to read the full Content ...




Exiting Windows the Cool and Quick Way



Exiting Windows the Cool and Quick Way

Normally it takes a hell lot of time just Shutting down Windows, you have to move your mouse to the Start Button, click on it, move it again over Shut Down, click, then move it over the necessary option and click, then move the cursor over the OK button and once again (you guessed it) click.This whole process can be shortened by creating shortcuts on the Desktop which will shut down Windows at the click of a button. Start by creating a new shortcut( right click and select New> Shortcut). Then in the command line box, type (without the quotes.)

Click her to know more ....




Sendmail and Beyond: Tips and Trick

Sendmail and Beyond: Tips and Trick


Welcome to yet another Hacking Truths manual. Although this manual comes after a long break, it is really nice to get back to writing for HT. Anyway, in the past, we have had a number of explanations on how to send forged emails, how to play with the Sendmail daemon, email headers and everything else to do with SMTP (Simple Mail Transfer Protocol) and emails. Although this manual too throws light on related matter, it is however more focused on advanced tips and tricks and other uncommon but extremely useful pieces of information.


Saturday, May 26, 2012

compiler design book pdf

compiler design book pdf

Compilers : Principles, Techniques & Tools By Alfred V.Aho, Monica S.Lam, Ravi Sethi & Jeffrey D.Ullman [Second Edition]

link 1  :  click here  download        

Compilers : Principles, Techniques & Tools By Alfred V.Aho, Ravi Sethi & Jeffrey D.Ullma



how to post in engineer portal or how to post in blogs

how to post in engineer portal:


                                  
                       to view image in larges size right click and load image on new tab










Suggested Mini Projects in Data Structures using C++


Mini Project Specification

1. Write a program that uses a hashing algorithm to create a list of inventory parts and their quantities sold in the past month. After creating the hashed list , write a simple menu driven user interface that allows the user to select from the following options:
a. Search for an inventory item and report its quantity sold
b. Print the inventory parts and their quantities sold
c. Analyze the efficiency of the hashing algorithm

 The parts data are contained in a text file, as shown in the following table. The key is the three digit part number. The quantity represents the units sold during the past month.
 Part Number
Quantity
121
12
123
11
190
43
112
90

2. Simulate a simple dictionary. Assume each character contains at least 10 vocabularies. Create an index page for all characters. Retrieve the word using index value. Assume that the index characters from
a to z.

3. Construct at least five heaps of size varying from 100 to 2000 filling with random numbers. Measure efficiency as the number of data moves in order to build the heap. Analyze the performance of the heaps while construction and produce a comparison report in the form of graph.

4. Consider a binary search tree to store data entities. Randomly generate the data entities and analyze the performance of the tree (insertion, deletion, searching) at various sizes of the tree (Use at least five different sizes of the tree). Produce report in the form of graph

5. Consider a height balanced tree (AVL) to store data entities. Randomly generate the data entities and analyze the performance of the tree (insertion, deletion, searching) at various sizes of the tree (Use at least five different sizes of the tree). Produce report in the form of graph.

6. Construct a linear list to store randomly generated numbers in ascending order. Analyze the performance of the list in terms of insertion at various sizes of the list.

7. Perform quick sort and shell sort for a randomly generated set of elements of sizes 100,200,500 and 800. Calculate the number of moves and comparisons for each sorting. Generate the report as a graph.

8. Generate a computerized telephone directory for a cellular service consisting of name and cell phone number. Present the user with a menu that allows the user to search the directory for a specified record, insert a new record, delete an existing record, or print the entire phone list. Produce the results as a text file.

9. Design a simple search engine to display the possible websites upon entering a search query. Use suitable data structure for storage and retrieval.

10. Write a program that uses the radix sort to sort 1000 random digits. Print the data before and after the sort. Each sort bucket should be a linked list. At the end of the sort, the data should be in the original array

11. An electronics goods dealer has 50 different types of item and for each item he has a maximum of 5 branded company products for sale. Read and store the monthly sales (day wise) of the shop in a multi list and produce the following reports.
List the day wise total sales amount of all goods
List the monthly sale details of refrigerator.
List the monthly sale details of all LG brand electronic goods

12. Construct the splay tree for storing N students Roll No (primary key), name and department then perform delete, modify and search operations on it. Determine the activity count of each node, where activity count is the number of times the node has been visited.

13. Design a stack and queue test driver. A test driver is a program created to test functions that are to be placed in a library. Its primary purpose is to completely test functions
a. Input Stack: used to store all user input
b. Input Queue: used to store all user input
c. Output Stack: used to store data deleted from input queue
d. Output Queue: used to store data deleted from input stack
Use a menu-driven interface that prompts the user to select either to insert or delete. If an insert is requested, the system should prompt the user for the integer to be inserted. The data are then inserted into the input stack and input queue. If a delete is requested , the data are deleted from both structures ; the data popped from the input stack are enqueued in the output queue, and the data dequeued from the input queue are pushed into the output stack

14. Create the index for a text book of at least 100 pages using alphabets.

Hints: Ignore the list following words such as the, there, these, those etc., what, which, where etc., is, are, to, too, also, if, must, can, may, should, so, hence, therefore, because, before, after and commonly used verbs.

15. Construct the Huffman Tree for the given text then, read a line of text and do text compression using the Huffman code. The Huffman code for each letter is derived from a full binary tree called the Huffman tree. Each leaf of the tree corresponds to a letter. The goal is to build a tree such that a letter with high frequency should have low depth. Use Priority queue to maintain the frequencies of each letter. Compute compression ratio for various ASCII text files

16. The details of employees (Emp.Id, Name, Department and Total Years of experience) of a company are to be maintained. The list indicates both alphabetical ordering of names, ascending order of Emp.Id Number and alphabetical ordering of department names .
Perform the following,
·         Insert a new employee detail in the appropriate position
·         Remove an employee detail where Emp.Id is given
·         Find an employee detail whose ID is given.
·         Find employee information whose name in a particular department is given.
·         List all employee detail in order of their name.

17. Our study of tree algorithmic has shown that most tree structures are quite efficient. Let’s examine the efficiency of Binary search tree, AVL tree and B-Tree. Compare the time complexities of the tree data structures (Binary search tree, AVL tree, B-Tree) for various operations (Insertion, deletion and searching). To determine a pattern run your program with arrays filed with random numbers... Prepare a short report of your findings with appropriate tables and graphs.

18. Develop an application that will implement all basic trees related concepts and operations and will show the trees graphically.

19. Develop an application that will implement and trace recursive functions graphically.

20. Design a Student Prerequisite Subjects Management System requires the use of linked list or tree to store different courses and their prerequisites and based on this list it will allow any student to take any course or not.

Monday, May 14, 2012

Sunday, May 13, 2012

XML Tutorial Extensible Markup Language


XML Tutorial  Extensible Markup Language


Extensible Markup Language (XML) is a W3C-recommended general-purpose markup language that supports a wide variety of applications. XML languages or 'dialects' may be designed by anyone and may be processed by conforming software. XML is also designed to be reasonably human-legible, and to this end, terseness was not considered essential in its structure. XML is a simplified subset of Standard Generalized Markup Language (SGML). Its primary purpose is to facilitate the sharing of data across different information systems, particularly systems connected via the Internet. Formally defined languages based on XML (such as RSS, GraphML, XHTML, Scalable Vector Graphics, MusicXML and thousands of other examples) allow diverse software to reliably understand information formatted and passed in these languages.
History

The versatility of SGML for dynamic information display was understood by early digital media publishers in the late 1980s prior to the rise of the Internet. By the mid-1990s some practitioners of SGML had gained experience with the then-new World Wide Web, and believed that SGML offered solutions to some of the problems the Web was likely to face as it grew. Dan Connolly added SGML to the list of W3C's activities when he joined the staff in 1995; work began in mid-1996 when Jon Bosak developed a charter and recruited collaborators. Bosak was well connected in the small community of people who had experience both in SGML and the Web. He received support in his efforts from Microsoft.

XML was designed by a working group of eleven members, supported by an (approximately) 150-member Interest Group. Technical debate took place on the Interest Group mailing list and issues were resolved by consensus or, when that failed, majority vote of the Working Group. The decision record was compiled by Michael Sperberg-McQueen on December 4th 1997. James Clark served as Technical Lead of the Working Group, notably contributing the empty-element "<empty/>" syntax and the name "XML". Other names that had been put forward for consideration included "MAGMA" (Minimal Architecture for Generalized Markup Applications), "SLIM" (Structured Language for Internet Markup) and "MGML" (Minimal Generalized Markup Language). The co-editors of the specification were originally Tim Bray and Michael Sperberg-McQueen. Halfway through the project Bray accepted a consulting engagement with Netscape, provoking vociferous protests from Microsoft. Bray was temporarily asked to resign the editorship. This led to intense dispute in the Working Group, eventually solved by the appointment of Microsoft's Jean Paoli as a third co-editor.
The XML Working Group never met face-to-face; the design was accomplished using a combination of email and weekly teleconferences. The major design decisions were reached in twenty weeks of intense work between July and November of 1996, when the first Working Draft of an XML specification was published. Further design work continued through 1997, and XML 1.0 became a W3C Recommendation on February 10, 1998.

XML 1.0 achieved the Working Group's goals of Internet usability, general-purpose usability, SGML compatibility, facilitation of easy development of processing software, minimization of optional features, legibility, formality, conciseness, and ease of authoring.

Clarifications and minor changes were accumulated in published errata and then incorporated into a Second Edition of the XML 1.0 Recommendation on October 6, 2000. Subsequent errata were incorporated into a Third Edition on February 4, 2004. Also published on the same day as XML 1.0 Third Edition was XML 1.1, a variant of XML that encourages more consistency in how characters are represented and relaxes restrictions on names, allowable characters, and end-of-line representations.

On August 16, 2006, XML 1.0 Fourth Edition and XML 1.1 Second edition were published to incorporate the accumulated errata. Both XML 1.0 Fourth Edition and XML 1.1 Second Edition are considered current versions of XML.

The features of XML

XML provides a text-based means to describe and apply a tree-based structure to information. At its base level, all information manifests as text, interspersed with markup that indicates the information's separation into a hierarchy of character data, container-like elements, and attributes of those elements. In this respect, it is similar to the LISP programming language's S-expressions, which describe tree structures wherein each node may have its own property list.

The fundamental unit in XML is the character, as defined by the Universal Character Set. Characters are combined to form an XML document. The document consists of one or more entities, each of which is typically some portion of the document's characters, stored in a text file.

XML files may be served with a variety of Media types. RFC 3023 defines the types "application/xml" and "text/xml", which say only that the data is in XML, and nothing about its semantics. The use of "text/xml" has been criticized as a potential source of encoding problems but is now in the process of being deprecated. RFC 3023 also recommends that XML-based languages be given media types beginning in "application/" and ending in "+xml"; for example "application/atom+xml" for Atom. This page discusses further XML and MIME.

The ubiquity of text file authoring software (basic text editors such as Notepad and TextEdit as well as word processors) facilitates rapid XML document authoring and maintenance. Prior to the advent of XML, there were very few data description languages that were general-purpose, Internet protocol-friendly, and very easy to learn and author. In fact, most data interchange formats were proprietary, special-purpose, "binary" formats (based foremost on bit sequences rather than characters) that could not be easily shared by different software applications or across different computing platforms, much less authored and maintained in common text editors.

By leaving the names, allowable hierarchy, and meanings of the elements and attributes open and definable by a customizable schema, XML provides a syntactic foundation for the creation of custom, XML-based markup languages. The general syntax of such languages is rigid — documents must adhere to the general rules of XML, assuring that all XML-aware software can at least read (parse) and understand the relative arrangement of information within them. The schema merely supplements the syntax rules with a set of constraints. Schemas typically restrict element and attribute names and their allowable containment hierarchies, such as only allowing an element named 'birthday' to contain 1 element named 'month' and 1 element named 'day', each of which has to contain only character data. The constraints in a schema may also include data type assignments that affect how information is processed; for example, the 'month' element's character data may be defined as being a month according to a particular schema language's conventions, perhaps meaning that it must not only be formatted a certain way, but also must not be processed as if it were some other type of data.

In this way, XML contrasts with HTML, which has an inflexible, single-purpose vocabulary of elements and attributes that, in general, cannot be repurposed. With XML, it is much easier to write software that accesses the document's information, since the data structures are expressed in a formal, relatively simple way.

XML makes no prohibitions on how it is used. Although XML is fundamentally text-based, software quickly emerged to abstract it into other, richer formats, largely through the use of datatype-oriented schemas and object-oriented programming paradigms (in which the document is manipulated as an object). Such software might treat XML as serialized text only when it needs to transmit data over a network, and some software doesn't even do that much. Such uses have led to "binary XML", the relaxed restrictions of XML 1.1, and other proposals that run counter to XML's original spirit and thus garner criticism.

Strengths of XML

* It is a simultaneously human and machine-readable format;
* It supports Unicode, allowing almost any information in any written human language to be communicated;
* It can represent the most general computer science data structures: records, lists and trees;
* Its self-documenting format describes structure and field names as well as specific values;
* The strict syntax and parsing requirements make the necessary parsing algorithms extremely simple, efficient, and consistent.

XML is also heavily used as a format for document storage and processing, both online and offline, and offers several benefits:

* Its robust, logically-verifiable format is based on international standards;
* The hierarchical structure is suitable for most (but not all) types of documents;
* It manifests as plain text files, unencumbered by licenses or restrictions;
* It is platform-independent, thus relatively immune to changes in technology;
* Its predecessor, SGML, has been in use since 1986, so there is extensive experience and software available.

Criticisms of XML

* Its syntax is verbose and redundant. This was intentional and contributes to ease of parsing and error detection, but also poses some problems. Intensively marked-up documents can become so cluttered as to be unreadable; however, there are different interpretations of "unreadable." To some, it means "unreadable in a plain text editor". In this respect, XML is favorable at least when compared to binary formats, which may not even render correctly in a text editor at all. Another interpretation of "unreadable" derives from comparing XML with other text-based formats, such as JSON or YAML; in which case the comparisons entail a certain degree of subjectivity.

* The redundancy may affect application efficiency through higher storage and transmission costs, even though compression is commonly very effective for XML data (precisely because its redundancy leads to low entropy). This is particularly problematic for multimedia applications running on cell phones and PDAs which attempt to use XML to describe images and video.

* Generic XML parsers must be able to recurse arbitrarily nested data structures and may perform additional checks to detect improperly formatted or differently ordered syntax or data (this is because the markup is descriptive and partially redundant, as noted above). This amounts to overhead for very basic uses of XML, which may be significant where resources are scarce - for example in embedded systems. Furthermore, additional security considerations arise when XML input is fed from untrustworthy sources and resource exhaustion or stack overflows are possible.

* Some consider the syntax to contain a number of obscure, unnecessary features born of its legacy of SGML compatibility. However, an effort to settle on a subset called "Minimal XML" led to the discovery that there was no consensus on which features were in fact obscure or unnecessary.

* The basic parsing requirements do not directly support data types; XML provides no specific notion of "integer", "string", "boolean", "date", and so on. For example, there is no provision for mandating that "3.14159" be a floating-point number rather than a seven-character string. Such conversions require processing. Again this is intentional: expressing the quantity ten as the string "10" completely avoids problems such as the integer storage formats of various computers, which also require conversions when data moves between machines. Consequently, this can be an advantage or disadvantage depending on the application. Most XML schema languages add datatype functionality.

* The hierarchical model for representation is limited when compared to the relational model, since it only gives a fixed, monolithic view of the tree structure: for example, either actors under movies, or movies under actors. This is countered with the statement that the hierarchical model for representation is more powerful because it can directly represent recursive and iterative structures without resorting to serial number fields, recursive join operations, and sorting; which model holds the practical advantage depends on the data and operations required. This objection does not apply to the use of XML to serialize relational data, but pertains to the use of XML to express data relations directly, because XML naturally expresses the hierarchical syntax of the SQL query language which is used to serialize relational databases as one method of database backup.

* Expressing overlapping (non-hierarchical) node relationships requires extra effort. Such techniques are a current research topic among researchers interested in XML.

* Mapping XML to the relational or object oriented paradigms is often cumbersome, although the reverse is typically easy. Some take this as evidence that XML's model is indeed the more powerful.

* Some have argued that XML can be used for data storage only if the file is of low volume, but this is only true given particular assumptions about architecture, data, implementation, and other issues. Even very large SGML and XML structures can be accessed very rapidly.

International use

XML supports the direct use of almost any Unicode character in element names, attributes, comments, character data, and processing instructions. Therefore, the following is a well-formed XML document, even though it includes both Chinese and Cyrillic characters:

<?xml version="1.0" encoding="UTF-8"?>
<俄语>Данные</俄语>

Versions of XML

There are two current versions of XML. The first, XML 1.0, was initially defined in 1998. It has undergone minor revisions since then, without being given a new version number, and is currently in its fourth edition, as published on August 16, 2006. It is widely implemented and still recommended for general use. The second, XML 1.1, was initially published on February 4, 2004, the same day as XML 1.0 Third Edition, and is currently in its second edition, as published on August 16, 2006. It contains features — some contentious — that are intended to make XML easier to use in certain cases[13] - mainly enabling the use of line-ending characters used on EBCDIC platforms, and the use of scripts and characters absent from Unicode 2.0. XML 1.1 is not very widely implemented and is recommended for use only by those who need its unique features.

XML 1.0 and XML 1.1 differ in the requirements of characters used for element and attribute names: XML 1.0 only allows characters which are defined in Unicode 2.0, which includes most world scripts, but excludes those which were added in later Unicode versions. Among the excluded scripts are Mongolian, Cambodian, Amharic, Burmese, and others.

Almost any Unicode character can be used in the character data and attribute values of an XML 1.1 document, even if the character is not defined, aside from having a code point, in the current version of Unicode. The approach in XML 1.1 is that only certain characters are forbidden, and everything else is allowed, whereas in XML 1.0, only certain characters are explicitly allowed, thus XML 1.0 cannot accommodate the addition of characters in future versions of Unicode.

In character data and attribute values, XML 1.1 allows the use of more control characters than XML 1.0, but, for "robustness", most of the control characters introduced in XML 1.1 must be expressed as numeric character references. Among the supported control characters in XML 1.1 are two line break codes that must be treated as whitespace. Whitespace characters are the only control codes that can be written directly.

There are also discussions on an XML 2.0, although it remains to be seen if such will ever come about. XML-SW (SW for skunk works), written by one of the original developers of XML, contains some proposals for what an XML 2.0 might look like: elimination of DTDs from syntax, integration of namespaces, XML Base and XML Information Set (infoset) into the base standard.

The World Wide Web Consortium also has an XML Binary Characterization Working Group doing preliminary research into use cases and properties for a binary encoding of the XML infoset. The working group is not chartered to produce any official standards. Since XML is by definition text-based, ITU-T and ISO are using the name Fast Infoset for their own binary infoset to avoid confusion (see ITU-T Rec. X.891 | ISO/IEC 24824-1).

Extensible Markup Language XML Tutorial

Quick syntax tour

The basic syntax for one element in XML is

<name attribute="value">content</name>

Here is an example of a simple recipe expressed using XML:

<?xml version="1.0" encoding="UTF-8"?>
<recipe name="bread" prep_time="5 mins" cook_time="3 hours">
<title>Basic bread</title>
<ingredient amount="3" unit="cups">Flour</ingredient>
<ingredient amount="0.25" unit="ounce">Yeast</ingredient>
<ingredient amount="1.5" unit="cups" state="warm">Water</ingredient>
<ingredient amount="1" unit="teaspoon">Salt</ingredient>
<instructions>
<step>Mix all ingredients together, and knead thoroughly.</step>
<step>Cover with a cloth, and leave for one hour in warm room.</step>
<step>Knead again, place in a tin, and then bake in the oven.</step>
</instructions>
</recipe>

XML declaration

The first line is the XML declaration: it is an optional line stating what version of XML is in use (normally version 1.0), and may also contain information about character encoding and external dependencies.

Elements

The remainder of this document consists of nested elements, some of which have attributes and content. An element typically consists of two tags, a start tag and an end tag, possibly surrounding text and other elements. The start tag consists of a name surrounded by angle brackets, like "<step>"; the end tag consists of the same name surrounded by angle brackets, but with a slash preceding the name, like "</step>". The element's content is everything that appears between the start tag and the end tag, including text and other (child) elements. The following is a complete XML element, with start tag, text content, and end tag:

<step>Knead again, place in a tin, and then bake in the oven.</step>

Attributes

In addition to text content, an element can contain attributes — name-value pairs included in the start tag after the element name. Attribute values must always be quoted, using single or double quotes, and each attribute name should appear only once in any element.

<ingredient amount="3" unit="cups">Flour</ingredient>

In this example, the ingredient element has two attributes: amount, having value "3", and unit, having value "cups". In both cases, at the markup level, the names and values of the attributes, just like the names and content of the elements, are just textual data — the "3" and "cups" are not a quantity and unit of measure, respectively, but rather are just character sequences that the document author may be using to represent those things.

Nesting

Elements may contain other elements:

<instructions>
<step>Mix all ingredients together, and knead thoroughly.</step>
<step>Cover with a cloth, and leave for one hour in warm room.</step>
<step>Knead again, place in a tin, and then bake in the oven.</step>
</instructions>

In this case, the instructions element contains three step elements. XML requires that elements be properly nested — elements may never overlap. For example, the code below is not well-formed XML, because the em and strong elements overlap:

<!-- WRONG! NOT WELL-FORMED XML! -->
<p>Normal <em>emphasized <strong>strong emphasized</em> strong</strong></p>

Document element

Every XML document must have exactly one top-level root element (alternatively called the document element), so the following would also be a malformed XML document: it has two top-level thing elements, with no overall parent element encasing them both.

<?xml version="1.0" encoding="UTF-8"?>
<!-- WRONG! NOT WELL-FORMED XML! -->
<thing>Thing one</thing>
<thing>Thing two</thing>

Empty elements

XML provides special syntax for representing an element with empty content. Instead of writing a start tag followed immediately by an end tag, a document may contain an empty or singleton tag. An empty tag is a start tag with a slash in front of the closing angle bracket. The following three examples are equivalent in XML.

<foo></foo>
<foo />
<foo/>

In practice, some XML processors may create a distinction by interpreting the textual value of <foo></foo> as an empty string and that of <foo /> and <foo/> as null.

Entity references

XML provides two methods for referring to special characters: character entity references and numeric character references.

An entity in XML is a named body of data, usually text, such as an unusual character.

An entity reference is a placeholder that represents that entity. It consists of the entity's name preceded by an ampersand ("&") and followed by a semicolon (";"). XML has five predeclared entities:

* &amp; (&)
* &lt; (<)
* &gt; (>)
* &apos; (')
* &quot; (")

Here is an example using a predeclared XML entity to represent the ampersand in the name "AT&T":

<company-name>AT&amp;T</company-name>

If more entities need to be declared, this is done in the document's Document Type Definition (DTD). A basic example of doing so in a minimal internal DTD follows. Declared entities can describe single characters or pieces of text, and can reference each other.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE example [
<!ENTITY copy "&#xA9;">
<!ENTITY copyright-notice "Copyright &copy; 2006, XYZ Enterprises">
]>
<example>
&copyright-notice;
</example>

When viewed in a suitable browser, the XML document above appears as:

<example> Copyright © 2006, XYZ Enterprises </example>

Numeric character references

Numeric character references look like entity references, but instead of a name, they contain the "#" character followed by a number. The number (in decimal or "x"-prefixed hexadecimal) represents a Unicode code point. Unlike entity references, they are neither predeclared nor do they need to be declared in the document's DTD. They have typically been used to represent characters that are not easily encodable, such as an Arabic character in a document produced on a European computer. The ampersand in the "AT&T" example could also be escaped like this (decimal 38 and hexadecimal 26 both represent the Unicode code point for the "&" character):

<company-name>AT&#38;T</company-name>
<company-name>AT&#x26;T</company-name>

There are many more rules necessary to be sure of writing well-formed XML documents, such as the use of namespaces and the exact characters allowed in an XML name, but this quick tour provides the basics necessary to read and understand many XML documents.

Correctness in an XML document

There are two standard measures of the correctness of an XML document:

* Well-formed. A well-formed document conforms to all of XML's syntax rules. For example, if a non-empty element has an opening tag with no closing tag, it is not well-formed. A document that is not well-formed is not considered to be XML; a parser is not allowed to process it.
* Valid. A valid document has data that conforms to a particular set of user-defined content rules, or XML schemas, that describe correct data values and locations. For example, if an element in a document is required to contain text that can be interpreted as being an integer numeric value, and it instead has the text "hello", is empty, or has other elements in its content, then the document is not valid. To be valid a document must also be well-formed.

Well-formed documents

An XML document is text, which is a sequence of characters. The specification requires support for Unicode encodings UTF-8 and UTF-16 (UTF-32 is not mandatory). The use of other non-Unicode based encodings, such as ISO-8859, is admitted and is indeed widely used and supported.

A well-formed document must conform to the following rules, among others:-

* One and only one root element exists for the document. However, the XML declaration, processing instructions, and comments can precede the root element.
* Non-empty elements are delimited by both a start-tag and an end-tag.
* Empty elements may be marked with an empty-element (self-closing) tag, such as <IAmEmpty />. This is equal to <IAmEmpty></IAmEmpty>.
* All attribute values are quoted, either single (') or double (") quotes. Single quotes close a single quote and double quotes close a double quote.
* Tags may be nested but must not overlap. Each non-root element must be completely contained in another element.
* The document complies to its character set definition. The charset is usually defined in the xml declaration but it can be provided by the transport protocol, such as HTTP. If no charset is defined, usage of a Unicode encoding is assumed, defined by the Unicode Byte Order Mark. If the mark does not exist, UTF-8 is the default.

Element names are case-sensitive. For example, the following is a well-formed matching pair

<Step> ... </Step>

whereas this is not

<Step> ... </step>

The careful choice of names for XML elements will convey the meaning of the data in the markup. This increases human readability while retaining the rigor needed for software parsing.

Choosing meaningful names implies the semantics of elements and attributes to a human reader without reference to external documentation. However, this can lead to verbosity, which complicates authoring and increases file size.

Valid documents

An XML document that complies with a particular schema, in addition to being well-formed, is said to be valid.

An XML schema is a description of a type of XML document, typically expressed in terms of constraints on the structure and content of documents of that type, above and beyond the basic constraints imposed by XML itself. A number of standard and proprietary XML schema languages have emerged for the purpose of formally expressing such schemas, and some of these languages are XML-based, themselves.

Before the advent of generalized data description languages such as SGML and XML, software designers had to define special file formats or small languages to share data between programs. This required writing detailed specifications and special-purpose parsers and writers.

XML's regular structure and strict parsing rules allow software designers to leave parsing to standard tools, and since XML provides a general, data model-oriented framework for the development of application-specific languages, software designers need only concentrate on the development of rules for their data, at relatively high levels of abstraction.

Well-tested tools exist to validate an XML document "against" a schema: the tool automatically verifies whether the document conforms to constraints expressed in the schema. Some of these validation tools are included in XML parsers, and some are packaged separately.

Other usages of schemas exist: XML editors, for instance, can use schemas to support the editing process (by suggesting valid elements and attributes names, etc).

DTD

The oldest schema format for XML is the Document Type Definition (DTD), inherited from SGML. While DTD support is ubiquitous due to its inclusion in the XML 1.0 standard, it is seen as limited for the following reasons:

* It has no support for newer features of XML, most importantly namespaces.
* It lacks expressivity. Certain formal aspects of an XML document cannot be captured in a DTD.
* It uses a custom non-XML syntax, inherited from SGML, to describe the schema.

XML Schema

A newer XML schema language, described by the W3C as the successor of DTDs, is XML Schema, or more informally referred to by the initialism for XML Schema instances, XSD (XML Schema Definition). XSDs are far more powerful than DTDs in describing XML languages. They use a rich datatyping system, allow for more detailed constraints on an XML document's logical structure, and are required to be processed in a more robust validation framework. XSDs also use an XML-based format which makes it possible to use ordinary XML tools to help process them, although WXS (W3C XML Schema) implementations require much more than just the ability to read XML.

Criticisms of WXS include the following:

* The specification is very large, which makes it difficult to understand and implement.
* The XML-based syntax leads to verbosity in schema description, which makes XSDs harder to read and write.
* Schema validation can be an expensive addition to XML parsing, especially for high volume systems.
* The modeling capabilities are very limited, with no ability to allow attributes to influence content models.
* The type derivation model is very limited, in particular that derivation by extension is rarely useful.
* Database-related data transfer has been supported with arcane ideas such as nillability but the requirements of industrial publishing are under-supported.
* The key/keyref/uniqueness mechanisms are not type aware
* The PSVI concept (Post Schema Validation Infoset) does not have a standard XML representation or API, thus it works against vendor independence unless revalidation is performed.

RELAX NG

Another popular schema language for XML is RELAX NG. Initially specified by OASIS, RELAX NG is now also an ISO international standard (as part of DSDL). It has two formats: an XML based syntax and a non-XML compact syntax. The compact syntax aims to increase readability and writability but, since there is a well-defined way to translate the compact syntax to the XML syntax and back again by means of James Clark's Trang conversion tool, the advantage of using standard XML tools is not lost. RELAX NG has a simpler definition and validation framework than XML Schema, making it easier to use and implement. It also has the ability to use datatype framework plug-ins; a RELAX NG schema author, for example, can require values in an XML document to conform to definitions in XML Schema Datatypes.

ISO DSDL and Other Schema Languages

The ISO DSDL (Document Schema Description Languages) standard brings together a comprehensive set of small schema languages, each targeted at specific problems. DSDL includes RELAX NG full and compact syntax, Schematron assertion language, and languages for defining datatypes, character repertoire constraints, renaming and entity expansion, and namespace-based routing of document fragments to different validators. DSDL schema languages do not have the vendor support of XML Schemas yet, and are to some extent a grassroots reaction of industrial publishers to the lack of utility of XML Schemas for publishing.

Some schema languages not only describe the structure of a particular XML format but also offer limited facilities to influence processing of individual XML files that conform to this format. DTDs and XSDs both have this ability; they can for instance provide attribute defaults. RELAX NG and Schematron intentionally do not provide these infoset augmentation facilities.

Displaying XML on the web

Because XML is a data description language, XML documents do not carry information about how to display the data. Without using CSS or XSL, a generic XML document is rendered as raw XML text by most web browsers. Some display it with 'handles' (e.g. + and - signs in the margin) that allow parts of the structure to be expanded or collapsed with mouse-clicks.

In order to style the rendering in a browser with CSS, the XML document must include a reference to the stylesheet:

<?xml-stylesheet type="text/css" href="myStyleSheet.css"?>

Note that this is different from specifying such a stylesheet in HTML, which uses the <link> element.

Extensible Stylesheet Language (XSL) can be used to alter the format of XML data, either into HTML or other formats that are suitable for a browser to display.

To specify client-side XSL Transformation (XSLT), the following processing instruction is required in the XML:

<?xml-stylesheet type="text/xsl" href="myTransform.xslt"?>

Client-side XSLT is supported by many web browsers, but not Opera before version 9.0. An alternative, rather than being dependent on the end-user's browser capabilities, is to use XSL to convert XML into a displayable format on the server. The end-user is not aware of what has gone on 'behind the scenes'; all they see is well-formatted, displayable data.

XML extensions

* XPath makes it possible to refer to individual parts of an XML document. This provides random access to XML data for other technologies, including XSLT, XSL-FO, XQuery etc. XPath expressions can refer to all or part of the text, data and values in XML elements, attributes, processing instructions, comments etc. They can also access the names of elements and attributes. XPaths can be used in both valid and well-formed XML, with and without defined namespaces.
* XQuery is to XML what SQL and PL/SQL are to relational databases: ways to access, manipulate and return XML.
* XML Namespaces enable the same document to contain XML elements and attributes taken from different vocabularies, without any naming collisions occurring.
* XML Signature defines the syntax and processing rules for creating digital signatures on XML content.
* XML Encryption defines the syntax and processing rules for encrypting XML content.
* XPointer is a system for addressing components of XML-based internet media.

Processing XML files

SAX and DOM are object oriented programming APIs widely used to process XML data. The first XML parsers exposed the contents of XML documents to applications as SAX events or DOM objects.

SAX is a lexical, event-driven interface in which a document is read serially and its contents are reported as "callbacks" to various methods on a handler object of the user's design. SAX is fast and efficient to implement, but difficult to use for extracting information at random from the XML, since it tends to burden the application author with keeping track of what part of the document is being processed. It is better suited to situations in which certain types of information are always handled the same way, no matter where they occur in the document.

DOM is an interface-oriented API that allows for navigation of the entire document as if it were a tree of "Node" objects representing the document's contents. A DOM document can be created by a parser, or can be generated manually by users (with limitations). Data types in DOM Nodes are abstract; implementations provide their own programming language-specific bindings. DOM implementations tend to be memory intensive, as they generally require the entire document to be loaded into memory and constructed as a tree of objects before access is allowed.

A form of XML access that has become increasingly popular in recent years is push parsing, which treats the document as if it were a series of items which are being read in sequence. This allows for writing of recursive-descent parsers in which the structure of the code performing the parsing mirrors the structure of the XML being parsed, and intermediate parsed results can be used and accessed as local variables within the methods performing the parsing, or passed down (as method parameters) into lower-level methods, or returned (as method return values) to higher-level methods. For instance, in the Java programming language, the StAX framework can be used to create what is essentially an 'iterator' which sequentially visits the various elements, attributes, and data in an XML document. Code which uses this 'iterator' can test the current item (to tell, for example, whether it is a start or end element, or text), and inspect its attributes (local name, namespace, values of XML attributes, value of text, etc.), and can also request that the iterator be moved to the 'next' item. The code can thus extract information from the document as it traverses it. One significant advantage of push-parsing methods is that they typically are much more speed- and memory-efficient than SAX and DOM styles of parsing XML. Another advantage is that the recursive-descent approach tends to lend itself easily to keeping data as typed local variables in the code doing the parsing, while SAX, for instance, typically requires a parser to manually maintain intermediate data within a stack of elements which are parent elements of the element being parsed. This tends to mean that push-parsing code is often much more straightforward to understand and maintain than SAX parsing code. Some potential disadvantages of push parsing are that it is a newer approach which is not as well known among XML programmers (although it is by far the most common method used for writing compilers and interpreters for languages other than XML), and that most existing push parsers cannot yet perform advanced processing such as XML schema validation as they parse a document.

Another form of XML Processing API is data binding, where XML data is made available as a custom, strongly typed programming language data structure, in contrast to the interface-oriented DOM. Example data binding systems are the Java Architecture for XML Binding (JAXB) and the Strathclyde Novel Architecture for Querying XML (SNAQue).

A filter in the Extensible Stylesheet Language (XSL) family can transform an XML file for displaying or printing.

* XSL-FO is a declarative, XML-based page layout language. An XSL-FO processor can be used to convert an XSL-FO document into another non-XML format, such as PDF.
* XSLT is a declarative, XML-based document transformation language. An XSLT processor can use an XSLT stylesheet as a guide for the conversion of the data tree represented by one XML document into another tree that can then be serialized as XML, HTML, plain text, or any other format supported by the processor.
* XQuery is a W3C language for querying, constructing and transforming XML data.
* XPath is a DOM-like node tree data model and path expression language for selecting data within XML documents. XSL-FO, XSLT and XQuery all make use of XPath. XPath also includes a useful function library.

The native file format of OpenOffice.org, AbiWord, and Apple's iWork applications is XML. Some parts of Microsoft Office 11 will also be able to edit XML files with a user-supplied schema (but not a DTD), and on June 2, 2005 Microsoft announced that, by late 2006 all the files created by users of its Office suite of software will be formatted with web-centered XML specifications. There are dozens of other XML editors available.


PL/SQL Tutorial


Basic Structure of PL/SQL


PL/SQL stands for Procedural Language/SQL. PL/SQL extends

SQL by adding constructs found in procedural languages,

resulting in a structural language that is more powerful than

SQL. The basic unit in PL/SQL is a block. All PL/SQL programs

are made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in he program. A

block has the following structure:

DECLARE

/* Declarative section: variables, types, and local

subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go

here. */

/* This is the only section of the block that is required. */

EXCEPTION

/* Exception handling section: error handling statements go

here. */

END;

Only the executable section is required. The other sections are

optional. The only SQL statements allowed in a PL/SQL

program are SELECT, INSERT, UPDATE, DELETE and several

other data manipulation statements plus some transaction

control. However, the SELECT statement has a special form in

which a single tuple is placed in variables; more on this later.

Data definition statements like CREATE, DROP, or ALTER are not

allowed. The executable section also contains constructs such

as assignments, branches, loops, procedure calls, and triggers,

which are all described below (except triggers). PL/SQL is not

case sensitive. C style comments (/* ... */) may be used.

To execute a PL/SQL program, we must follow the program

text itself by

* A line with a single dot ("."), and then
* A line with run;

As with Oracle SQL programs, we can invoke a PL/SQL program

either by typing in sqlplus.


Variables and Types

Information is transmitted between a PL/SQL program and the

database through variables. Every variable has a specific type

associated with it. That type can be

* One of the types used by SQL for database columns
* A generic type used in PL/SQL such as NUMBER
* Declared to be the same as the type of some database

column

The most commonly used generic type is NUMBER. Variables of

type NUMBER can hold either an integer or a real number. The

most commonly used character string type is VARCHAR(n),

where n is the maximum length of the string in bytes. This

length is required, and there is no default. For example, we

might declare:

DECLARE

price NUMBER;

myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though

Oracle does not support BOOLEAN as a type for database

columns.

Types in PL/SQL can be tricky. In many cases, a PL/SQL

variable will be used to manipulate data stored in a existing

relation. In this case, it is essential that the variable have the

same type as the relation column. If there is any type

mismatch, variable assignments and comparisons may not

work the way you expect. To be safe, instead of hard coding

the type of a variable, you should use the %TYPE operator. For

example:

DECLARE

myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for

the name column in relation Beers.

A variable may also have a type that is a record with several

fields. The simplest way to declare such a variable is to use

%ROWTYPE on a relation name. The result is a record type in

which the fields have the same names and types as the

attributes of the relation. For instance:

DECLARE

beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and

manufacture, assuming that the relation has the schema

Beers(name, manufacture).

The initial value of any variable, regardless of its type, is NULL.

We can assign values to variables, using the ":=" operator. The

assignment can occur either immediately after the type of the

variable is declared, or anywhere in the executable portion of

the program. An example:

DECLARE

a NUMBER := 3;

BEGIN

a := a + 1;

END;

run;

This program has no effect when run, because there are no

changes to the database.

Simple Programs in PL/SQL

The simplest form of program has some declarations followed

by an executable section consisting of one or more of the SQL

statements with which we are familiar. The major nuance is

that the form of the SELECT statement is different from its SQL

form. After the SELECT clause, we must have an INTO clause

listing variables, one for each attribute in the SELECT clause,

into which the components of the retrieved tuple must be

placed.

Notice we said "tuple" rather than "tuples", since the SELECT

statement in PL/SQL only works if the result of the query

contains a single tuple. The situation is essentially the same as

that of the "single-row select" discussed in Section 7.1.5 of the

text, in connection with embedded SQL. If the query returns

more than one tuple, you need to use a cursor. Here is an

example:

CREATE TABLE T1(

e INTEGER,

f INTEGER

);



DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

/* Above is plain SQL; below is the PL/SQL program. */

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

run;

Fortuitously, there is only one tuple of T1 that has first

component greater than 1, namely (2,4). The INSERT

statement thus inserts (4,2) into T1.

Control Flow in PL/SQL

PL/SQL allows you to branch and create loops in a fairly

familiar way.

An IF statement looks like:

IF <condition> THEN <statement_list> ELSE <statement_list>

END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF <condition_1> THEN ...

ELSIF <condition_2> THEN ...

... ...

ELSIF <condition_n> THEN ...

ELSE ...

END IF;

The following is an example, slightly modified from the

previous one, where now we only do the insertion if the second

component is 1. If not, we first add 10 to each component and

then insert:

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

run;

Loops are created with the following:

LOOP

<loop_body> /* A list of statements. */

END LOOP;

At least one of the statements in <loop_body> should be an

EXIT statement of the form

EXIT WHEN <condition>;

The loop breaks if <condition> is true. For example, here is a

way to insert each of the pairs (1, 1) through (100, 100) into T1

of the above two examples:

DECLARE

i NUMBER := 1;

BEGIN

LOOP

INSERT INTO T1 VALUES(i,i);

i := i+1;

EXIT WHEN i>100;

END LOOP;

END;

.run;

Some other useful loop-forming statements are:

* EXIT by itself is an unconditional loop break. Use it inside a

conditional if you like.
* A WHILE loop can be formed with

· WHILE <condition> LOOP

· <loop_body>

END LOOP;

* A simple FOR loop can be formed with:

· FOR <var> IN <start>..<finish> LOOP

· <loop_body>

·

END LOOP;

Here, <var> can be any variable; it is local to the for-loop and

need not be declared. Also, <start> and <finish> are

constants.

Cursors

A cursor is a variable that runs through the tuples of some

relation. This relation can be a stored table, or it can be the

answer to some query. By fetching into the cursor each tuple

of the relation, we can write a program to read and process the

value of each such tuple. If the relation is stored, we can also

update or delete the tuple at the current cursor position.

The example below illustrates a cursor loop. It uses our

example relation T1(e,f) whose tuples are pairs of integers.

The program will delete every tuple whose first component is

less than the second, and insert the reverse tuple into T1.

1) DECLARE

/* Output variables to hold the result of the query: */

2) a T1.e%TYPE;

3) b T1.f%TYPE;

/* Cursor declaration: */

4) CURSOR T1Cursor IS

5) SELECT e, f

6) FROM T1

7) WHERE e < f

8) FOR UPDATE;

9) BEGIN

10) OPEN T1Cursor;

11) LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

12) FETCH T1Cursor INTO a, b;

/* If there are no more rows to fetch, exit the loop: */

13) EXIT WHEN T1Cursor%NOTFOUND;

/* Delete the current tuple: */

14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;

/* Insert the reverse tuple: */

15) INSERT INTO T1 VALUES(b, a);

16) END LOOP;

/* Free cursor used by the query. */

17) CLOSE T1Cursor;

18) END;

19) .

20) run;

Here are explanations for the various lines of this program:

* Line (1) introduces the declaration section.
* Lines (2) and (3) declare variables a and b to have types

equal to the types of attributes e and f of the relation T1.

Although we know these types are INTEGER, we wisely make

sure that whatever types they may have are copied to the

PL/SQL variables (compare with the previous example, where

we were less careful and declared the corresponding variables

to be of type NUMBER).
* Lines (4) through (8) define the cursor T1Cursor. It ranges

over a relation defined by the SELECT-FROM-WHERE query.

That query selects those tuples of T1 whose first component is

less than the second component. Line (8) declares the cursor

FOR UPDATE since we will modify T1 using this cursor later on

Line (14). In general, FOR UPDATE is unnecessary if the cursor

will not be used for modification.
* Line (9) begins the executable section of the program.
* Line (10) opens the cursor, an essential step.
* Lines (11) through (16) are a PL/SQL loop. Notice that such a

loop is bracketed by LOOP and END LOOP. Within the loop we

find:
o On Line (12), a fetch through the cursor into the local

variables. In general, the FETCH statement must provide

variables for each component of the tuple retrieved. Since the

query of Lines (5) through (7) produces pairs, we have

correctly provided two variables, and we know they are of the

correct type.
o On Line (13), a test for the loop-breaking condition. Its

meaning should be clear: %NOTFOUND after the name of a

cursor is true exactly when a fetch through that cursor has

failed to find any more tuples.
o On Line (14), a SQL DELETE statement that deletes the

current tuple using the special WHERE condition CURRENT OF

T1Cursor.
o On Line (15), a SQL INSERT statement that inserts the

reverse tuple into T1.
* Line (17) closes the cursor.
* Line (18) ends the PL/SQL program.
* Lines (19) and (20) cause the program to execute.

Procedures

PL/SQL procedures behave very much like procedures in other

programming language. Here is an example of a PL/SQL

procedure addtuple1 that, given an integer i, inserts the tuple

(i, 'xxx') into the following example relation:

CREATE TABLE T2 (

a INTEGER,

b CHAR(10)

);



CREATE PROCEDURE addtuple1(i IN NUMBER) AS

BEGIN

INSERT INTO T2 VALUES(i, 'xxx');

END addtuple1;

run;

A procedure is introduced by the keywords CREATE PROCEDURE

followed by the procedure name and its parameters. An option

is to follow CREATE by OR REPLACE. The advantage of doing so

is that should you have already made the definition, you will

not get an error. On the other hand, should the previous

definition be a different procedure of the same name, you will

not be warned, and the old procedure will be lost.

There can be any number of parameters, each followed by a

mode and a type. The possible modes are IN (read-only), OUT

(write-only), and INOUT (read and write). Note: Unlike the type

specifier in a PL/SQL variable declaration, the type specifier in

a parameter declaration must be unconstrained. For example,

CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR

should be used instead. The actual length of a parameter

depends on the corresponding argument that is passed in

when the procedure is invoked.

Following the arguments is the keyword AS (IS is a synonym).

Then comes the body, which is essentially a PL/SQL block. We

have repeated the name of the procedure after the END, but

this is optional. However, the DECLARE section should not start

with the keyword DECLARE. Rather, following AS we have:

... AS

<local_var_declarations>

BEGIN

<procedure_body>

END;

.

run;

The run at the end runs the statement that creates the

procedure; it does not execute the procedure. To execute the

procedure, use another PL/SQL statement, in which the

procedure is invoked as an executable statement. For

example:

BEGIN addtuple1(99); END;

.

run;

The following procedure also inserts a tuple into T2, but it takes

both components as arguments:

CREATE PROCEDURE addtuple2(

x T2.a%TYPE,

y T2.b%TYPE)

AS

BEGIN

INSERT INTO T2(a, b)

VALUES(x, y);

END addtuple2;

.

run;

Now, to add a tuple (10, 'abc') to T2:

BEGIN

addtuple2(10, 'abc');

END;

.

run;

The following illustrates the use of an OUT parameter:

CREATE TABLE T3 (

a INTEGER,

b INTEGER

);



CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)

AS

BEGIN

b := 4;

INSERT INTO T3 VALUES(a, b);

END;

.

run;

DECLARE

v NUMBER;

BEGIN

addtuple3(10, v);

END;

run;

Note that assigning values to parameters declared as OUT or

INOUT causes the corresponding input arguments to be

written. Because of this, the input argument for an OUT or

INOUT parameter should be something with an "lvalue", such

as a variable like v in the example above. A constant or a

literal argument should not be passed in for an OUT/INOUT

parameter.

We can also write functions instead of procedures. In a

function declaration, we follow the parameter list by RETURN

and the type of the return value:

CREATE FUNCTION <func_name>(<param_list>) RETURN

<return_type> AS ...

In the body of the function definition, "RETURN <expression>;"

exits from the function and returns the value of <expression>.

To find out what procedures and functions you have created,

use the following SQL query:

select object_type, object_name

from user_objects

where object_type = 'PROCEDURE'

or object_type = 'FUNCTION';

To drop a stored procedure/function:

drop procedure <procedure_name>;

drop function <function_name>;

Discovering Errors

PL/SQL does not always tell you about compilation errors.

Instead, it gives you a cryptic message such as "procedure

created with compilation errors". If you don't see what is

wrong immediately, try issuing the command

show errors procedure <procedure_name>;

Alternatively, you can type, SHO ERR (short for SHOW ERRORS)

to see the most recent compilation error.

Printing Variables

Sometimes we might want to print the value of a PL/SQL local

variable. A ``quick-and-dirty'' way is to store it as the sole

tuple of some relation and after the PL/SQL statement print

the relation with a SELECT statement. A more couth way is to

define a bind variable, which is the only kind that may be

printed with a print command. Bind variables are the kind that

must be prefixed with a colon in PL/SQL statements.

The steps are as follows:

1. We declare a bind variable as follows:

VARIABLE <name> <type>

where the type can be only one of three things: NUMBER,

CHAR, or CHAR(n).

2. We may then assign to the variable in a following PL/SQL

statement, but we must prefix it with a colon.
3. Finally, we can execute a statement

PRINT :<name>;

outside the PL/SQL statement

Here is a trivial example, which prints the value 1.

VARIABLE x NUMBER

BEGIN

:x := 1;

END;

.

run;

PRINT :x;

Looking for more information of PL SQL tutorials:

1. PLSQL Tutorial

2. PL/SQL Tutorial

What is SQL?

The Structured Query Language is used in manipulating data stored in Relational Database Management Systems (RDBMS). SQL provides commands through which data can be extracted, sorted, updated, deleted and inserted. SQL has the full support of ANSI (American National Standards Institute), which has laid down certain rules for the language.
SQL can be used with any RDBMS such as MySQL, mSQL, PostgresSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres etc. All the important and common sql statements are supported by these RDBMS, however, each has its own set of proprietary statements and extensions.

SQL Data Manipulation Language (DM): SQL (Structured Query Language) is a syntax for executing queries. But the 
SQL Data Manipulation Language (DM): SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.  
 
Joins and Keys
Sometimes we have to select data from two tables to make our result complete. We have to perform a join.
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.
In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
When you look at the example tables below, notice that: 
  • The "Employee_ID" column is the primary key of the "Employees" table
  • The "Prod_ID" column is the primary key of the "Orders" table
  • The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names
Employees:
Employee_ID
Name
01
Hansen, Ola
02
Svendson, Tove
03
Svendson, Stephen
04
Pettersen, Kari
Orders:
Prod_ID
Product
Employee_ID
234
Printer
01
657
Table
03
865
Chair
03
Referring to Two Tables
We can select data from two tables by referring to two tables, like this:
Example
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
Result
Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
Example
Who ordered a printer?
SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'
Result
Name
Hansen, Ola
Using Joins
OR we can select data from two tables with the JOIN keyword, like this:
Example INNER JOIN
Syntax
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Result
Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
Example LEFT JOIN
Syntax
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
List all employees, and their orders - if any.
SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Result
Name
Product
Hansen, Ola
Printer
Svendson, Tove
 
Svendson, Stephen
Table
Svendson, Stephen
Chair
Pettersen, Kari
 
Example RIGHT JOIN
Syntax
SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
List all orders, and who has ordered - if any.
SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Result
Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
Example
Who ordered a printer?
SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'
Result
Name
Hansen, Ola

Create a Database
CREATE DATABASE database_name
Create a Table
CREATE TABLE Person 
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:
Data Type
Description
integer(size)
int(size)
smallint(size)
tinyint(size)
Hold integers only. The maximum number of digits are specified in parenthesis.
decimal(size,d)
numeric(size,d)
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
char(size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.
varchar(size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
date(yyyymmdd)
Holds a date

Create Index
Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. 
Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.
A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
The "column_name" specifies the column you want indexed.
A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.
CREATE INDEX index_name
ON table_name (column_name)
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
CREATE INDEX PersonIndex
ON Person (LastName)
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX PersonIndex
ON Person (LastName DESC)
If you want to index more than one column you can list the column names within the parentheses, separated by commas:
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
Drop Index
You can delete an existing index in a table with the DROP statement.
DROP INDEX table_name.index_name
Delete a Database or Table
To delete a database:
DROP DATABASE database_name
To delete a table (the table structure, attributes, and indexes will also be deleted):
DROP TABLE table_name
Alter Table
The ALTER TABLE statement is used to add or drop columns in an existing table.
ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE table_name
DROP COLUMN column_name
 
Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).

Person:
LastName
FirstName
Address
Pettersen
Kari
Storgt 20

Example
To add a column named "City" in the "Person" table:
ALTER TABLE Person ADD City varchar(30)
Result:
LastName
FirstName
Address
City
Pettersen
Kari
Storgt 20
 
Example
To drop the "Address" column in the "Person" table:
ALTER TABLE Person DROP COLUMN Address
Result:
LastName
FirstName
City
Pettersen
Kari
 

Function Syntax
The syntax for built-in SQL functions is:
SELECT function(column) FROM table
Types of Functions
There are several basic types and categories of functions in SQL. The basic types of functions are:
  • Aggregate Functions
  • Scalar functions
Aggregate functions
Aggregate functions operate against a collection of values, but return a single value.
Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!
"Persons" table (used in most examples)
Name
Age
Hansen, Ola
34
Svendson, Tove
45
Pettersen, Kari
19
Aggregate functions in MS Access
Function
Description
AVG(column)
Returns the average value of a column
COUNT(column)
Returns the number of rows (without a NULL value) of a column
COUNT(*)
Returns the number of selected rows
FIRST(column)
Returns the value of the first record in the specified field
LAST(column)
Returns the value of the last record in the specified field
MAX(column)
Returns the highest value of a column
MIN(column)
Returns the lowest value of a column
STDEV(column)
 
STDEVP(column)
 
SUM(column)
Returns the total sum of a column
VAR(column)
 
VARP(column)
 
Aggregate functions in SQL Server
Function
Description
AVG(column)
Returns the average value of a column
BINARY_CHECKSUM
 
CHECKSUM
 
CHECKSUM_AGG
 
COUNT(column)
Returns the number of rows (without a NULL value) of a column
COUNT(*)
Returns the number of selected rows
COUNT(DISTINCT column)
Returns the number of distinct results
FIRST(column)
Returns the value of the first record in the specified field
LAST(column)
Returns the value of the last record in the specified field
MAX(column)
Returns the highest value of a column
MIN(column)
Returns the lowest value of a column
STDEV(column)
 
STDEVP(column)
 
SUM(column)
Returns the total sum of a column
VAR(column)
 
VARP(column)

Scalar functions  
Scalar functions operate against a single value, and return a single value based on the input value.
Useful Scalar Functions in MS Access
Function
Description
UCASE(c)
Converts a field to upper case
LCASE(c)
Converts a field to lower case
MID(c,start[,end])
Extract characters from a text field
LEN(c)
Returns the length of a text field
INSTR(c)
Returns the numeric position of a named character within a text field
LEFT(c,number_of_char)
Return the left part of a text field requested
RIGHT(c,number_of_char)
Return the right part of a text field requested
ROUND(c,decimals)
Rounds a numeric field to the number of decimals specified
MOD(x,y)
Returns the remainder of a division operation
NOW()
Returns the current system date
FORMAT(c,format)
Changes the way a field is displayed
DATEDIFF(d,date1,date2)
Used to perform date calculations
SELECT AVG(column) FROM table
SELECT AVG(Age) FROM Persons WHERE Age>20
SELECT COUNT(Age) FROM Persons
SELECT FIRST(Age) AS lowest_age
FROM Persons
ORDER BY Age
GROUP BY
GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.
The syntax for the GROUP BY function is:
SELECT column,SUM(column) FROM table GROUP BY column
GROUP BY Example
This "Sales" Table:
Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
And This SQL:
SELECT Company, SUM(Amount) FROM Sales
Returns this result:
Company
SUM(Amount)
W3Schools
17100
IBM
17100
W3Schools
17100
The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
Returns this result:
Company
SUM(Amount)
W3Schools
12600
IBM
4500

HAVING CLAUSE
HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.
The syntax for the HAVING function is:
SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
This "Sales" Table:
Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
This SQL:
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000
Returns this result
Company
SUM(Amount)
W3Schools
12600

The SELECT INTO Statement
The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.
Syntax
SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source

Make a Backup Copy
The following example makes a backup copy of the "Persons" table:
SELECT * INTO Persons_backup

FROM Persons
The IN clause can be used to copy tables into another database:
SELECT Persons.* INTO Persons IN 'Backup.mdb'
FROM Persons
If you only want to copy a few fields, you can do so by listing them after the SELECT statement:
SELECT LastName, FirstName INTO Persons_backup
FROM Persons
You can also add a where clause. The following example creates a "Persons_backup" table with two columns (FirstName and LastName) by extracting the persons who lives in "Sandnes" from the "Persons" table:
SELECT LastName, Firstname INTO Persons_sandnes
FROM Persons

WHERE City
='Sandnes'

Selecting data from more than one table is also possible. The following example creates a new table "Empl_Ord_backup" that contains data from the two tables Employees and Orders:
SELECT Employees.Name,Orders.Product
INTO Empl_Ord_backup
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID 

Basic Structure of PL/SQL

PL
/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE

/* Declarative section: variables, types, and local subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go here. */

/* This is the only section of the block that is required. */

EXCEPTION

/* Exception handling section: error handling statements go here. */

END;

Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.

To execute a PL/SQL program, we must follow the program text itself by

* A line with a single dot ("."), and then
* A line with run;

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing in sqlplus.
Variables and Types

Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be

* One of the types used by SQL for database columns
* A generic type used in PL/SQL such as NUMBER
* Declared to be the same as the type of some database column

The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:

DECLARE

price NUMBER;

myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.

Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE

myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.

A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:

DECLARE

beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).

The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:

DECLARE

a NUMBER := 3;

BEGIN

a := a + 1;

END;

run;

This program has no effect when run, because there are no changes to the database.


Simple Programs in PL/SQL
The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements with which we are familiar. The major nuance is that the form of the SELECT statement is different from its SQL form. After the SELECT clause, we must have an INTO clause listing variables, one for each attribute in the SELECT clause, into which the components of the retrieved tuple must be placed.

Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only works if the result of the query contains a single tuple. The situation is essentially the same as that of the "single-row select" discussed in Section 7.1.5 of the text, in connection with embedded SQL. If the query returns more than one tuple, you need to use a cursor. Here is an example:

CREATE TABLE T1(

e INTEGER,

f INTEGER

);



DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

/* Above is plain SQL; below is the PL/SQL program. */

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

run;

Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely (2,4). The INSERT statement thus inserts (4,2) into T1.


Control Flow in PL/SQL

PL
/SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF <condition> THEN <statement_list> ELSE <statement_list> END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF <condition_1> THEN ...

ELSIF <condition_2> THEN ...

... ...

ELSIF <condition_n> THEN ...

ELSE ...

END IF;

The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

run;

Loops are created with the following:

LOOP

<loop_body> /* A list of statements. */

END LOOP;

At least one of the statements in <loop_body> should be an EXIT statement of the form

EXIT WHEN <condition>;

The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE

i NUMBER := 1;

BEGIN

LOOP

INSERT INTO T1 VALUES(i,i);

i := i+1;

EXIT WHEN i>100;

END LOOP;

END;

.run;

Some other useful loop-forming statements are:

* EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.
* A WHILE loop can be formed with

· WHILE <condition> LOOP

· <loop_body>

END LOOP;

* A simple FOR loop can be formed with:

· FOR <var> IN <start>..<finish> LOOP

· <loop_body>

·

END LOOP;

Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.
Cursors

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.
The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.

1) DECLARE

/* Output variables to hold the result of the query: */

2) a T1.e%TYPE;

3) b T1.f%TYPE;

/* Cursor declaration: */

4) CURSOR T1Cursor IS

5) SELECT e, f

6) FROM T1

7) WHERE e < f

8) FOR UPDATE;

9) BEGIN

10) OPEN T1Cursor;

11) LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

12) FETCH T1Cursor INTO a, b;

/* If there are no more rows to fetch, exit the loop: */

13) EXIT WHEN T1Cursor%NOTFOUND;

/* Delete the current tuple: */

14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;

/* Insert the reverse tuple: */

15) INSERT INTO T1 VALUES(b, a);

16) END LOOP;

/* Free cursor used by the query. */

17) CLOSE T1Cursor;

18) END;

19) .

20) run;

Here are explanations for the various lines of this program:

* Line (1) introduces the declaration section.
* Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).
* Lines (4) through (8) define the cursor T1Cursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.
* Line (9) begins the executable section of the program.
* Line (10) opens the cursor, an essential step.
* Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:
o On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
o On Line (13), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
o On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor.
o On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1.
* Line (17) closes the cursor.
* Line (18) ends the PL/SQL program.
* Lines (19) and (20) cause the program to execute.
Procedures

PL/SQL procedures behave very much like procedures in other programming language. Here is an example of a PL/SQL procedure addtuple1 that, given an integer i, inserts the tuple (i, 'xxx') into the following example relation:

CREATE TABLE T2 (

a INTEGER,

b CHAR(10)

);



CREATE PROCEDURE addtuple1(i IN NUMBER) AS

BEGIN

INSERT INTO T2 VALUES(i, 'xxx');

END addtuple1;

run;

A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters. An option is to follow CREATE by OR REPLACE. The advantage of doing so is that should you have already made the definition, you will not get an error. On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost.

There can be any number of parameters, each followed by a mode and a type. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write). Note: Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.

Following the arguments is the keyword AS (IS is a synonym). Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the END, but this is optional. However, the DECLARE section should not start with the keyword DECLARE. Rather, following AS we have:

... AS

<local_var_declarations>

BEGIN

<procedure_body>

END;

.

run;

The run at the end runs the statement that creates the procedure; it does not execute the procedure. To execute the procedure, use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example:

BEGIN addtuple1(99); END;

.

run;

The following procedure also inserts a tuple into T2, but it takes both components as arguments:

CREATE PROCEDURE addtuple2(

x T2.a%TYPE,

y T2.b%TYPE)

AS

BEGIN

INSERT INTO T2(a, b)

VALUES(x, y);

END addtuple2;

.

run;

Now, to add a tuple (10, 'abc') to T2:

BEGIN

addtuple2(10, 'abc');

END;

.

run;

The following illustrates the use of an OUT parameter:

CREATE TABLE T3 (

a INTEGER,

b INTEGER

);



CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)

AS

BEGIN

b := 4;

INSERT INTO T3 VALUES(a, b);

END;

.

run;

DECLARE

v NUMBER;

BEGIN

addtuple3(10, v);

END;

run;

Note that assigning values to parameters declared as OUT or INOUT causes the corresponding input arguments to be written. Because of this, the input argument for an OUT or INOUT parameter should be something with an "lvalue", such as a variable like v in the example above. A constant or a literal argument should not be passed in for an OUT/INOUT parameter.

We can also write functions instead of procedures. In a function declaration, we follow the parameter list by RETURN and the type of the return value:

CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...

In the body of the function definition, "RETURN <expression>;" exits from the function and returns the value of <expression>.

To find out what procedures and functions you have created, use the following SQL query:

select object_type, object_name

from user_objects

where object_type = 'PROCEDURE'

or object_type = 'FUNCTION';

To drop a stored procedure/function:

drop procedure <procedure_name>;

drop function <function_name>;
Discovering Errors

PL
/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as "procedure created with compilation errors". If you don't see what is wrong immediately, try issuing the command

show errors procedure <procedure_name>;

Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error.


Printing Variables
Sometimes we might want to print the value of a PL/SQL local variable. A ``quick-and-dirty'' way is to store it as the sole tuple of some relation and after the PL/SQL statement print the relation with a SELECT statement. A more couth way is to define a bind variable, which is the only kind that may be printed with a print command. Bind variables are the kind that must be prefixed with a colon in PL/SQL statements.

The steps are as follows:

1. We declare a bind variable as follows:

VARIABLE <name> <type>

where the type can be only one of three things: NUMBER, CHAR, or CHAR(n).

2. We may then assign to the variable in a following PL/SQL statement, but we must prefix it with a colon.
3. Finally, we can execute a statement

PRINT :<name>;

outside the PL/SQL statement

Here is a trivial example, which prints the value 1.

VARIABLE x NUMBER

BEGIN

:x := 1;

END;

.

run;

PRINT :x;
PL/SQL BLOCK
The pl/sql block contains the following section:--

-----The DECLARE section.
-----The Master BEGIN and END section that contains the EXCEPTION section.

The declare section contains declaration of memory variables, constants, cursors etc. The begin section contains sql executable statements and pl/sql executable statements. The exception section contains code to handle errors that may arise during the execution of the code block. The end declares the end of pl/sql block.

A bit about it's working. When you typed out the pl/sql block for execution. It is sent to the pl/sql engine, where procedural statements are executed; and sql statements are sent to the sql executor in the oracle engine. Since pl/sql engine resides in the oracle engine, the codes executes smoothly and efficiently.

PL/SQL DATA-TYPE

This is easy since it includes almost all the data types which u have used in sql such as date, varchar, number, char etc etc... Some of the attributes such as %TYPE is also used. This attribute automatically takes in the default data type of the sql table from which u have passed the query. We will discuss this later.

Remember in pl/sql a variable name must begin with a character and can be followed by maximum of 29 other characters. Reserved words can't be used unless enclosed within double quotes. Variables must be separated from each other by at least one space or by a punctuation mark. You can assign values of operator using := operator. I won't discuss about logical comparisons operators such as <, > , >=, NOT, TRUE, AND, OR, NULL etc since they r quite easy to understand.

HOW TO DISPLAY MESSAGES ON SCREEN ---

DBMS_OUTPUT : is a package that includes a number of procedure and functions that accumulate information in a buffer so that it can be retrieved later. These functions can also be used to display messages to the user.
PUT_LINE : Put a piece of information in the package buffer followed by an end-of-line marker. It can also be used to display message to the user. Put_line expects a single parameter of character data type. If used to display a message, it is the message 'string'.
EG: dbms_output.put_line(x);

REMEMBER: To display messages to the user the SERVEROUTPUT should be set to ON. SERVEROUTPUT is a sql*plus environment parameter that displays the information pased as a parameter to the PUT_LINE function.
EG: SET SERVEROUTPUT ON

A bit about comments. A comment can have 2 forms i.e.
-- The comment line begins with a double hyphen (--). The entire line will be treated as a comment.
-- The C style comment such as /* i am a comment */

CONDITIONAL CONTROL AND ITERATIVE CONTROL AND SEQUENTIAL CONTROL

IF and else.....
IF --Condition THEN
--Action
ELSEIF --Condition THEN
--Action
ELSE
--Action
END IF;


SIMPLE LOOP
loop
-- Sequence of statements;
end loop;

the loop ends when u use EXIT WHEN statement --condition

WHILE LOOP
While --condition
loop
--sequence of statements
end loop;

FOR LOOP
FOR i in 1..10
loop
--sequence of statements
end loop;

GOTO (sequential control)
GOTO X;
<< X >>

EXAMPLES

--ADDITION
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is ' || c);

Here & is used to take user input at runtime.....

--SUM OF 100 NUMBERS

Declare
a number;
s1 number default 0;
Begin
a:=1;
loop
s1:=s1+a;
exit when (a=100);
a:=a+1;
end loop;
dbms_output.put_line('Sum between 1 to 100 is '||s1);
End;

--SUM OF odd NUMBERS USING USER INPUT...for loop

declare
n number;
sum1 number default 0;
endvalue number;
begin
endvalue:=&endvalue;
n:=1;
for n in 1.. endvalue
loop
if mod(n,2)=1
then
sum1:=sum1+n;
end if
end loop;
dbms_output.put_line('sum = ' || sum1);
end;

--SUM OF 100 ODD NUMBER .. WHILE LOOP

declare
n number;
endvalue number;
sum1 number default 0;
begin
endvalue:=&endvalue;
n:=1;
while (n < endvalue)
loop
sum1:=sum1+n;
n:=n+2;
end loop;
dbms_output.put_line('Sum of odd numbers between 1 and ' || endvalue || ' is ' || sum1);
end;

--CALCULATION OF NET SALARY

declare
ename varchar2(15);
basic number;
da number;
hra number;
pf number;
netsalary number;
begin
ename:=&ename;
basic:=&basic;

da:=basic * (41/100);
hra:=basic * (15/100);

if (basic < 3000)
then
pf:=basic * (5/100);
elsif (basic >= 3000 and basic <= 5000)
then
pf:=basic * (7/100);
elsif (basic >= 5000 and basic <= 8000)
then
pf:=basic * (8/100);
else
pf:=basic * (10/100);
end if;
netsalary:=basic + da + hra -pf;
dbms_output.put_line('Employee name : ' || ename);
dbms_output.put_line('Providend Fund : ' || pf);
dbms_output.put_line('Net salary : ' || netsalary);
end;

--MAXIMUM OF 3 NUMBERS

Declare
a number;
b number;
c number;
d number;
Begin
dbms_output.put_line('Enter a:');
a:=&a;
dbms_output.put_line('Enter b:');
b:=&b;
dbms_output.put_line('Enter c:');
c:=&b;
if (a>b) and (a>c) then
dbms_output.putline('A is Maximum');
elsif (b>a) and (b>c) then
dbms_output.putline('B is Maximum');
else
dbms_output.putline('C is Maximum');
end if;
End;

--QUERY EXAMPLE--IS SMITH EARNING ENOUGH

declare
s1 emp.sal %type;
begin
select sal into s1 from emp
where ename = 'SMITH';
if(no_data_found)
then
raise_application_error
(20001,'smith is not present');
end if;

if(s1 > 10000)
then
raise_application_error
(20002,'smith is earning enough');
end if;

update emp set sal=sal + 500
where ename='SMITH';
end;

--PRIME NO OR NOT

DECLARE
no NUMBER (3) := &no;
a NUMBER (4);
b NUMBER (2);
BEGIN
FOR i IN 2..no - 1
LOOP
a := no MOD i;
IF a = 0
THEN
GOTO out;
END IF;
END LOOP;
<>
IF a = 1
THEN
DBMS_OUTPUT.PUT_LINE (no || ' is a prime number');
ELSE
DBMS_OUTPUT.PUT_LINE (no || ' is not a prime number');
END IF;
END;

--SIMPLE EXAMPLE OF LOOP STATEMENT I.E. EXIT WHEN

Declare
a number:= 100;
begin
loop
a := a+25;
exit when a=250;
end loop;
dbms_output.put_line (to_Char(a));
end;

--EXAMPLE OF WHILE LOOP

Declare
i number:=0;
j number:= 0;
begin
while i <=100 loop
j := j+1;
i := i +2;
end loop;
dbms_output.put_line(to_char(i));
end;

--EXAMPLE OF FOR LOOP

Declare
begin
for i in 1..10
loop
dbms_output.put_line(to_char(i));
end loop;
end;

--SEQUENTIAL CONTROL GOTO

declare
--takes the default datatype of the column of the table price
cost price.minprice%type;
begin
select stdprice into cost from price where prodial in (Select prodid from product where prodese = "shampoo");
if cost > 7000 then
goto Upd;
end if;
<< Upd >>
Update price set minprice = 6999 where prodid=111;
end;

--CALCULATE THE AREA OF A CIRCLE FOR A VALUE OF RADIUS VARYING FROM 3 TO 7. STORE THE RADIUS AND THE CORRESPONDING VALUES OF CALCULATED AREA IN A TABLE AREAS.

Declare
pi constant number(4,2) := 3.14;
radius number(5);
area number(14,2);

Begin
radius := 3;
While radius <=7
Loop
area := pi* power(radius,2);
Insert into areas values (radius, area);
radius:= radius+1;
end loop;
end;

--REVERSING A NUMBER 5639 TO 9365

Declare
given_number varchar(5) := '5639';
str_length number(2);
inverted_number varchar(5);

Begin
str_length := length(given_number);
For cntr in reverse 1..str_length
loop
inverted_number := inverted_number || substr(given_number, cntr, 1);
end loop;
dbms_output.put_line('The Given no is ' || given_number);
dbms_output.put_line('The inverted number is ' || inverted_number);
end;
EXCEPTION HANDLING IN PLSQL

Errors in pl/sql block can be handled...error handling refers to the way we handle the errors in pl/sql block so that no crashing stuff of code takes place...This is exactly the same as we do in C++ or java..right!!
There are two type:
===> predefined exceptions
===> user defined exceptions
The above 2 terms are self explanatory

predefined exceptions:

No-data-found == when no rows are returned
Cursor-already-open == when a cursor is opened in advance
Dup-val-On-index == for duplicate entry of index..
Storage-error == if memory is damaged
Program-error == internal problem in pl/sql
Zero-divide == divide by zero
invalid-cursor == if a cursor is not open and u r trying to close it
Login-denied == invalid user name or password
Invalid-number == if u r inserting a string datatype for a number datatype which is already declared
Too-many-rows == if more rows r returned by select statement

SYNTAX

begin
sequence of statements;
exception
when --exception name then
sequence of statements;
end;

EXAMPLES

--When there is no data returned by row
declare
price item.actualprice%type;
begin
Select actual price into price from item where qty=888;
when no-data-found then
dbms_output.put_line('item missing');
end;

--EXAMPLE OF USER DEFINED EXCEPTION

DECLARE
e_rec emp%ROWTYPE;
e1 EXCEPTION;
sal1 emp.sal%TYPE;
BEGIN
SELECT sal INTO sal1 FROM emp WHERE deptno = 30 AND ename = 'John';
IF sal1 < 5000 THEN
RAISE e1;
sal1 := 8500;
UPDATE emp SET sal = sal1 WHERE deptno = 30 AND ename = 'John';
END IF;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR (-20001, 'John is not there.');
WHEN e1 THEN
RAISE_APPLICATION_ERROR (-20002, 'Less Salary.');
END;

--EXAMPLE OF RAISE-APPLICATION-ERROR... THIS IS YOUR OWN ERROR STATEMENT...YOU RAISE YOUR OWN ERROR

Declare
s1 emp.sal %type;
begin
select sal into s1 from emp where ename='SOMDUTT';
if(no-data-found) then
raise_application_error(20001, 'somdutt is not there');
end if;
if(s1 > 10000) then
raise_application_error(20002, 'somdutt is earing a lot');
end if;
update emp set sal=sal+500 where ename='SOMDUTT';
end;

--INTERESTING EG OF USER DEFINED EXCEPTIONS

Declare
zero-price exception;
price number(8);
begin
select actualprice into price from item where ordid =400;
if price=0 or price is null then
raise zero-price;
end if;
exception
when zero-price then
dbms_output.put_line('raised xero-price exception');
end;

CURSORS

Cursor is a work area in pl/sql which is used by sql server used to store the result of a query. Each column value is pointed using pointer. You can independently manipulate cursor values. A bit about it's working..... suppose you ask for a query stored in the server ... at first a cursor consisting of query result is created in server...now the cursor is transferred to the client where again cursor is created and hence the result is displayed......

Cursors are of 2 types: implicit and explicit.......implicit cursors are created by oracle engine itself while explicit cursors are created by the users......cursors are generally used in such a case when a query returns more than one rows....normal pl/sql returning more than one rows givens error but using cursor this limitation can be avoided....so cursors are used....

Cursor attributes

%ISOPEN == returns true if ursor is open, false otherwise
%FOUND == returns true if recod was fetched successfully, false otherwise
%NOTFOUND == returns true if record was not fetched successfully, false otherwise
%ROWCOUNT == returns number of records processed from the cursor.

Very important: Cursor can be controlled using following 3 control statements. They are Open, Fetch, Close.....open statement identifies the active set...i.e. query returned by select statement...close statement closes the cursor...and fetch statement fetches rows into the variables...Cursors can be made into use using cursor for loop and fetch statement...we will see the corresponding examples...

EXAMPLES

--EXAMPLE OF SQL%FOUND (IMPLICIT CURSORS)

begin
update employee set salary=salary *0.15
where emp_code = &emp_code;
if sql%found then
dbms_output.put_line('employee record modified successfully');
else
dbms_output.put_line('employee no does not exist');
end if;
end;

--EXAMPLE FOR SQL%NOTFOUND (IMPLICIT CURSORS)

begin
update employee set salary = salary*0.15 where emp_code = &emp_code;
if sql%notfound then
dbms_output.put_line('employee no . does not exist');
else
dbms_output.put_line('employee record modified successfully');
end if;
end;

--EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT CURSORS)

declare
rows_affected char(4);
begin
update employee set salary = salary*0.15 where job='programmers';
rows_affected := to_char(sql%rowcount);
if sql%rowcount > 0 then
dbms_output.put_line(rows_affected || 'employee records modified successfully');
else
dbms_output.put_line('There are no employees working as programmers');
end if;
end;

Syntax of explicit cursor: Cursor cursorname is sql select statement;
Syntax of fetch : fetch cursorname into variable1, variable2...;
Syntax of close; close cursorname;
Syntax of open cursor; open cursorname;


--EXPLICIT CURSOR EG

DECLARE
CURSOR c1 is SELECT * FROM emp;
str_empno emp.empno%type;
str_ename emp.ename%type;
str_job emp.job%type;
str_mgr emp.mgr%type;
str_hiredate emp.hiredate%type;
str_sal emp.sal%type;
str_comm emp.comm%type;
str_deptno emp.deptno%type;
rno number;
BEGIN
rno := &rno;
FOR e_rec IN c1
LOOP
IF c1%rowcount = rno THEN
DBMS_OUTPUT.PUT_LINE (str_empno || ' ' || str_ename || ' ' || str_job || ' ' || str_mgr || ' ' || str_hiredate || ' ' || str_sal || ' ' || str_comm || ' ' || str_deptno);
END IF;
END LOOP;
END;

--ANOTHER EG DISPLAYING VALUE OF A TABLE

DECLARE
CURSOR c1 IS SELECT * FROM emp;
e_rec emp%rowtype;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO e_rec;
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;

-- Display details of Highest 10 salary paid employee

DECLARE
CURSOR c1 IS SELECT * FROM emp ORDER BY sal DESC;
e_rec emp%rowtype;
BEGIN
FOR e_rec IN c1
LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
EXIT WHEN c1%ROWCOUNT >= 10;
END LOOP;
END;

-- EXAMPLE OF CURSOR FOR LOOP

declare cursor c1 is select * from somdutt;
begin
for outvariable in c1
loop
exit when c1%notfound;
if outvariable.age < 21 then
dbms_output.put_line(outvariable.age || ' ' || outvariable.name);
end if;
end loop;
end;

--ref STRONG CURSORS

DECLARE
TYPE ecursor IS REF CURSOR RETURN emp%ROWTYPE;
ecur ecursor;
e_rec emp%ROWTYPE;
dn NUMBER;
BEGIN
dn := &deptno;
OPEN ecur FOR SELECT * FROM emp WHERE deptno = dn;
FOR e_rec IN ecur
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee No : ' || e_rec.empno);
DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || e_rec.salary);
END LOOP;
END;

--REF WEAK CURSORS

DECLARE
TYPE tcursor IS REF CURSOR;
tcur tcursor;
e1 emp%ROWTYPE;
d1 dept%ROWTYPE;
tname VARCHAR2(20);
BEGIN
tname := &tablename;
IF tname = 'emp' THEN
OPEN tcur FOR SELECT * FORM emp;
DBMS_OUTPUT.PUT_LINE ('Emp table opened.');
close tcur;
DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
ELSE IF tname = 'dept' THEN
OPEN tcur FOR SELECT * FROM dept;
DBMS_OUTPUT.PUT_LINE ('Dept table opened.');
close tcur;
DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
ELSE
RAISE_APPLICATION_ERROR (-20004, 'Table name is wrong');
END IF;
END;

--CURSOR FOR LOOP WITH PARAMETERS

Declare
Cursor c1(Dno number) is select * from emp where deptno = dno;
begin
for empree in c1(10) loop;
dbms_output.put_line(empree.ename);
end loop;
end;


TRIGGERS

Trigger is a stored procedure which is called implicitly by oracle engine whenever a insert, update or delete statement is fired.

Advantages of database triggers:
---> Data is generated on it's own
---> Replicate table can be maintained
---> To enforce complex integrity contraints
---> To edit data modifications
---> To autoincrement a field
etc..

Syntax: Create or replace trigger --triggername-- [before/after] [insert/pdate/delete] on --tablename-- [for each satement/ for each row] [when --condition--] plus..begin.and exception

Triggers are of following type: before or after trigger ....and for each row and for each statement trigger... before trigger is fired before insert/update/delete statement while after trigger is fired after insert/update/delete statement...for each row and for each statements triggers are self explainatory..


EXAMPLE

-- A database trigger that allows changes to employee table only during the business hours(i.e. from 8 a.m to 5.00 p.m.) from monday to saturday. There is no restriction on viewing data from the table -CREATE OR REPLACE TRIGGER Time_Check BEFORE INSERT OR UPDATE OR DELETE ON EMP BEGIN IF TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 10 OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 17 OR TO_CHAR(SYSDATE,'DAY') = 'SAT' OR TO_CHAR(SYSDATE,'DAY') = 'SAT' THEN RAISE_APPLICATION_ERROR (-20004,'YOU CAN ACCESS ONLY BETWEEN 10 AM TO 5 PM ON MONDAY TO FRIDAY ONLY.'); END IF; END; --YOU HAVE 2 TABLES WITH THE SAME STRUCTURE. IF U DELETE A RECORD FROM ONE TABLE , IT WILL BE INSERTED IN 2ND TABLE ED TRIGGERNAME Create or replace trigger backup after delete on emp fro each row begin insert into emp/values (:old.ename,:old.job,:old.sal); end; save the file.. and then sql> @ triggername --To STICK IN SAL FIELD BY TRIGGER MEANS WHEN U ENTER GREATER THAN 5000, THEN THIS TRIGGER IS EXECUTED Create or replace trigger check before insert on emp for each row when (New.sal > 5000); begin raise_application_error(-20000, 'your no is greater than 5000'); end; --NO CHANGES CAN BE DONE ON A PARTICULAR TABLE ON SUNDAY AND SATURDAY Create or replace trigger change before on emp for each row when (to_char(sysdate,'dy') in ('SAT','SUN')) begin raise_application_error(-200001, 'u cannot enter data in saturnday and sunday'); end; --IF U ENTER IN EMP TABLE ENAME FIELD'S DATA IN ANY CASE IT WILL BE INSERTED IN CAPITAL LETTERS'S ONLY Create or replace trigger cap before insert on emp for each row begin :New.ename = upper(:New.ename); end; --A TRIGGER WHICH WILL NOT ALLOW U TO ENTER DUPLICATE VALUES IN FIELD EMPNO IN EMP TABLE Create or replace trigger dubb before insert on emp for each row Declare cursor c1 is select * from emp; x emp%rowtype; begin open c1; loop fetch c1 into x; if :New.empno = x.empno then dbms_output.put_line('you entered duplicated no'); elseif :New.empno is null then dbms_output.put_line('you empno is null'); end if; exit when c1%notfound; end loop; close c1; end;

Remember trigger can be dropped using Drop Trigger triggername ; statement...

PROCEDURES AND FUNCTIONS

procedure is a subprogram...which consists of a set of sql statement. Procedures are not very different from functions. A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A stored procedure or function is a named pl/sql code block that have been compiled and stored in one of the oracle engines's system tables.

To make a procedure or function dynamic either of them can be passed parameters before execution. A procedure or function can then change the way it works depending upon the parameters passed prior to its execution.

Procedures and function are made up of a declarative part, an executable part and an optional exception-handling part

A declaration part consists of declarations of variables. A executable part consists of the logic i.e. sql statements....and exception handling part handles any error during run-time

The oracle engine performs the following steps to execute a procedure or function....Verifies user access, Verifies procedure or function validity and executes the procedure or function. Some of the advantages of using procedures and functions are: security, performance, memory allocation, productivity, integrity.

Most important the difference between procedures and functions: A function must return a value back to the caller. A function can return only one value to the calling pl/sql block. By defining multiple out parameters in a procedure, multiple values can be passed to the caller. The out variable being global by nature, its value is accessible by any pl/sql code block including the calling pl/sql block.

Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT} data type, ..) {IS, AS}
variable declarations; constant declarations; BEGIN
pl/sql subprogram body;
EXCEPTION
exception pl/sql block;
END;

Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..) RETURN data type {IS, AS}
variable declarations; constant declarations; BEGIN
pl/sql subprogram body;
EXCEPTION
exception pl/sql block;
END;

The above syntax i think is self explanatory...but i will give you some details...IN : specifies that a value for the argument must be specified when calling the procedure or function. argument : is the name of an argument to the procedure or function. parentheses can be omitted if no arguments are present. OUT : specifies that the procedure passes a value for this argument back to its calling environment after execution. IN OUT : specifies that a value for the argument must be specified when calling the procedure and that the procedure passes a value for this argument back to its calling environment after execution. By default it takes IN. Data type : is the data type of an argument.

EXAMPLES

--PROCEDURE USING NO ARGUMENT..AND USING CURSOR
CREATE OR REPLACE PROCEDURE P2 IS
cursor cur1 is select * from emp;
begin
for erec in cur1
loop
dbms_output.put_line(erec.ename);
end loop;
end;

--PROCEDURE USING ARGUMENT
CREATE OR REPLACE PROCEDURE ME( X IN NUMBER) IS
BEGIN
dbms_output.put_line(x*x);
end;

sql> exec me(3);


--FUNCTION using argument
CREATE OR REPLACE FUNCTION RMT(X IN NUMBER) RETURN NUMBER IS
BEGIN
dbms_output.put_line(x*x);
--return (x*x);
end;

(make a block like this to run it.....)
begin
dbms_output.put_line(rmt(3));
end;


--CREATE A PROCEDURE THAT DELETE ROWS FROM ENQUIRY
--WHICH ARE 1 YRS BEFORE

Create or replace procedure myprocedure is begin
delete from enquiry where enquirydate <= sysdate - 1;
end;

--CREATE A PROCEDURE THAT TAKES ARGUMENT STUDENT NAME,
--AND FIND OUT FEES PAID BY THAT STUDENT

CREATE or REPLACE procedure me (namee in varchar) is
cursor c1 is select a.feespaiddate from feespaid a, enrollment b, enquiry c
where
c.enquiryno = b.enquiryno and
a.rollno = b.rollno and
c.fname = namee;
begin
for erec in c1
loop
dbms_output.put_line(erec.feespaiddate);
end loop;
end;

--SUM OF 2 Numbers

CREATE or replace procedure p1 is
Declare
a number;
b number;
c number;
Begin
a:=50;
b:=89;
c:=a+b;
dbms_output.put_line('Sum of '||a||' and '||b||' is '||c);
End;

--DELETION PROCEDURE

create or replace procedure myproc is
begin
delete from enquiry where fname='somdutt';
end;

--IN and OUT procedure example

Create or replace procedure lest ( a number, b out number) is
identify number;
begin
select ordid into identity from item where
itemid = a;
if identity < 1000 then
b := 100;
end if;
end l

--in out parameter

Create or replace procedure sample ( a in number, b in out number) is
identity number;
begin
select ordid, prodid into identity, b from item where itemid=a;
if b<600 then
b := b + 100;
end if;
end;

now procedure is called by passing parameter

declare
a number;
b number;
begin
sample(3000, b)
dbms_output.put_line(1th value of b is 11 b);
end ;

--SIMILAR EXAMPLE AS BEFORE

create or replace procedure getsal( sal1 in out number) is
begin
select sal into sal1 from emp
where empno = sal1;
end ;

now use the above in plsql block

declare
sal1 number := 7999;
begin
getsal(sal1);
dbms_output.put_line('The employee salary is' || sal1);
end ;


You can make a procedure and functions similarly.....also if u wanna drop a function then use drop function functionname and for procedure use drop procedure procedurename


PACKAGES

A package is an oracle object, which holds other objects within it. Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions. Packages in plsql is very much similar to those packages which we use in JAVA......yeah!! java packages holds numerous classes..right!!!...

A package has 2 parts..... package specification and package body

A package specification part consists of all sort of declaration of functions and procedures while package body consists of codings and logic of declared functions and procedures...


EXAMPLE

--SIMPLEST EG

--specification
create or replace package pack2 is
function rmt(x in number) return number;
procedure rmt1(x in number);
end;

--body
create or replace package body pack2 is
function rmt(x in number) return number is
begin
return (x*x);
end;

procedure rmt1(x in number) is
begin
dbms_output.put_line(x*x);
end;
end;

(how to run.....)
exec packagename.procedurename
i.e.
exec pack2.rmt1(3);

As shown above u can put in complicated procedures and functions inside the package...I have just shown a simple example...you can easily modify the above code to fit your requirement......Just try out packages which includes cursors, procedures and functions..etc..Remeber pl/sql supports overloading...i.e. you can use the same function or procedure name in your application but with different no or type of arguments.


Slider

Image Slider By engineerportal.blogspot.in The slide is a linking image  Welcome to Engineer Portal... #htmlcaption

Tamil Short Film Laptaap

Tamil Short Film Laptaap
Laptapp

Labels

About Blogging (1) Advance Data Structure (2) ADVANCED COMPUTER ARCHITECTURE (4) Advanced Database (4) ADVANCED DATABASE TECHNOLOGY (4) ADVANCED JAVA PROGRAMMING (1) ADVANCED OPERATING SYSTEMS (3) ADVANCED OPERATING SYSTEMS LAB (2) Agriculture and Technology (1) Analag and Digital Communication (1) Android (1) Applet (1) ARTIFICIAL INTELLIGENCE (3) aspiration 2020 (3) assignment cse (12) AT (1) AT - key (1) Attacker World (6) Basic Electrical Engineering (1) C (1) C Aptitude (20) C Program (87) C# AND .NET FRAMEWORK (11) C++ (1) Calculator (1) Chemistry (1) Cloud Computing Lab (1) Compiler Design (8) Computer Graphics Lab (31) COMPUTER GRAPHICS LABORATORY (1) COMPUTER GRAPHICS Theory (1) COMPUTER NETWORKS (3) computer organisation and architecture (1) Course Plan (2) Cricket (1) cryptography and network security (3) CS 810 (2) cse syllabus (29) Cyberoam (1) Data Mining Techniques (5) Data structures (3) DATA WAREHOUSING AND DATA MINING (4) DATABASE MANAGEMENT SYSTEMS (8) DBMS Lab (11) Design and Analysis Algorithm CS 41 (1) Design and Management of Computer Networks (2) Development in Transportation (1) Digital Principles and System Design (1) Digital Signal Processing (15) DISCRETE MATHEMATICS (1) dos box (1) Download (1) ebooks (11) electronic circuits and electron devices (1) Embedded Software Development (4) Embedded systems lab (4) Embedded systems theory (1) Engineer Portal (1) ENGINEERING ECONOMICS AND FINANCIAL ACCOUNTING (5) ENGINEERING PHYSICS (1) english lab (7) Entertainment (1) Facebook (2) fact (31) FUNDAMENTALS OF COMPUTING AND PROGRAMMING (3) Gate (3) General (3) gitlab (1) Global warming (1) GRAPH THEORY (1) Grid Computing (11) hacking (4) HIGH SPEED NETWORKS (1) Horizon (1) III year (1) INFORMATION SECURITY (1) Installation (1) INTELLECTUAL PROPERTY RIGHTS (IPR) (1) Internal Test (13) internet programming lab (20) IPL (1) Java (38) java lab (1) Java Programs (28) jdbc (1) jsp (1) KNOWLEDGE MANAGEMENT (1) lab syllabus (4) MATHEMATICS (3) Mechanical Engineering (1) Microprocessor and Microcontroller (1) Microprocessor and Microcontroller lab (11) migration (1) Mini Projects (1) MOBILE AND PERVASIVE COMPUTING (15) MOBILE COMPUTING (1) Multicore Architecute (1) MULTICORE PROGRAMMING (2) Multiprocessor Programming (2) NANOTECHNOLOGY (1) NATURAL LANGUAGE PROCESSING (1) NETWORK PROGRAMMING AND MANAGEMENT (1) NETWORKPROGNMGMNT (1) networks lab (16) News (14) Nova (1) NUMERICAL METHODS (2) Object Oriented Programming (1) ooad lab (6) ooad theory (9) OPEN SOURCE LAB (22) openGL (10) Openstack (1) Operating System CS45 (2) operating systems lab (20) other (4) parallel computing (1) parallel processing (1) PARALLEL PROGRAMMING (1) Parallel Programming Paradigms (4) Perl (1) Placement (3) Placement - Interview Questions (64) PRINCIPLES OF COMMUNICATION (1) PROBABILITY AND QUEUING THEORY (3) PROGRAMMING PARADIGMS (1) Python (3) Question Bank (1) question of the day (8) Question Paper (13) Question Paper and Answer Key (3) Railway Airport and Harbor (1) REAL TIME SYSTEMS (1) RESOURCE MANAGEMENT TECHNIQUES (1) results (3) semester 4 (5) semester 5 (1) Semester 6 (5) SERVICE ORIENTED ARCHITECTURE (1) Skill Test (1) software (1) Software Engineering (4) SOFTWARE TESTING (1) Structural Analysis (1) syllabus (34) SYSTEM SOFTWARE (1) system software lab (2) SYSTEMS MODELING AND SIMULATION (1) Tansat (2) Tansat 2011 (1) Tansat 2013 (1) TCP/IP DESIGN AND IMPLEMENTATION (1) TECHNICAL ENGLISH (7) Technology and National Security (1) Theory of Computation (3) Thought for the Day (1) Timetable (4) tips (4) Topic Notes (7) tot (1) TOTAL QUALITY MANAGEMENT (4) tutorial (8) Ubuntu LTS 12.04 (1) Unit Wise Notes (1) University Question Paper (1) UNIX INTERNALS (1) UNIX Lab (21) USER INTERFACE DESIGN (3) VIDEO TUTORIALS (1) Virtual Instrumentation Lab (1) Visual Programming (2) Web Technology (11) WIRELESS NETWORKS (1)

LinkWithin