← Back to branch summary

~halega/+junk/sqlinstaller

« back to all changes in this revision

Viewing changes to SQLInstaller.Sample/Scripts/Oracle/Install/StoredProcedures/PRODUCTS.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/PRODUCTS.StoredProcedure.sql
 
1
 
 
2
CREATE OR REPLACE PROCEDURE PRODUCTSSELECT
 
3
(
 
4
    RESULTSET OUT SYS_REFCURSOR
 
5
)
 
6
IS
 
7
BEGIN
 
8
    OPEN RESULTSET FOR
 
9
        SELECT
 
10
                PRODUCTID,
 
11
                PRODUCTNAME,
 
12
                SUPPLIERID,
 
13
                CATEGORYID,
 
14
                QUANTITYPERUNIT,
 
15
                UNITPRICE,
 
16
                UNITSINSTOCK,
 
17
                UNITSONORDER,
 
18
                REORDERLEVEL,
 
19
                DISCONTINUED
 
20
        FROM PRODUCTS;
 
21
 
 
22
END;
 
23
/
 
24
 
 
25
CREATE OR REPLACE PROCEDURE PRODUCTSUPDATE
 
26
(
 
27
        VPRODUCTID IN PRODUCTS.PRODUCTID%type,
 
28
        VPRODUCTNAME IN PRODUCTS.PRODUCTNAME%type,
 
29
        VSUPPLIERID IN PRODUCTS.SUPPLIERID%type,
 
30
        VCATEGORYID IN PRODUCTS.CATEGORYID%type,
 
31
        VQUANTITYPERUNIT IN PRODUCTS.QUANTITYPERUNIT%type,
 
32
        VUNITPRICE IN PRODUCTS.UNITPRICE%type,
 
33
        VUNITSINSTOCK IN PRODUCTS.UNITSINSTOCK%type,
 
34
        VUNITSONORDER IN PRODUCTS.UNITSONORDER%type,
 
35
        VREORDERLEVEL IN PRODUCTS.REORDERLEVEL%type,
 
36
        VDISCONTINUED IN PRODUCTS.DISCONTINUED%type
 
37
)
 
38
IS
 
39
BEGIN
 
40
        UPDATE PRODUCTS
 
41
        SET
 
42
                PRODUCTID       = VPRODUCTID,
 
43
                PRODUCTNAME     = VPRODUCTNAME,
 
44
                SUPPLIERID      = VSUPPLIERID,
 
45
                CATEGORYID      = VCATEGORYID,
 
46
                QUANTITYPERUNIT = VQUANTITYPERUNIT,
 
47
                UNITPRICE       = VUNITPRICE,
 
48
                UNITSINSTOCK    = VUNITSINSTOCK,
 
49
                UNITSONORDER    = VUNITSONORDER,
 
50
                REORDERLEVEL    = VREORDERLEVEL,
 
51
                DISCONTINUED    = VDISCONTINUED
 
52
        WHERE
 
53
                PRODUCTID = VPRODUCTID
 
54
;
 
55
        
 
56
        
 
57
END;
 
58
/
 
59
 
 
60
 
 
61
 
 
62
CREATE OR REPLACE PROCEDURE PRODUCTSINSERT
 
63
(
 
64
        VPRODUCTID IN PRODUCTS.PRODUCTID%type,
 
65
        VPRODUCTNAME IN PRODUCTS.PRODUCTNAME%type,
 
66
        VSUPPLIERID IN PRODUCTS.SUPPLIERID%type,
 
67
        VCATEGORYID IN PRODUCTS.CATEGORYID%type,
 
68
        VQUANTITYPERUNIT IN PRODUCTS.QUANTITYPERUNIT%type,
 
69
        VUNITPRICE IN PRODUCTS.UNITPRICE%type,
 
70
        VUNITSINSTOCK IN PRODUCTS.UNITSINSTOCK%type,
 
71
        VUNITSONORDER IN PRODUCTS.UNITSONORDER%type,
 
72
        VREORDERLEVEL IN PRODUCTS.REORDERLEVEL%type,
 
73
        VDISCONTINUED IN PRODUCTS.DISCONTINUED%type
 
74
)
 
75
IS
 
76
BEGIN
 
77
 
 
78
 
 
79
        INSERT
 
80
        INTO PRODUCTS
 
81
        (
 
82
                PRODUCTID,
 
83
                PRODUCTNAME,
 
84
                SUPPLIERID,
 
85
                CATEGORYID,
 
86
                QUANTITYPERUNIT,
 
87
                UNITPRICE,
 
88
                UNITSINSTOCK,
 
89
                UNITSONORDER,
 
90
                REORDERLEVEL,
 
91
                DISCONTINUED
 
92
        )
 
93
        VALUES
 
94
        (
 
95
                VPRODUCTID,
 
96
                VPRODUCTNAME,
 
97
                VSUPPLIERID,
 
98
                VCATEGORYID,
 
99
                VQUANTITYPERUNIT,
 
100
                VUNITPRICE,
 
101
                VUNITSINSTOCK,
 
102
                VUNITSONORDER,
 
103
                VREORDERLEVEL,
 
104
                VDISCONTINUED
 
105
        );
 
106
END;
 
107
/
 
108
 
 
109
CREATE OR REPLACE PROCEDURE PRODUCTSDELETE
 
110
(
 
111
        VPRODUCTID IN PRODUCTS.PRODUCTID%type
 
112
)
 
113
IS
 
114
BEGIN
 
115
 
 
116
        DELETE  FROM PRODUCTS
 
117
        WHERE
 
118
                PRODUCTID = VPRODUCTID;
 
119
 
 
120
END;
 
121
/