In Oracle, "VARIABLE" is a SQL*Plus command that is used to declare and assign values to bind variables.

A bind variable is a placeholder in a SQL statement that can be replaced with a value at runtime. By using bind variables, you can make your SQL code more flexible and reusable, because you can change the value of the variable without modifying the SQL statement itself.

The "VARIABLE" command in SQLPlus is used to declare a bind variable, assign an initial value to it, and optionally set its data type. For example, you can use the following SQLPlus command to declare a bind variable called "my_var" and assign it an initial value of 10:

goCopy code
VARIABLE my_var NUMBER := 10;

Once you have declared a bind variable, you can reference it in a SQL statement using the "&" character followed by the variable name. For example, you can use the following SQL statement to retrieve data from a table and filter it based on the value of the "my_var" variable:

sqlCopy code
SELECT * FROM my_table WHERE my_column = &my_var;

When you run this statement in SQL*Plus, you will be prompted to enter a value for the "my_var" variable. The value you enter will be substituted for the "&my_var" placeholder in the SQL statement.

The "VARIABLE" command can be useful in a variety of scenarios, such as when you want to write a SQL script that prompts the user for input, or when you want to test a SQL statement with different input values.

[](data:image/svg+xml,%3csvg%20xmlns=%27http://www.w3.org/2000/svg%27%20version=%271.1%27%20width=%2730%27%20height=%2730%27/%3e)

so when I declare "variable my_var number", on the memory Oracle makes some place for the bind variable?

Yes, when you declare a bind variable using the "VARIABLE" command in Oracle SQL*Plus, Oracle sets aside memory to store the value of the variable.

The amount of memory allocated for the variable depends on its data type. For example, if you declare a bind variable as "VARIABLE my_var NUMBER", Oracle sets aside enough memory to store a 64-bit floating-point number, which is the data type for the NUMBER data type in Oracle. If you declare a bind variable as "VARIABLE my_var VARCHAR2(50)", Oracle sets aside enough memory to store a string of up to 50 characters.

When you assign a value to a bind variable using the ":=" operator, Oracle stores the value in the memory that has been allocated for the variable. When you reference the bind variable in a SQL statement using the "&" character, Oracle retrieves the value from memory and substitutes it for the variable in the SQL statement.

It's worth noting that if you declare a large number of bind variables or if you assign large values to them, you may run into memory issues. In such cases, you may need to optimize your SQL code or increase the amount of memory available to Oracle.

[](data:image/svg+xml,%3csvg%20xmlns=%27http://www.w3.org/2000/svg%27%20version=%271.1%27%20width=%2730%27%20height=%2730%27/%3e)

CREATE OR REPLACE PROCEDURE sal_empno( vempno in emp.empno%type, vename out emp.ename%type, vsal out emp.sal%type, vjob out emp.job%type) IS BEGIN select ename, sal, job into vename, vsal, vjob from emp where empno = vempno; END;

SELECT * FROM user_source;

DESC user_source;

--바인드 변수 : 프로시저를 실행할 때 결과를 돌려받는 변수 VARIABLE var_ename VARCHAR2(12); VARIABLE var_sal NUMBER; VARIABLE var_job VARCHAR2(10);

EXECUTE sal_empno(7788, :var_ename, :var_sal, 'manager');

PRINT var_ename; PRINT var_sal; PRINT var_job;

It looks like you have provided some code for an Oracle stored procedure, as well as some SQL commands to inspect the procedure and define bind variables.

The stored procedure sal_empno takes four parameters, vempno, vename, vsal, and vjob, and selects the employee name, salary, and job from the emp table where the empno matches the value of vempno. The selected values are then returned as output parameters.

The SELECT * FROM user_source command is used to retrieve information about the stored procedure. This command returns a list of all the procedures and functions that are defined in the current user's schema.