저장 프로시져, 저장 함수, 트리거 예제 모음 2
[간단한 저장 프로시져]
delimiter//
drop procedure if exists helloworld//
create procedure helloworld()
begin
select 'Hello World";
end
//
delimiter;
mysql> call helloworld();
|
[저장 프로시져 안에 변수들]
delimiter//
drop procedure if exists variable_demo()
create procedure variable_demo()
begin
declare my_integer int;
declare my_big_integer bigin;
declare my_currency numeric(8,2);
declare my_pi float default 3.1415926;
declare my_text text;
declare my_dob date default '1960-06-21';
declare my_varchar varchar(30) default 'hello world!';
set my_integer = 20;
set my_big_ineger = power(my_integer,3);
end
//
delimiter;
mysql> call my_sqrt(12);
|
[저장 프로시져에 파라미터]
delimiter//
drop procedure if exists my_sqrt//
create procedure my_sqrt(input_number int)
begin
declare l_sqrt float;
set l_sqrt=sqrt(input_number);
select l_sqrt;
end
//
delimiter;
MySQL에서 파라미터는 IN, OUT, INOUT을 정의할 수 있다.
IN : read-only
INOUT : read-write
OUT : write-only
|
[저장 프로시져에 OUT 파라미터 사용]
delimiter//
drop procedure if exists my_sqrt//
create procedure my_sqrt(input_number int, out out_number float)
begin
set out_number=sqrt(input_number);
end
//
delimiter;
mysql> call my_sqrt(12,@out_value);
mysql> select @out_value;
|
[IF문으로 조건 실행]
delimiter//
drop procedure if exist discounted_price//
create procedure discounted_price(normal_price numeric(8,2), out discount_price numeric(8,2))
begin
if(normal_price>500) then
set discount_price=normal_price*.8;
elseif (normal_price>100) then
set discount_price=normal_price*.9;
else
set discount_price=normal_price;
end if;
end
//
delimiter;
mysql> call discounted_price(300,@new_price);
mysql> select @new_price;
|
[저장 프로시져 안에 간단한 루프]
delimiter//
drop procedure if exists simple_loop//
create procedure simple_loop()
begin
declare counter int default 0;
my_simple_loop:loop
set counter=counter+1;
if count=10 then
leave my_simple_loop;
end if;
end loop my_simple_loop;
select 'I can count to 10';
end
//
delimiter;
|
[select into 구문이 인베디드된 저장 프로시져]
delimiter//
drop procedure if exists customer_sales//
create procedure customer_sales(in_customer_id int)
reads sql data
begin
declare total_sales numeric(8,2);
select sum(sale_value)
into total_sales
from sales
where customer_id=in_customer_id;
select concat('Total sales for',in_customer_id,'is',toal_sales);
end;
//
delimiter;
mysql> call customer_sales(2);
|
[ 커서를 사용하는 저장 프로시져]
delimiter//
drop procedure if exists cursor_example//
create procedure cursor_example()
reads sql data
begin
declare l_employee_id int;
declare l_salary numeric(8,2);
declare l_department_id int;
declare done int default 0;
declare cur1 cursor for
select employee_id, salary, department_id from employees;
declare continue handler for not found set done=1;
open cur1;
emp_loop:loop
fetch cur1 into l_employee_id, l_salary, l_department_id;
if done=1 then
leave emp_loop;
end if;
end loop emp_loop;
close cur1;
end;
//
delimiter;
|
[저장 프로시져에 무한한 SELECT 구문]
delimiter//
drop procedure if exists sp_emps_in_dept//
create procdeure sp_emps_in_dept(in_employee_id int)
begin
select employee_id,sumame,firstname,address1,address2,zipcode.date_of_birth
from employees
where department_id=in_employee_id
end
//
delimiter;
|
[인베디드 UPDATE와 저장 프로시져]
delimiter//
drop procedure if exists sp_update_salary//
create procedure sp_update_salary
in_employee_id int,
in_new_salary numeric(8,2))
begin
if in_new_salary < 5000 or in_new_salary > 500000 then
select 'illegal salary; salary must be between $5,000 and $500,000';
else
update employees
set salary=in_new_salary
where employee_id=in_employee_id;
end if;
end
//
delimiter;
|
[저장 프로시져에서 또 다른 저장 프로지셔 호출]
delimiter//
drop procedure if exists call_example//
create procedure call_example(employee_id int, employee_type varchar(20))
no sql
begin
declare l_bonus_amount numeric(8,2);
if employee_type='MANAGER' then
call calc_mamager_bonus(employee_id,l_bonus_amount);
else
call calc_minion_bonus(employee_id,l_bonus_amount);
end if;
call grant_bonus(employee_id,l_bonus_amount);
end;
//
delimiter;
|
[더 복잡한 저장 프로시져]
create procedure putting_it_all_togeter(in_department_id int)
modifies sql data
begin
declare l_employee_id int;
declare l_salary numeric(8,2);
declare l_department_id int;
declare l_new_salary numeric(8,2);
declare done int default 0;
declare cur1 cursor for
select employee_id, salary, department_id
from employees where department_id=in_department_id;
declare continue handler for not found set done=1;
create temporary table if not exists emp_raises(employee_id int, department_id int, new_salary numeric(8,2));
open cur1;
emp_loop:loop
fetch cur1 into l_employee_id, l_salary, l_department_id;
if done=1 then
leave emp_loop;
end if;
call new_salary(l_employee_id,l_new_salary);
if(l_new_salary<>l_salary) then
update employees
set salary=l_new_salary
where employee_id=l_employee_id;
insert into emp_raises(employee_id,department_id,new_salary)
values(l_employee_id,l_department_id,l_new_salary);
end if;
end loop emp_loop;
close cur1;
select employee_id, department_id,new_salary from emp_raises
order by employee_id;
end;
mysql> call cursor_examples2(18)//
|
[저장 함수]
delimiter//
drop function if exists f_discount_price//
create function f_discount_price
normal_price numeric(8,2))
returns numeric(8,2)
eterministic
begin
declare discount_price numeric(8,2);
if(normal_price>500) then
set discount_price=normal_price*.8;
elseif(normal_price>100) then
set discount_price=normal_price*.9;
else
set discount_price=normal_price;
end if;
return(discount_price);
end
//
delimister;
mysql> select f_discount_price(300);
|
[데이터베이스 트리거]
delimiter//
drop trigger sales_bi_trg//
create trigger sales_bi_trg
before insert on sales
for each row
begin
if new.sale_value > 500 then
set new.gree_shipping='Y';
else
set new.gree_shipping='N';
end if;
if nuew.sale_value > 1000 then
set new.discount=new.sale_value*.15;
else
set new.discount=0;
end if;
end
//
delimiter;
mysql> insert into sales(customer_id, product_id, sale_date, quantity, sale_value, department_id, sales_rep_id)
-> values(20,10,now(),20,10034,4,12);
mysql> select sale_value, free_shipping, discount from sales where sales_id=2500003;
|
[PHP에서 불려지는 저장 프로시져]
delimiter//
drop procedure if exists employee_list//
create procedure employee_list(in_dept_id int)
reads sql data
begin
select employee_id, surname, firstname from employees where department_id=in_dept_id;
end
//
delimiter;
|
[PHP 프로그램에서 저장 프로시져 호출]
<html>
<head>
<title>Employee listing</title>
<head>
<body>
<h1>Employee listing<h1>
<form method="post">
<p>Enter Department ID:
<input type="text" name="dept_id" size="4">
<input type="submit" name="submit" value="submit"><p>
</form>
<? php
$hostname = "localhost";
$username = "root";
$password = "암호"
$database = "prod";
if (isset ($_post['submit'])){
$dbh = new mysqli($hostname, $username, $password, $database);
if(mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$dept_id = $_post['dept_id'];
if ($result_set = $dbh->query("call employee_list($dept_id)")){
print('Mtable border="1" width="30%"><tr>'.
'<td>Employee_id</td><td>Surname</td>%s</td><td>%s</td></tr>\n",
$row->employee_id, $row->surname, $row->firstname);
}
} else {
printf("<p>Error:%d (%s\n", mysqli_errno($dbh),
mysqli_sqlstate($dbh), mysqli_error($dbh));
}
print("</table>")'
$dbh->close();
}
?>
</body>
</html>
|
운영자 : 김두형(www.sunmysql.co.kr)
email : beansoft@nate.com
업데이트 : 20080819
