Bug #17515 Specify procedural SQL types from data dictionary

• Submitted: 17 Feb 2006 9:07
• Modified: 29 May 2012 13:44
• Reporter: Kristian Koehntopp
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S4 (Feature request)
• Version: 5.0.18
• OS: Linux (Linux)
• CPU Architecture: Any

[17 Feb 2006 9:07] Kristian Koehntopp

Description:
- Oracle has a nice feature for procedure definition that looks up a type
  from the data dictionary:

create procedure(IN parameter somedb.sometable.somecol%type)...

parameter will now have the same type as somedb.sometable.somecol in the data dictionary, Oracle will look this up for you.

MySQL does not.

How to repeat:
Try the syntax in oracle, in MySQL

Suggested fix:
Duplicate that functionality. It is very convenient.

[8 Oct 2006 13:08] Roland Bouman

I totally agree with Kris. This is a very convenient syntax, it makes procedure code resilient to type changes in the schema. 

You can use this type of syntax for parameter and local variable declarations. 
A similar construct is available for cursor types:

declare csr CURSOR FOR ...
declare v_csr csr%rowtype;

fetch csr into v_csr;

if v_csr.col1 = 1 then
...
end if;

[29 May 2012 13:44] Valeriy Kravchuk

This is a duplicate of Bug #3286.