Translate TSQL to PLSQL

Question

In SQL Server you can do this:

DECLARE @ID int
SET @ID=1
SELECT * FROM Person WHERE ID=@ID

What is the equivalent Oracle code?

I have spent some time Googling this, but I have not found an anwser.

I believe [this link](http://plsql-tutorial.com/plsql-variables.htm) covers everything in your code.. — PM 77-1, Sep 19 '13 at 17:30

Seymour · Accepted Answer · 2013-09-19T20:55:39.010

1

The following link/page from the Oracle® Database PL/SQL User's Guide provides an overview of variable declaration:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/constantvar_declaration.htm

The sample below was taken from another page in the user’s guide.

DECLARE
  bonus  NUMBER(8,2);
  emp_id NUMBER(6) := 100;
BEGIN
  SELECT salary * 0.10 INTO bonus FROM employees 
    WHERE employee_id = emp_id;
END;
/

... and for your example ...

DECLARE
  p_id NUMBER(6) := 1;
BEGIN
  SELECT * FROM Person WHERE ID = p_id;
END;
/

Regards,

edited Sep 19 '13 at 20:55

answered Sep 19 '13 at 17:56

Seymour

7,043
12
44
51

@MaxVernon the following discusses the (/) ... http://stackoverflow.com/questions/193215/is-this-slash-character-in-an-oracle-pl-sql-script-an-error – Seymour Sep 19 '13 at 20:57
ok, the "/" is the equivalent of "GO" in SQL Server Management Studio? – Hannah Vernon Sep 19 '13 at 21:16

score 1 · Answer 2 · answered Sep 19 '13 at 20:10

1

You create a bind variable

VARIABLE ID number

Assigning the variable must be done in a PL/SQL block (execute is a short cut to do that)

execute :id := 1

You can then use it in a sql statement

SELECT * FROM Person WHERE ID=:ID ;

answered Sep 19 '13 at 20:10

Nicolas

923
7
11

score 0 · Answer 3 · edited May 23 '17 at 12:05

I haven't found a way to do the equivalent, either. Oracle's PL/SQL operates within blocks, and a block doesn't display a query result in the query results window (in SQL Developer, for example). You can have it output information to the Dbms Output, but it's not as quick and easy as it is in SQL Server.

Here's an example of a block.

DECLARE rid NUMBER := 1;
  rec UNIQUEVALTEST%ROWTYPE;

BEGIN 
  SELECT * INTO rec FROM UNIQUEVALTEST WHERE recid = rid;
  dbms_output.put_line(rec.FNAME);
END;

The PL/SQL tutorial that I read covers all of this, and it's the same one that PM 77-1 linked in his comment.

Trying to just do a quick select from a table using your variable doesn't really work out the same. For example, if you're using Oracle SQL Developer, the result is not going to display in your results Query Results frame. But if you're doing an update or an insert, it works out pretty much the same as SQL Server.

Here's an example, starting with creating some made-up test data.

--Create testing table
CREATE TABLE UniqueValTest (
  fname NVARCHAR2(100),
  lname NVARCHAR2(100),
  address NVARCHAR2(100),
  city NVARCHAR2(50),
  state NVARCHAR2(2),
  zip NVARCHAR2(5),
  age NUMBER,
  recid NUMBER
  );

--Create sample data
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOHN', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 28, 1);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JENNIFER', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 30, 2);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('RACHEL', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 3);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOSEPH', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 4);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('MARK', 'MCBRIDE', '228 MAIN ST', 'JAMESTOWN', 'LA', '12345', 55, 5);

And the variable:

DECLARE rid NUMBER := 1;

BEGIN 
  UPDATE UNIQUEVALTEST SET fname = 'JAKE' WHERE recid = rid;
END;

And test it:

SELECT * FROM UNIQUEVALTEST WHERE recid = 1;

Of course running a simple update like that would be unnecessarily complicated, but you can see the potential when you start using cursors and loops to give your code blocks more power.

SQL Fiddle example.

Translate TSQL to PLSQL

3 Answers3