Introduction to PLSQL

1
Introduction to PL/SQL

• Sen Zhang

2
Fundamentals
This lecture serves as an introduction to PL/SQL
a powerful programming language that works
hand in hand with SQL.
3
Objectives

• Learn the fundamentals of the PL/SQL programming
  language
• How to write and execute PL/SQL programs in
  SQLPlus
• Understand PL/SQL data type conversion functions
• Manipulate character strings in PL/SQL programs
• Learn how to debug PL/SQL programs

4
SQL vs. PL/SQL

• As we have learned, one benefit of SQL is that it
  is declarative, allowing us to easily create
  Oracle database tables and write queries to
  insert, update, delete, and view records without
  specifying too much detailed data manipulation
  steps.
• There is no free lunch though. The weakness for
  SQL is also due to its declarative language
  feature it has no support for procedural
  constructs such as flow control, or loop
  mechanisms, etc.
• While a single SQL command usually cannot satisfy
  Non-trivial business logics complex business
  logics demand more powerful programming scheme
  like procedural language.

5
Procedural vs. declarative

• Procedural programming requires that the
  programmer tells how to process data using
  detailed sequential or flow control instructions
  to.
• Bad Procedural languages result in many lines of
  code.
• Good Have more control on detail
• Declarative programming requires the programmer
  to tell what data is needed.
• Good The Declarative languages usually result in
  one statement of the desired result.
• Bad Have less control on data.

6

• SQL is not a procedural language but a
  declarative language.
• Using SQL, we carefully phrase what we want and
  then let the DBMS get it for us.
• You write a single SQL declaration and hand it to
  the DBMS. The DBMS then executes internal code,
  which is hidden from us.

7
Why SQL is declarative not procedural?

• SQL came into existence as a relational database
  query language.
• SQL was designed, evolved, optimized for
  structured data, i.e. table, manipulation, saving
  people from considering tedious step by step
  instructions.
• Being declarative is exactly what we want when it
  comes to data manipulation.
• Not optimized nor designed for logics.
• This is a problem!!!

8
An example.

• Let us look at the a simplified business logic
  (business rule) involved in creating a new user
  account, which is useful in any user restricted
  database application.
• Assuming we have a user table to store account
  infor
• Three inputs username, password, and password
  confirmation
• Verify two passwords, if not equal, report
  failure reason
• Otherwise, verify whether username is new or not.
• If username exists, the new account cannot be
  added to the user table report failure reason.
• If username does not exist, go ahead to insert
  the new record to table report success status.

9
Solution

• Here, at least, variables, conditional logic and
  branch action are involved, what will happen is
  conditioned by user input and the current
  database status.
• We, as programmers, can take the responsibility
  to coordinate the logic, can manually type
  several sql statements to get the job done.

10
End user vs. programmer

• But the terminal user is not necessarily a
  programmer.
• Most database users, reasonably assumed to have
  no programming knowledge at all, dont use SQL
  commands or SQLPLUS to interact with a database.
  Instead, users use GUI frontend interface to deal
  with backend database.
• The terminal user Is God! They just want to type
  into text boxes, and point and click buttons and
  . Wait output to show up.
• In run time, the end users provide input data and
  interpret output, but the logic needs to be
  implemented in the design time in the program
  which should have been done by programmers.

11

• This means we have to implement this logic in a
  program, using some kind of procedural
  programming language, not just SQL.
• Since database application is so important, other
  procedural programming languages have been
  designed to be able to contain SQL commands and
  interact with an Oracle database.
• We can write applications with SQL statements
  embedded within a host language such as C,
  Java, vb or vbscript etc..
• The SQL statements provide the database
  interface, while the host language provides the
  remaining support needed for the application to
  execute.

12

• We can take a lot at VB script solution in an asp
  page using ODBC and ADODB
• Java can also be used to talk to Oracle through
  JDBC.

13
PL/SQL

• PL/SQL is the Oracle solution to this need,
• 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.
• A procedural programming language that pure
  oracle applications uses to manipulate database
  data.
• A complement to SQL.
• An extension to SQL, allowing us do things we
  cannot do in SQL alone.

14
Good things combined

• A PL/SQL program combines good things from both
  sides, combining SQL quries with procedural
  commands for tasks such as
