You can add the NOT NULL option to any component declaration and so prevent the assigning of NULLs to that component. Each component has a unique name and specific datatype. Notice that the component declarations are like variable declarations. (deptno NUMBER(4) NOT NULL, - default is NULL allowed In the following example, you declare a RECORD type called DeptRecTyp: DECLARE You can declare record types and objects in the declarative part of any block, procedure, function, or package. A record that contains such components as the name, salary, and hire date of an employee would let you treat the data as a logical unit. This data is dissimilar in type but logically related. For example, suppose you have different kinds of data about an employee such as name, salary, hire date, and so on. Unlike PL/SQL tables, records have uniquely named components, which can belong to different datatypes. Objects of type RECORD are called records. The composite datatype RECORD lifts those restrictions. However, you cannot specify the datatypes of components in the record or define components of your own. You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched by a cursor.

For example, you reference the ninth row in the PL/SQL table named num_tab as follows: num_tab(9). You reference rows in a PL/SQL table using array-like syntax to specify the primary key value. The identifier num_tab represents an entire PL/SQL table. Once you define type NumTabTyp, you can declare PL/SQL tables of that type, as the next example shows: num_tab NumTabTyp In the following example, you declare a TABLE type called NumTabTyp. You can declare PL/SQL table types in the declarative part of any block, procedure, function, or package. The column can belong to any scalar type (such as CHAR, DATE, or NUMBER), but the primary key must belong to type BINARY_INTEGER, PLS_INTEGER or VARCHAR2. PL/SQL tables have only one column and use a primary key to give you array-like access to rows. Objects of type TABLE are called PL/SQL tables, which are modeled as (but not the same as) database tables. PL/SQL provides a composite datatype named TABLE. Implementation details in the package body are hidden and inaccessible. Only the declarations in the package specification are visible and accessible to applications.

PROCEDURE fire_employee (emp_id NUMBER) IS PROCEDURE hire_employee (empno NUMBER, ename CHAR. PACKAGE BODY emp_actions IS - package body In the following example, you 'package' two employment procedures: PACKAGE emp_actions IS - package specification The body defines cursors and subprograms it implements the specification. The specification is the interface to your applications it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. Packages usually have two parts: a specification and a body.

With the Procedural Database Extension, packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications. PL/SQL lets you bundle logically related types, program objects, and subprograms into a package. Table 7-1 shows the legal conversions between datatypes. The datatype of each actual parameter must be convertible to the datatype of its corresponding formal parameter. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. An OUT parameter lets you return values to the caller of a subprogram. An IN parameter lets you pass values to the subprogram being called. There are three parameter modes: IN (the default), OUT, and IN OUT. You use parameter modes to define the behavior of formal parameters. When called, this procedure accepts a new department name and location, selects the next value in a department-number database sequence, inserts the new number, name, and location into the dept table, then returns the new number to the caller. INSERT INTO dept VALUES (new_deptno, new_dname, new_loc) SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual For example, if you need a procedure that creates a new department, just write your own as follows: PROCEDURE create_dept That is, they let you tailor the PL/SQL language to suit your needs. Procedures and functions provide extensibility. Generally, you use a procedure to perform an action and a function to compute a value. PL/SQL has two types of subprograms called procedures and functions, which aid application development by letting you isolate operations.

Oracle pl sql execute procedure example