オラクルストアドプロシージャの事例を解説

-- Create table
create table EMP
(
  empno NUMBER(4) not null,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr NUMBER(4),
  hiredate DATE,
  sal NUMBER(7,2),
  comm NUMBER(7,2),
  deptno NUMBER(2)
)

create or replace procedure firstP(name in varchar2) is
/* where name is the parameter, in is the input, and varchar2 is the type */
begin
 /* dbms_output.put_line(); equivalent to output to the console, so we have a simple procedure completed
 Remember to use a semicolon at the end of a sentence, the procedure must be executed after it is written
 Save it to the database (F8) shortcut, or click on the top left corner to execute */
  dbms_output.put_line('My name is' ||name);/* dbms_output.put_line is equivalent to System.out.println("My name is"+name);*/ in JAVA
end firstP;

-- Created on Sunday, 2018/12/30 by ADMINISTRATOR 
declare 
  -- Local variables here
  /* test name name type use := to assign a value to the parameter, and end the sentence with a semicolon */
  name2 varchar2(64):= 'database';
begin
  -- Test statements here
  firstp(name2);
end;

create or replace procedure isifp(age in number) is
/* The if procedure starts with then and ends with end if; */
begin
  if (age > 30) then
    dbms_output.put_line('I am over 30 years old');
  else
    if (age < 10) then
      dbms_output.put_line('I'm still a child');
    else
      dbms_output.put_line('I'm struggling');
    end if;
  end if;

end;

create or replace procedure inandout(name in varchar2, age in number,outp out varchar2) is
/*in for input, out for output*/
begin
  outp:='my name is '||name ||',my age is '||age;/* equivalent to return outp in JAVA, but please note that multiple values can be returned in the procedure */
end inandout;

-- Created on Sunday, 2018/12/30 by ADMINISTRATOR 
declare 
  -- Local variables here
  name varchar2(64):='database';
  age number:=06;
  out_p varchar2(64);
begin
  -- Test statements here
  inandout(name,age,outp=>:out_p);
  /*outp here is the output parameter in the procedure, out_p is the alias used in the test*/
end;

create or replace procedure sysrefcursor(id in number, columnss out sys_refcursor) as
/*columnss out sys_refcursor is the output cursor*/
begin
  open columnss for
  select * from emp where empno=id;
end;

-- Created on Sunday, 2018/12/30 by ADMINISTRATOR 
declare 
  -- Local variables here
 cursor ee is select * from emp where empno=7934;
begin
  -- Test statements here
  for e in ee loop
  dbms_output.put_line('deptno:'||e.deptno);
  end loop;
end;

-- Created on Sunday, 2018/12/30 by ADMINISTRATOR 
declare 
  -- Local variables here
 cursor ee is select * from emp where empno=7934;
 cur ee % rowtype;
begin
  -- Test statements here
  open ee;
  loop
  fetch ee into cur;
  exit when ee%notfound;
  dbms_output.put_line('name:'||cur.ename);
  end loop;
  close ee;
end;

-- Created on Sunday, 2018/12/30 by ADMINISTRATOR 
declare 
  -- Local variables here
 cursor ee is select * from emp where job='CLERK';
begin
  -- Test statements here
  for e in ee loop
  dbms_output.put_line('deptno:'||e.deptno);
  end loop;
end;

create or replace procedure intop(id in number, print2 out varchar2) as
  e_name varchar2(64);
begin
  select ename into e_name from emp where empno = id;
  if e_name = 'ALLEN' then 
   dbms_output.put_line(e_name);
   print2:='my name is '||e_name;
   else if e_name = 'SMITH' then 
      print2:='print sql'||e_name;
      else
        print2:='print other';
      end if;
   end if;
end intop;

-- Create table
create table CLASSES
(
  id NUMBER not null,
  name VARCHAR2(14),
  classesc VARCHAR2(10),
  seq NUMBER(5)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  storage (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/recreate primary, unique and foreign key constraints 
alter table CLASSES
  add constraint PK_CLASSES primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  storage (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create sequence 
create sequence SEQ_CLASSES
minvalue 1
maxvalue 999999999999999999999999999999999999
start with 2
increment by 1
cache 20;

create or replace procedure proclasses(Names in varchar2,
                                       classescs in varchar) as
/*as is actually is*/ when we create the procedure
  id number;/* set variable name*/
  c number;
  seq number;
begin
  select SEQ_CLASSES.nextval into id from dual;/*Get the next sequence, use the into to assign the variable name to id*/
  dbms_output.put_line('classescs=' || classescs);/*Printed*/
  select count(*) into c from Classes where classesc = classescs;/*conditional judgment,classesc=in variable*/
  if (c > 0) then/*when the number is greater than 0*/
    select max(seq) + 1 into seq from Classes where classesc = classescs;
    dbms_output.put_line('first seq' || seq);
  else
    if (c = 0) then
      seq := 0;/*If the number of queries is 0, we assign the seq variable to 0*/
      dbms_output.put_line('c = 0 when seq' || seq);
    end if;
  end if;
  insert into classes
    (id, name, classesc, seq)
  values
    (id, names, classescs, seq);
 /*insert into this needless to say, we all understand; note that we must submit after inserting.
  Otherwise the data is not persisted to the database, this insert has no meaning */
end proclasses;

-- Created on Monday, 2019/1/7 by ADMINISTRATOR 
declare 
  -- Local variables here
  names varchar2(32):='Xiaoming';
  classescs varchar2(32):='Class 1';
begin
  -- Test statements here
  proclasses(names,classescs);
end;