sqlplus变量定义, 调用过程

 

oracle定义变量（常量）常用：declare、define、variable

1）define、variable用于sqlplus中，在整个sqlplus连接中都生效(until exit,disc是cut down session)，而declare用于pl/sql中。

2）variable（var）和define区别在于，前者用于绑定变量，后者是用于&或&&进行变量替换(使用场合,拿来当输入参数)。

define

SQL> define x='SCOTT'
SQL> define
DEFINE _DATE           = "29-9月 -13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "myorcl11" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE X               = "SCOTT" (CHAR)
SQL> select distinct owner from dba_segments where wner='&x';
old   1: select distinct owner from dba_segments where wner='&x'
new   1: select distinct owner from dba_segments where wner='SCOTT'

OWNER
------------------------------------------------------------
SCOTT

var

SQL> var y numb
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var y varchar2(20)
SQL> var
variable   y
datatype   VARCHAR2(20)
SQL> exec :y := 'SCOTT'

PL/SQL procedure successfully completed.

SQL> print :y

Y
-------
SCOTT

SQL> select distinct owner from dba_segments where   owner=':y'; --wrong

no rows selected

SQL> select distinct owner from dba_segments where wner=:y;

OWNER
------------------------------------------------------------
SCOTT

SQL> variable dba varchar2(30)

SQL> exec :dba := dbms_utility.make_data_block_address(4, 20);

PL/SQL procedure successfully completed.

SQL> print dba

DBA

--------------------------------

16777236

执行存储过程：(加楼上共4种方法)

法1

BEGIN

    getDeptCount;

END;

法2

EXEC getDeptCount    

法3

CALL  getDeptCount();

注意：

定义无参存储过程时，存储过程名后不能加()

在块中或是通过EXEC调用存储过程时可以省略()

通过CALL调用无参存储过程必须加上()

参数只能指定类型,不能指定长度精度范围,可以指定默认值：

varchar2(200)  ,只能用varchar2

    ...PROCEDURE add_deptno(v_deptno IN dept.deptno%TYPE,

                v_dname IN VARCHAR2,

                v_loc IN dept.loc%TYPE DEFAULT 'BEJING')...

给过程传递参数的方法：

    1，位置传递 exec add_dept(50,'SALES','BEIJING')

    2，名称传递 exec add_dept(v_dname=>'SALES',v_deptno=>50,v_loc=>'BEIJING')

    3，组合传递 exec add_dept(50,v_loc=>'BEIJING,v_dname=>'SALES')

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28859270/viewspace-773572/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28859270/viewspace-773572/