stored procedure

I think it's just a function.... maybe it didn't get to the point, please let me know.

[general structure]

create or replace procedure storage name (input variable name data structure of variable)

as

begin

Operation statement

end;

Call stored procedure

exec procedure name ()

[example 1] create a stored procedure and delete the student data of a student number in the score table

create or replace procedure p_delscore
(v_sno score.sno%type)
as
begin
  delete from score where sno=v_sno;
  if sql%rowcount=0 then
    dbms_output.put_line('nodata');
    else
    dbms_output.put_line('delete'||sql%rowcount||'row');
    commit; 
  end if;
end;

[implementation] exec p_delscore('0601020212 ')

[parse] if the data operation is successful, you must write a commit to submit. The most important thing about stored procedures is security. If the code stops running, the database will not be in a safe state.

[example 2] create a stored procedure, give the name of the Institute to be modified, and modify the name of the Department in the sdept table

create or replace procedure p_update
(v_name sdept.deptno%type,v_dname sdept.deptno%type)
as
begin
  update sdept set dname=v_dname where dname=v_name;
  if sql%rowcount!=0 then
    dbms_output.put_line('To update'||sql%rowcount||'That's ok');
    commit;
    else
    dbms_output.put_line('No data updates');
  end if;
end;

[implementation] exec p_update('School of computer information ',' School of computer science ')

[example 3] combined with the cursor, create a stored procedure, find the student numbers of all the failed students of a course, and store them in the failure table.

create table failgrade(
  sno char(15),
  cno char(15),
  grade number(6,2)
)

create or replace procedure p_inscore
(v_cname course.cname%type)
as
  cursor scfail_cur is 
  select * from score where grade<60  and cno =(select cno from course where cname =v_cname);
    sf scfail_cur%rowtype;
begin
open scfail_cur;
loop
  fetch scfail_cur into sf;
  if scfail_cur%rowcount=0 then
  dbms_output.put_line('nodata');
    exit;
  else
    exit when scfail_cur%notfound; 
    insert into failgrade values(sf.sno,sf.cno,sf.grade);
  end if;
end loop;
close scfail_cur;
end;