CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
C03 Is
C03 Is

Reputation: 9

What is an easy way to write a stored procedure in Oracle using SQL Developer?

Here is the procedure: 

CREATE PROCEDURE show @faculty VARCHAR(20) 
AS
BEGIN
    SELECT * 
    FROM tutor
    WHERE title LIKE @faculty+'%'
END

Here is the procedure execution:

EXEC show 'FI'

How can I rewrite this for SQL Developer?

Upvotes: 0

Views: 72

Answers (1)

user330315
user330315

Reputation:

With Oracle 12 you can do this:

CREATE PROCEDURE show(p_faculty varchar)
AS
  cur SYS_REFCURSOR; 
BEGIN 
  OPEN cur FOR 
    SELECT * 
    FROM tutor
    WHERE title LIKE p_faculty || '%';
  DBMS_SQL.RETURN_RESULT(cur); 
END; 
/

Then run it with:

exec show_x ('FI');

Upvotes: 2

Related Questions