• manipulating variable values,
• Evaluating IF/THEN decision control structures,
• Creating loop structures that repeat instructions
  multiple times until the loop reaches an exit
  condition.
• Build in functions
• User defined functions
• Encapsulations using packages

15

• A Full-featured procedural programming language
• An interpreted language, which means that a
  program called the PL/SQL interpreter checks each
  program command for syntax errors, translates
  each command into machine language, and then
  executes each program command, one command at a
  time.
• PL/SQL commands are not case-sensitive, except
  for character strings, which you must enclose in
  single quotation marks.
• The PL/SQL interpreter ignores blank spaces and
  line breaks.
• A semicolon () marks the end of each PL/SQL
  command.
• The programming style is a mixture of
  conventional statements (if, while, etc.) and SQL
  statements.

16
What are the benefits PL/SQL brings to us?

• Using PL/SQL, you can use various regular
  procedural programming language constructs to
  implement complex business logics.
• You can also develop stored procedures,
  functions, and triggers easily and in a modular
  fashion. We are used to stored data, but not
  stored commands. Yes! Here, we will be able to
  make procedures stored in the database.
• The stored components are stored directly in the
  database, which makes the program available to
  all database users if the access privileges has
  been properly granted.
• This stored feature also makes it easier to
  manage database applications, providing a unified
  API interface to different front end applications
  and different front end languages.

17
Stored?

• It means surviving the sessions or connections.
• Stored on server permenently.

18
How to edit PL/SQL programs

• Type in any text editor, submitted to Oracle
  server through SQLPlus

19
Comments

• Not executed by interpreter
• C style comments (/ ... /) may be used.
• Enclosed between / and /
• On one line beginning with --

20
PL/SQL is a programming language or procedural
language

• Since we are talking about a procedural language,
  we are interested in various language constructs
  which constitute a procedural language.
• Reserved word
• Data type
• Variable
• Statement
• Arithmetic operation
• Logical operation
• Control constructs
• Loop
• Branch
• block
• Built in functions
• How to define user defined functions
• How to call user defined functions

21

• PL/SQL is not case sensitive.

22
Reserved word

• Each of the reserved words has a special
  syntactic meaning to PL/SQL.
• So, you should not use them to name program
  objects such as constants, variables, or cursors.
  
• Also, some of these words (marked by an asterisk)
  are reserved by SQL.
• So, you should not use them to name database
  objects such as columns, tables, or indexes.
• http//thinkunix.net/unix/db/oracle/docs-7.3/DOC/s
  erver/doc/PLS23/ape.htm

23
Variables and Data Types

• Information is transmitted between a PL/SQL
  program and the database through variables.
• Variables need to be declared!

24
Data types

• PL/SQL is a strongly typed language, which means
  that you must write a command that explicitly
  declares each variable and specifies its data
  type before you use the variable.
• With a strongly typed language, you can assign
  values to variables and compare variable values
  only for variables with the same data type or
  compatible data type.

25

• Every PL/SQL variable has a specific type
  associated with it.
• There are four kinds of data types
• Scalar data type
• One of the types used by SQL for database columns
  
• A generic type used in PL/SQL such as NUMBER
• Composite data type
• Reference data type
• Declared to be the same as the type of some
  database column
• LOB data types for huge binary data used by
  images and sounds

26
Scalar Data Types

• Represent a single value

These data types are directly from data types
used by SQL database field specification.
27
Bridging role reflected in data types

• Some other data types used PL/SQL are more
  general purpose programming language oriented,
  not corresponding to database data types.
• INTEGER
• BOOLEAN
• DECIMAL
• Note that PL/SQL allows BOOLEAN variables, even
  though Oracle does not support BOOLEAN as a type
  for database columns.

28
Composite data types

• Composite data types
• RECORD contains multiple scalar values, similar
  to a table record
• TABLE tabular structure with multiple columns
  and rows
• VARRAY variable-sized array

29
Reference data types

• In many cases, a PL/SQL variable will be used to
  manipulate data stored in a existing table. In
  this case, it is essential that the variable have
  the same type (compatible is also ok in some
  situation) as the relation column.
• Directly reference a specific database field or
  record and assume the data type of the associated
  field or record
• TYPE same data type as a database field
• ROWTYPE same data type as a database record

30
Data Types in PL/SQL can be tricky!

• 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.nameTYPE
• gives PL/SQL variable myBeer whatever type was
  declared for the name column in relation Beers

