Category: plsql

PL/SQL – Transactions ”; Previous Next In this chapter, we will discuss the transactions in PL/SQL. A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database. A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone. Starting and Ending a Transaction A transaction has a beginning and an end. A transaction starts when one of the following events take place − The first SQL statement is performed after connecting to the database. At each new SQL statement issued after a transaction is completed. A transaction ends when one of the following events take place − A COMMIT or a ROLLBACK statement is issued. A DDL statement, such as CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed. A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed. User disconnects from the database. User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed. SQL*Plus terminates abnormally, a ROLLBACK is automatically performed. A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement. Committing a Transaction A transaction is made permanent by issuing the SQL command COMMIT. The general syntax for the COMMIT command is − COMMIT; For example, INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”kaushik”, 23, ”Kota”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, ”Komal”, 22, ”MP”, 4500.00 ); COMMIT; Rolling Back Transactions Changes made to the database without COMMIT could be undone using the ROLLBACK command. The general syntax for the ROLLBACK command is − ROLLBACK [TO SAVEPOINT < savepoint_name>]; When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes − ROLLBACK; Savepoints Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command. The general syntax for the SAVEPOINT command is − SAVEPOINT < savepoint_name >; For example INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ”Rajnish”, 27, ”HP”, 9500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, ”Riddhi”, 21, ”WB”, 4500.00 ); SAVEPOINT sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000; ROLLBACK TO sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 7; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 8; COMMIT; ROLLBACK TO sav1 − This statement rolls back all the changes up to the point, where you had marked savepoint sav1. After that, the new changes that you make will start. Automatic Transaction Control To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as − SET AUTOCOMMIT ON; You can turn-off the auto commit mode using the following command − SET AUTOCOMMIT OFF; Print Page Previous Next Advertisements ”;

