Oracle PL/SQL SQL语句编程

匿名块

创建匿名块

–输出HelloWorld
BEGIN
dbms_output.put_line(‘HelloWorld’);
END;
/

–输出SCOTT用户的薪水
DECLARE
–定义变量
v_sal NUMBER(5);–变量名称不要和表名、列名重复
v_deptno emp.deptno%type;–该变量类型和emp表的deptno列的类型一致

BEGIN
select sal,deptno into v_sal,v_deptno from emp where ename = ‘SCOTT’;
dbms_output.put_line(‘SCOTT用户的薪水是:’ || v_sal || ‘，部门编号是:’ || v_deptno);
END;
/

–输入用户名称，输出该用户的薪水
DECLARE
–定义变量
v_sal NUMBER(5);–变量名称不要和表名、列名重复
v_deptno emp.deptno%type;–该变量类型和emp表的deptno列的类型一致
v_ename emp.ename%type;
BEGIN
select sal,deptno,ename into v_sal,v_deptno,v_ename from emp where ename = ‘&name’;
dbms_output.put_line(v_ename || ‘用户的薪水是:’ || v_sal || ‘，部门编号是:’ || v_deptno);
END;
/

匿名块只能使用一次

过程

创建过程

–统计10部门的最高薪水
CREATE OR REPLACE PROCEDURE maxSalByDepton_pro IS
v_maxSal emp.sal%type;
BEGIN
select max(sal) into v_maxSal from emp where deptno = 10;
dbms_output.put_line(‘10部门的最高薪水是：’ || v_maxSal);
END;

–统计某个部门的最高薪水
CREATE OR REPLACE PROCEDURE maxSalByDepton_pro(v_deptno number,v_maxSal out emp.sal%type) IS
BEGIN
select max(sal) into v_maxSal from emp where deptno = v_deptno;
dbms_output.put_line(v_deptno || ‘部门的最高薪水是：’ || v_maxSal);
END;

调用

调用过程
call maxSalByDepton_pro();

–匿名块/过程/函数调用：
DECLARE
v_sal emp.sal%type;
BEGIN
–赋值
–v_sal := ‘a’;
–字符串内显示单引号 （用两个单引号） ’ ” ’
maxSalByDepton_pro(20,v_sal);
dbms_output.put_line(v_sal);
END;
/
–单独调用：
call maxSalByDepton_pro(20,v_sal);

函数

创建函数

–统计某个部门的最高薪水
CREATE OR REPLACE FUNCTION maxSalByDepton_fun(v_deptno number) RETURN number IS
v_maxSal emp.sal%type;
BEGIN
select max(sal) into v_maxSal from emp where deptno = v_deptno;
dbms_output.put_line(v_deptno || ‘部门的最高薪水是：’ || v_maxSal);
return v_maxSal;
END;

调用函数

DECLARE
v_sal emp.sal%type;
BEGIN
v_sal := maxSalByDepton_fun(20);
dbms_output.put_line(v_sal);
END;
/

复合类型

创建带有复合类型的过程

–查找指定的员工编号对应的员工信息
create or replace procedure emp_pro1(v_empno number) is
–自定义一个复合类型
TYPE emp_type IS RECORD(v_empno emp.empno%type,v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);
–创建复合类型的变量
v_emp_type emp_type;

begin
select empno,ename,sal,job into v_emp_type from emp;
dbms_output.put_line(v_emp_type.v_empno || ‘,’ || v_emp_type.v_ename);
end;
/

异常

创建带有异常处理和复合类型的过程

查找指定的员工编号对应的员工信息
create or replace procedure emp_pro1(v_empno number) is
–自定义一个复合类型
TYPE emp_type IS RECORD(v_empno emp.empno%type,v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);
–创建复合类型的变量
v_emp_type emp_type;

begin
select empno,ename,sal,job into v_emp_type from emp where empno = v_empno;

–定义异常处理
exception
when no_data_found then
dbms_output.put_line(‘没有对应的记录’);
when too_many_rows then
dbms_output.put_line(‘只能对应一条记录’);
when others then
dbms_output.put_line(‘other’);

end;
/

分支

分页的时候
总记录数 每页显示的记录数
页数 = 总记录数/每页显示的记录数

包含分支的匿名块

DECLARE
v_count number(2);
v_perPage number := 5;
v_pages number(4,2);
BEGIN
select count(*) into v_count from emp;
v_pages := trunc(v_count/v_perPage);
if mod(v_count,v_perPage) > 0 then
v_pages := v_pages + 1;
end if;
dbms_output.put_line(‘总记录数：’ || v_count || ‘,每页显示记录数：’ || v_perPage ||’,总页数：’ || v_pages);
END;
/

游标和循环

创建包含游标和循环的过程

create or replace procedure cursor_pro(v_deptno number) is
–定义游标类型
type emp_cursor is ref cursor;
–定义游标类型的变量
v_emp_cursor emp_cursor;

v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;

begin
–循环
loop
fetch v_emp_cursor into v_empno,v_ename,v_job;
exit when v_emp_cursor%notfound;
dbms_output.put_line(v_empno||’,’||v_ename||’,’||v_job);
end loop;
CLOSE v_emp_cursor;

end;
/

包

创建包

CREATE OR REPLACE PACKAGE 包名 IS
procedure t_pro;–包内的过程
function t_fun RETURN NUMBER;–包内的函数
END;
/

创建包体

CREATE OR REPLACE PACKAGE BODY 包名 is
procedure t_pro is
begin
dbms_output.put_line(‘procedure’);
end;
function t_fun RETURN NUMBER is
begin
dbms_output.put_line(‘function’);
end;
end;
/

调用包

call 包名.t_pro();