本讲课程目标:
1.了解基本的PL/SQL块和结构
2.描述变量的在PL/SQL中的重要性
3.声明变量和执行PL/SQL块
一:PL/SQL的块结构
- DECLARE (Optional)
- Variables, cursors, user-defined exceptions
- BEGIN (Mandatory)
- SQL statements
- PL/SQL statements
- EXCEPTION (Optional)
- Actions to perform when errors occur
- END; (Mandatory)
在begin区内的sql和pl/sql语句都需要以';'号结束,DECLARE,BEGIN,EXCEPTION关键字后面不需要跟';'号,END关键字则需要。PL/SQL的块类型主要有匿名(anonymous),过程(procedure)和函数(function),函数需要定义返回
二:变量的特点
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance
在PL/SQL中声明变量,可以在declare和begin区对变量进行声明,在begin区定义的变量可以覆盖之前定义的变量;变量的输出有IN,OUT,IN OUT三种类型
Declare and initialize variables in the declaration section.
Assign new values to variables in the executable section.
Pass values into PL/SQL blocks through parameters.
View results through output variables.
变量的类型:
PL/SQL variables
Non-PL/SQL variables: Bind and host variables(用在sqlplus和isqlplus环境下)
所有的PL/SQL变量都有相应的数据类型,数据类型指定存储格式,约束和可用的值范围;PL/SQL变量支持的数据类型有:scalar, composite, reference, LOB
三:声明变量
语法:identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
变量声明的指导原则
1.统一的命令规范,变量名尽量以v_开头,要避免变量名和字段名相同,否则变量将会优先被解释成字段运行
变量名不能超过30个字节,需要以字母开头
2.一行定义一个变量
3.需要初始化的变量定义not null和constant
4.对变量进行赋初始值使用':='或者default保留字
示例:
- SQL> set serveroutput on
- SQL> declare
- 2 v_hiredate DATE;
- 3 v_deptno number(2) not null :=10;
- 4 v_location varchar2(10) default 'fuzhou';
- 5 c_comm constant number := 1400;
- 6 begin
- 7 select hire_date into v_hiredate from hr.employees where employee_id=100;
- 8 dbms_output.put_line('The hire_date is '||v_hiredate);
- 9* end;
- SQL> /
- The hire_date is 1987-06-17-00:00:00
- PL/SQL procedure successfully completed
CONSTANT表示变量为固定变量,必须指定初始值;%TYPE属性可以指定变量的类型同基表的数据类型一致,这样可以避免将代码写死
绑定变量示例:调用绑定变量的时候需要加':'号
- SQL> variable v_salary number
- SQL> begin
- 2 select salary into :v_salary
- 3 from hr.employees where employee_id=100;
- 4 dbms_output.put_line('The result is '||:v_salary);
- 5 end;
- 6 /
- The result is 100000
- PL/SQL procedure successfully completed.
替换变量示例:定义替换变量使用define命令,调用替换变量使用'&'符号
- SQL> define v_emp_id = 100
- SQL> variable v_salary number
- SQL> set verify off
- SQL> begin
- 2 select salary into :v_salary
- 3 from hr.employees where employee_id=&v_emp_id;
- 4 dbms_output.put_line('The emp_id is '||&v_emp_id);
- 5 dbms_output.put_line('The salary is '||:v_salary);
- 6* end;
- SQL> /
- The emp_id is 100
- The salary is 100000
- PL/SQL procedure successfully completed.
本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/625399如需转载请自行联系原作者
ylw6006