Oracle PL/SQL编程语法
时间：2021-07-01 10:21:17
帮助过：5人阅读
plsql块结构,计算a，b的和
declare
a int:
=10;
b int:
=20;
c int;
begin
c:=a
+b;
dbms_output.put_line(c);
end;
--%type数据类型，输出员工名称和职务信息
declare
var_ename scott.emp.ename%type;
var_job scott.emp.job%type;
begin
select ename,job
into var_ename,var_job
from scott.emp
where empno
=7369;
dbms_output.put_line(var_ename||‘的职务是:‘||var_job);
end;
--record类型
declare
type emp_type is record
(
var_ename varchar2(
50),
var_job varchar2(
20),
var_sal number
);
empinfo emp_type;
begin
select ename,job,sal
into empinfo
from scott.emp
where empno
=7369;
dbms_output.put_line(empinfo.var_ename);
end;
--%rowtype数据类型
declare
rowVar_emp scott.emp%rowtype;
begin
select *
into rowVar_emp
from scott.emp
where empno
=7369;
dbms_output.put_line(rowVar_emp.ename);
end;
----------------------------------------------------------------流程控制------------------------------------------------------------
--if ...then 比较字符串长短，输出长的字符串
declare
var_name1 varchar2(
50);
var_name2 varchar2(
50);
begin
var_name1:=‘dog100‘;
var_name2:=‘dog232332‘;
if
length(var_name1)>length(var_name2)
then
dbms_output.put_line(var_name1);
else
dbms_output.put_line(var_name2);
end if;
end;
--case 输出季节的月份
declare
season int:
=2;
info varchar2(
100);
begin
case season
when 1 then
info:=‘1,2,3‘;
when 2 then
info:=‘4,5,6‘;
when 3 then
info:=‘7,8,9‘;
when 4 then
info:=‘10,11,12‘;
else
info :=‘dog‘;
end case;
dbms_output.put_line(info);
end;
-------------------------------------------------------------------循环语句---------------------------------------------------------
--loop 计算1到100自然数之和
declare
sum_i int:
=0;
i int:
=0;
begin
loop
i:=i
+1;
sum_i:=sum_i
+i;
exit when i
=100;
end loop;
dbms_output.put_line(sum_i);
end;
--while
declare
sum_i int:
=0;
i int:
=0;
begin
while
i<=100
loop
sum_i:=sum_i
+i;
i:=i
+1;
end loop;
dbms_output.put_line(sum_i);
end;
--for
declare
sum_i int:
=0;
begin
for i
in reverse 1..
100
loop
sum_i:=sum_i
+i;
end loop;
dbms_output.put_line(sum_i);
end;
--------------------------------------------------------------------游标-------------------------------------------------
--显式游标,读取雇员信息
declare
cursor cur_emp(var_job
in varchar2:
=‘SALESMAN‘)
is select empno,ename,sal
from scott.emp
where job
=var_job;
type record_emp is record
(
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
into emp_row;
while cur_emp
%found
loop
dbms_output.put_line(emp_row.var_ename);
fetch cur_emp
into emp_row;
end loop;
close cur_emp;
end;
--隐式游标，工资上调20%
begin
update scott.emp
set sal
=sal
*(
1+0.2)
where job
=‘SALESMAN‘;
if sql
%notfound
then
dbms_output.put_line(‘No‘);
else
dbms_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);
end loop;
end;
--通过for循环语句循环游标，显式游标
declare
cursor cursor_emp
is
select * from scott.emp
where job
=‘SALESMAN‘;
begin
for emp_record
in cursor_emp
loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
---------------------------------------------------------------------------------------------
Oracle PL/SQL编程语法
标签：out while sea color else sum tput 语法 upd
