PL SQL(变量)

Declare

v_test VARCHAR2(10);

BEGIN

v_test := 'Li Hao';

dbms_output.put_line(v_test);

END;

使用 SELECT INTO 语句来初始化变量

DECLARE

v_salary number(5);

BEGIN

select salary into v_salary from employees

where employee_id=100;

dbms_output.put_line(v_salary);

end;

 

DECLARE

v_salary number(5);

v_fname VARCHAR2(20);

BEGIN

select salary,FIRST_NAME into v_salary, v_fname from employees

where employee_id=100;

dbms_output.put_line(v_fname || 'gong zi ' || v_salary);

end;

 

%TYPE 

declare

v_fname students.first_name%TYPE;

BEGIN

SELECT first_name into v_fname FROM students where stu_id = 1;

    dbms_output.put_line(v_fname);

end;

 

SET SERVEROUTPUT ON

declare

v_fname VARCHAR2(8);

BEGIN

SELECT first_name into v_fname FROM students where stu_id = 1;

    dbms_output.put_line(v_fname);

end;

ALTER TABLE students MODIFY first_name CHAR(10);

 

 

declare

v_fname students.first_name%TYPE;

BEGIN

SELECT first_name into v_fname FROM students where stu_id = 1;

    dbms_output.put_line(v_fname);

end;

常量 

 Declare

a CONSTANT NUMBER(7,6) := 3.123456;

BEGIN

 DBMS_OUTPUT.PUT_LINE(a);

 END;

 

不能这样写

 

Declare

a CONSTANT NUMBER(7,6) not null default 1.123456;

BEGIN

 DBMS_OUTPUT.PUT_LINE(a);

 END;

绑定变量

set serveroutput on

VARIABLE v_bind1 VARCHAR2(25);

exec :v_bind1 := 'xingming';

BEGIN

:v_bind1 := 'li hao';

end;

 

 

显示绑定变量一（DBMS_OPTPUT） 

 

 

显示绑定变量二（pint）

 

 

显示绑定变量三（Set autoprint on;）

设置 AutoPrint 参数，该参数将自动打印绑定变量的值。

Set autoprint on;

VARIABLE v_bind2 VARCHAR2(25);

exec :v_bind2 := 'Li Hao'