31
Variables

• Variables
• Used to store numbers, character strings, dates,
  and other data values
• Avoid using keywords, table names and column
  names as variable names
• Must be declared with data type before use
  variable_name data_type_declaration
• Userid varchar2(10)
• Default value is always NULL when declared
  without being initialized.
• The initial value of any variable, regardless of
  its type, is NULL.

32
Variable names are any valid PL/SQL identifiers.

• Read book page 300 for what a valid identifier
  is.

33
Remarks

• Data types in a procedure definition
  specification cannot have size specifications.
• For instance, you can specify that a parameter is
  a NUMBER datatype, but not a NUMBER(10,2)

34
Assignment Statements

• We can assign values to variables, using the ""
  operator. Like any other programming languages
  you might have used before, the assignment can
  occur either immediately after the type of the
  variable is declared, or anywhere in the
  executable portion of the program.
• Assigns a value to a variable
• variable_name value
• Value can be a literal
• s_first_name Steven'
• Value can be another variable
• first_name s_first_name

35

• How to link variables between SQL command and PL
  command?
• We will solve this problem in sample code.

36
Some simple statements

• Return
• Goto ltlabelgt
• Exit, break a loop

37
Arithmetic Operators in PL/SQL
38
Most SQL Relational Operators can be used for
PL/SQL
 

 
 
 
 
 
 
39
Logical Operators

• and, or, not

40
Expressions

• Simple arithmetic expressions
• Simple relational expressions
• Simple logical expressions
• Nested and compound expressions

41
Built-in functions

• You can also you built-in functions to perform
  common tasks such as manipulating numbers or
  character strings.

42
PL/SQL Data Conversion Functions
43
Manipulating Character Strings with PL/SQL

• To concatenate two strings in PL/SQL, you use the
  double bar () operator
• new_string string1 string2
• To remove blank leading spaces use the LTRIM
  function
• string LTRIM(string_variable_name)
• To remove blank trailing spaces use the RTRIM
  function
• string RTRIM(string_variable_name)
• To find the number of characters in a character
  string use the LENGTH function
• string_length LENGTH(string_variable_name)

44
Manipulating Character Strings with PL/SQL

• To change case, use UPPER, LOWER, INITCAP
• INSTR function searches a string for a specific
  substring
• start_position INSTR(original_string,
  substring)
• SUBSTR function extracts a specific number of
  characters from a character string, starting at a
  given point
• extracted_string SUBSTR(string_variable,
  starting_point, number_of_characters)

45
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

46

• A PL/SQL block contains 1 or more PL/SQL
  statements. Such a block must at least have the
  two keywords begin and end
• begin
• PL contaminated SQL statements
• The executable section also contains constructs
  such as assignments, branches, loops, procedure
  calls, and .
• end
• I call them PL contaminated SQL statements,
  because they are not pure SQL, they contains
  variables of PL part.
• Sometimes, not PL contaminated for trivial
  operations.
• http//www.adp-gmbh.ch/ora/plsql/block.html

47

• 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.
• For most cases, they have to be PL contaminated
  though!
• Data definition statements like CREATE, DROP, or
  ALTER are not allowed.

48
A PL/SQL block

• The header section
• Declaration section, optional
• Execution section
• Optional exception section

49

• 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.
• If the query returns more than one tuple, you
  need to use a cursor,!!!! as described in the
  future lectures.

50
PL/SQL Program Blocks
51
The scope of A PL/SQL block for local variables

• A PL/SQL block establishes a scope for all
  locally-declared variables.
• Outside of the block, those variables do not
  exist.

52
Executing a PL/SQL Program in SQLPlus
The built-in function SYSDATE returns a DATE
value containing the current date and time on
your system.
53
How to test out the PL/SQL program?

• Create program in text editor
• Paste into SQLPlus window
• Press Enter, type / then enter to execute.
• The forward slash (/) tells SQLPLUS to go ahead
  and process the commands in the program.

54
Debugging PL/SQL Programs

• Syntax error
• Command does not follow the guidelines of the
  programming language
• Generates compiler or interpreter error messages
• Logic error
• Program runs but results in an incorrect result
• Caused by mistakes at semantic level in
  programing

55
Finding and Fixing Syntax Errors

• Interpreter flags the line number and character
  location of syntax errors
