Introduction to PL/SQL

Introduction to PL/SQL

The output:

declare: Variables are declare between this and the [b]begin[/b] keywords.

into: The value fetched using SQL is assigned to the variable.

dbms_output.put_line: Displays the variable’s value on screen and moves on to the next line.

Entering dbms_output.put, without a _line suffix may not produce an output.

This isn’t a procedure or a function but an anonymous block, if script had been unsaved it would have lost.

If it was a function it would have been saved in the database.

The dbms_output.put_line can be considered a form of debugging to check if the code is working properly. By continuously printing out the variable’s values to the screen, the code can be verified for correctness.

Example: To retrieve data for 5 units of a product, a Java program would have to perform 5 round trips to the database. With PL/SQL this can be achieved in only one go.

With some projects, where data isn’t being retrieved from a database quickly enough, code may need to be transferred from a Java program to PL/SQL.

In terms of performance, stored procedures are the fastest blocks of code in databases.

SGA – library cache (System Global Area) stores compiled SQL & PL/SQL code that has already been run, if the DBMS needs to run this code again it is fetched from the library cache without needing to be parsed again.

Parsing is a demanding operation on the database and takes time (as it checks whether there is authorization to use a database, if the needed columns are present within the table, etc.).

Procedures can be called using any programming language or by SQL. They can be run from any database that can be queried.

PL/SQL Program Block

The structure of a PL/SQL Program Block

declare

begin (required)

program logic (required)

exception

end (required)

All commands should end with a semicolon ;.

Example: The following code outputs “Hello” on to the screen

Example: Outputs Hello(1 to 5).

Output of the previous code block

There is no need to use a declare here. The loop carries out an insert from 1 to 1000.

(NOTE: ‘loop’ and ‘end loop’ could have been written here instead of begin and end.)

As the insert and commit statements are enclosed in the loop, there is a commit operation following every insert operation. There isn’t much need for a rollback as if there hasn’t been an insert the statement is drawn back anyway. If the commit operation is done outside of a loop then it would be more useful to have a rollback.

This example isn’t one that really shows exception handling.

The PL/SQL assignment operator is :=

The code has been changed to catch errors messages as shown in the following lines.

Output

DDL commands can’t be written in SQL directly but DML commands such as insert, update and delete can.

Dynamic SQL commands are required to run DDL commands such as alter, delete, drop and truncate.

Example: “execute immediate ‘drop table members”

Dynamic SQL is required to run a command as shown on line 2. The same is necessary to dynamically retrieve a table name. However dynamic SQL isn’t required to retrieve values from a table.

This is a general requirement for outputting the record counts of tables, e.g. for tables containing more than a million records.

A forward slash ‘/’ re-runs the previous statement. run does the same and in addition it outputs the statement on to the screen.

The dot operator ‘.’ keeps the syntax in the buffer and places it in the command line, as previously mentioned, it requires a forward slash to run it.

As an exception handler for catching errors, “WHEN others THEN” should be written last. This is because general errors that haven’t been assigned, or specified, get processed at this point.

If select has 3 values ‘into’ should also have 3 values, ‘into’ statements should only be a single line. For example if there are 2 people to be entered, then an ‘into’ shouldn’t used.

Unlike SQL, PL/SQL also allows the assignment of Boolean variables, where variable naming conventions apply.

These naming conventions are applicable to tables, columns and variables in PL/SQL.
Data Types

BEGIN

A boolean can be assigned a value of true or false

In toad: view —> toad options

Select PL/SQL from languages

Click on code templates

Enter the preferred abbreviations in the opened window (i.e. declare begin end > dbe)

These can be entered while coding using ctrl+space

— This outputs female — if the false is changed to true then the output is male Note: Booleans can’t be used in columns.

Remove accidental numbers between 0 and 49. iAs with programming languages, if a constant is declared in the declare section, it’s assigned value cannot later be changed

This example shows that functions can be used without statements such as ‘select user from dual:’. Comparison Operators

— counts less than 10 will not get output onto the screen.

This can be achieved with a single query:

— The copy of departments remains empty if the rows aren’t retrieved this way.

— A condition of 1=1 being enforced results in rows being created together.

— commit can also be placed after end.

Should the commit be placed in or outside of the procedure ? If placed within the procedure, the user that runs the procedure would also have performed a commit.
Example:

