--注释
-- 注释单行
/* */ 多行
--简单的语句块
declare
v_name varchar2(20);
begin
v_name := 'han';
dbms_output.put_line(v_name);
end;
/
--语句块的组成
list
declare
v_num number := 0;
begin
v_num := 4/v_num;
dbms_output.put_line(v_num);
end;
/
declare
v_num number := 0;
begin
v_num := 4/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:长字符串,最长2G
7. boolean:布尔类型,可以取值true、false和null(注意)
--变量声明 :constant 相当于 finally
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 varchar2(20) not null := 'myname';
begin
dbms_output.put_line('v_temp value' || v_temp);
end;
--变量声明 %type
declare
v_temp number := 2;
v_temp1 v_temp%type;
v_temp2 v_temp1%type;
begin
v_temp := 2;
v_temp2 := 3;
dbms_output.put_line(v_temp2 || v_temp);
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) := 7369;
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 := 'aaa';
v_temp.loc := 'beijing'
dbms_output.put_line(v_temp.deptno||'--'||v_temp.dname|| '--' ||v_temp.loc);
end;
/
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaa';
v_temp.loc := 'bejing';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname||''||v_temp.loc);
end;
/
--PL/SQL中SQL的使用 select 必需有 into:(表示有且只有一个),必需有返回值
--复制表数据:create table emp as select * from emp
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_deptno dept2.deptno%type;
v_dname dept2.dname%type;
v_count number;
begin
update dept2 set dname='hanlipeng' where deptno=10;
commit;
select dname into v_dname from dept2 where deptno=10;
dbms_output.put_line(v_dname);
end;
/
declare
v_deptno emp2.deptno%type := 10;
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;
/
-- 注释单行
/* */ 多行
--简单的语句块
declare
v_name varchar2(20);
begin
v_name := 'han';
dbms_output.put_line(v_name);
end;
/
--语句块的组成
list
declare
v_num number := 0;
begin
v_num := 4/v_num;
dbms_output.put_line(v_num);
end;
/
declare
v_num number := 0;
begin
v_num := 4/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:长字符串,最长2G
7. boolean:布尔类型,可以取值true、false和null(注意)
--变量声明 :constant 相当于 finally
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 varchar2(20) not null := 'myname';
begin
dbms_output.put_line('v_temp value' || v_temp);
end;
--变量声明 %type
declare
v_temp number := 2;
v_temp1 v_temp%type;
v_temp2 v_temp1%type;
begin
v_temp := 2;
v_temp2 := 3;
dbms_output.put_line(v_temp2 || v_temp);
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) := 7369;
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 := 'aaa';
v_temp.loc := 'beijing'
dbms_output.put_line(v_temp.deptno||'--'||v_temp.dname|| '--' ||v_temp.loc);
end;
/
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaa';
v_temp.loc := 'bejing';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname||''||v_temp.loc);
end;
/
--PL/SQL中SQL的使用 select 必需有 into:(表示有且只有一个),必需有返回值
--复制表数据:create table emp as select * from emp
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_deptno dept2.deptno%type;
v_dname dept2.dname%type;
v_count number;
begin
update dept2 set dname='hanlipeng' where deptno=10;
commit;
select dname into v_dname from dept2 where deptno=10;
dbms_output.put_line(v_dname);
end;
/
declare
v_deptno emp2.deptno%type := 10;
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;
/