Database management using mysql (advance) Part 2

STORE PROCEDURE / FUNCTION

Store store procedure and function is a new facility of MySQL version 5.0.Stroe procedure is a set of SQL stored into a MySQL server. The advantage of using store procedure, the MySQL client does not need to write SQL commands to the server but only have to call the procedure that has been stored on the server (if available). The difference between procedures and functions in MySQL is almost similar to the procedures and functions in programming languages. Function returns a scalar value and can be called in the statement of another procedure or function. Procedure is called via the CALL command and can return values through output variables.
How to create a procedure or function through several steps, among others:
• Create a database in advance, because the store procedures and functions stored in the database rather than in the DBMS.
• Or if the database was made before, entered into the database in question.
• Perform command <karakter> delimiter. Example delimiter |, this step would change the character of the stops on the MySQL server; be |. Create Create Procedure and Function
Before making procedure or function first make sure that you have entered into a database (use nama_database). The general form of the create command procedures and functions are


Create procedure
<nama_procedure>
(Parameters)
<karakteristik procedure>
<badan program>
Create function
<nama_procedure>
(Parameters)
<karakteristik function>
<data data return>
<badan program>
Where:
Parameters consist of the type parameter = [IN, OUT, or INOUT], the parameter name and data type parameters.
Characteristics = consists of the SQL language, comments, etc..
The data type = data type that can be restored are all valid data types diMySQL.
Program = all valid SQL syntax procedure.

There are three types of parameters: IN means the variable serves only as an input parameter, OUT means the variable parameter serves as a place to store the output value of the procedure, and means INOUT parameter serves as the input variables and output value storage procedures.
Example procedure:

mysql> delimiter | [Enter]
mysql> create procedure simple (out param1 int)
begin
select count (*) into param1 from t;
end | [Enter]
mysql> call simple (@ a) | [Enter]
mysql> select @ a | [Enter]

Notice in the example above, a simple procedure that has a function to calculate the number of records in table t. Then the number of columns is inserted into the variable param1.
select count (*) into param1 from t;
Then there is executed before the create procedure command delimiter. Which replaces the character stops MySQL from; be |. The result of MySQL will ignore character, and regard it as a regular character. Such as command delimiter is not present, then the instance where there is a character row ke3di; end statement. MySQL will assume the create procedure command line ended there. End to bottom is not part of the agency program procedures. As a result, create a command procedure . in 6th row, a procedure that has been created called by the function call. Do not forget to provide parameters as required by the procedure
simple. In the last line, the variable parameters to store the output of the procedure is shown.
Why is the variable parameter for the procedure above using additional characters @? This is so variable parameters can be applied to select the function. If the variable parameters of all replaced without the @, then select a command | does not display any results. Example function:

mysql> create function hello (s char (20))
returns char (50)
return concat ('hello,', s ,'!'); |
mysql> select hello ('world') |

The results of the above function is an output containing the word "hello world". Such a program functions such as program procedure above,

create function simple2 (a int)
returns an int
begin
select count (*) into a from customers;
return a;
end |

MySQL will issue an error message as follows:

ERROR 1298 (0A000): Statements like SELECT, INSERT, UPDATE (and others) are
not allowed in a FUNCTION

Which means command select, insert, and update the program is invalid on line function. Besides, the parameters (IN, OUT, INOUT) is also invalid in function.Itulah extra bit of difference in procedures and functions that have not been mentioned above.



STORE PROCEDURE AND TRIGGER

We have already learned about the store procedure in the DBMS MySQL, but MySQL does not yet support triggers facilities. Trigger is usually used together with the store procedure / function. Store procedure in Postgre diMySQL.Store slightly different procedure or a function we call it in Postgre divided into 3 sections
1. SQL functions, functions which contain only the SQL language.
2. Procedure Language Functions, Functions are built using a procedural language. Postgre
supports several procedural languages such as PL / pgsql, PL / TCL, PL / Perl, and PL / Python.
3. C functions, functions that are built with the C language as a shared object.
SQL Functions
SQL function is a function composed of the SQL language. In the unloaded program other than SQL commands. Line program does not begin with end begin. An example follows:

create function decrease_ipk (integer, real)
real returns
as' update student set GPA = GPA $
2 where nim = $ 1;
SELECT GPA from student where nim = $ 1; '
language sql;

