0

Does MySQL have variable table as SQL-Server or user-defined-type in Oracle? My scenario is to select records into a container and iterate through the container for further action.

I had tried CURSOR but seems not efficient when comes to multiple columns. Also tried CREATE TEMPORARY TABLE with IS EXIST, due to the life cycle, I believe is not a good practice.

airmon-ng
  • 1
  • 1
  • 4
  • Possible duplicate of [Create table variable in MySQL](https://stackoverflow.com/questions/1524858/create-table-variable-in-mysql) – Mo Chavoshi Jun 05 '18 at 09:23
  • What are you using here? `mysql` and `sql-server` are 2 completely different RDBMS, and `plsql` isn't used by either of them (it's used by `oracle`). Please [edit](https://stackoverflow.com/posts/50696511/edit) your post and correct your tags. Thanks. – Thom A Jun 05 '18 at 09:23
  • Temporary tables live and die with your connection. – Krish Jun 05 '18 at 09:24
  • Hi Mohammad Chavoshi, definitely not duplicated. I had tried tmp table. Hi Larnu, please understand that i am going to use MySQL, looking for something similar as variable table or user-defined-type. Hi krish KM, correct, that why I am said above, it is not a good practice for my scenario. – airmon-ng Jun 05 '18 at 09:55

2 Answers2

0

MySQL does not have table variable. Use cursor to iterate selected dataset will be the practice so far. Bear in mind, cursor should exist once in a begin end block, consecutive needs should use nested begin end block to wrap up your cursor.

airmon-ng
  • 1
  • 1
  • 4
-1

sometimes cursor is costly. In oracle, you can create user defined object and can be use it as a list. like this:-

create or replace type lv_your_object as object(
    cloumn1 INTEGER,
    column2 VARCHAR2(1000),
    column3 VARCHAR2(1000),
    cloumn4 INTEGER,
);
/
CREATE OR REPLACE TYPE lv_your_object_list FORCE IS TABLE OF lv_your_object;
/
Atul Kr Dey
  • 160
  • 1
  • 13