Introduction to PL/SQL

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 SQL*Plus • 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, don’t 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 SQL*Plus

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/server/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.name%TYPE; • 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 <label> • 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