postgresql 替换全部替换字符串 plsql中替换函数

在sqlplus或其他工具中，开发和执行SQL语句时，经常会遇到以下三种变量：

• 脚本替换变量：& / && / define / undefine
• SQL语句绑定变量：: / var(iable) / exec
• PL/SQL存储过程变量：declare

本文将对Oracle SQL中的变量进行分析探讨。

1. 脚本替换变量

替换变量可以放在SQL或PL/SQL语句中的任意位置，包含select值或字段，from表名，where字段或条件，order by、group by、having等任意位置，以及PL/SQL语句的deaclre、begin中的任意位置。

替换变量在使用时，进行的是字符串替换，没有数据类型之分，全部为字符串，在SQL语句执行之前已由sqlplus等工具完成了SQL语句的字符串替换。需要注意的是，如果SQL语句中，替换变量所在的位置应为字符或日期类型的值，则最好用单引号扩起来。

1.1 使用规则

替换变量的使用，可以分别以下几个基本动作：

• 交互输入：当&或&&引用的变量没有定义时，会提示用户输入替换值
• 变量引用：当&或&&引用的变量已经定义时，会引用变量的值
• 字符替换：通过字符串替换的方式，将该位置出现的替换变量替换为对应变量值或输入值
• 变量定义：通过define命令定义或修改，或变量未定义时&&第一次交互输入的值

替换变量使用过程中，所设计的操作符或命令如下：

• &：变量引用、交互输入和字符替换
• &&：变量引用、交互输入、字符替换和变量定义
• define/undefine：定义与清除变量

1.2 命令帮助

SQL> help define
 DEFINE
 ------
 Specifies a substitution variable and assigns a CHAR value to it, or
 lists the value and variable type of a single variable or all variables.
 DEF[INE] [variable] | [variable = text]

SQL> help undefine
 UNDEFINE
 --------
 Deletes one or more substitution variables that you defined either
 explicitly (with the DEFINE command), or implicitly (with a START
 command argument).
 UNDEF[INE] variable ...

1.3 应用详解

• &：如果变量已定义时，则引用变量并替换；如果变量无定义，交互输入替换值，替换，但不定义变量，即如果后面又出现该替换变量，则仍需要交互输入替换值，当然也可以使用define提前定义替换变量值，规避交互输入。

SQL> select '&name','&name' from dual;
Enter value for name: Li
Enter value for name: Qiang
old   1: select '&name','&name' from dual
new   1: select 'Li','Qiang' from dual

'L 'QIAN
-- -----
Li Qiang

SQL> select &col,&col from v$database;
Enter value for col: name
Enter value for col: open_mode
old   1: select &col,&col from v$database
new   1: select name,open_mode from v$database

NAME	  OPEN_MODE
--------- --------------------
ORCL	  READ WRITE

SQL> /
Enter value for col: open_mode
Enter value for col: name
old   1: select &col,&col from v$database
new   1: select open_mode,name from v$database

OPEN_MODE	     NAME
-------------------- ---------
READ WRITE	     ORCL

SQL> define col
SP2-0135: symbol col is UNDEFINED

• &&：如果变量已定义时，则引用变量并替换；如果变量无定义，交互输入替换值，替换并定义变量。也可以使用define提前定义替换变量值，规避交互输入。

SQL> select '&&name','&&name' from dual;
Enter value for name: Li
old   1: select '&&name','&&name' from dual
new   1: select 'Li','Li' from dual

'L 'L
-- --
Li Li

SQL> select &&col,&&col from v$database;
Enter value for col: name
old   1: select &&col,&&col from v$database
new   1: select name,name from v$database

NAME	  NAME
--------- ---------
ORCL	  ORCL

SQL> /
old   1: select &&col,&&col from v$database
new   1: select name,name from v$database

NAME	  NAME
--------- ---------
ORCL	  ORCL

SQL> define col
DEFINE COL	       = "name" (CHAR)

• define：用于查看、定义或修改替换变量值。define定义的变量，可以使用&或&&直接引用和替换，无需再交互输入变量值。
• unfine：用于清除变量。

SQL> define col
DEFINE COL	       = "name" (CHAR)
SQL> undefine col
SQL> define col
SP2-0135: symbol col is UNDEFINED

SQL> define col=name
SQL> select &&col from v$database;
old   1: select &&col from v$database
new   1: select name from v$database

NAME
---------
ORCL

SQL> select &col from v$database;
old   1: select &col from v$database
new   1: select name from v$database

NAME
---------
ORCL

• PL/SQL中的使用示例

SQL> declare
  2  n number;
  3  begin
  4  select count(*) into n from &t;
  5  dbms_output.put_line(n);
  6  end;
  7  /
Enter value for t: v$database
old   4: select count(*) into n from &t;
new   4: select count(*) into n from v$database;
1

PL/SQL procedure successfully completed.

SQL> /
Enter value for t: v$datafile
old   4: select count(*) into n from &t;
new   4: select count(*) into n from v$datafile;
4

PL/SQL procedure successfully completed.

2. SQL语句绑定变量

绑定变量用于，将一组只有检索值或插入值不同，而其他部分相似的SQL语句，统一为绑定变量替换后的SQL语句形式，从而实现软解析。

绑定变量只能用于SQL语句的where或values部分，形式上会在变量名前面加冒号。在PL/SQL语句中，SQL语句外的变量均为PL/SQL存储过程变量，可以使用using命令，将带绑定变量的SQL语句和存储过程变量的值，一起发送给SQL引擎。

2.1 使用规则

