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;

/

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