第四章 PL/SQL变量的声明与使用
<h2>第四章的一些示例</h2>
<pre><code>-- 变量使用示例
declare
v_dogid number(10) not null :=38;
v_name varchar2(25) :='White Tiger';
c_color constant varchar2(15) :='White';
v_birthday date;
declare
v_desc varchar2(100) := '中国妇女解放运动的先驱-----';
v_poineer varchar2(25) ;
begin
dbms_output.put_line(v_desc||v_poineer);
v_poineer :='苏妲已';
dbms_output.put_line(v_desc||v_poineer);
end;
declare
v_desc varchar2(100) := '中国妇女解放运动的先驱-----';
v_poineer varchar2(25) := '潘金莲' ;
begin
dbms_output.put_line(v_desc||v_poineer);
v_poineer := '苏妲已';
v_desc := '中国历史上最成功和最美的职业女性-----';
dbms_output.put_line(v_desc||v_poineer);
end;
--定界符
set serveroutput on
declare
v_special_day varchar2(250) := q'[Happy International Woman's Day on 8th march to all lovely women]' ;
begin
dbms_output.put_line(v_special_day);
v_special_day := q'!To many Chinese people,Qixi was celebrated as the "lovers' day".!';
dbms_output.put_line(v_special_day);
end;
--定义几种常量
declare
v_ename varchar2(25);
v_total_sal number(12,2) := 0;
v_count binary_integer := 0;
v_shipdate date := sysdate+21 ;
c_tax_rate constant number(4,2) := 19.50;
c_color constant varchar2(15) :='White';
v_flag boolean not null := false;
--%TYPE属性
v_hiredate emp.hiredate%TYPE ;
v_4Months_interest number(6,4) :=3.25;
v_2Months_interest v_4Months_interest%TYPE := 4.40;
--布尔变量的声明与使用
v_dog1_weight := 28;
v_dog2_weight := 38;
v_dog1_weight < v_dog2_weight;
declare
true_love boolean := false;
begin
true_love :=null;
end;
--绑定变量(绑定变量的number类型不能用长度)
SQL> variable g_dog_weight number
SQL> begin
SQL> :g_dog_weight :=38 ;
SQL> end;
SQL> /
SQL> print g_dog_weight
SQL> desc emp
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER(7,2)
--绑定变量的一个实例
variable g_ename varchar2(10)
variable g_job varchar2(9)
variable g_sal number
begin
select ename,job,sal into :g_ename,:g_job,:g_sal
from emp where empno=7902;
end;
/
print g_ename
print g_job
print g_sal
--打包所有全局变量
print
--验证值
select ename,job,sal from emp where empno=7902;
--将以上显示操作自动化
set autoprint on
--关闭替代变量的原始值与新值的对比
--筛选的员工工号设置为手动输入的变量
set verify off
set autoprint on
variable g_ename varchar2(10)
variable g_job varchar2(9)
variable g_sal number
declare
v_empno number(4) := &empno;
begin
select ename,job,sal into :g_ename,:g_job,:g_sal
from emp where empno = v_empno;
end;
/</code></pre>