Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Use Variables in SQL Statements

By: FYIcenter.com

(Continued from previous topic...)

Can Variables Be Used in SQL Statements?

Yes, you can use variables in SQL statements as part of any expressions. The tutorial script provides you some good examples:

(Connect to XE with SQL*Plus)

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

DECLARE
  var_id NUMBER;
  var_name CHAR(10);
BEGIN
  var_id := 29;
  var_name := 'Bob';
  INSERT INTO student VALUES(var_id, var_name, 'Henry');
  var_name := 'Joe';
  INSERT INTO student VALUES(var_id+1, var_name, 'Bush');
  var_name := 'Fyi';
  UPDATE student SET first_name = var_name
    WHERE id = var_id+1;
  DELETE FROM student WHERE id = var_id;                    
END;   
/

SELECT * FROM student;
ID       FIRST_NAME  LAST_NAME   
-------- ----------- ----------- 
30       Fyi         Bush   

(Continued on next topic...)

  1. Can DML Statements Be Used in PL/SQL?
  2. Can DDL Statements Be Used in PL/SQL?
  3. Can Variables Be Used in SQL Statements?
  4. What Happens If Variable Names Collide with Table/Column Names?
  5. How To Resolve Name Conflicts between Variables and Columns?
  6. How To Assign Query Results to Variables?
  7. Can You Assign Multiple Query Result Rows To a Variable?
  8. How To Invoke Built-in Functions in PL/SQL?
  9. How To Retrieve the Count of Updated Rows?
  10. What Is the Implicit Cursor?
  11. How To Assign Data of the Deleted Row to Variables?
  12. What Is a RECORD in PL/SQL?
  13. How To Define a Specific RECORD Type?
  14. How To Define a Variable of a Specific RECORD Type?
  15. How To Assign Values to Data Fields in RECORD Variables?
  16. How To Retrieve Values from Data Fields in RECORD Variables?
  17. How To Define a Data Field as NOT NULL?
  18. How To Define a RECORD Variable to Store a Table Row?
  19. How To Assign a Table Row to a RECORD Variable?
  20. How To Insert a RECORD into a Table?
  21. How To Update a Table Row with a RECORD?
  22. How To Define a Variable to Match a Table Column Data Type?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...