oracle过程与函数的基本语法

-- 预定义异常
set serveroutput on;
declare
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 9999;
exception
  when too_many_rows then
    dbms_output.put_line('实际返回的行数超出请求的行数......');
  when no_data_found then
    dbms_output.put_line('未找到数据......');
  when others then
    dbms_output.put_line('未知异常......');
end;

-- 自定义异常
set serveroutput on;
declare
  user_exception exception;
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 7900;
  if v_emp.ename <> 'jack' then
    raise user_exception;
  end if;
exception
  when user_exception then
    dbms_output.put_line('本用户不是JACK！');
end;

--动态sql
begin
  execute immediate 'create table sub (suid number, suname varchar2(100))';
end;

set serveroutput on;
declare
  --record类型
  type re_type is record (
    f_name varchar2(20),
    f_type varchar2(20),
    f_desc varchar2(20)
  );
  -- 定义键值集合类型（方便添加的一种做法）
  type tb_type is table of re_type index by binary_integer;
  v_sql_table tb_type;
  v_tablename varchar2(10) := 'student';
  -- 零时到ddl create语句
  v_temp_ddl varchar2(500);
begin
  -- 为义键值集合赋值
  v_sql_table(1).f_name := ' stu_id ';
  v_sql_table(1).f_type := ' number ';
  v_sql_table(1).f_desc := ' primary key ';
  v_sql_table(2).f_name := ' stu_name ';
  v_sql_table(2).f_type := ' varchar2(20) ';
  v_sql_table(2).f_desc := ' not null ';
  --构建ddl语句
  v_temp_ddl := 'create table ' || v_tablename || '(';
  for i in 1..v_sql_table.count loop
    v_temp_ddl := v_temp_ddl || v_sql_table(i).f_name || 
      v_sql_table(i).f_type || v_sql_table(i).f_desc || ',';
  end loop;
  -- 去除 ','
  v_temp_ddl := substr(v_temp_ddl, 0, length(v_temp_ddl)-1) || ')';
  --dbms_output.put_line(v_temp_ddl);
  --执行创建
  execute immediate v_temp_ddl;
end;

-- 简单过程
create or replace procedure p1
as
begin
  dbms_output.put_line('第一个过程！');
end;

-- 执行过程的第一种方式！
set serveroutput on;
call p1();

-- 执行过程的第二种方式！
set serveroutput on;
begin
  p1();
end;

--带输入/输出参数
create or replace procedure p2(input in number, output out varchar)
as
begin
  select ename into output from emp where empno = input;
end;

set serveroutput on;
declare
  outvalue emp.ename%type;
begin
  p2(7900, outvalue);
  dbms_output.put_line(outvalue);
end;

-- 按参数名称传递
set serveroutput on;
declare
  outvalue emp.ename%type;
begin
  p2(output=>outvalue, input=>7900);
  dbms_output.put_line(outvalue);
end;

-- inout
create or replace procedure p3(var in out varchar)
as
begin
  select job into var from emp where ename = var;
end;

set serveroutput on;
declare
  outvalue emp.ename%type := 'JAMES';
begin
  p3(outvalue);
  dbms_output.put_line(outvalue);
end;

-- 定义函数
create or replace function f1 return varchar2
as
begin
  return '这是一个函数！';
end;

-- 第一种调用方式
select f1() from dual;

-- 第二种调用方式
set serveroutput on;
begin
  dbms_output.put_line(f1());
end;

create or replace function f2(outs in out varchar2) return varchar2
as
begin
  outs := 'xxxx';
  return '这是一个函数！' || outs;
end;

set serveroutput on;
declare 
  v varchar2(100) := '大家好！';
  v_out varchar2(100);
begin
  v_out := f2(v);
  dbms_output.put_line(v_out);
end;

-- 查看子程序的代码
desc  user_source;
select name, text from user_source where name='P1';

desc user_objects;
SELECT object_name  FROM  USER_OBJECTS
WHERE OBJECT_TYPE
IN('PROCEDURE','FUNCTION','PACKAGE');

--程序包  1.创建包规范
create or replace package mypkg
as
  function myf1 return varchar2;
  procedure mypro;
end;

-- 2.创建包主体
create or replace package body mypkg
as
  --fun
  function myf1 return varchar2
  as
  begin
    return '包中的F1...';
  end;
  --proc
  procedure mypro 
  as 
  begin
    dbms_output.put_line('包中到PROC......');
  end;
end;

--调用包中到子程序
select mypkg.myf1() from dual;
set serveroutput on;
call mypkg.mypro();