• If error message appears and the flagged line
  appears correct, the error usually occurs on
  program lines preceding the flagged line
• Comment out program lines to look for hidden
  errors
• One error (such as missing semicolon) may cause
  more fix one error at a time

56
Finding and Fixing Logic Errors

• Locate logic errors by viewing variable values
  during program execution
• There is no SQLPlus debugger
• Use DBMS_OUTPUT statements to print variable
  values

57
Objectives

• Create PL/SQL decision control structures
• Use SQL queries in PL/SQL programs
• Create loops in PL/SQL programs
• Create PL/SQL tables and tables of records
• Use cursors to retrieve database data into PL/SQL
  programs
• Use the exception section to handle errors in
  PL/SQL programs

58
PL/SQL Control Structures

• PL/SQL allows you to branch and create loops and
  function calls in the way that you have been
  doing in C/Java.
• If statement
• Loops three different iteration constructs.
• Loop
• While
• For
• ..

59
PL/SQL Decision Control Structures

• One way branch!
• We also say the commands are conditioned.
• Use IF/THEN structure to execute code if
  condition is true.
• IF condition THEN
• commands that execute if condition is TRUE
• END IF
• If condition evaluates to NULL it is considered
  false

60
Two way decision

• Use IF/THEN/ELSE to execute code if condition is
  true or false
• IF condition THEN
• commands that execute if condition is TRUE
• ELSE
• commands that execute if condition is FALSE
• END IF
• Can be nested be sure to end nested statements

61
PL/SQL Decision Control Structures

• Multi-way branches
• Use IF/ELSIF to evaluate many conditions
• IF condition1 THEN
• commands that execute if condition1 is TRUE
• ELSIF condition2 THEN
• commands that execute if condition2 is
  TRUE
• ELSIF condition3 THEN
• commands that execute if condition3 is
  TRUE
• ...
• ELSE
• commands that execute if none of the
• conditions are TRUE
• END IF

62
IF/ELSIF Example
63
Complex Conditions

• Created with logical operators AND, OR and NOT
• AND is evaluated before OR
• Use () to set precedence

64
MIX SQL and PL/SQL

• DDL commands may not be used in PL/SQL (usually
  we do not do that anyway.)

65
Using SQL Queries in PL/SQL Programs (This is
where SQL and PL/SQL communicate.)

• Insert, delete and update statements (we also
  call them action queries, because these commands
  perform an action that changes the data values in
  the database.) can be used as in SQLPlus
• Usually use variables in action queries

66
Using SQL Queries in PL/SQL Programs

• Select command can be used, but how to use them
  usually depending on what will be returned from
  select command.
• Single row, then use into assign the retrieved
  values to variables. Then you can manipulate the
  values in program commands.
• Multiple row, you want to use cursor.

67
Using an Implicit Cursor(Implicit Cursor will be
discussed!)

• Executing a SELECT query creates an implicit
  cursor
• To retrieve it into a variable use INTO
• SELECT field1, field2, ...
• INTO variable1, variable2, ...
• FROM table1, table2, ...
• WHERE join_ conditions
• AND search_condition_to_retrieve_1_record
• Can only be used with queries that return exactly
  one record

68

• The select statement that finds the total number
  of employee is a simple select statement with
  added keyword INTO. The INTO part of the
  statement is required in order to put the values
  returned by the select statement into the
  corresponding PL/SQL variables.
• If it returns more than one record, obviously,
  there will something wrong.
• To address this issue, we usually need to use
  explicit cursor.

69
Loops

• Program structure that executes a series of
  program statements, and periodically evaluates an
  exit condition to determine if the loop should
  repeat or exit
• Pretest loop evaluates the exit condition before
  any program commands execute
• Posttest loop executes one or more program
  commands before the loop evaluates the exit
  condition for the first time
• PL/SQL has 5 loop structures

70
The LOOP...EXIT Loop

• LOOP
• program statements
• IF condition THEN
• EXIT
• END IF
• additional program statements
• END LOOP

71
The LOOP...EXIT WHEN Loop

• LOOP
• program statements
• EXIT WHEN condition
• END LOOP

72
The WHILE...LOOP

• WHILE condition LOOP
• program statements
• END LOOP

73
The Numeric FOR Loop

• FOR counter_variable IN start_value .. end_value
• LOOP
• program statements
• END LOOP

