Add %rowtype Attribute To The View

Availability

This feature is available since MogDB 5.0.0.

Introduction

This feature adds the %rowtype attribute to the view, which is supported as a parameter or variable. When you use %ROWTYPE to declare a variable that matches a view, the variable will have the same structure as the view row.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Syntax Description

CREATE PROCEDURE pro_name (input IN view_name%rowtype) IS
BEGIN
...
END;

Example

MogDB=# DROP SCHEMA IF EXISTS test_view_sch;
NOTICE: SCHEMA "test_view_sch" does NOT exist, skipping

MogDB=# CREATE SCHEMA test_view_sch;

MogDB=# CREATE TABLE test_view_sch.tb_test (c1 INT, c2 varchar2);

MogDB=# CREATE VIEW test_view_sch.v_terst AS SELECT c1, c2 FROM test_view_sch.tb_test;

MogDB=# CREATE OR REPLACE PROCEDURE sp_p1 (i_proname IN test_view_sch.v_terst%rowtype) IS
BEGIN
  RAISE NOTICE 'i_proname.c1 is %, i_proname.c2 is %', i_proname.c1, i_proname.c2;
END;
/
CREATE PROCEDURE

MogDB=# DECLARE 
var1 test_view_sch.v_terst%rowtype := (1111, 'xxxx');
BEGIN
sp_p1(i_proname => var1);
END;
/
NOTICE:  i_proname.c1 is 1111, i_proname.c2 is xxxx
ANONYMOUS BLOCK EXECUTE

MogDB=# DROP SCHEMA test_view_sch CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table test_view_sch.tb_test
drop cascades to view test_view_sch.v_terst
drop cascades to function sp_p1(test_view_sch.v_terst)
DROP SCHEMA