PLS-21:PL/SQL Stored Procedure(PLS-21)
Stored program includes procedure, function and package
have a name;can take and return parameters;can be stored in database; all users can call.
Advantages are easy maintenance, security and performance(no parse, no network latency and integrity)
How to develop the SP:
write->load the code with sqlplus or other tools->source code->compile->parsed code->{exec SP} on sqlplus
or {begin helloworld;end;}->drop SP
create or replace procedure SPname as
PLS-22: Using Parameters in PL/SQL Procedure
create or replace procedure hello(p_name IN VARCHAR2, p_age OUT NUMBER)//formal parameters
hello('hello', age) or hello(p_age=>7,p_name=>'infor')//actual parameters
create or replace procedure hello(p_name IN OUT NUMBER)
x:=15;
hello(x);
PLS-23: PL/SQL Stored Functions
The number of predifined functions by oracle is 200+;
it must return a value;
it is not allowed to commit or rollback or updatein the function body for select query;
it is not allowed to select in the function body for update query;
create or replace function hello(p_name IN varchar2) return varchar2 as
v_result varchar2(100);
drop function hello;
you can check the body by accessing to the <user_source> table;
PLS-24: PL/SQL Package Introduction
How to implement it:
create or replace package xyz as
function F1(p1 number) retrun number;
procedure p1(p2 number);
end xyz
create or replace package body xyz as
end xyz
How to call it:
xyz.insert_number(444)
xyz.isEven(89;)
What's the advantage
modulize
easy maintenance and readibility
code hiding (private function and API)
overloading
performance
PLS-25: Visibility Rules in PL/SQL Packages
Package without body is ok
there are global fields and private fields