PL/SQL data type

PL/SQL data type

1. Variables and constants
Variable Syntax: variable_name data_type [[not null] [: = VALUE]
Note: not null is a non-NULL constraint on variable definitions. It must assign a non-NULL initial value to the variable and cannot be changed to NULL at any time.
-- Sample variables:
SQL> SERVEROUT ON
SQL> DECLARE
V_num NUMBER (4 );
BEGIN
V_num: = 20;
DBMS_OUTPUT.PUT_LINE ('variable name v_num value: '| v_num );
END;
/

CONSTANT Syntax: constant_name CONSTANT data_type [: = VALUE]
-- Constant example:
DECLARE
C_num constant number (4): = 100;
BEGIN
DBMS_OUTPUT.PUT_LINE ('constant name c_num value: '| c_num );
END;

 
2. Composite data types
2.1% TYPE
Reference the type of a variable or database column as the Data Type of a variable.
SET SERVEROUT ON
DECLARE
V_ename scott. emp. ename % TYPE;
V_sal1 number (11 );
V_sal2 v_sal1 % TYPE;
BEGIN
SELECT ename, sal INTO v_ename, v_sal1 FROM scott. emp WHERE empno = 7369;
V_sal2: = v_sal1;
DBMS_OUTPUT.PUT_LINE (v_ename | 'salary is '| v_sal2 );
END;

2.2% ROWTYPE
Reference a row in a database table as the data type, that is, the RECORD type (RECORD type), which is an additional type of PL/SQL. Indicates a record, which is equivalent to an object in JAVA. You can use "." To access the attributes in the record.

12345678910111213 DECLARE

V_emp scott. emp % ROWTYPE;

BEGIN

SELECT * INTO v_emp FROM scott. emp WHERE empno = 7654;

DBMS_OUTPUT.PUT_LINE ('name: '| v_emp.ename );

DBMS_OUTPUT.PUT_LINE ('department No.: '| v_emp.deptno );

END;

 
2.3 record type
Syntax: TYPE record_type is record (
Field1_name data_type_and_size [not null] [: = default_value],
...
Fieldn_name data_type_and_size [not null] [: = default_value]
);
Store logically related data as a unit and use the TYPE keyword to define the display record TYPE. Use % ROWTYPE as the implicit record TYPE.
-- Example 1:
DECLARE

TYPE emp_record_type is record (

Ename VARCHAR2 (10 ),

Deptno NUMBER (2)

);

Emp_record emp_record_type;

BEGIN

SELECT ename, deptno INTO emp_record FROM scott. emp WHERE empno = 7654;

DBMS_OUTPUT.PUT_LINE ('name: '| emp_record.ename );

DBMS_OUTPUT.PUT_LINE ('department No.: '| emp_record.deptno );

END;

-- Example 2:
DECLARE

TYPE emp_record_type is record (

Name scott. emp. ename % TYPE,

Salary scott. emp. sal % TYPE,

Deptno scott. emp. deptno % TYPE

);

Emp_record emp_record_type;

BEGIN

Select ename, sal, deptno into emp_record from scott. emp where empno = & no;

DBMS_OUTPUT.PUT_LINE ('name: '| emp_record.name );

DBMS_OUTPUT.PUT_LINE ('department No.: '| emp_record.deptno );

END;

Or:
1234567891011121314151617181920212223 DECLARE

TYPE emp_record_type is record (

Name scott. emp. ename % TYPE,

Salary scott. emp. sal % TYPE,

Deptno scott. emp. deptno % TYPE

);

Emp_record emp_record_type;

BEGIN

Select ename, sal, deptno into emp_record.name, emp_record.salary, emp_record.deptno from scott. emp where empno = & no;

DBMS_OUTPUT.PUT_LINE ('name: '| emp_record.name );

DBMS_OUTPUT.PUT_LINE ('department No.: '| emp_record.deptno );

END;

-- Example 3:
-- Retrieve data
DECLARE
Dept_record scott. dept % ROWTYPE;
BEGIN
Dept_record.deptno: = 1;
Dept_record.dname: = 'admin ';
Dept_record.loc: = 'nc ';
Insert into scott. dept values dept_record;
END;
-- Insert data
DECLARE
Dept_record scott. dept % ROWTYPE;
BEGIN
Dept_record.deptno: = 2;
Dept_record.dname: = 'admin ';
Insert into scott. dept (deptno, dname) values (dept_record.deptno, dept_record.dname );
END;
-- Update Data
DECLARE
Dept_record scott. dept % ROWTYPE;
BEGIN
Dept_record.deptno: = 1;
Dept_record.dname: = 'hzy ';
Update scott. dept set row = dept_record where deptno = dept_record.deptno;
END;
-- Delete data
DECLARE
Dept_record scott. dept % ROWTYPE;
BEGIN
Dept_record.deptno: = 1;
Delete from scott. dept where deptno = dept_record.deptno;
END;

PL/SQL Developer Practical Skills

Oracle PL/SQL Composite data types

Oracle | PL/SQL Check constraints usage

PL/SQL storage functions and stored procedures