useful-qry/function-in-db/procedures/conv_eyetest_sorder.sql

drop procedure if exists conv_eyetest_sorder;

DELIMITER //
create procedure conv_eyetest_sorder()

SQL SECURITY INVOKER
BEGIN

-- Declare _json1 text;

Declare _line_id int;
Declare _not_found boolean;

Declare _c1 varchar(64); Declare _c2 varchar(64); Declare _c3 varchar(64); Declare _c4 varchar(64); Declare _c5 varchar(64);
Declare _c6 varchar(64); Declare _c7 varchar(64); Declare _c8 varchar(64); Declare _c9 varchar(64); Declare _c10 varchar(64);
Declare _c11 varchar(64); Declare _c12 varchar(64); Declare _c13 varchar(64); Declare _c14 varchar(64); Declare _c15 varchar(64);
Declare _c16 varchar(64); Declare _c17 varchar(64); Declare _c18 varchar(64); Declare _c19 varchar(64); Declare _c20 varchar(64);
Declare _c21 varchar(64); Declare _c22 varchar(64); Declare _c23 varchar(64); Declare _c24 varchar(64); Declare _c25 varchar(64);

Declare _line_cur_eyetest cursor for 
    select 
		c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25
	from zdb_eyetest
	-- where c1 = '2'
	order by c1 asc;

Declare continue handler for 
        not found set _not_found = true;

set _not_found = false;  -- 이것이 Multi cursor를 쓰는 핵심이다.
open _line_cur_eyetest;
	loop_eyetest: loop
		fetch _line_cur_eyetest into 
			_c1, _c2, _c3, _c4, _c5, _c6, _c7, _c8, _c9, _c10, _c11, _c12, _c13, _c14, _c15, _c16, _c17, _c18, _c19, _c20,
			_c21, _c22, _c23, _c24, _c25 ;
		if _not_found then leave loop_eyetest; end if;

		SET @json1 = '{
			"REyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "",
				"BaseUd": "",
				"BareEye": "",
				"Adjust": ""
			},
			"LEyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "",
				"BaseUd": "",
				"BareEye": "",
				"Adjust": ""
			},
			"RContLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"Add": "",
				"Bc": "",
				"Dia": "",
				"Kerato": "",
				"Prescript": "",
				"Adjust": ""
			},
			"LContLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"Add": "",
				"Bc": "",
				"Dia": "",
				"Kerato": "",
				"Prescript": "",
				"Adjust": ""
			},
			"Symptoms": "",
			"OldREyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "/",
				"BaseUd": "/",
				"BareEye": "",
				"Adjust": ""
			},
			"OldLEyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "/",
				"BaseUd": "/",
				"BareEye": "",
				"Adjust": ""
			},
			"CcREyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "/",
				"BaseUd": "/",
				"BareEye": "",
				"Adjust": ""
			},
			"CcLEyeLens": {
				"Sph": "",
				"Cyl": "",
				"Axis": "",
				"LongPd": "",
				"Add": "",
				"ShortPd": "",
				"BaseIo": "/",
				"BaseUd": "/",
				"BareEye": "",
				"Adjust": ""
			},
			"FixEye": 0,
			"Stereosia": 0,
			"ColorVision": 0,
			"OdImGrid": 0,
			"OsImGrid": 0,
			"Screen": {
				"Shielding": {
				"Long": "",
				"Short": ""
				},
				"FourDot": {
				"Long": 0,
				"Short": 0,
				"LongStr": "",
				"ShortStr": ""
				},
				"Converge": {
				"Normal": 0,
				"Str": ""
				},
				"Near": {
				"OdStr": "",
				"OsStr": ""
				},
				"Easy": {
				"OuStr": "",
				"OdStr": "",
				"OsStr": ""
				}
			},
			"BroadH": {
				"X": "[]",
				"Y": "[]",
				"Drage": "[]"
			},
			"Bincular": {
				"Location": {
				"LongHori": "",
				"LongVert": "",
				"ShortHori": "",
				"ShortVert": ""
				},
				"Relative": {
				"Pra": "",
				"Nra": ""
				},
				"Lag": 0,
				"Aca": "",
				"Fusion": {
				"LongBo": {
					"Clo": "",
					"Dis": "",
					"Rec": ""
				},
				"LongBi": {
					"Clo": "",
					"Dis": "",
					"Rec": ""
				},
				"LongOdBd": {
					"Dis": "",
					"Rec": ""
				},
				"LongOdBu": {
					"Dis": "",
					"Rec": ""
				},
				"LongOsBd": {
					"Dis": "",
					"Rec": ""
				},
				"LongOsBu": {
					"Dis": "",
					"Rec": ""
				},
				"ShortBo": {
					"Clo": "",
					"Dis": "",
					"Rec": ""
				},
				"ShortBi": {
					"Clo": "",
					"Dis": "",
					"Rec": ""
				},
				"ShortOdBd": {
					"Dis": "",
					"Rec": ""
				},
				"ShortOdBu": {
					"Dis": "",
					"Rec": ""
				},
				"ShortOsBd": {
					"Dis": "",
					"Rec": ""
				},
				"ShortOsBu": {
					"Dis": "",
					"Rec": ""
				}
				}
			},
			"Pameter": {
				"Oh": {
				"R": "",
				"L": ""
				},
				"Pd": {
				"R": "",
				"L": ""
				},
				"Distance": {
				"R": "",
				"L": ""
				},
				"Inset": {
				"R": "",
				"L": ""
				},
				"Face": "",
				"Tilt": "",
				"Length": ""
			},
			"Payment": {
				"CardCompany": "",
				"CashReceipt": "",
				"GiftCard": ""
			}
		}';

		update dbr_sorder set json1 =  
			json_replace(@json1, 
				'$.REyeLens.Sph', _c2, 
				'$.REyeLens.Cyl', _c3,  
				'$.REyeLens.Axis', _c4,  
				'$.REyeLens.LongPd', _c5,  
				'$.REyeLens.Add', _c6,  
				'$.REyeLens.ShortPd', _c7,  

				'$.LEyeLens.Sph', _c8,  
				'$.LEyeLens.Cyl', _c9,  
				'$.LEyeLens.Axis', _c10,  
				'$.LEyeLens.LongPd', _c11,  
				'$.LEyeLens.Add', _c12,  
				'$.LEyeLens.ShortPd', _c13,  

				'$.RContLens.Sph', _c14,  
				'$.RContLens.Cyl', _c15,  
				'$.RContLens.Axis', _c16,  
				'$.RContLens.Add', _c17,  
				'$.RContLens.Bc', _c18,  
				'$.RContLens.Dia', _c19,  

				'$.LContLens.Sph', _c20,  
				'$.LContLens.Cyl', _c21,  
				'$.LContLens.Axis', _c22,  
				'$.LContLens.Add', _c23,  
				'$.LContLens.Bc', _c24,  
				'$.LContLens.Dia', _c25
			)
		where id = _c1;

	end loop loop_eyetest;
close _line_cur_eyetest;

END;
//
DELIMITER ;
call conv_eyetest_sorder();