PL/SQL程序结构
set serveroutput on
--求两数之和 ,之差 相除
declare
a number(2):=10;
b number(2):=20;
c number(7,2);
begin
a:=&num1;
b:=&num2;
c:=(a+b)/(a-b);
dbms_output.put_line('结果为'||c);
exception
when zero_divide then
dbms_output.put_line('除数为0');
end;
/
--查询编号为7782员工姓名
declare
var_empno number;
var_ename varchar2(50);
begin
select empno,ename into var_empno, var_ename from emp where empno=7782;
if sql%found then
dbms_output.put_line('雇员编号:'||var_empno||'雇员名称'||var_ename);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回记录超过一行');
when no_data_found then
dbms_output.put_line('无数据记录');
end;
/
--查询编号为7782员工姓名和工资
declare
var_empno number;
var_ename varchar2(50);
var_sal number;
begin
select empno,ename,sal into var_empno, var_ename, var_sal from emp where empno=7782;
if sql%found then
dbms_output.put_line('empno:'||var_empno||' ename:'||var_ename||' sal:'||var_sal);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回记录超过一行');
when no_data_found then
dbms_output.put_line('无数据记录');
end;
/
--查询编号为7782员工姓名、工资、职位、所在部门
set serveroutput on
declare
var_ename emp.ename%type;
var_sal emp.sal%type;
var_job emp.job%type;
var_dname dept.dname%type;
begin
select ename,sal,job,dept.dname into var_ename, var_sal, var_job, var_dname from emp join dept on dept.deptno=emp.deptno where empno=7782;
if sql%found then
dbms_output.put_line('ename:'||var_ename||' sal:'||var_sal||' job:'||var_job||' dname:'||var_dname);
end if;
end;
/
--查询编号为7499员工信息
declare
info emp%rowtype;
begin
select * into info from emp where empno=7499;
dbms_output.put_line('empno:'||info.empno||', ename:'||info.ename||', sal:'||info.sal||', job:'||info.job||', deptno:'||info.deptno);
end;
/
set serveroutput on
--求两数之和 ,之差 相除
declare
a number(2):=10;
b number(2):=20;
c number(7,2);
begin
a:=&num1;
b:=&num2;
c:=(a+b)/(a-b);
dbms_output.put_line('结果为'||c);
exception
when zero_divide then
dbms_output.put_line('除数为0');
end;
/
--查询编号为7782员工姓名
declare
var_empno number;
var_ename varchar2(50);
begin
select empno,ename into var_empno, var_ename from emp where empno=7782;
if sql%found then
dbms_output.put_line('雇员编号:'||var_empno||'雇员名称'||var_ename);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回记录超过一行');
when no_data_found then
dbms_output.put_line('无数据记录');
end;
/
--查询编号为7782员工姓名和工资
declare
var_empno number;
var_ename varchar2(50);
var_sal number;
begin
select empno,ename,sal into var_empno, var_ename, var_sal from emp where empno=7782;
if sql%found then
dbms_output.put_line('empno:'||var_empno||' ename:'||var_ename||' sal:'||var_sal);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回记录超过一行');
when no_data_found then
dbms_output.put_line('无数据记录');
end;
/
--查询编号为7782员工姓名、工资、职位、所在部门
set serveroutput on
declare
var_ename emp.ename%type;
var_sal emp.sal%type;
var_job emp.job%type;
var_dname dept.dname%type;
begin
select ename,sal,job,dept.dname into var_ename, var_sal, var_job, var_dname from emp join dept on dept.deptno=emp.deptno where empno=7782;
if sql%found then
dbms_output.put_line('ename:'||var_ename||' sal:'||var_sal||' job:'||var_job||' dname:'||var_dname);
end if;
end;
/
--查询编号为7499员工信息
declare
info emp%rowtype;
begin
select * into info from emp where empno=7499;
dbms_output.put_line('empno:'||info.empno||', ename:'||info.ename||', sal:'||info.sal||', job:'||info.job||', deptno:'||info.deptno);
end;
/