إجراءات, functions; or they
can write in-line segments of PL/SQL within SQL*Plus
scripts.
This fast guide
from SearchOracle.com provides PL/SQL tips, expert
explanations, and web resources and will help both newbies and veterans
become better acquainted with this database
language and hone their developer skills.
The following explanation of PL/SQL is
excerpted from "Learning
Oracle PL/SQL" by Bill Pribyl & Steven Feuerstein.
"Structured Query Language is a language based on
set theory, so
it is all about manipulating sets of data. SQL consists of a relatively
small number of main commands such as SELECT, INSERT, CREATE, and
GRANT; in fact, each statement accomplishes what might take hundreds of
lines of procedural code to accomplish. That's one reason SQL-based
databases are so widely used. The big joke about the name "SQL" is that
it is not really structured, is not just for queries, and (some argue)
is not even a real language. Nevertheless, it's the closest thing there
is to a lingua franca for relational databases such as Oracle's
database server, IBM's DB2 and Microsoft's SQL Server.
"A language extension is a set of features that
somehow enhance
an existing language. This phrase might imply, incorrectly, that PL/SQL
is a special version of SQL. That isn't the case, however. PL/SQL is a
programming language in its own right; it has its own syntax, its own
rules, and its own compiler. You can write PL/SQL programs with or
without any SQL statements. Some authors assert that PL/SQL is a
superset of SQL, but that's a bit of an overstatement, because only the
most common SQL statements can be used easily in a PL/SQL program.
"PL/SQL, then, is a language that is closely related
to SQL,
but one that allows you to write programs as an ordered series of
statements. Or, if you want a definition of PL/SQL that befits a
programmer:
"PL/SQL is a procedural (Algol-like) language with
support for
named program units and packages; much of its syntax is borrowed from
Ada, and from Oracle's SQL it derives its datatype space and many
built-in functions."
PL/SQL basics
PL/SQL
offers a variety of benefits to Oracle DBMS users, including the
ability to modularize program development, support for error handling,
portability across platforms and much more. The following resources
will help newbies learn more about the benefits and uses of PL/SQL.
PL/SQL
language elements,
as noted in the "PL/SQL User's Guide and Reference," include comments,
assignments, statements, procedures, functions, packages, expressions,
logical operators, strings, variable names, declarations, types, loop,
data functions, exceptions, objects, pipe messages, records, triggers
and alerts. Here you'll find tips and advice pertaining to various
PL/SQL elements.
PL/SQL
datatypes,
as noted in the "PL/SQL User's Guide and Reference," include numbers,
characters, large objects, boolean, date, time and intervals. The
following tips and expert advice will offer you methods for working
with various datatypes.
A trigger
is a set of statements that automatically "fires off" an action when a
specific operation, such as changing data in a table, occurs. They are
used to preserve data integrity by consistently checking on or changing
data. Here is a list of tips and tricks to help you work with PL/SQL
triggers.
A stored
procedure
is a set of statements with an assigned name that's stored in the
database in compiled form so that it can be shared by a number of
programs. Stored procedures are helpful in controlling access to data,
preserving data integrity and improving productivity. Here are tips and
expert advice for working with PL/SQL stored procedures.
Dynamic
SQL
facilitates the automatic generation and execution of program
statements. It is useful for building ad hoc query systems and
executing DDL inside PL/SQL, and it is often needed in Web-based
applications. Here you'll find advice for working with dynamic SQL.
Whether
you are a newbie or a seasoned SQL scripting expert looking to tweak
query
performance, this learning guide to the Structured Query Language (SQL)
has something for you. These seven chapters will assist you with how to
script SQL to get the data you need by looking at solutions to
real-world
problems. The advice listed below comes primarily from SQL guru Rudy
Limeback.
Chances are, you are here because you want to know
more about how to get the data from a relational
database. It helps to know more about how the different
tables are connected. That's where primary and foreign keys come in.
The most important thing to remember about primary
keys is that you must ensure
that they are unique.
It seems obvious, but defining primary keys can get messy when data
rows get moved to other tables. Your database design should take into
consideration subtypes
and supertypes.
The key point in comparing data
in tables is to realize that the data are not stored in any specific
order. To get the
first 10, the last 10, or some other arbitrary number of records,
you have to define the order in terms of a particular column, using the
ORDER BY clause. Here are examples of how to get :
SQL queries can be written in many ways. In this
webcast on Oracle
SQL tuning secrets,
guru Don Burleson discusses a few techniques, including using
materialized views, for improving the speed of SQL queries. In excerpts
from her book Oracle
SQL and Index Internals, author
Kimberly Floss explores Oracle's internal mechanisms like the
cost-based optimizer and indexing.
Here are some examples that can help you decide what
the
best solution is:
Crash
Course: Oracle basics
This guide helps you to research, compare or work with the Oracle DBMS.
It provides Oracle basics for versions 8.x, 9.x and 10g.
Learning
Guide -- Performance tuning
Newbies and experts will benefit from this guide; you'll establish a
smart approach to tuning, use and interpret Oracle's tuning utilities
and identify specific problem areas.
Learning
guide to PL/SQL
This fast guide to PL/SQL can help both newbies and veterans become
better acquainted with the language and hone their developer skills.
Learning
guide: Oracle security This guide covers a wide variety of
topics on many aspects of Oracle security to help you lock down your
data.
Guide
to Oracle freeware and shareware A library of freeware,
shareware or inexpensive tools (less than $100) to help with Oracle
administration and development.
Learning
Guide: Backup and recovery
This guide covers everything from the basics of creating a backup and
recovery plan to the specifics of using RMAN and other methods of hot
and cold backups.
Learning
Guide: SQL
Whether you are a newbie or a seasoned expert looking to tweak query
performance, this learning guide can help you figure out how to get the
data you need by looking at solutions to real-world problems.
Linuxtopia hosts an excellent collection of current, up-to-date Linux Books,
Tutorials, and Manuals. These cover many topics,
including Gtk+/Gnome, MySQL, Linux Security, Red Hat
Enterprise Linux, CentOS Enterprise Linux, Red Hat Fedora Core, Ubuntu,
Debian GNU/Linux, Command-Line Tools, C Programming, Perl Scripting,
Bash Scripting, Linux Kernel Module Programming, Xen Linux
Virtualization, Linux Tools & Utilities, GCC, Linux Debugging,
and Linux Developer Tools Guides.
This guide gives you a well presented detailed overview of
J2EE and includes articles, tutorials, examples, tips, tools, and expert advice to
increase your J2EE knowledge and understanding quickly. J2EE is an
abbreviation for Java 2 Platform
Enterprise Edition, which is the Java platform
designed for the mainframe-scale computing
typical of large enterprises. Sun Microsystems (together with industry
partners such as IBM) designed J2EE to simplify application development
in a thin client tiered environment. J2EE simplifies application
development and decreases the need for programming and programmer
training by creating standardized, reusable modular components and by
enabling the tier to handle many aspects of programming automatically.
Financial
Brokerage Service (FBS),
is an end-to-end application which demonstrates several new features
defined in the EJB 2.1 specification and commonly accepted J2EE Design
Patterns. (Oracle)
Application: The Virtual
Shopping Mall 1.3 (VSM 1.3) is a sample application that
demonstrates the various features of the J2EE platform. (Oracle)
Application: J2EE
Connector Examples Package demonstrates the use of an entity
Enterprise JavaBean to interact with a J2EE Connector resource adapter.
(BEA Systems)
Linux Security is one of the most
important considerations in any enterprise IT shop, and there are so
many resources available it can be difficult to know where to
start. Techtarget.com has compiled the
following wealth of information in their Linux Security
Learning Guide. This will help get you up to speed
quickly with learning about and understanding Linux Security.
How
to be a 'security warrior,' part 1 Security Warrior co-author Anton Chuvakin describes
several
useful security tools you can use to outfit your secure Linux
enterprise, including Nessus, Bastille, and nmap.
Commentary: Addition
of IPsec locks down 2.6 kernel
Site expert Ken Milberg discusses the security improvements built into
the 2.6 kernel -- most importantly, IPsec and Netfilter.
This SearchWebServices.com guide introduces you to .NET (dotNet), explains
best practices and pitfalls to avoid and provides troubleshooting help and
advice. You'll find .NET articles, tutorials, tips, tools, white
papers, expert advice and more to increase your .NET knowledge and help
you to better understand this technologiy. Microsoft .NET is a
set of software technologies
for connecting information, users, systems, and devices. This new
generation of technology is based on Web services, which are small
building-block applications that can connect to each other as well as
to other, larger applications over the Internet."
This SearchWebServices.com learning guide introduces you to
Service-Oriented Architecture, or SOA. In this SOA learning guide,
you'll find articles, tips, expert advice, white papers and more that
will explain how SOA fits into the world of Web services.
Here is a free online book about IT security that covers
both security policies and technical security subjects. Read the short introduction
first. If you're interested in technical guidelines, rather than
organisation / policies, read the practical
security summary before looking at the technical sections.
There
is a lot of new material the autor has written since
he wrote this book, please see www.boran.com/security/sp
for these security articles.
This guide explains what XML is, how it's used, and where
it fits into the world of Web services. Here you will find XML articles, tutorials,
examples, tips, tools, white papers, expert advice and more to increase
your XML knowledge and understanding. XML (Extensible Markup
Language) is a flexible way to create common information
formats and then consistenly share both the format and the data on the WWW,
intranets, and elsewhere. Using a standard way of describing
data, as XML allows, enables users to send an
intelligent agent (a program) to a target, such as a web site, gather
data, make a comparison of the data, and then send
back results to the user.
Securing XML is an essential element in keeping Web
services secure. This XML Security Learning Guide is a compilation of
resources that review different types of XML security standards and approaches for keeping your XML Web services secure.
This SearchWebServices.com guide introduces you to Web
Services Security -- highlighting the inherent challenges of securing Web services while
providing you with strategies to overcome these challenges. You'll find
articles, tips, white papers, expert advice and more to arm yourself
against would-be invaders.
A
Regular
Expression (abbreviated as regexp
or regex, with plural forms regexps,
regexes, or regexen ) is a string that
describes or matches a set of strings, according to certain syntax
rules. Regular expressions are used by many text editors
and utilities to search and manipulate bodies of text based on certain
patterns. Many programming languages support regular expressions for
string manipulation. For example, Perl
and Tcl
have a powerful regular expression engine built directly into their
syntax. The set of utilities (including the editor sed
and the filter grep ) provided by Unix
distributions were the first to popularize the concept of regular
expressions.
A regular expression, often called a pattern,
is an
expression that describes a set of strings. They are usually used to
give a concise description of a set, without having to list all
elements. For example, the set containing the three strings Handel,
Händel, and Haendel can
be described by the pattern "H(ä|ae? )ndel" (or alternatively,
it is said that the pattern matches
each of the three strings). Most formalisms provide the following
operations to
construct regular expressions.
alternation
A vertical bar
separates alternatives. For example, "gray|grey" matches gray
or grey, which can commonly be shortened to
"gr(a|e)y".
grouping
Parentheses
are used to define the scope and precedence of the
operators. For example, "gray|grey" and "gr(a|e)y" are different
patterns, but they both describe the set containing gray
and grey.
quantification
A
quantifier after a character or group specifies how often that
preceding expression is allowed to occur. The most common quantifiers
are ?, *, and +:
? The question mark
indicates
there is 0 or 1 of the previous expression. For
example, "colou?r" matches both color and colour.
*
The
asterisk indicates there are 0, 1 or any number of
the previous expression. For example, "go*gle" matches ggle,
gogle, google, etc.
+
The
plus sign indicates that there is at least 1 of the
previous expression. For example, "go+gle" matches gogle,
google, etc. (but not ggle ).
These constructions can be combined to
form arbitrarily complex
expressions, very much like one can construct arithmetical expressions
from the numbers and the operations +, -, * and /.
As an example, the pattern
"((great )*grand )?(father|mother)" matches any
ancestor: father, mother, grand
father, grand mother, great
grand father, great grand mother, great
great grand father, great great grand mother,
great great great grand father, great
great great grand mother and so on.
Here is a table
to help you understand how to write Regular Expressions. This
Regular Expression Syntax table describes and gives an example of the
characters and sequences that can be used.
Regular
Expression Syntax
Character
Description
\
Marks
the next
character as either a special
character or a literal. For example, "n" matches the character "n".
"\n" matches a newline character. The sequence "\\" matches "\" and
"\(" matches "(".
^
Matches the
beginning of input.
$
Matches
the end of
input.
*
Matches
the preceding
character zero or more times.
For example, "zo*" matches either "z" or "zoo".
+
Matches
the preceding
character one or more times.
For example, "zo+" matches "zoo" but not "z".
?
Matches
the preceding
character zero or one time.
For example, "a?ve?" matches the "ve" in "never".
.
Matches
any single
character except a newline
character.
(pattern )
Matches pattern
and remembers
the match. The matched substring can be retrieved from the resulting Matches
collection, using Item [0]...[n]. To match
parentheses characters ( ), use "\(" or "\)".
x|y
Matches
either x
or y.
For example, "z|wood" matches "z" or "wood". "(z|w)oo" matches "zoo" or
"wood".
{n}
n
is a nonnegative integer. Matches exactly n times.
For example, "o{2}" does not match the "o" in "Bob," but matches the
first two o's in "foooood".
{n,}
n is
a nonnegative integer.
Matches at least n times. For example, "o{2,}" does
not match the "o" in "Bob" and matches all the o's in "foooood."
"o{1,}" is equivalent to "o+". "o{0,}" is equivalent to "o*".
{n,m}
m
and n are
nonnegative integers. Matches at least n and at
most m times. For example, "o{1,3}" matches the
first three o's in "fooooood." "o{0,1}" is equivalent to "o?".
[xyz]
A character set.
Matches any one of the enclosed
characters. For example, "[abc]" matches the "a" in "plain".
[^xyz]
A negative character
set. Matches any character not
enclosed. For example, "[^abc]" matches the "p" in "plain".
[a-z]
A range of characters.
Matches any character in the
specified range. For example, "[a-z]" matches any lowercase alphabetic
character in the range "a" through "z".
[^m-z]
A negative range
characters. Matches any character
not in the specified range. For example, "[m-z]" matches any character
not in the range "m" through "z".
\b
Matches
a word
boundary, that is, the position
between a word and a space. For example, "er\b" matches the "er" in
"never" but not the "er" in "verb".
\B
Matches
a non-word
boundary. "ea*r\B" matches the
"ear" in "never early".
\d
Matches
a digit
character. Equivalent to [0-9].
\D
Matches
a
non-digit character. Equivalent to
[^0-9].
\f
Matches
a form-feed character.
\n
Matches
a newline
character.
\r
Matches
a carriage
return character.
\s
Matches
any white
space including space, tab,
form-feed, etc. Equivalent to "[ \f\n\r\t\v]".
\S
Matches
any
nonwhite space character. Equivalent to
"[^ \f\n\r\t\v]".
\t
Matches
a tab character.
\v
Matches
a vertical tab character.
\w
Matches
any word
character including underscore.
Equivalent to "[A-Za-z0-9_]".
\W
Matches
any
non-word character. Equivalent to
"[^A-Za-z0-9_]".
\num
Matches num,
where num
is a positive integer. A reference back to remembered matches. For
example, "(.)\1" matches two consecutive identical characters.
\n
Matches
n, where n is an octal escape
value. Octal escape values must be 1, 2, or 3 digits long. For example,
"\11" and "\011" both match a tab character. "\0011" is the equivalent
of "\001" & "1". Octal escape values must not exceed 256. If
they do, only the first two digits comprise the expression. Allows
ASCII codes to be used in regular expressions.
\xn
Matches
n,
where n
is a hexadecimal escape value. Hexadecimal escape values must be
exactly two digits long. For example, "\x41" matches "A". "\x041" is
equivalent to "\x04" & "1". Allows ASCII codes to be used in
regular expressions.
