Kamagra repose sur le sildénafil comme principe actif, avec un mode d’action identique à celui du Viagra. La forme galénique en gel oral permet une absorption plus rapide et une concentration plasmatique maximale plus précoce que les comprimés. Le mécanisme implique l’inhibition compétitive de la PDE5, entraînant une relaxation musculaire lisse locale et une vasodilatation ciblée. La demi-vie courte, environ 4 heures, limite la durée d’action. L’élimination se fait après métabolisme hépatique, impliquant majoritairement le CYP3A4. L’incidence d’effets indésirables comprend céphalées, rougeurs et congestion nasale, de façon transitoire. Dans les comparatifs pharmacologiques, acheter kamagra sans ordonnance est associé aux présentations galéniques alternatives disponibles.

Microsoft powerpoint - p4-oracle-plsql-04

5. Subprograms
5. Subprograms
• What are Subprograms ?
– Subprograms are named PL/SQL blocks that can take • PL/SQL has two types of subprograms
5 Subprograms
• Syntax
5.1 Procedures
[CREATE [OR REPLACE]]
PROCEDURE procedure_name
[(parameter[, parameter].)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
executable statements
[EXCEPTION
exception handlers]
- where parameter stands for the following syntax:
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]]
datatype
[{:= | DEFAULT}
DEFAULT} expression]
5 Subprograms
.5.1 Procedures
PROCEDURE procedure_name[(parameter[, parameter].)]
Procedure Specification
Declarative Part
[local declarations]
executable Part
executable statements
[EXCEPTION
exception handlers]
Exception-handling Part
END [name];
Procedure Body
Programming Lab : No4 : Ex1
PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
current_salary REAL;
salary missing EXCEPTION;
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current salary IS NULL THEN
RAISE salary_missing;
UPDATE emp SET sal = sal + amount
WHERE empno = emp_id;
EXCEPTION
DATA FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, 'No such emp
number');
WHEN salary_missing THEN
INSERT INTO
emp audit VALUES (
END raise_salary;
emp num := 1111;
raise_salary(emp_num, 10);
5 Subprograms
.5.2 Functions
5 Subprograms
.5.2 Functions
• Example
FUNCTION sal
sal_ok (salary
REAL, title
VARCHAR2)
RETURN BOOLEAN IS
losal, hisal
INTO min_sal, max_sal
FROM sals
WHERE job
RETURN (salary >= min
max_sal);
END sal_ok;
Programming Lab : No4 : Ex2 –
function create
create or replace function insert_one(
stu_number varchar2,
varchar2)
return integer is
insert into student(snumber, sname)
values (stu_number, stu_name);
if ( SQL%ROWCOUNT > 0 ) then
DBMS_OUTPUT.PUT_LINE('Insert OK');
return(1);
DBMS_OUTPUT.PUT_LINE('Insert Failed');
end insert_one;
Programming Lab : No4 : Ex2 –
function call
integer;
result := insert_one('9757005', 'Ko') ;
DBMS_OUTPUT.PUT_LINE('OK');
DBMS_OUTPUT.PUT_
---------------------------------------------------------
variable i number
execute :i := insert_one('9757009', 'Kim.D')
Programming Lab : No4 : Ex3 –
function create
create or replace function delete_one(stu_number varchar2)
return integer is
delete from student where snumber = stu number;
stu_number
if ( SQL%ROWCOUNT > 0 ) then
return(SQL%ROWCOUNT) ;
return(0);
Programming Lab : No4 : Ex3 –
function call
i := delete_one('9757005');
if ( i >= 0 ) then
DBMS_OUTPUT.PUT_LINE(i || ' students deleted');
DBMS_OUTPUT.PUT_LINE('FAIL');
execute :i := delete_one('9757009');
5 Subprograms
.5.4 Packaging
– grouping logically related subprograms in – the subprograms can be shared by many applications. – packa • subprogram bodies• they are invisible to applications. • Thus, packages allow you to hide implementation details.
5 Subprograms
.5.4 Packaging
CREATE PACKAGE emp_actions AS -- package spec
PROCEDURE hire em
p id INTEGER, name
VARCHAR2, .);
PROCEDURE fire_employee (emp_id INTEGER);
PROCEDURE raise
raise_salary (emp
(emp_id INTEGER
END emp_actions;
5. Subprograms
• example – package body
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee
(emp_id INTEGER, name VARCHAR2, .) IS
INSERT INTO
END hire_employee;
PROCEDURE fire_employee (emp_id INTEGER) IS
DELETE FROM emp WHERE empno = emp id;
END fire_employee;
PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
UPDATE emp SET sal = sal + amount
WHERE empno = emp_id;
END raise_salary;
actions;
5 Subprograms
5.5 Specifying Parameters
– an IN parameter acts like a constant
• Therefore, it cannot be assigned a value.
PROCEDURE d b
bit_account (
IN INTEGER,
minimum_purchase CONSTANT REAL DEFAULT 10.0;
service charge
service_charge CONSTANT REAL DEFAULT 0 50;
IF amount < minimum_purchase THEN
vice charge
ice_charge; -- ca ses
compilation error
END debit_account;
5 Subprograms
5.5 Specifying Parameters
return values to the caller of a subprogram – an OUT parameter acts like a variable– The actual parameter • it cannot be a constant or an expression. • For example, _bonus(7499, salary + commission); --
causes compilation error
5. Subprograms
5.5 Specifying Parameters
– OUT formal parameters are initialized to NULL– So, the datatype of an OUT formal parameter • cannot be a subtype defined as NOT NULL SUBTYPE Counter IS INTEGER NOT NULL;
rows Counter
PROCEDURE count_emps (n OUT Counter) IS
SELECT COUNT(*) INTO n FROM emp;
count_emps(rows); -- raises VALUE_ERROR
5 Subprograms
5.5 Specifying Parameters
• pass initial values to the subprogram being called • and return updated values • an initialized variable• Therefore, it can be assigned a value and • its value can be assigned to another variable.
5. Subprograms
g Parameters
• NOCOPY
– the OUT and IN OUT parameters are passed by value.
• if the subprogram exits normally, the values assigned to the OUT and IN OUT formal l parameters are copied i corresponding actual parameters.
• When the parameters hold large data structures – such as collections, records, and instances of object typ – all this copying slows down execution and uses up memory. • To prevent that, you can specify the NOCOPY hint, – which allows the PL/SQL compiler to pass OUT and IN OUT 5 Subprograms
5.6 Overloading
can use the same name for veral different TYPE DateTabTyp IS date;
TYPE RealTabTyp IS number;
hiredate_tab DateTabTyp;
comm_tab RealTabTyp;
RealTabTyp
indx BINARY_INTEGER;
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
indx := 50;
initialize(hiredate_tab, indx); -- calls first
initialize(comm_tab, indx); -- calls second version

Source: http://dblab.sangji.ac.kr/downloads/dbp/P4-oracle-plsql-04.pdf