38. 在客户端输出helloworld
set serveroutput on;//默认是off,设成on是让Oracle可以在客户端输出数据
begin
dbms_output.put_line('helloworld');
end;
/
----------------pl/sql变量的赋值与输出----
declare
v_name varchar2(20);//声明变量v_name变量的声明以v_开头
begin
v_name:= 'myname';
dbms_output.put_line(v_name);
end;
/
39. pl/sql对于异常的处理(除数为0)
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;
/
40. 变量的声明
binary_integer:整数,主要用来计数而不是用来表示字段类型 比number效率高
number:数字类型
char:定长字符串
varchar2:变长字符串
date:日期
long:字符串,最长2GB
boolean:布尔类型,可以取值true,false,null//最好给一初值
----------变量的声明,使用 '%type'属性---------
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
/
--使用%type属性,可以使变量的声明根据表字段的类型自动变换,省去了维护的麻烦,而且%type属性,可以用于变量身上
41.组合变量
---------------Table变量类型(相当于java里面的数组)--------------------
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnostype_table type_table_empno;
begin
v_empnos(0):= 7345;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;
42. Record变量类型(相当于Java里面的类)
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变量,直接参照表来声明record-------------------
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(vtemp.deptno || '' || v temp.dname)
end;
43. select语句的运用(必须保证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;
-------------------------select语句的应用(record)------------------------------
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output_line(v_emp.ename);
end;
------------- insert语句的应用 -----------------------------
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type :='aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;
-------------update 语句的应用------------------------------
declare
v_deptno emp2.deptno%type := 50;
v_count number;
begin
update emp2 set sal = sal/2 where deptno =v_deptno;
dbms_output.put_line(sql%rowcount || ‘条记录被影响’);
commit;
end;
注:sql%rowcount统计上一条sql语句更新的记录条数
44.ddl语言,数据定义语言
begin
execute immediate 'create table T (nnn varchar(30) default ''a'')';
end;
------------------ifelse语句--------------------------------------
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal< 2000) then
dbms_output.put_line('low');
els if(v_sal > 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('height');
end if;
end;
45. 循环
do while 循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i:= i + 1;
exit when (i>=11);
end loop;
end;
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 1..10 loop
dbms_output.put_line(k);
end loop;
end;