set serveroutput on;
begin
dbms_output.put_line('Hello World!');
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:长字符 最长2g
7. boolean 布尔类型 true fasle 和null值 ,不给初值为null
例子:
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;
---------------------------
v_temp value:
---------------------------
declare
v_empno number(4);
v_empno2 emp.empno%type
v_empno3 vempno2%type
bengin
dbms_output.put_line('Test');
end;
--Table变量类型 相当于java数组
set serveroutput on;
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;
************
9999
************
--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 :='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
************
50 aaa
************
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname :='aaa';
v_temp.loc :='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
***************
50 aaa
***************
PLSQL的SQL语句
PLSQL中select语句返回一条且只有一条数据
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;
***************
SMITH 800
***************
简便写法
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_ename ||' '|| v_sal);
end;
***************
SMITH 800
***************
--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 :=10;
v_count number;
begin
-- update emp2 set sal = sal/2 where deptno=v_deptno;--影响3条
-- select count(*) into v_count from emp2;--影响1条,指生成1个值
select deptno into v_deptno from emp2 where empno=7369;
dbms_output.put_line(sql%rowcount||'跳记录被影响 ');
--sql%rowcount sql是关键字 rowcount属性
commit;
end;
----------------------------------------
PLSQL的DDL语句 执行需要immediate
begin
execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;
-- if语句
取出7369的薪水 如果小于'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 --elsif
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
******
low
******
--循环
1. 相当于do.. while..
declare
i binary_integer :=1;
begin
loop
dbms_output.put_line(i);
i:= i+1;
exit when(i >=1);
end loop;
end;
*****
1
2
3
4
5
6
7
8
9
******
10
--相当于while
declare
j binary_integer :=1;
begin
while j< 11 loop
dbms_output.put_line(j);
j:=j+1;
end loop;
end;
-- 相当于for 循环
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 10..1 loop
dbms_output.put_line(k);
end loop;
end;