在pl/sql里,如何执行一个存储过程?方法有多种?我一般是调试。在网上,我常看到别人这样执行:exec getmult(:a);比如例子:
SQL>create or replace procedure getmult(result out sys_refcursor)
2 is
3 begin
4 open result for select * from ttt;
5* end;
SQL> /过程已创建。SQL> variable a refcursor
SQL> exec getmult(:a);PL/SQL 过程已成功完成。SQL> print :a;ID NAME
---------- ----------
1 a
2 b这里exec 和print好像执行不了,我在pl/sql develope里面试过
SQL>create or replace procedure getmult(result out sys_refcursor)
2 is
3 begin
4 open result for select * from ttt;
5* end;
SQL> /过程已创建。SQL> variable a refcursor
SQL> exec getmult(:a);PL/SQL 过程已成功完成。SQL> print :a;ID NAME
---------- ----------
1 a
2 b这里exec 和print好像执行不了,我在pl/sql develope里面试过
1.PL/sql块:
begin
存储过程名(参数列表);
end;
2.exec 存储过程名(参数列表)
3.call 存储过程名(参数列表) 楼主说的print不行执行存储过程。
而是在函数执行之后用来答应出返回的变量的值。
3.call 存储过程名(参数列表)
这两个能在pl/sql developer里执行?好像不行呢。print也不能打印执行的结果吧?
call 存储过程名(参数列表) 上面两个能在pl/sql developer的命令窗口执行(Command window,非SQL window)
pl/sql developer的命令窗口是模仿sqlplus的,但只支些部分sqlplus的命令下面是pl/sql developer的Command window支持的sqlplus命令参考:Supported commands
Besides all SQL statements, the command window supports the following standard SQL*PLus commands in this release:Command
Meaning
/
Executes the SQL buffer
? [Keyword]
Provides SQL help on the keyword
@[@] [Filename] [Parameter list]
Runs the specified command file, passing the specified parameters
ACC[EPT] Variable [DEF[AULT] Value] [PROMPT Text | NOPR[OMPT]]
Allows the user to enter the value of a substitution variable
CL[EAR] [SCR[EEN]]
Clears the screen
CL[EAR] SQL
Clears the SQL buffer
COL[UMN] [Column] [Format] [NEW_VALUE Variable]
Defines the format of a column, displays the format of a column, or displays all column formats
CON[NECT] [username/password@database]
Connects to the database with the speciffied user
DEF[INE] [Variable] [ = Text]
Defines a substitution variable, displays a variable, or displays all substitution variables.
DESC[RIBE] Object
Gives a description of the specified object
DISC[CONNECT]
Disconnects from the database
EDIT
Displays a text editor to edit the SQL buffer
EXEC[UTE] Procedure
Executes the specified procedure
EXIT [APPLICATION]
Quits a running script or closes the Command Window. Adding the APPLICATION parameter will also close PL/SQL Developer.
GET [Filename]
Loads a command file into the editor
HOST [Command]
Executes the host command
HELP [Keyword]
Provides SQL help on the keyword
PAUSE [Message]
Displays the message and pauses until the user presses Okay or Cancel
PRI[NT] [Variable]
Displays the value of the bind variable, or all bind variables
PROMPT [Text]
Displays the specified text
QUIT [APPLICATION]
Quits a running script or closes the Command Window. Adding the APPLICATION parameter will also close PL/SQL Developer.
R[UN]
Executes the SQL buffer
REM[ARK] [Text]
A comment line
SET AUTOP[RINT] [ON | OFF]
Determines if bind variables are automatically displayed after executing a SQL statement or PL/SQL block.
SET COLSEP [Separator | OFF]
Determines the column separator (default = “ ”).
SET CON[CAT] [Character | ON | OFF]
Determines the character that terminates a substitution variable reference (default = .)
SET DEF[INE] [Character | ON | OFF]
Determines the character that starts a substitution variable reference (default = &)
SET ECHO [ON | OFF]
Determines if executed commands in a script are displayed
SET ESC[APE] [Character | ON | OFF]
Determines the character that escapes the character that starts a substitution variable reference (default = \)
SET FEED[BACK] [ON | OFF]
Determines if the number of affected rows of a SQL statement is displayed
SET HEA[DING] [ON | OFF]
Determines if headings are displayed above the columns of a result set
SET LONG [Width]
Determines the maximum display width of a long column
SET NUM[WIDTH] [Width]
Determines the maximum display width of a number column without precision
SET PAGES[IZE] [Size]
Determines the number of lines that are displayed for a result set, before the headings are repeated
SET PROMPT [Prompt]
Replace the standard SQL> prompt. Instead of a literal text you can also use the variables [user], [db], or [connection]. Furthermore you can include a bind variable (set prompt :bind_var_name).
SET SCAN [ON | OFF]
Determines if substitution variables should be scanned
SET SERVEROUT[PUT] [ON | OFF] [SIZE n]
Determines if output of calls to dbms_output.put_line is displayed, and what the size of the output buffer is
SET SPOOL*DIRECTORY [Directory]
Determines in which directory spool files are stored if the SPOOL command does not specify an absolute path.
SET TERM[OUT] [ON | OFF]
Determines if output of executed SQL statements is displayed
SET TIMI[NG] [ON | OFF]
Determines if timing information about executed SQL statements is displayed
SET VER[IFY] [ON | OFF]
Determines if substitution variables are displayed when used in a SQL statement or PL/SQL block
SHO[W] ERR[ORS] [Type Name]
Displays errors for the previous compilation, or for the specified object
SHO[W] REL[EASE]
Displays Oracle release information for the current connection
SHO[W] SQLCODE
Displays the result code of the executed SQL statement
SHO[W] USER
Displays the username of the current connection
SPO[OL] [Filename [APPEND]] | OFF | OUT]
Starts or stops spooling and optionally print (OUT)
STA[RT] [Filename] [Parameter list]
Runs the specified command file, passing the specified parameters
STORE SET [Filename]
Stores the values of all options in the filename. You can execute this file later to restore these options.
UNDEF[INE] Variable
Undefines the given substitution variable
VAR[IABLE] [Variable] [Datatype]
Defines a bind variable, displays a bind variable, or displays all bind variables.
WHENEVER [OSERROR | SQLERROR] [Action]
Specify an action whenever an OS error or SQL error occurs. The action can either be EXIT or CONTINUE, optionally followed by COMMIT or ROLLBACK.