Unit 5 composite datatypes

Composite Datatypes, Named Blocks -Procedures, Functions, Packages & Triggers

1. UNIT V COMPOSITE DATA TYPES Dr R.Khanchana Assistant Professor Department of Computer Science Sri Ramakrishna College of Arts and Science for Women

2. COMPOSITE DATA TYPES Composite data types are like scalar data types. Scalar data types are atomic, because they do not consist of a group. Composite data types, on the other hand, are groups or collections. Examples RECORD TABLE nested TABLE VARRAY.

3. PL/SQL RECORDS PL/SQL records are similar in structure to a row in a database table. A record consists of components of any scalar, PL/SQL record, or PL/SQL table type. These components are known as fields, and they have their own values. PL/SQL records are similar in structure to “ struct ” in the C Language.

4. It makes easier by transferring the entire row in to a record rather than each column into a variable separately. A PL/SQL record is based on a cursor, a table’s row, or a user-defined record type. A record can be explicitly declared based on a cursor or a table: CURSOR cursorname IS SELECT query Recordname CursorName%ROWTYPE ; A record can also be based on another composite data type called TABLE. PL/SQL RECORDS

5. Creating PL/SQL Record This is used to create user-defined record. Create a RECORD type first, and then you declare a record with that RECORD type. The general syntax is TYPE recordtypename is RECORD (fieldname1 datatype | variable%TYPE | table.column%TYPE | table%ROWTYPE [[NOT NULL]:= | DEFAULT Expression][,fieldname2….,FielfName3…); recordname recordtypename ;

6. Example: TYPE employee_rectype IS RECORD (e-last VARCHAR2(15), e_first VARCHAR2(15), e_sal NUMBER(8,2)); employee_rec employee_rectype ; Employee_rectype is the user-defined RECORD type. Three fields are included in its structure; e_last,e_first and e_sal . The record employee_rec is a record declared with the user-defined record type employee_rectype . Each field declaration is similar to a scalar variable declaration. Creating PL/SQL Record

7. Another declaration with the %TYPE attribute. TYPE employee_rectype IS RECORD ( e_id NUMBER(3) NOT NULL:=111, e_last employee.Lname%TYPE , e_first employee.Fname%TYPE , e_sal employee.Salary%TYPE ); Employee_rec employee_rectype ; The NOT NULL constraint can be used for any field to prevent Null values, but that field must be initialized with a value Creating PL/SQL Record

8. Referencing Fields in a Record A filed in a record has a name that is given in the RECORD-type definition. Cannot reference a field by its name only; Must use the record name as a qualifier: r ecordname.fieldname The record name and field name are joined by a dot(.). e- sal can be referenced as employee_rec.e_sal Can use a field in a assignment statement to assign a value of it. EG: employee_rec.e_sal :=100000; employee_rec.e_last :=‘ jordan ’;

9. Working with records A record is known in the block where it is declared. When the block ends, the record no longer exists. Can assign values to a record from columns in a row by using SELECT statement or FETCH statement. The order of fields in a record must match the order of columns in the row. The record can be assigned to another record if both records have the same structure. A record can be set to NULL, and all fields will be set to NULL. Do not try to assign a NULL to a record that has fields with NOT NULL constraint. EG: Employee_rec :=NULL;

10. The record declared with %ROWTYPE has the same structure as the table’s row. For EG: emp_rec employee%ROWTYPE ; Emp_rec assumes the structure of the EMPLOYEE table. The fields in the emp_rec take their column names and their and their data types from the table. It is advantageous to use %ROWTYPE , because it does not require you to know the column names and their datatypes in the underlying table. If you change the datatype or size of a column, the record is created at execution time and is defined with updated table structure. The fields in the record declared with %ROWTYPE are referenced with the qualified name recordname,fieldname . Working with records

11. The SELECT query retrieves a row into the record based on the student ID entered at the prompt. The field in the record are printed using the recordname,fieldname notation.

12. NESTED RECORDS Nested record can be created including a record into another record as a field. The record that contains another record as a field is called the enclosing record. For EG: DECLARE TYPE address_rectype IS RECORD (first VARCHAR2(15), last VARCHAR2(15), street VARCHAR2(25), city VARCHAR2(15) State CHAR(2), Zip CHAR(5));

13. TYPE all_address_rectype IS RECORD ( home_address address_rectype , bus_address address_rectype , vacation address address_rectype ); address_rec all_address_rectype ; In this all_address_rectype nests address_rectype as a field type. If decide to use an unnested simple record, the record becomes cumbersome. There are six fields in address_rectype . Have to use six fields each for each of the three record fields home_address , bus_address , and vacation_address , which will result in a total of 18 fields. Nested records makes code more readable and easier to maintain. You can nest records to multiple levels. Dot notation is also used to reference fields in the nested situation. EG: address_rec.home_address.city references a field called city in the nested record home_address which is enclosed by record address_rec . NESTED RECORDS

14. PL/SQL TABLES A table, like a record, is a composite data structure in PL/SQL. A PL/SQL table is a single-dimensional structure with a collection of elements that store the same type of value. It is like array in other programming languages, in COBOL arrays are called tables. There is dissimilarities between a traditional array and a PL/SQL table. A table is a dynamic structure that is not constrained, whereas an array is not dynamic in most computer languages.

15. Declaring a PL/SQL Table A PL/SQL TABLE declaration is done in two steps , like a record declaration: 1. Declare a PL/SQL table type with a TYPE statement. The structure could use any of the scalar data types. 2. Declare an actual table based on the type declared in the previous step. SYNTAX: TYPE tabletypename IS TABLE OF datatype | variablename%TYPE | tablename.columnname%TYPE [NOT NULL] INDEX BY BINARY_INTEGER;

16. Example: TYPE deptname_table_type IS TABLE OF dept.DeptName%TYPE INDEX BY BINARY_INTEGER; TYPE major_table_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; Declare a table type with a scalar data type (VARCHAR2, DATE, BOOLEAN, or POSITIVE) or with declaration attribute %TYPE. The table may have a Null value. Add INDEX BY BINARY_INTEGER clause to the declaration Declaring a PL/SQL Table

17. This is the only available clause for indexing a table at present. Indexing speeds up the search process from the table. The primary key is stored internally in the table along with the data column. The table consist of two columns, the index/primary key column and the data column . syntax: tablename tabletypename ; Deptname_table deptname_table_type ; Major_table major_table_type ; Declaring a PL/SQL Table

18. The table represents primary key column and data column. cannot name these columns by user. The primary key has the type BINARY_INTEGER, and the data column is of any valid type. There is no limit on the number of elements but you cannot initialize elements of a table at declaration time. Declaring a PL/SQL Table

19. Referencing table elements/rows The row in a table are referenced in the same way that an element in an array is referenced. You cannot reference a table by its name only. Primary key value should be used in pair of parentheses as its subscript or index. tablename ( primarykeyvalue ) The valid assignment for table’s row: Deptname_table (5):=‘Human Resources’; Major_table (100):= v_major

20. Use expression or value other than a BINARY_INTEGER value, and PL/SQL will convert it. deptname_table (25.7):=‘Training; deptname_table (‘5’||’00’):=‘Research’; deptname_table (v_num+7):=‘Development’; In c and VB language will specify number of elements in an array when declaring an array. The memory locations are reserved for elements in an array at the declaration time. Referencing table elements/rows

21. In a PL/SQL table the primary key values are not pre-assigned. Row is created when you assign value to it. If a row does not exist and you try to access it, the PL/SQL predefined server exception N0_DATA_FOUND is raised. Can keep track of rows primary key values if you use them in sequence and keep track minimum and maximum value. Referencing table elements/rows

22. Assigning values to rows in PL/SQL Table Assigning values to tables in 3 ways Direct assignment Assignment in a loop Aggregate assignment Direct assignment Assign a values to a row with an assignment statement, as you already learned in the previous topic. This is preferred only few assignments are to be made. If entire database table values are to be assigned to a table, a looping method is preferable.

23. Assigning values to rows in PL/SQL Table Direct assignment Assign a values to a row with an assignment statement, as you already learned in the previous topic. This is preferred only few assignments are to be made. If entire database table values are to be assigned to a table, a looping method is preferable.

24. Assignment in a loop Any of the three PL/SQL loops could be used to assign values to rows in a table. The diagram represents all Sunday dates for year 2004 to table. The primary key index value will vary from 1 to 52. The table column will contain dates for 52 sundays . Assigning values to rows in PL/SQL Table

25. Assigning values to rows in PL/SQL Table

26. Aggregate assignment Table values can be assigned to another table. The datatypes of both tables must be compatible. When assign tables values to another table, the table receiving those values loses all its previous primary key values as well as its data column values. If assign empty table with no rows to another table with rows, the recipient table is created. It loses all its rows Both tables must have the same type for such an assignment. Assigning values to rows in PL/SQL Table

27. Built-In Table Methods The built-in table methods are procedures or functions that provide information about a PL/SQL table. The table lists built-in table methods and their use. SYNTAX: tablename.methodname [(index1 [, index2])]

28. It declares two table types, the two tables based on these TABLE types are parallel tables. The corresponding values in the two tables are related. The program populates these two tables using a cursor FOR loop. Another simple FOR loop is used to print information from two tables. Built-In Table Methods

29. TABLE OF RECORDS The PL/SQL table type is declared with a data type. Record type can be used as a table’s datatype . The %ROWTYPE declaration attribute can be used to define the record type. When a table is based on record, the record must consist of fields with scalar data type. The record must not contain nested record. The following will tell different ways to declare table types based on records.

30. PL/SQL VARRAYS A varray is another composite data type or collection type in PL/SQL. Varray stands for variable-size array . They are single-dimensional, bounded collections of elements with the same data type . They retain their ordering and subscripts when stored in and retrieved from a database table. They are similar to a PL/SQL table, and each element is assigned a subscript/index starting with 1.

31. A PL/SQL VARRAY type with a TYPE statement. The TYPE declaration includes a size to set the upper bound of a Varray . The lower bound is always one. Declare an actual varray based on the type declared in the previous step. SYNTAX: TYPE varraytypename IS VARRAY (size) OF ElementType [NOT NULL]; Varrayname varraytypename ; EG: TYPE Lname_varray_type IS VARRAY(5) OF employee.Lname%TYPE ; Lname_varray Lname_varray_type := Lname_varray_type (); PL/SQL VARRAYS

32. When a varray is declared, it is NULL. It must be initialized before referencing its elements. In second step of varray’s declaration, the assignment initializes it. The EXTEND method is used before adding a new element to varray . In example upperbound is would be five which limits number of elements to five. A cursor FOR loop then adds elements to the varray . The use of EXTEND method before assigning a value to the new element. PL/SQL VARRAYS

33. The COUNT method returns the number of elements, the LIMIT method the upper bound, the FIRST method the first subscript, and the LAST method the last subscript. In oracle 9i it is possible to create collection of a collection (multilevel collection) like a varray of varrays . EG:DECLARE TYPE varray_type1 IS VARRAY(3) of NUMBER; TYPE varray_type2 IS VARRAY(2) of varray_type1; PL/SQL VARRAYS

34. PL/SQL NAMED BLOCKS An anonymous block can call other types of blocks are called procedures and functions. Procedures and functions are called named blocks and they can be called with parameters. Anonymous block can be nested within a procedure, function, or other anonymous block. The purpose of procedure or functional call is to modularize a PL/SQL program. A named PL/SQL block is compiled when it is created or altered. Compilation process consist of three steps: syntax error checking, binding and p-code generation.

35. A syntactically error-free program’s variables are assigned storage in binding stage. The list of instructions called p-code is generated for PL/SQL engine. P-code stored in database for all named blocks. Procedures Functions Packages Triggers PL/SQL NAMED BLOCKS

36. A procedure is a named PL/SQL program block that can perform one or more tasks. A procedure is the building block of modular programming. SYNTAX: CREATE [OR REPLACE] PROCEDURE procedurename [ (parameter1 [, parameter2..]) ] IS [constant/variable declaration] BEGIN executable statements [EXCEPTION exception handling statements ] END [ procedurename ]; PROCEDURES

37. Where procedurename is user-supplied name that follows rules used in naming identifiers. Parameter list has name of parameters passed to procedure by calling program as well as information passed from procedure to calling program. PROCEDURES

38. Local constants and variables are declared after reserved word IS. If there are no local identifiers to declare, there I nothing between reserved words IS and BEGIN. The executable statements are written after BEGIN and before EXCEPTION or END. There must be atleast one executable statement in the body. The reserved word exception and exception handling statements are optional. PROCEDURES

39. CALLING A PROCEDURE Call to procedure is made through executable PL/SQL statement. The procedure is called by specifying its name along with list of parameters in paranthesis . Call statement ends with semicolon. Syntax : procedurename [ (parameter1,…) ]; EG: monthly_salary ( v_salary ); Calculate_net (v_monthly_salary,0.28); Display_messages ; Can use variable, constant, expression or literal values as a parameter.

40. Procedure definition that comes before reserved word IS is called procedure header. Procedure header contains name of procedure and parameter list with data types. CREATE OR REPLACE PROCEDURE monthly_salary ( v_salary_in IN employee.salary%TYPE ) CREATE OR REPLACE PROCEURE calculate_net ( v_monthly_salary_in Innempliyee.salary%TYPE , v_taxrate_in IN NUMBER) CREATE OR REPLACE PROCEDURE display_messages PROCEDURE HEADER

41. Parameter in the header contains name of parameter along with its type. Parameters names used in procedure header do not have to be same as the names used in call. Number of parameters in call and in header must match, and parameters must be in same order. PROCEDURE HEADER

42. It contains declaration executable, and exception-handling section. Declaration and exception-handling section are optional, executable section contains action statements, and it must contain atleast one. Procedure body starts after reserved word IS. If there is no local declaration, IS is followed by reserved word BEGIN. Body ends with reserved word END. There can be more than one END statement in program, it is good idea to use procedure name as optional label after END. Procedure Body

43. Parameters Parameters are used to pass values back and forth from calling environment to oracle server. Values passed are processed and/or returned with a procedure execution. There are three types of parameters: IN,OUT, AND IN OUT.

44. ACTUAL AND FORMAL PARAMETERS: Parameters passed in call statement are called actual parameter. Parameter names in header of module are called formal parameters. Actual parameters and their matching formal parameters must have same data types. On procedure call, parameters are passed without data types. Procedure header contains formal parameters with data types, but size of data type is not required.

45. Matching actual and formal parameters There are two different ways in PL/SQL to link formal and actual parameter In positional notation, formal parameter is linked with an actual parameter implicitly by position. Positional notation is more commonly used for parameter matching. In named notation, formal parameter is linked with an actual parameter explicitly by name. Formal parameter and actual parameter are linked in call statement with symbol => SYNTAX: formalparametername => argumentvalue Eg : EMPNO => 543

46. If a procedure with same name already exists, it is replaced. You can type in any editor such as notepad. When you run it procedure created message is displayed. Procedure named dependent_info is compiled into p-code and then stored in database for future execution. It can be executed in SQL*plus with EXECUTE command. Eg : SQL>EXECUTE dependent_info Matching actual and formal parameters

47. If you receive error use command SHOW ERROR.Procedure becomes invalid if table on which it is based is deleted or altered. Compiled version of procedure is stored and version should be re-compiled in case of alterations to table. Recompile using procedure ALTER PROCEDURE procedurename COMPILE; Procedure search_emp receives three parameters: i_empid , o_last , o_first , are used for writing. Procedure searches for employee’s name based on V_ID that is passed If employee Is not found exception is handled in procedure. If employee is found procedure send out last name last name and first name. Matching actual and formal parameters

48. FUNCTIONS Function is a named PL/SQL block, it is a stored block. Difference between function and procedure is function always returns value to calling block. Function is characterized as: Function can be passed zero or more parameters Function must have explicit RETURN statement in executable section to return value. Data type of return value must be declared in function’s header. A function cannot be executed as a stand-alone program.

49. Function may have parameter of IN,OUT and IN OUT types, but primary use of function is to return a value with an explicit RETURN statement. Use of OUT and IN OUT parameter types in function is rare and considered to be bad practice. SYNTAX: CREATE [ OR REPLACE ] FUNCTION functionname [ (parameter1 [, parameter2…]) ] RETURN DataType IS [ constant | variable declaration ] BEGIN Executable statements RETURN returnvalue [ EXCEPTION Exception-handling statements RETURN returnvalue ] END [ functionname ]; FUNCTIONS

50. RETURN statement does not have to be last statement in body of function. Body may contain more than one RETURN statement, but only one is executed with each function call. If you have return statement in exception you need one return for each exception. FUNCTIONS

51. FUNCTION HEADER Function header comes before reserved word IS. Headers contains name of function, list of parameters and RETURN data type. FUNCTION BODY : It should contain atleast one executable statement, if no declaration reserved word BEGIN follows IS. if there is no exception handler then EXCEPTION keyword could be omitted. Function name label next to END is optional. There can be more than one return statement but only one RETURN is executed in function call.

52. RETURN DATATYPES Function can return a value with scalar data type, such as VARCHAR2, NUMBER, BINARY_INTEGER, or BOOLEAN. It also return composite/complex datatype such as PL/SQL table, PL/SQL record, nested table, VARRAY or LOB.

53. Calling a function Function call is similar to procedure call, function call is made via executable statement such as assignment, selection or output statement. V_salary := get_salary (& emp_id ); IF emp_exists ( v_empid )…

54. Calling a function from SQL statement: Stored function block can be called from SQL statement, such a SELECT. For eg : SELECT get_deptname (10) FROM dual; This function call with parameter 10 will return dapartment name Finance in N2 example tables. Substitution variables can be used as parameters. Calling a function

55. Package is a collection of PL/SQL objects. Objects in a package are grouped within BEGIN and END blocks. Packages may contain: Cursors Scalar variables Composite variables Consonants Exception names TYPE declarations for records and tables Procedures and f Functions. Oracle has many built-in packages, objects in package is declared as public objects. Can restrict access to package to its specification, and can hide actual programming aspect. PACKAGES

56. Package follows some rules of object-oriented programming and it gives programmers some object-oriented capabilities. Package complies successfully even without a body if specification compiles. When object in package is referenced for first time, entire package is loaded into memory. All package elements are available from that point on, because entire package stays in memory. One-time loading improves performance and is very useful when functions and procedures in it are accessed frequently. Package follows top-down design. PACKAGES

57. Structure of a package: package provides extra layer to module. A module has a header and a body, whereas package has a specification and a body. Module header specifies name and parameters, which tell us how to call that module. Package specification tell us how to call different modules within a package. PACKAGE SPECIFICATION: It does not contain any code, it contains information about elements of package. It contains definition of functions and procedure, declaration of global or public variables and anything can be declared in PL/SQL block declaration. PACKAGES

58. Objects in specification of package are called public objects. Syntax: CREATE [OR REPLACE ] PACKAGE packagename IS [constant, variable and type declarations] [exception declarations] [cursor specifications] [function specifications] [procedure specification] END [ packagename ]; Example : PACKAGE bb_team IS total_players CONSTANT INTEGER :=12; Player_on_di EXCEPTION; FUNCTION team_average (points IN NUMBER, players IN NUMBER) RETURN NUMBER; END bb_team ; PACKAGES

59. Package body: Package body contains actual programming code for modules described in specification section. It contains code for modules not described in specification section. Module code in body without description in specification module is called private module. SYNTAX: PACKAGE BODY package name IS [variable and type declarations] [cursor specifications and SELECT queries] [header and body of functions] [header and body of procedures] [BEGIN executable statements] [EXCEPTION exception handlers ] END [ packagename ]; PACKAGES

60. When you reference object in a package you must qualify it with name of that package using dot notation. If you do not use dot notation to reference a object compilation will fail. Eg : IF bb_team.total_players <10 THEN EXCEPTION WHEN bb_team.player_on_dl THEN Where total_players and player_on_dl are modules in bb_team package. PACKAGES

61. Variables, constants, exceptions are declared in specification must not be declared again in package body. Number of cursor and module definition in specification must match number of cursor and module headers in body. Any element declared in specification can be referenced in body. PACKAGES

62. TRIGGERS Database trigger( Trigger ) is a PL/SQL block. It is stored in database and is called automatically when a triggering event occurs. User cannot call a trigger explicitly. Triggering event is based on Data Manipulation Language statement such as INSERT,UPDATE or DELETE. Execution of trigger is also called firing the trigger.

63. CREATE means creating new trigger and REPLACE means replacing a existing trigger. Trigger cannot use transaction control language statement, such as COMMIT, ROLLBACK, or SAVEPOINT. Procedure or function called by trigger cannot perform transaction control language. Variable in a trigger cannot be declared with LOGIN or LONG RAW data types. Simple DML Triggers Before Trigger After Trigger Instead of Trigger TRIGGERS

64. BEFORE TRIGGERS : Before trigger is fired before execution of DML statement. It is used when you want to plug into some values in new row. AFTER TRIGGER: After trigger fires after DML statement is executed. It utilizes built-in boolean functions INSERING,UPDATING and DELETING. If triggering event is one of three DML statements, function related to DML statement returns TRUE and other two return FALSE. FOR EACH ROW command is known as row trigger. Statement trigger is fired only once for statement, irrespective of number of rows affected by DML statement. TRIGGERS

65. Instead of trigger : BEFORE and AFTER triggers are based on database tables. Oracle provides instead of triggers which is not based on table, but is based on view. INSTEAD OF trigger is row trigger, if a view is based on SELECT query that contains set operators, group functions, GROUP BY and HAVING clauses, DISTINCT function, join or ROWNUM pseudocolumn , data manipulation is not possible through it. INSTEAD OF trigger is used to modify a table that cannot be modified through a view. TRIGGERS

66. This trigger fires instead of DML statements such as DELETE,UPDATE,INSERT.

67. Formal parameters in a modules header must match actual parameters in call to module with positional or named notation. Function call is made via executable PL/SQL statement, such as assignment or an IF statement. Structure of package includes a specification and a body. Trigger is based on DML statements such as INSERT,DELETE & UPDATE. INSTEAD OF trigger is based on a view instead of database table. TRIGGERS

68. oracle maintains informative data dictionary. Few data dictionary views are useful for getting information about stored PL/SQL blocks. USER_PROCEDURES, USER-TRIGGERS,USR-SOURCE, USER_OBJECTS and USER_ERRORS. DESCRIBE command is used to find names of column in each data dictionary view. Procedure body consist of declaration, executable and exception option. Three types of parameters IN, OUT and INOUT type passes value into subprogram and returns value. DATA DICTIONARY VIEWS: