CREATE OR REPLACE PROCEDURE TEST_RECORD1 AS
/*
*功能：用来说明自定义记录的使用,在select into子句中使用记录变量
*范围：适用于处理单行多列的数据

*/
BEGIN
--自定义记录类型
DECLARE
    TYPE EMP_RECORD_TYPE IS RECORD(
      ENAME EMP.ENAME%TYPE,
      EMPNO EMP.EMPNO%TYPE,
      JOB   EMP.JOB%TYPE);

    --使用自定义记录类型创建变量
    EMP_RECORD EMP_RECORD_TYPE;
    --数据操作处理部分
BEGIN
    SELECT ENAME, EMPNO, JOB INTO EMP_RECORD FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO);
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.JOB);
END;
END TEST_RECORD1;

CREATE OR REPLACE PROCEDURE TEST_RECORD2 AS
/*
*功能：用来说明自定义记录的使用,在select into子句中使用记录成员
*范围：适用于处理单行多列的数据

*/
BEGIN
--自定义记录类型
DECLARE
    TYPE EMP_RECORD_TYPE IS RECORD(
      ENAME EMP.ENAME%TYPE,
      EMPNO EMP.EMPNO%TYPE,
      JOB   EMP.JOB%TYPE);

    --使用自定义记录类型创建变量
    EMP_RECORD EMP_RECORD_TYPE;
    --数据操作处理部分
BEGIN
    SELECT ENAME, EMPNO, JOB INTO EMP_RECORD.ename,emp_record.empno,emp_record.job FROM EMP WHERE EMPNO = 7369;
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO);
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
    DBMS_OUTPUT.PUT_LINE(EMP_RECORD.JOB);
END;
END TEST_RECORD2;

"PROCEDURE TEST_RECORD3 AS
"
" /*
"
" *功能：用来说明自定义记录的使用,在values子句中使用记录变量
"
" *范围：适用于处理单行多列的数据
"
"
"
" */
"
"BEGIN
"
" --自定义记录类型,dept_record代表DEPT表中的一行
"
" DECLARE
"
"    DEPT_RECORD DEPT%ROWTYPE;
"
"    --为记录成员给值
"
" BEGIN
"
"    DEPT_RECORD.DEPTNO := '1';
"
"    DEPT_RECORD.DNAME := 'test';
"
"    DEPT_RECORD.LOC    := 'life';
"
"    --数据操作处理部分
"
"    INSERT INTO DEPT VALUES DEPT_RECORD;
"
"    COMMIT;
"
" END;
"
"END TEST_RECORD3;
"

"PROCEDURE TEST_RECORD4 AS
"
" /*
"
" *功能：用来说明自定义记录的使用,在values子句中使用记录成员
"
" *范围：适用于处理单行多列的数据
"
"
"
" */
"
"BEGIN
"
" --自定义记录类型,dept_record代表DEPT表中的一行
"
" DECLARE
"
"    DEPT_RECORD DEPT%ROWTYPE;
"
"    --为记录成员给值
"
" BEGIN
"
"    DEPT_RECORD.DEPTNO := '2';
"
"    DEPT_RECORD.DNAME := 'test';
"
"    DEPT_RECORD.LOC    := 'life';
"
"    --数据操作处理部分
"
"    INSERT INTO DEPT VALUES (dept_record.deptno,dept_record.dname,dept_record.loc);
"
"    COMMIT;
"
" END;
"
"END TEST_RECORD4;
"

"PROCEDURE TEST_RECORD5 AS
"
" /*
"
" *功能：用来说明自定义记录的使用,在update set子句中使用记录变量
"
" *范围：适用于处理单行多列的数据
"
"
"
" */
"
"BEGIN
"
" --自定义记录类型,dept_record代表DEPT表中的一行
"
" DECLARE
"
"    DEPT_RECORD DEPT%ROWTYPE;
"
"    --为记录成员给值
"
" BEGIN
"
"    DEPT_RECORD.DEPTNO := '3';
"
"    DEPT_RECORD.DNAME := 'test';
"
"    DEPT_RECORD.LOC    := 'life';
"
"    --数据操作处理部分
"
"    UPDATE dept SET ROW = dept_record WHERE deptno =1;
"
"    COMMIT;
"
" END;
"
"END TEST_RECORD5;
"

"PROCEDURE TEST_RECORD6 AS
"
" /*
"
" *功能：用来说明自定义记录的使用,在update set子句中使用记录成员
"
" *范围：适用于处理单行多列的数据
"
"
"
" */
"
"BEGIN
"
" --自定义记录类型,dept_record代表DEPT表中的一行
"
" DECLARE
"
"    DEPT_RECORD DEPT%ROWTYPE;
"
"    --为记录成员给值
"
" BEGIN
"
"    DEPT_RECORD.DEPTNO := '3';
"
"    DEPT_RECORD.DNAME := 'test';
"
"    DEPT_RECORD.LOC    := 'cd';
"
"    --数据操作处理部分
"
"    UPDATE dept SET loc = dept_record.loc WHERE deptno =3;
"
"    COMMIT;
"
" END;
"
"END TEST_RECORD6;

"PROCEDURE TEST_RECORD7 AS
"
" /*
"
" *功能：用来说明自定义记录的使用,在delete子句中使用记录成员
"
" *范围：适用于处理单行多列的数据
"
"
"
" */
"
"BEGIN
"
" --自定义记录类型,dept_record代表DEPT表中的一行
"
" DECLARE
"
"    DEPT_RECORD DEPT%ROWTYPE;
"
"    --为记录成员给值
"
" BEGIN
"
"    DEPT_RECORD.DEPTNO := '3';
"
"    DEPT_RECORD.DNAME := 'test';
"
"    DEPT_RECORD.LOC    := 'cd';
"
"    --数据操作处理部分
"
"    DELETE FROM dept WHERE deptno = dept_record.deptno;
"
"    COMMIT;
"
" END;
"
"END TEST_RECORD7;
"
"

"PROCEDURE TEST_RECORD8 AS
"
"/*
"
"*功能：用来说明索引表的使用
"
"*范围：适用于处理多行多列的数据
"
"
"
"*/
"
"BEGIN
"
" --自定义记录类型
"
" DECLARE
"
"    TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
"
"    ename_table ename_table_type;
"
"    --数据操作处理部分
"
" BEGIN
"
"    SELECT ename INTO ename_table(-1) FROM emp WHERE empno=7369;
"
"    DBMS_OUTPUT.PUT_LINE(ename_table(-1));
"
"
"
" END;
"
"END TEST_RECORD8;
"

ITPub博客

plsql记录类型

最新文章