MySQL 저장 프로시져, 저장 함수, 트리거 예제 모음 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>