欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle数据库sql语句之PLSQL程序结构讲解

程序员文章站 2022-05-02 23:03:49
pl/sql程序结构 set serveroutput on --求两数之和 ,之差 相除 declare a number(2):=10; b number(2):=20; c number(7,...

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;

/