set serveroutput on;
--最简单的语句块
begin
dbms_output.put_line('HelloWorld');
end;
/
--语句块的组成
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
/
--常用变量类型
1. binary_integer:整数,主要用来计算而不是用来表示字段类型
2. number:数字类型
3. char:定长字符串
4. varchar2:变长字符串
5. date:日期
6. long:长字符串,最长2GB
7. boolean:布尔类型,可以取值为true、false和null值
--变量声明规则
1. 变量名不能够使用保留字,如from、select等
2. 第一个字符必须是字母
3. 变量名最多包含30个字符
4. 不要数据库的表或列同名
5. 每一行只能声明一个变量
--变量声明
declare
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar(20) not null := 'MyName';
begin
dbms_output.put_line('v_temp value:' || v_temp);
end;
--变量声明,使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 emp.empno%type;
begin
dbms_output.put_line('Test');
end;
/
--Table变量类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
/
--Record变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--SQL语句的运用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
/
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
/
--insert语句
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values(v_deptno, v_dname, v_loc);
commit;
end;
/
declare
v_deptno emp2.deptno%type := 50;
v_count number;
begin
--update emp2 set sal = sal/2 where deptno = v_deptno;
--select deptno into v_deptno from emp2 where empno = 7369;
select count(*) into v_count from emp2;
dbms_output.put_line(sql%rowcount || '条记录被影响');
commit;
end;
/
DDL语句
begin
execute immediate 'create table t (nnn varchar2(20) default ''aaa'')';
end;
/
--if语句
取出7369的薪水,如果<1200,输出'low',如果<2000输出'middle',否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
/
--循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i >= 11);
end loop;
end;
/
--练习
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
/
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
/
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
/