oracle数据库sql语句之PLSQL程序结构讲解
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_pide 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;
/