{"id":403,"date":"2025-05-09T12:40:05","date_gmt":"2025-05-09T10:40:05","guid":{"rendered":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/?page_id=403"},"modified":"2025-05-09T12:40:05","modified_gmt":"2025-05-09T10:40:05","slug":"sql-protseduur","status":"publish","type":"page","link":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/sql-protseduur\/","title":{"rendered":"SQL protseduur"},"content":{"rendered":"\n<p><strong>Nagu funktsioonid programmeerimise keeles<\/strong><br>salvestatud protseduurid (Store procedure)<br>tegevused, mis saab automaatselt teha (INSERT INTO, ALTER TABLE, SELECT, DELETE, UPDATE)<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE linnad(\n\tlinnID int PRIMARY KEY identity (1,1),\n\tlinnNimi varchar(30) unique,\n\telanikeArv int not null,\n\tmaakond varchar(25)\n\t);<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>\t--Protseduur, mis lisab (INSERT) tabelisse andmed &amp;\n\t-- kohe n\u00e4itab (SELECT) tabeli\nCREATE PROCEDURE lisaLinn\n\t@linnNimi varchar(30),\n\t@elanikeArv int,\n\t@maakond varchar(25)\nAS\nBEGIN\n\tINSERT INTO linnad(linnNimi,elanikeArv,maakond)\n\tVALUES (@linnNimi, @elanikeArv, @maakond);\n\tSELECT * FROM linnad;\nEND\n\n-- kutse\nExec lisaLinn 'Tallinn', 650000, 'Harju';\nExec lisaLinn 'Tartu', 20000, 'Tartu';\nExec lisaLinn 'P\u00e4rnu', 30000, 'P\u00e4rnu';\nExec lisaLinn 'Keila', 1000, 'Harju';\n\n-- protseduuri kustutamine\nDROP Procedure lisaLinn;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- prtotseduur, mis kustutab tabelist linnID j\u00e4rgi\nCREATE PROCEDURE kustutaLinn\n@id int\nAS\nBEGIN\nSELECT * FROM linnad;\nDELETE FROM linnad where linnID=@id;\nSELECT * FROM linnad;\nEND\n\n-- kutse \nEXEC kustutaLinn 4;\nEXEC kustutaLinn @id=4;\n\n-- protseduur mis uuendab tabeli ja suurendab elanike arv 10%\nCREATE Procedure uuenaLinn\n@arv decimal(5,2)\nAS\nBEGIN\nSELECT * FROM linnad;\nUPDATE linnad SET elanikeArv=elanikeArv*@arv;\nSELECT * FROM linnad;\nEND\n\n--kutse\nEXEC uuenaLinn 0.05;\nDROP Procedure uuenaLinn;\nUPDATE linnad SET elanikeArv=20000 WHERE linnID=2;\nSELECT * FROM linnad;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image.png?w=270\" alt=\"\" class=\"wp-image-191\"\/><\/figure>\n\n\n\n<ol class=\"wp-block-list\">\n<li>LisaLinn<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-1.png?w=966\" alt=\"\" class=\"wp-image-192\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-2.png?w=310\" alt=\"\" class=\"wp-image-193\"\/><\/figure>\n\n\n\n<ol class=\"wp-block-list\">\n<li>kustutaLinn<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-3.png?w=867\" alt=\"\" class=\"wp-image-195\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-4.png?w=319\" alt=\"\" class=\"wp-image-197\"\/><\/figure>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>uuenaLinn<\/strong><\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-5.png?w=894\" alt=\"\" class=\"wp-image-198\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/jaramirgutsuljak.wordpress.com\/wp-content\/uploads\/2025\/04\/image-6.png?w=380\" alt=\"\" class=\"wp-image-200\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Nagu funktsioonid programmeerimise keelessalvestatud protseduurid (Store procedure)tegevused, mis saab automaatselt teha (INSERT INTO, ALTER TABLE, SELECT, DELETE, UPDATE)<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-403","page","type-page","status-publish","hentry"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/pages\/403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/comments?post=403"}],"version-history":[{"count":1,"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/pages\/403\/revisions"}],"predecessor-version":[{"id":404,"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/pages\/403\/revisions\/404"}],"wp:attachment":[{"href":"https:\/\/jaramirgutsuljak23.thkit.ee\/wp\/wp-json\/wp\/v2\/media?parent=403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}