oracle高级4

来源：互联网发布：手机刷银行卡软件编辑：程序博客网时间：2024/06/06 03:25

复合类型:
第一种:可变长的数组类型:
SET SERVEROUT ON
      DECLARE
        TYPE sname_type IS VARRAY(5) OF VARCHAR2(10);
        sname_varry sname_type;
        v_id Students.student_id%TYPE;
      BEGIN
        v_id := &student_id;
        sname_varry := sname_type('张三','李四','黄五');
        SELECT name
        INTO sname_varry(3)
        FROM Students
        WHERE student_id = v_id;
        DBMS_OUTPUT.PUT_LINE ('学生1姓名：'||sname_varry(1));
        DBMS_OUTPUT.PUT_LINE ('学生2姓名：'||sname_varry(2));
        DBMS_OUTPUT.PUT_LINE ('学生2姓名：'||sname_varry(3));
      END;

第二种:
变长数组作为表列的数据类型

CREATE TYPE studname_type IS VARRAY(15) OF VARCHAR2(10);

CREATE TABLE hierophants(
hierophant_id NUMBER(5)
CONSTRAINT hierophant_pk PRIMARY KEY,
hierophant_name VARCHAR2(10) NOT NULL,
student_name studname_type
);

BEGIN
INSERT INTO hierophants
VALUES(10101,'王彤',studname_type('王晓芳','张纯玉','刘春苹'));
END;

第三:读取数组

SET SERVEROUT ON
      DECLARE
        studname_varry studname_type;
      BEGIN
        SELECT student_name INTO studname_varry
          FROM hierophants
            WHERE hierophant_name = '王彤';
        DBMS_OUTPUT.PUT_LINE ('王彤导师的研究生姓名：');
        FOR i IN 1..studname_varry.COUNT LOOP
           DBMS_OUTPUT.PUT_LINE (studname_varry(i));
        END LOOP;
      END;

第四赋值:
DECLARE
        studname_varry studname_type :=
          studname_type('王一','张三','刘四');
      BEGIN
        UPDATE hierophants
        SET student_name = studname_varry
        WHERE hierophant_name = '王彤';
      END;

0 0