oracle pl/sql编程详解,Oracle PL/SQL编程语法详解

--plsql块结构,计算a，b的和

declareaint:=10;

bint:=20;

cint;beginc:=a+b;

dbms_output.put_line(c);end;--%type数据类型，输出员工名称和职务信息

declarevar_ename scott.emp.ename%type;

var_job scott.emp.job%type;begin

selectename,jobintovar_ename,var_jobfromscott.empwhere empno=7369;

dbms_output.put_line(var_ename||'的职务是:'||var_job);end;--record类型

declaretype emp_typeisrecord

(

var_enamevarchar2(50),

var_jobvarchar2(20),

var_salnumber);

empinfo emp_type;begin

selectename,job,salintoempinfofromscott.empwhere empno=7369;

dbms_output.put_line(empinfo.var_ename);end;--%rowtype数据类型

declarerowVar_emp scott.emp%rowtype;begin

select *

intorowVar_empfromscott.empwhere empno=7369;

dbms_output.put_line(rowVar_emp.ename);end;----------------------------------------------------------------流程控制--------------------------------------------------------------if ...then 比较字符串长短，输出长的字符串

declarevar_name1varchar2(50);

var_name2varchar2(50);beginvar_name1:='dog100';

var_name2:='dog232332';iflength(var_name1)>length(var_name2)thendbms_output.put_line(var_name1);elsedbms_output.put_line(var_name2);end if;end;--case 输出季节的月份

declareseasonint:=2;

infovarchar2(100);begin

caseseasonwhen 1 theninfo:='1,2,3';when 2 theninfo:='4,5,6';when 3 theninfo:='7,8,9';when 4 theninfo:='10,11,12';elseinfo :='dog';end case;

dbms_output.put_line(info);end;-------------------------------------------------------------------循环语句-----------------------------------------------------------loop 计算1到100自然数之和

declaresum_iint:=0;

iint:=0;beginloop

i:=i+1;

sum_i:=sum_i+i;exit when i=100;endloop;

dbms_output.put_line(sum_i);end;--while

declaresum_iint:=0;

iint:=0;begin

whilei<=100loop

sum_i:=sum_i+i;

i:=i+1;endloop;

dbms_output.put_line(sum_i);end;--for

declaresum_iint:=0;begin

for i in reverse 1..100loop

sum_i:=sum_i+i;endloop;

dbms_output.put_line(sum_i);end;--------------------------------------------------------------------游标---------------------------------------------------显式游标,读取雇员信息

declare

cursor cur_emp(var_job in varchar2:='SALESMAN')is selectempno,ename,salfromscott.empwhere job=var_job;

type record_empisrecord

(

var_empno scott.emp.empno%type,

var_ename scott.emp.ename%type,

var_sal scott.emp.sal%type

);

emp_row record_emp;begin

open cur_emp('MANAGER');fetch cur_emp intoemp_row;while cur_emp%found

loop

dbms_output.put_line(emp_row.var_ename);fetch cur_emp intoemp_row;endloop;closecur_emp;end;--隐式游标，工资上调20%

begin

updatescott.empset sal=sal*(1+0.2)where job='SALESMAN';if sql%notfound thendbms_output.put_line('No');elsedbms_output.put_line(sql%rowcount);end if;end;--通过for循环语句循环游标，隐式游标

begin

for emp_record in (select * from scott.emp where job='SALESMAN')

loop

dbms_output.put_line(emp_record.ename);endloop;end;--通过for循环语句循环游标，显式游标

declare

cursor cursor_emp is

select * from scott.emp where job='SALESMAN';begin

for emp_record incursor_emp

loop

dbms_output.put_line(emp_record.ename);endloop;end;---------------------------------------------------------------------------------------------