PL/SQL – Functions ”; Previous Next In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too. Creating a Function A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows − CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, …])] RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name]; Where, function-name specifies the name of the function. [OR REPLACE] option allows the modification of an existing function. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure. The function must contain a return statement. The RETURN clause specifies the data type you are going to return from the function. function-body contains the executable part. The AS keyword is used instead of the IS keyword for creating a standalone function. Example The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table. We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; / When the above code is executed using the SQL prompt, it will produce the following result − Function created. Calling a Function While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function. A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program. To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block − DECLARE c number(2); BEGIN c := totalCustomers(); dbms_output.put_line(”Total no. of Customers: ” || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Total no. of Customers: 6 PL/SQL procedure successfully completed. Example The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values. DECLARE a number; b number; c number; FUNCTION findMax(x IN number, y IN number) RETURN number IS z number; BEGIN IF x > y THEN z:= x; ELSE Z:= y; END IF; RETURN z; END; BEGIN a:= 23; b:= 45; c := findMax(a, b); dbms_output.put_line(” Maximum of (23,45): ” || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Maximum of (23,45): 45 PL/SQL procedure successfully completed. PL/SQL Recursive Functions We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion. To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as − n! = n*(n-1)! = n*(n-1)*(n-2)! … = n*(n-1)*(n-2)*(n-3)… 1 The following program calculates the factorial of a given number by calling itself recursively − DECLARE num number; factorial number; FUNCTION fact(x number) RETURN number IS f number; BEGIN IF x=0 THEN f := 1; ELSE f := x * fact(x-1); END IF; RETURN f; END; BEGIN num:= 6; factorial := fact(num); dbms_output.put_line(” Factorial ”|| num || ” is ” || factorial); END; / When the above code is executed at the SQL prompt, it produces the following result − Factorial 6 is 720 PL/SQL procedure successfully completed. Print Page Previous Next Advertisements ”;

PL/SQL – Variables ”; Previous Next In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable”s memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable. The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name. PL/SQL programming language allows to define various types of variables, such as date time data types, records, collections, etc. which we will cover in subsequent chapters. For this chapter, let us study only basic variable types. Variable Declaration in PL/SQL PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable”s value and the storage location is identified by the variable name. The syntax for declaring a variable is − variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid PL/SQL data type or any user defined data type which we already have discussed in the last chapter. Some valid variable declarations along with their definition are shown below − sales number(10, 2); pi CONSTANT double precision := 3.1415; name varchar2(25); address varchar2(100); When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. For example − sales number(10, 2); name varchar2(25); address varchar2(100); Initializing Variables in PL/SQL Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following − The DEFAULT keyword The assignment operator For example − counter binary_integer := 0; greetings varchar2(20) DEFAULT ”Have a Good Day”; You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable. It is a good programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the following example which makes use of various types of variables − DECLARE a integer := 10; b integer := 20; c integer; f real; BEGIN c := a + b; dbms_output.put_line(”Value of c: ” || c); f := 70.0/3.0; dbms_output.put_line(”Value of f: ” || f); END; / When the above code is executed, it produces the following result − Value of c: 30 Value of f: 23.333333333333333333 PL/SQL procedure successfully completed. Variable Scope in PL/SQL PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope − Local variables − Variables declared in an inner block and not accessible to outer blocks. Global variables − Variables declared in the outermost block or a package. Following example shows the usage of Local and Global variables in its simple form − DECLARE — Global variables num1 number := 95; num2 number := 85; BEGIN dbms_output.put_line(”Outer Variable num1: ” || num1); dbms_output.put_line(”Outer Variable num2: ” || num2); DECLARE — Local variables num1 number := 195; num2 number := 185; BEGIN dbms_output.put_line(”Inner Variable num1: ” || num1); dbms_output.put_line(”Inner Variable num2: ” || num2); END; END; / When the above code is executed, it produces the following result − Outer Variable num1: 95 Outer Variable num2: 85 Inner Variable num1: 195 Inner Variable num2: 185 PL/SQL procedure successfully completed. Assigning SQL Query Results to PL/SQL Variables You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept. Let us create a table named CUSTOMERS − (For SQL statements, please refer to the SQL tutorial) CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Table Created Let us now insert some values in the table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”kaushik”, 23, ”Kota”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, ”Komal”, 22, ”MP”, 4500.00 ); The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL − DECLARE c_id customers.id%type := 1; c_name customers.name%type; c_addr customers.address%type; c_sal customers.salary%type; BEGIN SELECT name, address, salary INTO c_name, c_addr, c_sal FROM customers WHERE id = c_id; dbms_output.put_line (”Customer ” ||c_name || ” from ” || c_addr || ” earns ” || c_sal); END; / When the above code is executed, it produces the following result − Customer Ramesh from Ahmedabad earns 2000 PL/SQL procedure completed successfully Print Page Previous Next Advertisements ”;

PL/SQL – Collections ”; Previous Next In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection. PL/SQL provides three collection types − Index-by tables or Associative array Nested table Variable-size array or Varray Oracle documentation provides the following characteristics for each type of collections − Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes Variablesize array (Varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes We have already discussed varray in the chapter ”PL/SQL arrays”. In this chapter, we will discuss the PL/SQL tables. Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot. Index-By Table An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string. An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name; Example Following example shows how to create a table to store integer values along with names and later it prints the same list of names. DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN — adding elements to the table salary_list(”Rajnish”) := 62000; salary_list(”Minakshi”) := 75000; salary_list(”Martin”) := 100000; salary_list(”James”) := 78000; — printing the table name := salary_list.FIRST; WHILE name IS NOT null LOOP dbms_output.put_line (”Salary of ” || name || ” is ” || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Salary of James is 78000 Salary of Martin is 100000 Salary of Minakshi is 75000 Salary of Rajnish is 62000 PL/SQL procedure successfully completed. Example Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list(counter) := n.name; dbms_output.put_line(”Customer(”||counter||”):”||name_lis t(counter)); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed Nested Tables A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects − An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically. An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it. A nested table is created using the following syntax − TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name; This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause. A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database. Example The following examples illustrate the use of nested table − DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER; names names_table; marks grades; total integer; BEGIN names := names_table(”Kavita”, ”Pritam”, ”Ayan”, ”Rishav”, ”Aziz”); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line(”Total ”|| total || ” Students”); FOR i IN 1 .. total LOOP dbms_output.put_line(”Student:”||names(i)||”, Marks:” || marks(i)); end loop; END; / When the above code is executed at the SQL prompt, it produces the following result − Total 5 Students Student:Kavita, Marks:98 Student:Pritam, Marks:97 Student:Ayan, Marks:78 Student:Rishav, Marks:87 Student:Aziz, Marks:92 PL/SQL procedure successfully completed. Example Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal |

PL/SQL – Strings ”; Previous Next The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three kinds of strings − Fixed-length strings − In such strings, programmers specify the length while declaring the string. The string is right-padded with spaces to the length so specified. Variable-length strings − In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place. Character large objects (CLOBs) − These are variable-length strings that can be up to 128 terabytes. PL/SQL strings could be either variables or literals. A string literal is enclosed within quotation marks. For example, ”This is a string literal.” Or ”hello world” To include a single quote inside a string literal, you need to type two single quotes next to one another. For example, ”this isn””t what it looks like” Declaring String Variables Oracle database provides numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with an ”N” are ”national character set” datatypes, that store Unicode character data. If you need to declare a variable-length string, you must provide the maximum length of that string. For example, the VARCHAR2 data type. The following example illustrates declaring and using some string variables − DECLARE name varchar2(20); company varchar2(30); introduction clob; choice char(1); BEGIN name := ”John Smith”; company := ”Infotech”; introduction := ” Hello! I””m John Smith from Infotech.”; choice := ”y”; IF choice = ”y” THEN dbms_output.put_line(name); dbms_output.put_line(company); dbms_output.put_line(introduction); END IF; END; / When the above code is executed at the SQL prompt, it produces the following result − John Smith Infotech Hello! I”m John Smith from Infotech. PL/SQL procedure successfully completed To declare a fixed-length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical − red_flag CHAR(1) := ”Y”; red_flag CHAR := ”Y”; PL/SQL String Functions and Operators PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL − S.No Function & Purpose 1 ASCII(x); Returns the ASCII value of the character x. 2 CHR(x); Returns the character with the ASCII value of x. 3 CONCAT(x, y); Concatenates the strings x and y and returns the appended string. 4 INITCAP(x); Converts the initial letter of each word in x to uppercase and returns that string. 5 INSTR(x, find_string [, start] [, occurrence]); Searches for find_string in x and returns the position at which it occurs. 6 INSTRB(x); Returns the location of a string within another string, but returns the value in bytes. 7 LENGTH(x); Returns the number of characters in x. 8 LENGTHB(x); Returns the length of a character string in bytes for single byte character set. 9 LOWER(x); Converts the letters in x to lowercase and returns that string. 10 LPAD(x, width [, pad_string]) ; Pads x with spaces to the left, to bring the total length of the string up to width characters. 11 LTRIM(x [, trim_string]); Trims characters from the left of x. 12 NANVL(x, value); Returns value if x matches the NaN special value (not a number), otherwise x is returned. 13 NLS_INITCAP(x); Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT. 14 NLS_LOWER(x) ; Same as the LOWER function except that it can use a different sort method as specified by NLSSORT. 15 NLS_UPPER(x); Same as the UPPER function except that it can use a different sort method as specified by NLSSORT. 16 NLSSORT(x); Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used. 17 NVL(x, value); Returns value if x is null; otherwise, x is returned. 18 NVL2(x, value1, value2); Returns value1 if x is not null; if x is null, value2 is returned. 19 REPLACE(x, search_string, replace_string); Searches x for search_string and replaces it with replace_string. 20 RPAD(x, width [, pad_string]); Pads x to the right. 21 RTRIM(x [, trim_string]); Trims x from the right. 22 SOUNDEX(x) ; Returns a string containing the phonetic representation of x. 23 SUBSTR(x, start [, length]); Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied. 24 SUBSTRB(x); Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems. 25 TRIM([trim_char FROM) x); Trims characters from the left and right of x. 26 UPPER(x); Converts the letters in x to uppercase and returns that string. Let us now work out on a few examples to understand the concept − Example 1 DECLARE greetings varchar2(11) := ”hello world”; BEGIN dbms_output.put_line(UPPER(greetings)); dbms_output.put_line(LOWER(greetings)); dbms_output.put_line(INITCAP(greetings)); /* retrieve the first character in the string */ dbms_output.put_line ( SUBSTR (greetings, 1, 1)); /* retrieve the last character in the string */ dbms_output.put_line ( SUBSTR (greetings, -1, 1)); /* retrieve five characters, starting from the seventh position. */ dbms_output.put_line ( SUBSTR (greetings, 7, 5)); /* retrieve the remainder of the string, starting from the second position. */ dbms_output.put_line ( SUBSTR (greetings, 2)); /* find the location of the first “e” */ dbms_output.put_line ( INSTR (greetings, ”e”)); END; / When the above code is executed at the SQL prompt, it produces the following result − HELLO WORLD hello world Hello World h d World ello World 2 PL/SQL procedure successfully completed. Example 2 DECLARE greetings varchar2(30) := ”……Hello World…..”; BEGIN dbms_output.put_line(RTRIM(greetings,”.”)); dbms_output.put_line(LTRIM(greetings, ”.”)); dbms_output.put_line(TRIM( ”.” from greetings)); END; / When the above code is executed at the SQL prompt, it produces the following result − ……Hello World Hello World….. Hello World PL/SQL procedure successfully completed. Print Page Previous Next

PL/SQL – Exceptions ”; Previous Next In this chapter, we will discuss Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions − System-defined exceptions User-defined exceptions Syntax for Exception Handling The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN − DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling goes here > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements …….. WHEN others THEN exception3-handling-statements END; Example Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters − DECLARE c_id customers.id%type := 8; c_name customerS.Name%type; c_addr customers.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE (”Name: ”|| c_name); DBMS_OUTPUT.PUT_LINE (”Address: ” || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output.put_line(”No such customer!”); WHEN others THEN dbms_output.put_line(”Error!”); END; / When the above code is executed at the SQL prompt, it produces the following result − No such customer! PL/SQL procedure successfully completed. The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block. Raising Exceptions Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception − DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END; You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way. User-defined Exceptions PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. The syntax for declaring an exception is − DECLARE my-exception EXCEPTION; Example The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised. DECLARE c_id customers.id%type := &cc_id; c_name customerS.Name%type; c_addr customers.address%type; — user defined exception ex_invalid_id EXCEPTION; BEGIN IF c_id <= 0 THEN RAISE ex_invalid_id; ELSE SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE (”Name: ”|| c_name); DBMS_OUTPUT.PUT_LINE (”Address: ” || c_addr); END IF; EXCEPTION WHEN ex_invalid_id THEN dbms_output.put_line(”ID must be greater than zero!”); WHEN no_data_found THEN dbms_output.put_line(”No such customer!”); WHEN others THEN dbms_output.put_line(”Error!”); END; / When the above code is executed at the SQL prompt, it produces the following result − Enter value for cc_id: -6 (let”s enter a value -6) old 2: c_id customers.id%type := &cc_id; new 2: c_id customers.id%type := -6; ID must be greater than zero! PL/SQL procedure successfully completed. Pre-defined Exceptions PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions − Exception Oracle Error SQLCODE Description ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value. CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with unique index. INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor. INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number. LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with an invalid username or password. NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows. NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being connected to the database. PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem. ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type. SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized. STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted. TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row. VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs. ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero. Print Page Previous Next Advertisements ”;

PL/SQL – Arrays ”; Previous Next In this chapter, we will discuss arrays in PL/SQL. The PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered collection of data, however it is often better to think of an array as a collection of variables of the same type. All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element. An array is a part of collection type data and it stands for variable-size arrays. We will study other collection types in a later chapter ”PL/SQL Collections”. Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. Creating a Varray Type A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray. The basic syntax for creating a VARRAY type at the schema level is − CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type> Where, varray_type_name is a valid attribute name, n is the number of elements (maximum) in the varray, element_type is the data type of the elements of the array. Maximum size of a varray can be changed using the ALTER TYPE statement. For example, CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); / Type created. The basic syntax for creating a VARRAY type within a PL/SQL block is − TYPE varray_type_name IS VARRAY(n) of <element_type> For example − TYPE namearray IS VARRAY(5) OF VARCHAR2(10); Type grades IS VARRAY(5) OF INTEGER; Let us now work out on a few examples to understand the concept − Example 1 The following program illustrates the use of varrays − DECLARE type namesarray IS VARRAY(5) OF VARCHAR2(10); type grades IS VARRAY(5) OF INTEGER; names namesarray; marks grades; total integer; BEGIN names := namesarray(”Kavita”, ”Pritam”, ”Ayan”, ”Rishav”, ”Aziz”); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line(”Total ”|| total || ” Students”); FOR i in 1 .. total LOOP dbms_output.put_line(”Student: ” || names(i) || ” Marks: ” || marks(i)); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Total 5 Students Student: Kavita Marks: 98 Student: Pritam Marks: 97 Student: Ayan Marks: 78 Student: Rishav Marks: 87 Student: Aziz Marks: 92 PL/SQL procedure successfully completed. Please note − In Oracle environment, the starting index for varrays is always 1. You can initialize the varray elements using the constructor method of the varray type, which has the same name as the varray. Varrays are one-dimensional arrays. A varray is automatically NULL when it is declared and must be initialized before its elements can be referenced. Example 2 Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ Following example makes the use of cursor, which you will study in detail in a separate chapter. DECLARE CURSOR c_customers is SELECT name FROM customers; type c_list is varray (6) of customers.name%type; name_list c_list := c_list(); counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter + 1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line(”Customer(”||counter ||”):”||name_list(counter)); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed. Print Page Previous Next Advertisements ”;

PL/SQL – Triggers ”; Previous Next In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events − A database manipulation (DML) statement (DELETE, INSERT, or UPDATE) A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers can be defined on the table, view, schema, or database with which the event is associated. Benefits of Triggers Triggers can be written for the following purposes − Generating some derived column values automatically Enforcing referential integrity Event logging and storing information on table access Auditing Synchronous replication of tables Imposing security authorizations Preventing invalid transactions Creating Triggers The syntax for creating a trigger is − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; Where, CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name. {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view. {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation. [OF col_name] − This specifies the column name that will be updated. [ON table_name] − This specifies the name of the table associated with the trigger. [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE. [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger. WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers. Example To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values − CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary – :OLD.salary; dbms_output.put_line(”Old salary: ” || :OLD.salary); dbms_output.put_line(”New salary: ” || :NEW.salary); dbms_output.put_line(”Salary difference: ” || sal_diff); END; / When the above code is executed at the SQL prompt, it produces the following result − Trigger created. The following points need to be considered here − OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers. If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state. The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table. Triggering a Trigger Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ”Kriti”, 22, ”HP”, 7500.00 ); When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result − Old salary: New salary: 7500 Salary difference: Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table − UPDATE customers SET salary = salary + 500 WHERE id = 2; When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result − Old salary: 1500 New salary: 2000 Salary difference: 500 Print Page Previous Next Advertisements ”;

PL/SQL – Constants and Literals ”; Previous Next In this chapter, we will discuss constants and literals in PL/SQL. A constant holds a value that once declared, does not change in the program. A constant declaration specifies its name, data type, and value, and allocates storage for it. The declaration can also impose the NOT NULL constraint. Declaring a Constant A constant is declared using the CONSTANT keyword. It requires an initial value and does not allow that value to be changed. For example − PI CONSTANT NUMBER := 3.141592654; DECLARE — constant declaration pi constant number := 3.141592654; — other declarations radius number(5,2); dia number(5,2); circumference number(7, 2); area number (10, 2); BEGIN — processing radius := 9.5; dia := radius * 2; circumference := 2.0 * pi * radius; area := pi * radius * radius; — output dbms_output.put_line(”Radius: ” || radius); dbms_output.put_line(”Diameter: ” || dia); dbms_output.put_line(”Circumference: ” || circumference); dbms_output.put_line(”Area: ” || area); END; / When the above code is executed at the SQL prompt, it produces the following result − Radius: 9.5 Diameter: 19 Circumference: 59.69 Area: 283.53 Pl/SQL procedure successfully completed. The PL/SQL Literals A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, TRUE, 786, NULL, ”tutorialspoint” are all literals of type Boolean, number, or string. PL/SQL, literals are case-sensitive. PL/SQL supports the following kinds of literals − Numeric Literals Character Literals String Literals BOOLEAN Literals Date and Time Literals The following table provides examples from all these categories of literal values. S.No Literal Type & Example 1 Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 3.14159 +7800.00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3 2 Character Literals ”A” ”%” ”9” ” ” ”z” ”(” 3 String Literals ”Hello, world!” ”Tutorials Point” ”19-NOV-12” 4 BOOLEAN Literals TRUE, FALSE, and NULL. 5 Date and Time Literals DATE ”1978-12-25”; TIMESTAMP ”2012-10-29 12:01:01”; To embed single quotes within a string literal, place two single quotes next to each other as shown in the following program − DECLARE message varchar2(30):= ”That””s tutorialspoint.com!”; BEGIN dbms_output.put_line(message); END; / When the above code is executed at the SQL prompt, it produces the following result − That”s tutorialspoint.com! PL/SQL procedure successfully completed. Print Page Previous Next Advertisements ”;

PL/SQL – Procedures ”; Previous Next In this chapter, we will discuss Procedures in PL/SQL. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the ”Modular design”. A subprogram can be invoked by another subprogram or program which is called the calling program. A subprogram can be created − At the schema level Inside a package Inside a PL/SQL block At the schema level, subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or the CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement. A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. We will discuss packages in the chapter ”PL/SQL – Packages”. PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms − Functions − These subprograms return a single value; mainly used to compute and return a value. Procedures − These subprograms do not return a value directly; mainly used to perform an action. This chapter is going to cover important aspects of a PL/SQL procedure. We will discuss PL/SQL function in the next chapter. Parts of a PL/SQL Subprogram Each PL/SQL subprogram has a name, and may also have a parameter list. Like anonymous PL/SQL blocks, the named blocks will also have the following three parts − S.No Parts & Description 1 Declarative Part It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution. 2 Executable Part This is a mandatory part and contains statements that perform the designated action. 3 Exception-handling This is again an optional part. It contains the code that handles run-time errors. Creating a Procedure A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows − CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, …])] {IS | AS} BEGIN < procedure_body > END procedure_name; Where, procedure-name specifies the name of the procedure. [OR REPLACE] option allows the modification of an existing procedure. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure. procedure-body contains the executable part. The AS keyword is used instead of the IS keyword for creating a standalone procedure. Example The following example creates a simple procedure that displays the string ”Hello World!” on the screen when executed. CREATE OR REPLACE PROCEDURE greetings AS BEGIN dbms_output.put_line(”Hello World!”); END; / When the above code is executed using the SQL prompt, it will produce the following result − Procedure created. Executing a Standalone Procedure A standalone procedure can be called in two ways − Using the EXECUTE keyword Calling the name of the procedure from a PL/SQL block The above procedure named ”greetings” can be called with the EXECUTE keyword as − EXECUTE greetings; The above call will display − Hello World PL/SQL procedure successfully completed. The procedure can also be called from another PL/SQL block − BEGIN greetings; END; / The above call will display − Hello World PL/SQL procedure successfully completed. Deleting a Standalone Procedure A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is − DROP PROCEDURE procedure-name; You can drop the greetings procedure by using the following statement − DROP PROCEDURE greetings; Parameter Modes in PL/SQL Subprograms The following table lists out the parameter modes in PL/SQL subprograms − S.No Parameter Mode & Description 1 IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference. 2 OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value. 3 IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value. IN & OUT Mode Example 1 This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters. DECLARE a number; b number; c number; PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z:= x; ELSE z:= y; END IF; END; BEGIN a:= 23; b:= 45; findMin(a, b, c); dbms_output.put_line(” Minimum of (23, 45) : ” || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Minimum of (23, 45) : 23 PL/SQL procedure successfully completed. IN & OUT Mode Example 2 This procedure computes the square of value of a passed value. This example shows how we can