Scribd
Upload
15 views

SQL Level2

Uploaded by

hamzatourmine9
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
15 views

SQL Level2

Uploaded by

hamzatourmine9
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
You are on page 1/ 6

Procedures

Stored procedures are used to group one or more transact-sql-tatemants (insert select …)

 WHEN I want repet the tasck

 Stored è it will sord into db and it wil be a one of object that made thes db

 For create a procedure we use the syntax


Create procedure procedure_name
As
Begin
//logic
End
Expamle èSQL SERVER
Create procedure selectData
As
BEGEN
Select * from users
END
EXAMPLE è MYSQL
DELIMITER $$

CREATE PROCEDURE SHOWDATA()


BEGIN
SELECT * FROM `USER`;
END $$

DELIMITER ;
SQL SERVER
Thes procedure are stocked in the file hase name
1èprogramabilty
2èsotored procedure
MYSQL ROUTINESS
èwhen I want to excute or call the procedure I use
 Mysql : call showdata()
 Sql server :excute showsata
Stored procedure paramerts
DELEMITER $$
Create procedure procedure_name( @var_name var_type)
BEGEN
//logec
END $$
DELEMITER ;
èreturn value ====> OUTPUT or OUT
è IN

Function (video number 3 …)


1. Function are return a values
2. We can use functions insade of select satatment

Built in functions :
1. String
functions(upper(),concat(),lower(),substring(),charindex()
,len(),replace()..)
2. Date function
(current_timestamp()datename(),year(),dateadd(),dateYe
ar(),date(),datedif()…)
3. Numeric function (min(),sqrt(),max(),avg()…)
4. System function

User defined function :


Create function function_name(par type,par1 type)
Returns data_type
Begen
//logic
Return some_values
End
example
DELIMITER //
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
BEGIN
DECLARE result INT;
SET result = a + b;
RETURN result;
END;
//
DELIMITER ;

We can call a user defined function


SELECT my_function(parameter1, parameter2, ...);

Table Variables & Table Valued function Video7:

Trigger
I. DML trigger
 Excute auto matique
Create trigger trigger_name
(After |Before) (insert|Update|DELETE)
On table_name for Each ROW
BEGEN

--variable declarations
--trigger code
END

//BEFOREè the trigger it will run BEFORE events [insert,update,delete]


// AFTER(‫ )بعد‬è the trigger it will run After events [insert,update,delete]
// instead of è

Raiserror(‘message’16,1)

View ??
Kly premerry : not null – unique

Foreign key :
Create trigger foreign_key

Create trigger foreign_key


before delate on parent_table
for eash row
begen
Declare childCount int ;
Select count(*)
Into childcount
From childtable
Where foreign_key_column = old.id
If childcount >0 then
Set message = ‘can not delate parent because there are related’
End if
end

You might also like