ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sqlplus变量定义, 调用过程

sqlplus变量定义, 调用过程

原创 Linux操作系统 作者:tthero00boo 时间:2013-09-29 10:21:23 0 删除 编辑
 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-30

  • 博文量
    31
  • 访问量
    142151