המחברת של יאיר: SQL

ישנם שתי דרכים לעדכון ב PL\SQL Developer ישירות מתוך טבלת התוצאות המוצגת:

קליק ימני על שם הטבלה בחלון ה SQL, בתפריט שייבחר נלחץ על "Edit Data". בחלון שייפתח נוכל לערוך את הנתונים ישירות מהטבלה שתופיע למטה. במידת הצורך ניתן להוסיף לטבלה where לצימצום שורות.
נכתוב "for update" בסוף השאילתא לדוג' -

SELECT * FROM TABLE  for update;

תחביר בסיסי (מקור ויקיפדיה)

DECLARE
 v VARCHAR2(100); /* הגדרת משתנה מסוג VARCHAR2) */
BEGIN
 SELECT 'Hello world' -- שליפת ערך לתוך המשתנה
 INTO v
 FROM dual;

 dbms_output.put_line ( v ); -- הדפסת המשתנה
EXCEPTION 
 when OTHERS then /* טיפול בשגיאות */
 dbms_output.put_line ( 'error: ' || sqlerrm );
END;

או (מקור)

<<label>>   -- this is optional
DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
-- this section is mandatory, must contain at least one executable statement
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
-- this section is optional
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode )  );
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm   );
END;

תנאים
IF...ELSEIF...CASE WHEN
לדוג' (מקור ויקיפדיה)

IF :NEW.FieldName is null
THEN
NEW.FieldName :=Value:
END IF;

CASE Expressions

ביטויי CASE מאפשרים לנו להשתמש בלוגיקה של IF ...THEN ... ELSE בתוך שאילתת SQL,
להלן דוגמא כשהתחביר מודגש:

SELECT name,
   CASE Age

   WHEN Age>100 THEN 'Old'

   WHEN Age<3 THEN 'Baby'
   ELSE 'Hi' 
   END
   FROM customers;

GRANT / REVOKE

GRANT - הענקת הרשאות (privileges)
REVOKE - הורדת הרשאות
הרשאות לטבלה יכולות להיות כל שילוב של SELECT, INSERT, 
UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL
לדוג'


GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_NAME TO user1;

 היררכיות
במקרה של טבלה בה הנתונים מסודרים בצורה היררכית, נבצע שליפה לפי הסדר ההיררכי,
לפי הSyntax הבא -

START WITH כאן נכניס את המצב הראשוני, את שורש ההיררכיה
CONNECT BY PRIOR וכאן את ההקשר בין שורת האב לשורת הילדים (כמו מזהה אב בשורת הבן למשל)
<הסבר לא גמור-צריך דוגמא טובה>

ADD_MONTHS - מקבלת תאריך ומספר ומחזירה את התאריך ומספר החודשים לאחריו
AND and OR are special operators that you can use withWHERE to filter the query on two or more conditions.
AVG() takes a column name as an argument and returns the average value for that column.
ALTER TABLE changes an existing table.
דוגמא להוספת העמוד -
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

הוספת עמודה כמו למעלה תציב עמודה עם תאים ריקים, ניתן להגדיר שהתאים יהיו מלאים כך
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT DEFAULT 'zebra';