A money transfer transaction scenario of where 50TL is sent from account no. 24 to account no. 30.

Example:

There was an error while the money was being withdrawn from account no. 20 and deposited into account no. 30.

If there was an upper bound constraint of 100TL and the amount to be transferred is 110TL, in such a scenario the amount won’t be transferred to account no. 30 and there would be a rollback.

When the procedure works, both transactions (of withdraw and deposit) should work together, if it doesn’t then both transactions should be rolled back.

In the example scenario of transferring 50TL from account no. 24 to account no. 30; if there is a commit after the first update, i.e. withdrawal, and due to an arbitrary constraint the money can’t be transferred to the receiving account the money would have been lost.

Running an update frequently is demanding on the dbms’ resources. Using the procedure enter an account number as well as the balance increase amount; if entering -50 for example, then 50TL should be deducted. An entry of +50 would deposit 50TL into the account. A commit done immediately following the amount deposit would result in an inconsistency, for this scenario.

Changes would still appear even if there isn’t a commit in the session. They wouldn’t appear in the other sessions.

“novalidate” in the commit insertions results in only validating the new data and not the old data. There is no alteration of data that doesn’t meet the conditions of the constraints.

The default setting is “enablevalidate”.

create sequence EmpIDsequence:

As this is the Primary Key in the table it can’t insert the same numbers, instead it inserts the values from the sequence.

GO TO

This isn’t used often in corporate projects and has been removed from most programming languages.

Label

Can be considered a type of bookmark for executable statements within the code which the GOTO statement transfers control to.

LOOP

Using a ‘for’ statement isnt compulsory to run loops, ‘loop’ keywords are independent of ‘for’ statements.

Printing from 1 to 10 using Loop

If the count was omitted, the loop would have entered an infinite loop. Placing “exit when count> 100;” at the beginning is good practice.

It’s easier to achieve results, as shown in the following example using a ‘for’.

Use a ‘for’ loop if the final count is known.

WHILE

Used whenever the final count is unknown. Checks the condition at the beginning of the loop.

Note: This example didn’t work as it was supposed to.

CASE

The difference between decode and case: Case can be used by itself, however Decode can’t be.

Exception

As good practice, errors starting with ora- shouldn’t be displayed to users, who may be unfamiliar with the error codes.

These errors should be caught with an exception to display them as messages such as “Unable to find record”, etc.

If team doesn’t contain any data, by default the error message would be ORA-0143 no data found. By having exception handling code in every procedure, a less technical and easier to understand error message can be displayed to users.
‘others’ is an Oracle operator that catches all error messages and can be used to output a generic error message.

An example of a Divide by Zero error in Oracle:

The previous code would output: “There has been an error.”

The error output: “Divide by zero error.”

A test scenario where the same Dept (department_id) is entered consecutively.

As a result the dbms is unable to find 2 different records with the same department_id:

The previous line can be understood as: don’t display messages, its better to use an exception. When there is an actual error it overrides others.

SQLERRM is an important DBMS feature as it can at least output the type of error that has been encountered.

An example of SQLERRM being more useful is that it can output an error message in a local language.

SQLERRM is a better procedure as it can output the ORA-01422 error dialog in a local language.

At least it can do something like:

Pragma Exception_INIT

As this is an undefined error, it can be declared using ‘pragma’, which is a useful way of catching undeclared errors.

Error: Can’t leave a not null field !
Implicit Cursors

SQL%FOUND: If the SQL changes any records, SQL%FOUND becomes FALSE as all of the values in the example become less than 1000. SQL%NOTFOUND becomes TRUE, as SQL%NOTFOUND is the reverse of SQL%FOUND.

To delete values larger than 30, SQL%FOUND becomes TRUE. This can be considered a confirmation of running the SQL query.

User-Defined Events

Example:

A condition where a salary shouldn’t be less than the average salary has been defined.

This is to be applied to new users so that their entered salary amount is checked against the average and a higher salary should be entered.

Oracle doesn’t have error handling if a lower salary has been entered. Error handling due to the business logic should be defined by the software developer.

In an example condition of not allowing more than 10 people to be on leave during a working day. If the human resources department wants to grant leave to 11 people, the dbms should catch and output a defined business logic error.

Note: A range of 20000 – 20999 has been reserved for users.

The examples shown here should not be placed in Procedures or Functions as any errors would be sent to the application server instead of being displayed to the user.