Record type (study note)

Define record type syntax

Type name is RECORD (member name data type [[Not NULL] [: = default] expression],... Member name data type [[Not NULL] [: = default] expression]);

example One, record type receive query return result

TYPE Emp_type isRECORD (--Defining record TypesEname EMP. Ename%TYPE, JOB EMP. JOB%TYPE, HireDate EMP. HireDate%TYPE, SAL EMP. SAL%TYPE, COMM emp.comm%TYPE); V_emp Emp_type; --defines a specified compound type variableBEGINV_eno:= &ENO; SELECTename, JOB, HireDate, SAL, COMM intov_emp fromEMPWHEREEMPNO=V_eno; Dbms_output. Put_Line (SQL%ROWCOUNT || 'Employee Number:' ||V_eno|| 'Name:' ||v_emp. Ename|| 'Position:' ||V_emp. JOB|| 'Date of entry:' ||To_char (v_emp. HireDate,'YYYY-MM-DD')|| 'Base Salary:' ||v_emp. SAL|| 'Commission:' ||NVL (V_emp.comm,0)); EXCEPTION whenOTHERS ThenRaise_application_error (-20009,'This employee information does not exist');END;

Example Two

DECLARETYPE Dept_type isRECORD (Deptno dept.deptno%TYPE:= About,--Define default valuesDname Dept.dname%TYPE, loc dept.loc%TYPE); V_dept Dept_type;BEGINV_dept.dname:='TT';--assigning values to record type membersV_dept.loc:='Shanghai'; Dbms_output.put_line ('Department Number:'||V_dept.deptno||'Department Name:'||V_dept.dname||'Department Location:'||v_dept.loc);END;

example Three, defining a nested record type

DECLARETYPE Dept_type isRECORD (DEPTNO DEPT. DEPTNO%TYPE:=  About,--Define default valuesDname DEPT. Dname%TYPE, LOC DEPT. LOC%TYPE); TYPE Emp_type isRECORD (--Defining record TypesEMPNO EMP. EMPNO%TYPE, ename EMP. Ename%TYPE, JOB EMP. JOB%TYPE, HireDate EMP. HireDate%TYPE, SAL EMP. SAL%TYPE, COMM emp.comm%TYPE, DEPT dept_type); V_emp Emp_type;BEGIN  SELECTE.empno, E.ename, E.job, E.hiredate, E.sal, E.empno, D.deptno, D.dname, D.loc intov_emp. EMPNO, V_emp. Ename, V_emp. JOB, V_emp. HireDate, V_emp. SAL, V_emp.comm, V_emp. DEPT. DEPTNO, V_emp. DEPT. Dname, V_emp. DEPT. LOC fromEMP E, DEPT DWHEREE.empno=D.deptno (+)      andEMPNO= &EMPNO; Dbms_output. Put_Line (SQL%ROWCOUNT || 'Employee Number:' ||V_emp. EMPNO|| 'Name:' ||v_emp. Ename|| 'Position:' ||V_emp. JOB|| 'Date of entry:' ||To_char (v_emp. HireDate,'YYYY-MM-DD')|| 'Base Salary:' ||v_emp. SAL|| 'Commission:' ||NVL (V_emp.comm,0)); Dbms_output. Put_Line ('Department Number:' ||V_emp. DEPT. DEPTNO|| 'Department Name:' ||v_emp. DEPT. Dname|| 'Department Location:' ||v_emp. DEPT. LOC);END;

example Four, adding a new record

DECLARETYPE Dept_type isRECORD (DEPTNO DEPT. DEPTNO%TYPE,--Define default valuesDname DEPT. Dname%TYPE, LOC DEPT. LOC%TYPE); V_dept Dept_type;BEGINV_dept.deptno:=&Deptno; V_dept. Dname:='&dname';--assigning values to record type membersV_dept. LOC:='&loc'; INSERT  intoDeptVALUESv_dept;--Insert record type directlyEND;

example Five, modifying a record

DECLARETYPE Dept_type isRECORD (DEPTNO DEPT. DEPTNO%TYPE,--Define default valuesDname DEPT. Dname%TYPE, LOC DEPT. LOC%TYPE); V_dept Dept_type;BEGINV_dept.deptno:=&Deptno; V_dept. Dname:='&dname';--assigning values to record type membersV_dept. LOC:='&loc';UPDATEDeptSETROW=V_deptWHEREDeptno=V_dept.deptno; EXCEPTION whenOTHERS ThenDbms_output.put_line ('no such department .'); END;

Record type (study note)