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_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;

/