74
Cursors

• Pointer to a memory location that the DBMS uses
  to process a SQL query
• Use to retrieve and manipulate database data

75

• When you execute a SQL statement from PL/SQL, the
  Oracle RDBMS assigns a private work area for that
  statement. This work area contains information
  about the SQL statement and the set of data
  returned or affected by that statement. The
  PL/SQL cursor is a mechanism by which you can
  name that work area and manipulate the
  information within it.

76
Implicit Cursor
77
Using an Implicit Cursor

• Executing a SELECT query creates an implicit
  cursor
• To retrieve it into a variable use INTO
• SELECT field1, field2, ...
• INTO variable1, variable2, ...
• FROM table1, table2, ...
• WHERE join_ conditions
• AND search_condition_to_retrieve_1_record
• Can only be used with queries that return exactly
  one record

78

• The select statement that finds the total number
  of employee is a simple select statement with
  added keyword INTO. The INTO part of the
  statement is required in order to put the values
  returned by the select statement into the
  corresponding PL/SQL variables.
• If it returns more than one record, obviously,
  there will something wrong.

79
Explicit Cursor

• Use for queries that return multiple records or
  no records
• Must be explicitly declared and used

80
Cursor

• In its simplest form, you can think of a cursor
  as a pointer into a relation in the database or
  dynamically generated from other relations.
• For example, the following cursor declaration
  associates the entire employee table with the
  cursor named employee_cur
• Step 1 cursor declaration
• CURSOR employee_cur
• IS
• SELECT FROM employee
• Step 2 Once you have declared the cursor, you
  can open it
• OPEN employee_cur
• Step 3 And then you can fetch data from it row
  by row, usually inside a loop
• FETCH employee_cur INTO employee_rec
• In this case, each record fetched from this
  cursor represents an entire record in the
  employee table.
• Step 4 finally, You can close the cursor
• CLOSE employee_cur

81
Using an Explicit Cursor

• Declare the cursor
• CURSOR cursor_name IS select_query
• Open the cursor
• OPEN cursor_name
• Fetch the data rows
• LOOP
• FETCH cursor_name INTO variable_name(s)
• EXIT WHEN cursor_nameNOTFOUND
• Close the cursor
• CLOSE cursor_name

82
Explicit Cursor with ROWTYPE
83
Cursor FOR Loop

• Automatically opens the cursor, fetches the
  records, then closes the cursor
• FOR variable_name(s) IN cursor_name LOOP
• processing commands
• END LOOP
• Cursor variables cannot be used outside loop

84
Using Cursor FOR Loop
85
Handling Runtime Errors in PL/SQL Programs

• Runtime errors cause exceptions
• Exception handlers exist to deal with different
  error situations
• Exceptions cause program control to fall to
  exception section where exception is handled

86
Predefined Exceptions
87
Undefined Exceptions

• Less common errors
• Do not have predefined names
• Must declare your own name for the exception code
  in the declaration section
• DECLARE
• e_exception_name EXCEPTION
• PRAGMA EXCEPTION_INIT(e_exception_name,
• -Oracle_error_code)

88
User-Defined Exceptions

• Not a real Oracle error
• Use to enforce business rules

89
Summary

• PL/SQL is a programming language for working with
  an Oracle database
• Scalar, composite and reference variables can be
  used
• The IF/THEN/ELSE decision control structure
  allows branching logic
• Five loop constructs allow repeating code
• Cursors are returned from queries and can be
  explicitly iterated over
• Exception handling is performed in the exception
  section. User defined exceptions help to enforce
  business logic

90
Summary

• PL/SQL is a programming language for working with
  an Oracle database
• Scalar, composite and reference variables can be
  used
• The IF/THEN/ELSE decision control structure
  allows branching logic
• Five loop constructs allow repeating code

91
What can you do using PL/SQL

• Something procedural instead of declarative.
• Stored procedure
• Stored function
• Trigger
• Cursor manipulation
• Exception control
• .

92
Stored procedure

• Procedure, a function that does not return output
  through return value.
• Prototype, signature, and definition of the
  procedure,
• Procedure header includes name(a parameter list)
• A Parameter list includes a list of parameters
• A parameter list includes parameter name, in or
  out mode, and parameter data type.
• Name
• Procedure body
• Stored
• Precompiled ?
• modular