在 SQL 开发器中运行存储过程? ？

Executing easy. Getting the results can be hard.

Take a look at this question I asked Best way/tool to get the results from an oracle package procedure

The summary of it goes like this.

Assuming you had a Package named mypackage and procedure called getQuestions. It returns a refcursor and takes in string user name.

All you have to do is create new SQL File (file new). Set the connection and paste in the following and execute.

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;

Use:

BEGIN


PACKAGE_NAME.PROCEDURE_NAME(parameter_value, ...);


END;

Replace "PACKAGE_NAME", "PROCEDURE_NAME", and "parameter_value" with what you need. OUT parameters will need to be declared prior to.

With simple parameter types (i.e. not refcursors etc.) you can do something like this:

SET serveroutput on;
DECLARE
InParam1 number;
InParam2 number;
OutParam1 varchar2(100);
OutParam2 varchar2(100);
OutParam3 varchar2(100);
OutParam4 number;
BEGIN
/* Assign values to IN parameters */
InParam1 := 33;
InParam2 := 89;


/* Call procedure within package, identifying schema if necessary */
schema.package.procedure(InParam1, InParam2,
OutParam1, OutParam2, OutParam3, OutParam4);


/* Display OUT parameters */
dbms_output.put_line('OutParam1: ' || OutParam1);
dbms_output.put_line('OutParam2: ' || OutParam2);
dbms_output.put_line('OutParam3: ' || OutParam3);
dbms_output.put_line('OutParam4: ' || OutParam4);
END;
/

var InParam1 number;
var InParam2 number;
var OutParam1 varchar2(100);
var OutParam2 varchar2(100);
var OutParam3 varchar2(100);
var OutParam4 number;


BEGIN
/* Assign values to IN parameters */
:InParam1 := 33;
:InParam2 := 89;


/* Call procedure within package, identifying schema if necessary */
schema.package.procedure(:InParam1, :InParam2,
:OutParam1, :OutParam2, :OutParam3, :OutParam4);
END;
/


-- Display OUT parameters
print :OutParam1;
print :OutParam2;
print :OutParam3;
print :OutParam4;

Open the procedure in SQL Developer and run it from there. SQL Developer displays the SQL that it runs.

BEGIN
PROCEEDURE_NAME_HERE();
END;

var out_para_name refcursor;
execute package_name.procedure_name(inpu_para_val1,input_para_val2,... ,:out_para_name);
print :out_para_name;

Can't believe, this won't execute in SQL Developer:

var r refcursor;
exec PCK.SOME_SP(:r,
'02619857');


print r;

BUT this will:

var r refcursor;
exec TAPI_OVLASCENJA.ARH_SELECT_NAKON_PRESTANKA_REG(:r, '02619857');


print r;

Obviously everything has to be in one line..

I wasn't able to get @Alex Poole answers working. However, by trial and error, I found the following works (using SQL Developer version 3.0.04). Posting it here in case it helps others:

SET serveroutput on;


DECLARE
var InParam1 number;
var InParam2 number;
var OutParam1 varchar2(100);
var OutParam2 varchar2(100);
var OutParam3 varchar2(100);
var OutParam4 number;


BEGIN
/* Assign values to IN parameters */
InParam1 := 33;
InParam2 := 89;


/* Call procedure within package, identifying schema if necessary */
schema.package.procedure(InParam1, InParam2,
OutParam1, OutParam2, OutParam3, OutParam4);


/* Display OUT parameters */
dbms_output.put_line('OutParam1: ' || OutParam1);
dbms_output.put_line('OutParam2: ' || OutParam2);
dbms_output.put_line('OutParam3: ' || OutParam3);
dbms_output.put_line('OutParam4: ' || OutParam4);
END;

Using SQL Developer Version 4.0.2.15 Build 15.21 the following works:

SET SERVEROUTPUT ON
var InParam1 varchar2(100)
var InParam2 varchar2(100)
var InParam3 varchar2(100)
var OutParam1 varchar2(100)


BEGIN
/* Assign values to IN parameters */
:InParam1 := 'one';
:InParam2 := 'two';
:InParam3 := 'three';


/* Call procedure within package, identifying schema if necessary */
schema.package.procedure(:InParam1, :InParam2, :InParam3, :OutParam1);
dbms_output.enable;
dbms_output.put_line('OutParam1: ' || :OutParam1);
END;
/

