323xdZbz

1-------------------------------------------------------------------------------------------------------------------------------------
2EX 5
3-------------------------------------------------------------------------------------------------------------------------------------
4static void Insertexample()
5            // inserts a new record in workplace
6        {
7             
8            string cprstring;
9            string companystring;
10            Console.WriteLine("Indtast cpr-number");
11            cprstring = Console.ReadLine();
12            Console.WriteLine("Indtast company-number");
13            companystring = Console.ReadLine();
14 
15            string sql;
16            sql = "insert into workplace values ('"+ cprstring +  "'," + companystring + ")";
17            //OracleCommand cmd = new OracleCommand();
18 
19            DbCommand cmd = dbFactory.CreateCommand();
20            cmd.Connection = con;
21            cmd.CommandText = sql;
22 
23            // lćs tabellen person
24            try
25            {   // ĺbn forbindelse til database
26                con.Open();
27                cmd.ExecuteNonQuery();
28                Console.WriteLine("The insert is done");
29            }
30            catch (OracleException ex)
31            {
32 
33                if (ex.Message.Contains("ORA-02291: integrity constraint (SYSTEM.CPRFOREIGN)"))
34                    Console.WriteLine("The cpr number does not exits");
35                if (ex.Message.Contains("ORA-02291: integrity constraint (SYSTEM.COMPANYFOREIGN)"))
36                    Console.WriteLine("The company number does not exits");
37                if(ex.Message.Contains("ORA-00001"))
38                    Console.WriteLine("Person already exists in company");
39                       
40            }
41 
42            finally
43            {   // luk forbindelse til databasen
44                con.Close();
45            }
46 
47            Console.WriteLine();
48            Console.WriteLine("---------------------------------------------------------------------");
49        }
50-------------------------------------------------------------------------------------------------------------------------------------
51ex 9
52-------------------------------------------------------------------------------------------------------------------------------------
53create or replace trigger delaccount
54after delete on account
55for each row
56begin
57if:old.balance >0 then
58raise_application_error(-20001, 'You still have cash buddy');
59rollback;
60end if;
61end;
62 
63delete from account where accountno =1;
64insert into account values(5,1515151515, 0);
65-------------------------------------------------------------------------------------------------------------------------------------
66EX 13
67-------------------------------------------------------------------------------------------------------------------------------------
68-- Type and table
69CREATE OR REPLACE TYPE myarrtype IS VARRAY(5) OF int;
70CREATE TABLE student
71(
72studentno int,
73name VARCHAR(30),
74grades myarrtype
75);
76 
77-- Insert
78DECLARE
79arr1 myarrtype;
80arr2 myarrtype;
81BEGIN
82  arr1 := myarrtype(11, 13, 9, 10, 13);
83  arr2 := myarrtype(5, 3, 7, 6, 6);
84  INSERT INTO student VALUES (1, 'Clever Boy', arr1);
85  INSERT INTO student VALUES (2, 'Fool', arr2);
86END;
87 
88-- Procedure
89CREATE OR REPLACE PROCEDURE gradeSum(id IN int)
90AS
91myGrades myarrtype;
92myName VARCHAR(30);
93sumOfGrades int;
94BEGIN
95  sumOfGrades := 0;
96  SELECT name, grades INTO myName, myGrades FROM student WHERE STUDENTNO=id;
97  FOR i IN 1..myGrades.COUNT LOOP
98    sumOfGrades := sumOfGrades + myGrades(i);
99  END LOOP;
100  DBMS_OUTPUT.put_line('Name is ' || myName);
101  DBMS_OUTPUT.put_line('Sum of grades ' || sumOfGrades);
102END;
103 
104-- Run procedure
105BEGIN
106gradeSum(1);
107END;
108-------------------------------------------------------------------------------------------------------------------------------------
109EX 15
110-------------------------------------------------------------------------------------------------------------------------------------
111select * from (
112          select name,
113                 salary,
114                 dense_rank() over (order by salary desc) as rnk
115          from person)
116    where rnk <= 3 and rownum<=3;