← Back to branch summary

~halega/+junk/sqlinstaller

« back to all changes in this revision

Viewing changes to SQLInstaller.Sample/Scripts/Oracle/Install/StoredProcedures/CATEGORIES.StoredProcedure.sql

  • Committer: sk
  • Date: 2011-09-10 05:32:36 UTC
  • Revision ID: halega@halega.com-20110910053236-1877r3p0k4a64bgx
Tags: 1.2.2
1.2.2

Show diffs side-by-side

added added

removed removed

Lines of Context:
SQLInstaller.Sample/Scripts/Oracle/Install/StoredProcedures/CATEGORIES.StoredProcedure.sql
 
1
 
 
2
CREATE OR REPLACE PROCEDURE CATEGORIESSELECT
 
3
(
 
4
    RESULTSET OUT SYS_REFCURSOR
 
5
)
 
6
IS
 
7
BEGIN
 
8
    OPEN RESULTSET FOR
 
9
        SELECT
 
10
                CATEGORYID AS "CategoryID",
 
11
                CATEGORYNAME AS "CategoryName",
 
12
                DESCRIPTION AS "Description",
 
13
                PICTURE AS "Picture"
 
14
        FROM CATEGORIES;
 
15
 
 
16
END;
 
17
/
 
18
 
 
19
CREATE OR REPLACE PROCEDURE CATEGORIESUPDATE
 
20
(
 
21
        VCATEGORYID IN CATEGORIES.CATEGORYID%type,
 
22
        VCATEGORYNAME IN CATEGORIES.CATEGORYNAME%type,
 
23
        VDESCRIPTION IN CATEGORIES.DESCRIPTION%type,
 
24
        VPICTURE IN CATEGORIES.PICTURE%type
 
25
)
 
26
IS
 
27
BEGIN
 
28
        UPDATE CATEGORIES
 
29
        SET
 
30
                CATEGORYID      = VCATEGORYID,
 
31
                CATEGORYNAME    = VCATEGORYNAME,
 
32
                DESCRIPTION     = VDESCRIPTION,
 
33
                PICTURE = VPICTURE
 
34
        WHERE
 
35
                CATEGORYID = VCATEGORYID
 
36
;
 
37
        
 
38
        
 
39
END;
 
40
/
 
41
 
 
42
 
 
43
 
 
44
CREATE OR REPLACE PROCEDURE CATEGORIESINSERT
 
45
(
 
46
        VCATEGORYID IN CATEGORIES.CATEGORYID%type,
 
47
        VCATEGORYNAME IN CATEGORIES.CATEGORYNAME%type,
 
48
        VDESCRIPTION IN CATEGORIES.DESCRIPTION%type,
 
49
        VPICTURE IN CATEGORIES.PICTURE%type
 
50
)
 
51
IS
 
52
BEGIN
 
53
 
 
54
 
 
55
        INSERT
 
56
        INTO CATEGORIES
 
57
        (
 
58
                CATEGORYID,
 
59
                CATEGORYNAME,
 
60
                DESCRIPTION,
 
61
                PICTURE
 
62
        )
 
63
        VALUES
 
64
        (
 
65
                VCATEGORYID,
 
66
                VCATEGORYNAME,
 
67
                VDESCRIPTION,
 
68
                VPICTURE
 
69
        );
 
70
END;
 
71
/
 
72
 
 
73
CREATE OR REPLACE PROCEDURE CATEGORIESDELETE
 
74
(
 
75
        VCATEGORYID IN CATEGORIES.CATEGORYID%type
 
76
)
 
77
IS
 
78
BEGIN
 
79
 
 
80
        DELETE  FROM CATEGORIES
 
81
        WHERE
 
82
                CATEGORYID = VCATEGORYID;
 
83
 
 
84
END;
 
85
/