AS כינוי טבלה בשם באורקל אין צורך בכך וניתן לכתוב את שם הטבלה רווח ולאחריו הכינוי. במקרה של טבלה נוספת יש להפריד עם פסיק
```
SELECT c.OrderID, c.ProductID, p.ProductName, p.Price
FROM   Price AS c
JOIN   Products AS p ON (c.ProductID = p.ProductID)
```
COUNT takes the name of a column(s) as an argument and counts the number of rows where the value(s) is not NULL.
CONCAT - CONCAT returns char1 concatenated with char2 - for example SELECT CONCAT(CONCAT(last_name, '''s job category is '),job_id) return -
```
Hall's job category is SA_REP
```
CREATE TABLE creates a new table.

Example - CREATE TABLE celebs (name TEXT, age INTEGER);

להגדרת התאים כחייבים להיות מלאים
Example - CREATE TABLE celebs (name TEXT NOT NULL, age INTEGER NOT NULL);

להגדרת התאים כחייבים להיות UNIQUE
Example - CREATE TABLE celebs (name TEXT UNIQUE, age INTEGER NOT NULL);

DECODE - הפונקציה הזו משווה בין שני ערכים ומחזירה תשובה אם שווים, התשובה יכולה להיות כל פונקציה או שכמו בתשובה פשוט ניתן להוסיף אם אינם שווים ,לפי הסינטקס הבא
SELECT DECODE(P1,P2, 'Equal', 'Not Equal')
DELETE FROM deletes rows from a table.
DISTINCT is used to remove duplicates from the result set. The syntax for the DISTINCT clause in Oracle/PLSQL is:
```
SELECT DISTINCT expressions
FROM tables
[WHERE conditions];
```
DATEDIFF - מחזיר פער בין תאריכים, מספר של חודשים/ימים/שנים וכדו' לדוג
SELECT DATEDIFF(day, '2000/08/16', '2022/08/16') AS NumDayDiff;
DATEPART - מחזיר חלק התאריך מתוך העמודה לדוג'
SELECT DATEPART(yyyy, '2019/05/12') AS DateparT; יחזיר את השנה 2019
EXTRACT - מחלץ מחרוזת מתאריך לפי שליפה של חודש או שעה לדוג'
SELECT (EXTRACT(YEAR from עמודת תאריך)) from table
getdate() - מחזיר את ערך התאריך של היום, התאריך הנוכחי 'דומה קצת ל SYSDATE
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
INSERT INTO adds a new row to a table.

Example - INSERT INTO celebs (name, age) VALUES ('Harrison Ford', 76);

INSTR - מציאת מיקום תת מחרוזת בתוך מחרוזת שלמה
INTICAP(string) - מחזירה מחרוזת כשבכל מילה האותיות קטנות וכל האותיות הראשונות גדולות
SELECT queries data from a table.
שליפה ממספר טבלאות תעשה ע"י הפרדה של פסיקים בין עמודה לעמודה מטבלה, כשהייצוג יהיה ע"י שם טבלה נקודה ושם העמודה המבוקשת
UPDATE - עדכון ושינוי נתונים נעשה ייעשה על פי התחביר הבא

Example -

UPDATE table_name

SET column = new_value

WHERE condition/s

LIKE and BETWEEN are special operators that can be used in a WHERE clause

LIKE '%a%' כל מילה שיש באמצע שלה את האות שבין האחוזים
LIKE '_a%' כל מילה שהאות השנייה שלה היא האות הראשונה בא"ב באנגלית

LIMIT - מגביל את כמות השורות המוחזרות
LAST_DAY - מחזירה את היום האחרון בחודש
HAVING במידע אגרגרטיבי נשתמש בו ולא ב WHERE.
LENGTH - אורך תוכן בעמודה --> SELECT LENGTH('Israel') FROM dual;
in SQL Server we use LEN --> SELECT LEN('Israel');
SUBSTR - מחזיר חלק ממחרוזת לפי ציון מיקום
LOWER (string) - UPPERמחזירה מחרוזת כשכל האותיות הגדולות הומרו לקטנות, ההיפך מ
MAX() לוקחת את שם העמודה בסוגריים ומחזירה את הערך המקסימלי, אם ישנם מספר ערכים ובעיקר אם הערכים אינם מספריים כדאי להשתמש בפונקציה Rank כפי שמופיע בהמשך.
MIN() לוקחת את שם העמודה בסוגריים ומחזירה את הערך המינמלי , אם ישנם מספר ערכים ובעיקר אם הערכים אינם מספריים כדאי להשתמש בפונקציה Rank כפי שמופיע בהמשך.
MOD - מחזירה שארית של שני מספרים
MONTHS_BETWEEN - מחזירה מספר החודשים בין שני תאריכים
NEXT_DAY - מקבלת תאריך ויום בשבוע, ומחזירה את התאריך הקרוב של היום בשבוע.
IN - this operator is used when you want to compare a column with more then one value. for example - if you need to select all people from NY,CA,LA instead of using OR you can use IN lie that - WHERE city IN ('NY','CA','LA');
NOT IN - כמו מה שצוין למעלה רק לא.. די ברור
NVL(exp1,exp2) - מקבל שני נתונים האחד עמודה השני, אם הערך בעמודה הוא null מזין במקום את הערך השני
NULLIF (exp1,exp2) - אם הביטויים שווים מציבה null אם שונים מחזירה את הערך הראשון של exp1
ORDER BY lets you sort the results of the query in either ascending or descending order.
default - ASC to sort the records in a descending order, you can use the DESC keyword
טיפ - יצירת סדר לפי שתי עמודות מתבקש למשל בשם פרטי ושם ומשפחה
OFFSET - בעוד שהפקודה שמעל מגבילה את כמות השורות, הפקודה הזו שבאה איתה קובעת אילו שורות יוחזרו. רוצה להביא רק 5 ? תוצאות אבל אילו ? החמש הראשונות, החמש השניות, החמש השלישיות וכן הלאה..
getdate() - מחזיר את ערך התאריך של היום, התאריך הנוכחי 'דומה קצת ל SYSDATE
PRIMARY KEY
את העמודה הזו נגדיר ביצירת הטבלה והיא לא זקוקה לעדכון התאים בה משום שהיא ממספרת אוטומאטית את המזהה של כל שורה .לדוגמא
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT,
grade INTEGER
);
ROUND() takes two arguments, a column name and the number of decimal places to round the values in that column.
RANK - זוהי פונקציה אנליטית שתפקידה לתת לערכים דירוג מספרי מאחד והלאה, הפונקציה תבוא עם סוגריים ובמבנה שלאחריו תבוא הפוקנציה over
SELECT RANK()
OVER ([ עמודה ] order_by_clause).
לדוגמא אם נרצה לקבל את שמות התלמידים הראשונים שהגיעו בסשנים שונים לפי תאריך הגעה לכיתה נוכל לדרג אותם בצורה הבאה
SELECT Time, Name,
RANK () OVER (partition by Name by Time desc)
FROM Class
SUM() takes the column name as an argument and returns the sum of all the values in that column.
STDDEV - פונקציה המחזירה סטיית תקן מהממוצע
TO_DATE - שאילתא על שדה זמן באמצעות יצירת פורמט זמן לדוג
TO_DATE('2006/12/25', 'yyyy/mm/dd')
Result: date value of Dec 25, 2016
TO_NUMBER - המרה לערך נומרי,מספרי על פי פורמט שמצויין בסוגרים לאחר הפסיק כמו בדוג' מטה
TO_NUMBER('string', '99.99')
TO_CHAR - מקבלת תאריך/שעה/מספר והופכת אותם למחרוזת
TOP - צימצום התוצאות למספר שיוגדר ב TOP או לאחוז כפי שיוגדר
TRIM - פונקציה שמחזירה טקסט ללא תווים עודפים שניתו להגדיר או בברירת המחדל היא מורידה רווחים מיותרים לפני ואחרי הטקסט
OVER- בתוך הסוגריים של הפונקציה הזו נגדיר את הערכים עליהם נרצה לבצע את הפונקציות האנליטיות כמו בדוגמא למעלה עם פונקציית הדירוג RANK.
PARTIOTION - משמשת בפונקציה rank ליצירת הפרדה לפי מחלקות וכך נקבל במקום רצף של 1 והלאה מספר רצפים כאלו לפי מספר המחלקות. בדוגמא למעלה השמות הם המחלקות ונראה דירוג לפי הגעה של כל תלמיד לכיתה לפי שמו מהפעם הראשונה ועד לאחרונה.
TRANSACTION- נשתמש בטרנזקציות לאיגוד ומיטוב ביצועי תהליכים לפי התבנית הבאה
BEGIN TRANSACTION;
INSERT INTO example_table....
END TRANSACTION;
SUBSTR - מחרוזת, מיקום מספרי, ומספר אותיות- יחזירו את תת המחרוזת מהמיקום המספרי ועד למספר האותיות
לדוג' - SELECT SUBSTR('ABCD',2,2) יחזיר 'BC'
כמו כן מיקום עם הסימון '-' יספור מהסוף להתחלה, השימוש בו יכול להיות מאוד שימושי כשאנחנו רוצים להמיר מספר בודד לייצוג של שתי ספרות מדקות דקות עם '0' בהתחלה, נשלוף זאת כך
SELECT mm || ':' || SUBSTR('00' || ss, -2, 2), .....
REPLACE - מכניסים לסוגריים, עמודה, ביטוי להחלפה ואת ההחלפה
לדוג' - REPLACE( 'test', 't', 'T' ) -> TesT
TRUNC- מקצץ מספרים ותאריכים - מספר עשרוני מחזירה עם ספרות עשרוניות לפי המספר בסוגריים תאריך לפי הפרמטר מחזירה את תחילתו, למשל בשנה תחזיר את תחילת השנה
לדוג' TRUNC(TO_DATE('21-MAY-22','DD-MON-YY'), 'YEAR')
יחזיר 01-01-22.
UPPER (string) - LOWERמחזירה מחרוזת כשכל האותיות הקטנות הומרו לגדולות, ההיפך מ
WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.

DECLARE - מקטע ההצהרה בתחילת בלוק של pl/sql

BEGIN - END - בין שנייהם נמצא המקטע הראשי

שליפות מ - Data Dictionary של Oracle.

טיפים

אם נרצה למספר את שורות הטבלה ששלפנו נעטוף את השליפה ב

SELECT A.* , ROWNUM from (SQL) A;

המחברת של יאיר

יום שני, 25 באוקטובר 2021

ללמוד תכנות בחינם

יום שלישי, 27 ביולי 2021

PL\SQL

יום ראשון, 3 בנובמבר 2019

CAST - המרת פורמטים ב SQL

יום ראשון, 3 ביולי 2016

SQL בקצרה

טיפים