The command 'new_value' for SQLPlus

最新推荐文章于 2025-01-11 09:11:28 发布

转载最新推荐文章于 2025-01-11 09:11:28 发布 · 47 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/princessd8251/p/3866380.html

本文介绍了在Oracle数据库中使用SELECT语句返回的结果为变量赋值的方法，并通过几个实例展示了如何在创建目录、表格及插入数据时应用这一技巧。

Format:

column column_name new_value var_name

Meaning:

use the column_name of a select statment to construct a variable, later we can use &var_name to refer its value.

If the Select statement returned more that one row, the last row value will be assigned to the variable.

Example 1:

-- create directory and varchar2 array
col value new_value user_dump_directory;
select value from v$parameter where name = 'user_dump_dest';
create or replace directory user_dump_dir as '&user_dump_directory';

Example 2

set feedback off
set verify off

create table new_value_test_from (
  some_field_name Varchar2(20)
);

create table new_value_test_to (
  another_field Varchar2(20)
);

insert into new_value_test_from values ('Maegerli Mucki');
column some_field_name new_value some_var_name
select some_field_name from new_value_test_from;
insert into new_value_test_to values ('&some_var_name');
select * from new_value_test_to;
drop table new_value_test_from;
drop table new_value_test_to;

set feedback off
set verify off

create table new_value_test (
  some_field_name Varchar2(20)
);

Example 3

insert into new_value_test values ('Maegerli Mucki');
insert into new_value_test values ('Globi');
column some_field_name new_value some_var_name
select some_field_name from new_value_test;
drop table new_value_test;

转载于:https://www.cnblogs.com/princessd8251/p/3866380.html