For those using SqlDeveloper 3+, in case you missed that:

SqlDeveloper has feature to execute stored proc/function directly, and output are displayed in a easy-to-read manner.

Just right click on the package/stored proc/ stored function, Click on Run and choose target to be the proc/func you want to execute, SqlDeveloper will generate the code snippet to execute (so that you can put your input parameters). Once executed, output parameters are displayed in lower half of the dialog box, and it even have built-in support for ref cursor: result of cursor will be displayed as a separate output tab.

Creating Pl/SQL block can be painful if you have a lot of procedures which have a lot of parameters. There is an application written on python that do it for you. It parses the file with procedure declarations and creates the web app for convenient procedure invocations.

None of these other answers worked for me. Here's what I had to do to run a procedure in SQL Developer 3.2.20.10:

SET serveroutput on;
DECLARE
testvar varchar(100);
BEGIN
testvar := 'dude';
schema.MY_PROC(testvar);
dbms_output.enable;
dbms_output.put_line(testvar);
END;

And then you'd have to go check the table for whatever your proc was supposed to do with that passed-in variable -- the output will just confirm that the variable received the value (and theoretically, passed it to the proc).

NOTE (differences with mine vs. others):

• No : prior to the variable name
• No putting .package. or .packages. between the schema name and the procedure name
• No having to put an & in the variable's value.
• No using print anywhere
• No using var to declare the variable

All of these problems left me scratching my head for the longest and these answers that have these egregious errors out to be taken out and tarred and feathered.

Though this question is quite old, I keep stumbling into same result without finding an easy way to run from sql developer. After couple of tries, I found an easy way to execute the stored procedure from sql developer itself.

• Under packages, select your desired package and right click on the package name (not on the stored procedure name).

• You will find option to run. Select that and supply the required arguments. Click OK and you can see the output in output variables section below

I'm using SQL developer version 4.1.3.20

--for setting buffer size needed most of time to avoid `anonymous block completed` message
set serveroutput on size 30000;


-- declaration block in case output need to catch
DECLARE
--declaration for in and out parameter
V_OUT_1 NUMBER;
V_OUT_2 VARCHAR2(200);
BEGIN


--your stored procedure name
schema.package.procedure(
--declaration for in and out parameter
V_OUT_1 => V_OUT_1,
V_OUT_2 => V_OUT_2
);
V_OUT_1 := V_OUT_1;
V_OUT_2 := V_OUT_2;
-- console output, no need to open DBMS OUTPUT seperatly
-- also no need to print each output on seperat line
DBMS_OUTPUT.PUT_LINE('Ouput => ' || V_OUT_1 || ': ' || V_OUT_2);
END;

To run procedure from SQL developer-only execute following command

EXECUTE PROCEDURE_NAME;

I had a stored procedure that returned a cursor, in my case it was actually of a custom package type (T_CURSOR, looks like a convention to me) that is defined as REF CURSOR.

There may be a better way to do this, but I defined variables for all the columns of the table that the cursor was iterating, looped the cursor fetching each row into those variables, then printed them out.

SET serveroutput on;
DECLARE
testvar number;
v_cur  SYS_REFCURSOR;
ORIGINAL_EMP_NUM  NUMBER;
TEMPORARY_EMP_NUM NUMBER;
ORG_UNIT_CODE VARCHAR2(2 BYTE);
MRU_CODE  VARCHAR2(10 BYTE);
CTRL_COMPANY_CODE VARCHAR2(10 BYTE);
IS_TEMP_FLAG  VARCHAR2(1 BYTE);
BEGIN
testvar := 420;
foo.updates.get_temporary_authorisations(testvar, v_cur);
dbms_output.enable;
dbms_output.put_line(testvar);
  

LOOP
FETCH v_cur INTO ORIGINAL_EMP_NUM, TEMPORARY_EMP_NUM, ORG_UNIT_CODE, MRU_CODE, CTRL_COMPANY_CODE, IS_TEMP_FLAG;
EXIT WHEN v_cur%NOTFOUND;
dbms_output.put_line(ORIGINAL_EMP_NUM || ',' || TEMPORARY_EMP_NUM || ',' || ORG_UNIT_CODE || ',' || MRU_CODE|| ',' || CTRL_COMPANY_CODE|| ',' || IS_TEMP_FLAG);
END LOOP;
CLOSE v_cur;
END;