plsql-02-记录类型

----------------------------------------------------------------

记录类型

----------------------------------------------------------------

3.1 自定义记录类型

declare

--定义一个记录类型

type customer_type is record(

v_cust_name varchar2(25),

v_cust_id number(10)

);

--声明自定义记录类型的变量

v_customer_type customer_type ;

begin

v_customer_type.v_cust_name :='测试赋值'；

v_customer_type.v_cust_id :=1001;

dbms_output.put_line(v_customer_type.v_cust_name ||' , '||v_customer_type.v_cust_id );

end;

3.2 自定义记录类型

declare

--定义一个记录类型

type emp_record is record(

v_name varchar2(25),

v_email varchar2(25),

v_salary number(8, 2),

v_job_id varchar2(10)

);

v_emp_record emp_record;

begin

--通过 select ... into ... 语句为变量赋值

select last_name, email, salary, job_id into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||

v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);

end;

4. 使用 %type 定义变量，动态的获取数据的声明类型

declare

type emp_record is record(

v_name employees.last_name%type,

v_id employees.id%type,

v_email employees.email%type,

v_salary employees.salary%type

);

v_emp_record emp_cord;

begin

--通过 select ... into ... 语句为变量赋值

select last_name, email, salary, job_id into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||

v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);

end;

5. 使用 %rowtype

declare

v_emp_record employees%rowtype;

begin

--通过 select ... into ... 语句为变量赋值

select * into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||

v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||

v_emp_record.hire_date);

end;

6.1 赋值语句：通过变量实现查询语句

declare

v_emp_record employees%rowtype;

v_employees_id employees.employees_id%type;

begin

v_employees_id :=186;

--通过 select ... into ... 语句为变量赋值

select * into v_emp_record

from employees

where employee_id = v_employee_id;

-- 打印变量的值

dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||

v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||

v_emp_record.hire_date);

end;

6.2 通过变量实现DELETE、INSERT、UPDATE等操作

declare

v_emp_id employees. employees_id %type;

begin

v_emp_id :=101;

update emplyees set employee_id=v_emp_id;

--commit;

--rollback;

end;