Oracle: PL/SQL

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

I will make a deal with you:  If you refer me to a company that needs database support, from a few hours per week to full time, and I am able to sign a contract with them.

Then I will give you 10% of the monthly contract or deal price every month.  When the contract ends, and we re-sign the contract, I will again give you 10% of the monthly contract price.  This will go on until the company no longer employs or contracts me or my agents to look after their databases.

I can do this, because that 10% is my marketing budget.  When we re-sign the contract, in the future, it may depend on you giving the thumbs up again, and that is worth 10% of the monthly contract price, to be given to you as commission.

Contact: Franz

PL/SQL – Programming Language SQL

(Introduction)

PL/SQL is tightly integrated with SQL.  SQL is the most widely used database manipulation language.

• PL/SQL allows you to use all of the SQL data manipulation capabilities.  It allows you to manipulate transactions and cursors.  You can access all the SQL functions and operators.  You also have access to all the pseudo columns like ‘sysdate’.
• Most importantly PL/SQL supports all of the SQL data types, like ‘NUMBER’, ‘VARCHAR2’; as well as its own data types, like %TYPE, %ROWTYPE etc.
• One outstanding feature of PL/SQL, is that it lets you run a SQL query, in a cursor; and then process the rows of that cursor one by one in a loop.

PL/SQL can potentially be written to perform well, in high performance environments.

• You can send a block of statements to the database, which will reduce traffic between the database and the application.
• You can use bind variables, which allows Oracle to re-use the execution paths of SQL statements, which can substantially improve performance.  Without the bind variables, Oracle would need to repeatedly re-parse the SQL statements.
• Sub-Programs, are compiled once, and can be executed repeatedly.  Also sub-program compiled code can be shared between multiple users at runtime.  This optimizes the performance, and reduces overhead, increasing performance substantially.
• The Optimizer for the PL/SQL compiler, can re-arrange the compiled code for higher performance.
• For optimum performance, you need to design your code for performance, using the high performance features of PL/SQL.

PL/SQL code in different constructs, such as procedures and triggers, is very much the same.  The code you will create in SQL*Developer, or Oracle*Forms, or a discreet procedure in SQL*Plus, or a database trigger, is very much the same; which helps to make you more productive.  The good news is that PL/SQL is exactly the same regardless of which version of Unix/Linux or Windows or other operating system you are running your Oracle database on.

Because PL/SQL stored procedures are stored on the database server, instead of in the client applications; they are easier to manage, and deliver a higher performance.

To me, one of the exciting things about PL/SQL, is that you can use it to generate web-pages directly from the database.  This means that you can implement an internet based application, using only PL/SQL; you will however need, PL/SQL gateway and PL/SQL Web Toolkit, to produce a high class application.

The Features of PL/SQL

• You combine the power of SQL, with the power of a procedural language.
• You can declare variables, cursors and constants.  You can control the flow of programs and sub-programs, and trap errors.
• A PL/SQL program exists within a block.  The most important parts of the block are ‘BEGIN’ and ‘END’.  All the code is contained within this area.  Optionally you can start the block with a ‘DECLARE’ section, and have an ‘EXCEPTION’ section just before the ‘END’ keyword.  You can even start off by having a label, just before the top of the BLOCK.

For Example:

<Label>  - optional

DECLARE – optional

   Local types, variables, subprograms and cursors are defined here.

BEGIN – required, this is where the executable part of the PL/SQL starts

   Statements

EXCEPTION – optional, this is where your error handing is done.

END; - required, this denotes the end of the block.

• A sub-program is a named PL/SQL block.  It can be either a ‘FUNCTION’, which must return a value, or a ‘PROCEDURE’, which can optionally return a value.
• A Package is a collection of sub-programs, that can be invoked from the package.  To invoke a specific sub-program within a package, you will call it like this:  <package_name>.<sub-program_name>(parameters)
• A trigger is a named PL/SQL block, that is invoked, in response to a specific event in the database.
• To view direct output from the PL/SQL program, you can use amongst other methods, the DBMS_OUTPUT package to view.
• There are three categories of control statements in PL/SQL.  These three constructs, enable a fully functional programming language, when combined with SQL.
• Conditional statements
• Loop statements
• Sequential control statements

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.