Jon Crawford Jon Crawford - 11 days ago 6
MySQL Question

Having a hard time creating a procedure in SQL

I need to create a procedure for class, but I think the sql version in the book is VERY old. For example, I don't think I can't use "Create or replace" like the book says, but "create", "drop", "create", but that is beyond the scope.

My issue is that I am having trouble setting the %TYPE. I use WAMP, I created the procedure in Notepad++, and pasted it to the console. I then opened up phpmyadmin, pasted it into the query window, and was rewarded with more verbose error message. Book: "A guide to SQL" by Phil Pratt and Mary Last, Ninth Edition https://www.amazon.com/Guide-SQL-Philip-J-Pratt/dp/111152727X . Book has TAL Distributers, SOLARIS comdominium group, and COLONIAL adventure tours databases it that helps anyone. The instructor provided the sql file to create the databases as a time saver. This is for the last chapter, ch.8 Creation code:

delimiter ;;
use tal;;
CREATE PROCEDURE CHANGE_ITEM_PRICE(I_ITEM_NUM IN item.item_num%TYPE, I_NEW_PRICE IN item.price%TYPE) AS
BEGIN
UPDATE item SET price = I_NEW_PRICE
WHERE item_num = I_ITEM_NUM;
END;;
delimiter ;


2 Errors:

Unrecognized data type. (near "IN" at position 56)


and another for position 91.

Any thoughts? I'm not looking to be spoon fed here, I just need a little guidance please.

Answer
I_ITEM_NUM IN item.item_num%TYPE

This is not a valid procedure argument declaration for MySQL. You have to name the type with something like INT or DATE or VARCHAR(length) or another known type. MySQL has no syntax like you show to dynamically query the type of a named column.

Out of curiosity, where did you get that syntax? Is it part of some other brand of SQL database? I've never seen it before.


Aha, I found it:

https://www.postgresql.org/docs/current/static/sql-createfunction.html says:

The type of a column is referenced by writing table_name.column_name%TYPE. Using this feature can sometimes help make a function independent of changes to the definition of a table.

That's in the PostgreSQL documentation. PostgreSQL and MySQL are not the same software, and there are many examples of syntax and features that each has that the other does not.

This syntax is also supported by Oracle: %TYPE attribute. Actually, I assume Oracle did it before PostgreSQL.