Above function requires two input parameters of each of type integer and real. The function also returns a value of type real. Different input parameters to the function in MySQL, do not require a variable name. To access the parameters used character $ followed by the number. so, the above function to make cuts GPA of students who use nim as with the first input parameter with the second parameter numbers. Examples of the use of the above function is
select decrease_ipk (9107,0.03);
The result is a column with a new GPA of students is what returned 9107.Nilai by function. Thus the value must be of type real, according to the declaration returns.see the function above! Although the declaration has returns, but there is no implicit definition of what direturn value. Actually, the function will return the results from SELECT command line results ke4.Apabila select more than one column, then the declaration also returns more than one variable with data type is adjusted to the value that will be issued. Functions PL / SQL there is little difference with the procedure that we have learned in MySQL.PL more similar to the procedure in procedural programming languages. In defaultPL / pgsql is not supported by each database created in Postgre. Therefore we must install it first. Please remember, PL / pgsql apply in the
database rather than in the DBMS Postgre.Cara to install the PL / pgsql by typing commands on the Linux console
createlang-U postgres plpgsql academic U
means that the username that does the installation command. This username must be the owner of the database in question. Academic is a database that will be installed PL / pgsql. To view the status of the installation if it is successfully able to type the following command at the Linux console:
createlang-l academic
Example of a procedure is as follows:
create function increase of (integer)
returns integer as
'Declare an integer: = 4;
begin
return $ 1 + value;
end; '
language 'plpgsql';
The general form sql least equal to the function. The difference in body program begins with begin and end with end. Also at the agency there are functions return the program also to define the value returned by the function. caller examples above function as follows:
SELECT increase of (5);
The result is a value of 9. Other examples of variable declarations in PL / pgsql as follows:
nim char (8) NOT NULL;
url varchar: =''http://www.sun.com'';
default integer value 6;
user_id constant integer: = 5;
In the procedural language variable force on the block where the variable is declared. For more details see the following example:
create function block ()
returns integer as
'Declare an integer value: = 1;
begin
raise notice''variable value =%'', value;
value: = 2;
declare the value of integer: = 3;
begin
raise notice''variable value =%'', value;
end;
raise notice''variable value =%'', value;
return value;
end; '
language 'plpgsql';
how to block calls is select (). The result will appear notice as follows:
NOTICE: variable value = 1
NOTICE: variable value = 3
NOTICE: variable value = 2
Note the above program body function. Function returns a value. Then declare statements not included in the scope begin and end. Block courses are marked with declare and begin ... end. Function at the top has 2 blocks of the program. Variables declared in a particular block only applies to block it. In line notice sign% will be replaced with value. This is the same as the printf command that includes the variables in C.
Trigger
Trigger is usually used with procedure. Because if the procedure will be used must be called using ordinary SELECT function. But the procedure can also be invoked when a table has a data manipulation. The function of the trigger is:
1. Prevent the insertion of data errors.
2. Prevent deletion of data errors.
3. Preventing errors updating the data.
Trigger keep the table from data manipulation. In case of data manipulation, the trigger will execute the procedure that already exists. Following example, we have a procedure
cek_nim function create ()
returns opaque as
'Begin
if new.nim! =''^ [09] * $
''Then
NIM raise exception''must''numbers only;
end if;
return new;
end; '
language 'plpgsql';
Then we create a trigger that applied for student table
create trigger trigger_cek_nim
before insert or update
on student
for each row
cek_nim procedure execute ();
Explanation of the above function as follows:
Function does not accept input parameters. Then the function will check the table nim whether new columns contain numeric characters only. This Statement
"^ [09] * $
"Is a regular ekspresion which means all characters including only number, sign "! = "Means not equal. If the conditions are met so exception displayed.
In the event of an insertion into the student table as follows:
insert into student values
('990F ',' Andy M ', 18, 0.0),
('9019 ',' Great N ', 19, 3.3);
So Postgre will create a temporary table named new with columns of the same name as yours student table. The table can only be filled by one record of data. Then before the new table data is inserted, the trigger calls the function cek_nim (). Function cek_nim nim validation check is entered. Apparently for the first record containing neem character, then the new table can not be in put in the student table. Because there are words on statement trigger FOR each ROW, then the second record into tablenew. And so on until all the records examined by cek_nim function ().

View
View used pasting the results of a query into a particular name. Usually this is necessary if we want to save a query that (fairly) complex. To access a view, we cukop call the view that the SELECT.
The command to create a view is as follows:

CREATE [OR REPLACE] VIEW name [(column_name [, ....])] AS query

To remove a view using the DROP VIEW command name of View