�ݺ�ߣ

Sadıqov Xəyyam
Prosedurlar, Funksiyalar və Paketler
http://www.azeroug.org

2/
Özüm haqqda
Şənbə, 14 Sentyabr 2013
Sadıqov Xəyyam
Azerfon LLC
Billing and VAS Engineer
SQL and PL/SQL Trainer
Sadıgov Xəyyam

3/
PL/SQL
Şənbə, 14 Sentyabr 2013 Sadıgov Xəyyam

4/
Procedurlar və Functionlar nədi ?
– PL/SQL bloklar
– PL/SQL subprogram adlanırlar
– Anonym bloklar kimi blok strukturları var
• Könüllü deklarativ bölmə (DECLARE -siz)
• Məcburi işləyən bölmə
• Istisnalar idarə etmək ücün könüllü bölmə
Sadıgov Xəyyam

5/
Anonym blokların ve Subprogramların
fərqi nedi ?

6/
Anonym blokların ve Subprogramların
fərqi nedi ?

7/
Subprogramlarin üstünlükləri
• Easy maintenance
• Code Reuse
• Improved data security
• Improved performance
• Improved code clarity
Sadıgov Xəyyam

8/
Procedure
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
[ AUTHID DEFINER | CURRENT_USER ]
IS|AS
procedure_body;

9/
Procedure
...
CREATE TABLE dept AS SELECT * FROM departments;
CREATE PROCEDURE add_dept IS
v_dept_id dept.department_id%TYPE;
v_dept_name dept.department_name%TYPE;
BEGIN
v_dept_id:=280;
v_dept_name:='ST-Curriculum';
INSERT INTO dept(department_id,department_name)
VALUES(v_dept_id,v_dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT
||' row ');
END;

10/
Procedure parameterler
• Formal və Faktiki
• İN – read-only
• OUT – write-only
• İN OUT – read-write
Sadıgov Xəyyam

11/
Procedure

12/
Invoking Procedures
Haradan proceduru execute ede bilersiz:
– Anonim blok
– Başqa procedur
– Applicationdan
• Oracle Job
• Third-party application
Note: proceduru SQL de istifade etmek olmaz.
Sadıgov Xəyyam

13/
Notation by name, by position

14/
Function
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
RETURN datatype
[ AUTHID [ DEFINER | CURRENT_USER ]]
IS|AS
function_body;

15/
Function
CREATE FUNCTION check_sal RETURN Boolean IS
v_dept_id employees.department_id%TYPE;
v_empno employees.employee_id%TYPE;
v_sal employees.salary%TYPE;
v_avg_sal employees.salary%TYPE;
BEGIN
v_empno:=205;
SELECT salary,department_id INTO v_sal,v_dept_id FROM employees
WHERE employee_id= v_empno;
SELECT avg(salary) INTO v_avg_sal FROM employees WHERE
department_id=v_dept_id;
IF v_sal > v_avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

16/
Invoking a Function
BEGIN
IF (check_sal IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('The function returned
NULL due to exception');
ELSIF (check_sal) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
END;
/

17/
Function
DROP FUNCTION check_sal;
CREATE FUNCTION check_sal(p_empno employees.employee_id%TYPE)
RETURN Boolean IS
v_dept_id employees.department_id%TYPE;
v_sal employees.salary%TYPE;
v_avg_sal employees.salary%TYPE;
BEGIN
SELECT salary,department_id INTO v_sal,v_dept_id FROM employees
WHERE employee_id=p_empno;
SELECT avg(salary) INTO v_avg_sal FROM employees
WHERE department_id=v_dept_id;
IF v_sal > v_avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
...

18/
Invoking Function
BEGIN
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 205');
IF (check_sal(205) IS NULL) THEN
ELSIF (check_sal(205)) THEN
DBMS_OUTPUT.PUT_LINE('Salary > average');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary < average');
END IF;
DBMS_OUTPUT.PUT_LINE('Checking for employee with id 70');
IF (check_sal(70) IS NULL) THEN
ELSIF (check_sal(70)) THEN
...
END IF;
END;
/

19/
Restrictions on User-Defined Functions
Query və DML da işlənilen funkciyada olmaz:
• OUT yada IN OUT parametler
• Commit yada roll back
• create a savepoint or roll back to a savepoint,
• alter the session or the system.
• DDL
• Eyni tablicada deyişiklik etmek
Amma
• INSERT etmek (from SELECT statement)
• INSERT from a subquery in a DML statement
Sadıgov Xəyyam

20/
Package Architecture

21/
Package spec

22/
Package body

23/
Example

24/
Execute

25/
Advantages of PL/SQL Packages
• Modularity
• Easier Application Design
• Information Hiding
• Added Functionality
• Better Performance
Sadıgov Xəyyam

26/
Product-Specific Packages
• DBMS_ALERT
• DBMS_OUTPUT
• DBMS_PIPE
• UTL_FILE
• UTL_HTTP
• UTL_SMTP
Sadıgov Xəyyam

27/
Drop
• Procedures
• DROP PROCEDURE procedure_name
• Function
• DROP FUNCTION function_name
• Package
• DROP PACKAGE package_name
Sadıgov Xəyyam

28/
Data dictionary
• user_procedures
• OBJECT_TYPE=‘PROCEDURE’
• OBJECT_TYPE=‘FUNCTION’
• OBJECT_TYPE=‘PACKAGE’
• user_source
• TYPE=‘PROCEDURE’
• TYPE=‘FUNCTION’
• TYPE=‘PACKAGE’
• user_errors
Sadıgov Xəyyam

29/
Sizin suallarınız

30/
Son
Sadıgov Xəyyam
khsadigov@azerfon.az
Mob: (070) 201 - 13 - 93
Skype: khsadigov

�ݺ�ߣ

Azer oug sadigov xayyam

Recommended

More Related Content

Featured (20)

Azer oug sadigov xayyam

Editor's Notes