例子1:查询雇员编号为7788雇员姓名和工资
DECLARE
v_name VARCHAR2(10);
v_sal NUMBER(5);
BEGIN
SELECT ename,sal INTO v_name,v_sal
FROM emp WHERE empno=7788;
DBMS_OUTPUT.PUT_LINE('7788号雇员是:'||v_name||',工资为:'||to_char(v_sal));
END;
记录:
TYPE r_record is record(
v_name emp.ename%TYPE,
v_job emp.job%TYPE,
v_sal emp.sal%TYPE);
变量定义:r_emp r_record;
r_employee r_record;
变量赋值:
select ename,job,sal INTO r_emp from emp where empno=7934;
或:
r_employee.v_ename:='JACK';
r_employee.v_job:='CLERK';
r_employee.v_sal:=890.98;
PL/SQL不直接支持DDL语句
若要在PL/SQL中使用DDL语句,可以采用:
EXECUTE immediate 'DDL语句'
例子:
Execute immediate 'create table test(coll int)';
IF THEN ELSE
declare
v_sal number(7,2);
begin
select sal into v_sal from emp where ename='SMITH';
if(v_sal>4000) then
dbms_output.put_line('high');
elsif(v_sal>2000) then
dbms_output.put_line('Middle');
else
dbms_output.put_line('low');
end if;
end;
循环:
declare
i integer;
sum1 integer;
begin
i:=0;
sum1:=0;
loop
sum1:=sum1+i;
i:=i+2;
exit when i>100;
end loop;
dbms_output.put_line(sum1);
end;
declare
v_count NUMBER(2):=1;
BEGIN
while v_count<6 loop
insert into emp(empno,ename)
values(5000+v_count,'临时');
v_count:=v_count+1;
end loop;
commit;
end;
求1!+2!+。。。+10!
declare
v_total NUMBER(8):=0;
v_ni number(8):=0;
j number(5);
begin
for i in 1..10 loop
j:=1;
v_ni:=1;
while j<=i
loop
v_ni:=v_ni*j;
j:=j+1;
end loop;--内循环求N!
v_total:=v_total_v_ni;
end loop;
dbms_output.put_line(v_total);
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13442480/viewspace-1039585/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13442480/viewspace-1039585/