绑定变量在赋值或引用时，需要在变量名前加冒号“:”。这样做对PL/SQL语句块来说，是为了与存储过程变量区分，对SQL语句来说，是为了表示这是个绑定变量，SQL执行前不做变量引用，从而实现SQL软解析，最后SQL执行时，再做变量引用。

与绑定变量有关的命令如下，前3个为sqlplus工具命令，最后1个为PL/SQL语句命令。

• variable：绑定变量定义与赋值
• execute：绑定变量赋值，
• print：打印绑定变量的值
• execute immediate（PL/SQL）：执行动态SQL，并使用using附加绑定变量

绑定变量有数据类型之分，在sqlplus中使用variable命令定义绑定变量时，需要声明数据类型，在PL/SQL中包含绑定变量的SQL中，可以不指定数据类型，PL/SQL会根据using后跟的值类型，来确定绑定变量数据类型。

• NUMBER
• CHAR CHAR (n [CHAR|BYTE])
• NCHAR NCHAR (n)
• VARCHAR2 (n [CHAR|BYTE])
• NVARCHAR2 (n)
• CLOB NCLOB
• REFCURSOR
• BINARY_FLOAT BINARY_DOUBLE

2.2 命令帮助

SQL> help variable

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

 VAR[IABLE] [<variable> [type][=value]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

 One can assign value to a variable for input with the new syntax
 Example:
   VARIABLE tmp_var VAHRCHAR2(10)=Smith
   or
   VARIABLE tmp_var VAHRCHAR2(10)
   VARIABLE tmp_var=Smith
   EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)
   
SQL> help print

 PRINT
 -----

 Displays the current values of bind variables, or lists all bind
 variables.

 PRINT [variable ...]
 
 SQL> help exec

 EXECUTE
 -------

 Executes a single PL/SQL statement or runs a stored procedure.

 EXEC[UTE] statement

2.3 应用详解

• sqlplus中使用绑定变量

sqlplus工具中，通过variable命令创建绑定变量并赋值，再执行包含绑定变量的SQL语句，sqlplus会将SQL语句和绑定变量值，一起发送给SQL引擎。

SQL> variable fileno number = 1
--或
SQL> variable fileno number
SQL> exec :fileno := 1;
PL/SQL procedure successfully completed.

SQL> print fileno
    FILENO
----------
	 1

SQL> select file#,name from v$datafile where file# = :fileno;

     FILE#        NAME
---------------  ----------------------
	 1           /data/ORCL/system01.dbf

• PL/SQL语句中使用

PL/SQL语句中使用绑定变量时，需要使用execute immediate执行带有绑定变量的SQL语句，使用into接收select语句的输出，使用using附加绑定变量的值。using中的值，可以是实际值，也可以是存储过程变量。

--using实际值
SQL> declare
  2  oid varchar2(200);
  3  begin 
  4  execute immediate 'select object_id from dba_objects where owner = :1 and object_name = :2' into oid using 'MDSYS','R_TABLE';
  5  dbms_output.put_line(oid);
  6  end;
  7  /
70560
PL/SQL procedure successfully completed.

--using存储过程变量
SQL> declare
  2  oid varchar2(200);
  3  own varchar2(10) := 'MDSYS';
  4  obn varchar2(10) := 'R_TABLE';
  5  begin
  6  execute immediate 'select object_id from dba_objects where owner = :1 and object_name = :2' into oid using own,obn;
  7  dbms_output.put_line(oid);
  8  end;
  9  /
70560
PL/SQL procedure successfully completed.

3. PL/SQL存储过程变量

PL/SQL存储过程变量，需要在declare字句中进行声明，赋值时使用“:=”冒号等号赋值符。在赋值或引用存储过程变量时，直接写变量名即可，不需要加&或:号。

3.1 使用规则

与PL/SQL存储过程相关的操作如下：

• declare：变量声明，生命变量名、数据类型，并可以赋值
• %TYPE：引用某个表的某一列的数据类型
• %ROWTYPE：引用某个表的表结构
• var := value：变量赋值
• select col into var：将select结果赋值给变量
• execute immeidate 'select ...' into var：将SQL结果赋值给变量

3.2 应用详解

变量使用前必须声明，变量声明必须在declare字句中。

可以在变量声明时，同时赋值，也可以在begin字句使用变量前进行赋值。

直接键入变量名，即可引用变量，无需加任何修饰符。

--声明时直接赋值
SQL> declare
  2  out varchar2(20) := 'My Output';
  3  begin
  4  dbms_output.put_line(out);
  5  end;
  6  /
My Output

--在begin中赋值
SQL> declare  
  2  out varchar2(20);
  3  begin
  4  out := 'My Output';
  5  dbms_output.put_line(out);
  6  end;
  7  /
My Output

--使用select into语句赋值，into在select和from之间
SQL> declare
  2  name v$database.name%TYPE;
  3  begin
  4  select name into name from v$database;
  5  dbms_output.put_line(name);
  6  end;
  7  /
ORCL

--使用execute immediate执行SQL语句后，将结果赋值给变量，into语句后面
SQL> declare
  2  name v$database.name%TYPE;
  3  begin
  4  execute immediate 'select name from v$database' into name;
  5  dbms_output.put_line(name);
  6  end;
  7  /
ORCL

--将表结构作为变量的数据类型，可以通过点号，引号某列的值
SQL> declare
  2  out v$database%ROWTYPE;
  3  begin
  4  select * into out from v$database;
  5  dbms_output.put_line(out.name);
  6  end;
  7  /
ORCL