Oracle PL/SQL
عنوان گروه یا کانال:

Oracle PL/SQL


توضیحات:
شناسه: @
تعداد اعضا: 1000
مدوسا ۱۰۶۹۵۵

#Subject 
Dear Oracle Database Users 
Good Morning, It's very early in Germany(05:23) where I am living in nowadays, 
First of all I appreciate Mr. Hassanpour and dear Mr.Ahmadi to structured and prepared such a good spot let us to share what we gained in Oracle database concepts, please forgive me to put these words in English I will explain it a little further, I employed in a famous company in the field of IT and Industrial Automation IRISA when I was being familiar with Oracle database and Oracle Database programming, it was so attractive and motivate me to learn about concepts more and more, it's my honor to work there with my best teacher with so amazing personality Mr. Jalalipour, he inspire me in all of my life and thank him from here, I've been here since April 2015 and I'm working here as a Oracle Database Plsql developer, I decided to share my experience with you for this purpose I must to get some permissions from my company and someone who will help me on this way, please give me my word if you want to promote yourself in your life please please please be wisdom and share your knowledge share your knowledge this is most important things that I gain in my life. 
because I want to issue some Oracle database secrets here I have to write my words in English and thank you to Amis Technology and Laurent Schneider from Swiss Bank and all of you help us to be better, in next post I will start to put some tips in Oracle SQL
مدوسا ۱۰۶۹۵۵

#مطلب 
#SQLDaily 
Note: When selecting from more than on table, the best practice is to prefix all columns. Not only does this improve readability, but it also helps Oracle to parse the query quicker.
مدوسا ۱۰۶۹۵۵

#SQLDaily 
I would like to share with you one phenomenal query that I hope you have never seen before but to explain this query I have to start introduction about Joins Types:  
All of us when decide to join some tables use this type of join, An equijoin is a join where keys of both tables are matched using equal comparison operator: 
Select  
     e.Ename, 
     d.Deptno, 
     d.loc 
from 
    Emp e, 
    Dept d 
where e.deptno=d.deptno 
an alternative syntax does exactly the same thing using the JOIN keyword. The following is an example of a NATURAL JOIN: 
Select  
   e.Ename, 
   d.Deptno, 
   d.loc 
from 
  Emp e 
 Natural Join 
 Dept d; 
the join column are not defined. Natural join matches all columns with the same name, in EMP and DEPT, there is only one column with the same name and it is called DEPTNO 
the next syntax is JOIN with USING 
Select  
  e.Ename, 
  d.Deptno, 
  d.loc 
from 
  Emp e 
  Join 
  Dept d 
  using (DEPTNO); 
the USING clause helps by specifying the join column. The join Column cannot be prefixing (DON'T FORGET THIS) 
the third syntax is JOIN with ON. It is the most robust syntax as it allows prefixing: 
Select  
  e.Ename, 
   d.Deptno, 
  d.loc 
from 
  Emp e 
  Join 
  Dept d 
  on (e.DEPTNO=d.DEPTNO); 
The join columns are defined in the ON clause. 
A left or right outer join is a join where rows in one table that do not have a matching row in the second table are selected with null values for the unknown columns: 
SELECT column_name(s) 
  FROM table1 
  LEFT/RIGHT OUTER JOIN table2 
  ON table1.column_name=table2.column_name; 
a full outer join retrieves rows from both tables whether or not they have a matching row: 
Important Note: in release prior to 11g, it does a UNION ALL of a left JOIN and right JOIN to include all non-matched rows. in 11g, the full outer join is much faster than before as the optimizer uses a new operation called HASH JOIN FULL OUTER that scans each table only once instead of doing a union of two joins.
مراد ۹۰۵۱۲

مدوسا ۱۰۶۹۵۵

#SQLDaily 
a cross join computes all rows from one table to the rows of the other table: 
  Select 
  d1.dname, 
  d2.dname 
  from Dept d1 cross join Dept d2; 
the syntax Cross Join is useful when combined with other joins: 
 
With 
   Jobs 
As 
( Select Distinct Job From Emp ) 
select jobs.job,Dept.deptno,count(emp.empno) 
from emp right join (jobs cross join dept) on ( jobs.job=emp.job and dept.deptno=emp.deptno) 
group by jobs.job,dept.deptno;
مدوسا ۱۰۶۹۵۵

#SQLDaily 
a cross join computes all rows from one table to the rows of the other table: 
  Select 
  d1.dname, 
  d2.dname 
  from Dept d1 cross join Dept d2; 
the syntax Cross Join is useful when combined with other joins: 
 
With 
   Jobs 
As 
( Select Distinct Job From Emp ) 
select jobs.job,Dept.deptno,count(emp.empno) 
from emp right join (jobs cross join dept) on ( jobs.job=emp.job and dept.deptno=emp.deptno) 
group by jobs.job,dept.deptno;
Oracle PL/SQL
محمدمنصور ۷۶۳۲۲

#پاسخ
به نظرم بهتره یه تریگر بنویسد تا داده‌ها قبل از ورود clean شوند
امیرمحسن ۶۰۶۴۹

برای تعداد جداول زیاد به نظرتون مناسب است که تریگر بنویسیم ؟
مدوسا ۱۰۶۹۵۵

#SQLDAILY 
Now see this incredible Query is called Partition Outer Join: 
the partitioned outer join (10g) selects the partition key of the outer table even where there are no matching rows: 
 
Select 
  d.deptno, 
  e.job, 
  count(e.empno) 
 From 
Emp e 
         Partition by (e.job) 
right join dept d 
on(e.deptno=d.deptno) 
group by 
d.deptno,e.job 
order by d.deptno,e.job;
محمدمنصور ۷۶۳۲۲

برای تعداد جداول زیاد به نظرتون مناسب است که تریگر بنویسیم ؟
#پاسخ
من خودم دست به داده‌ها برای این قضیه نمیزنم اگه واقعا نیازه همچین کاری انجام بشه به نظرم تریگر مناسبترین روشه
كرم ۱۲۳۸۷۶

#پاسخ
پیشنهاد خود اوراکل
11g:
Character set: Al32utf8
National character set: Al32utf8
12c:
Character set: Al32utf8
National character set: Al16utf16
محرمعلی ۱۳۹۶۱۰

آدرس جديد گروه لينك:
https://telegram.me/joinchat/CFJHpj08PnGUxxzTQx4yRA
محرمعلی ۱۳۹۶۱۰

#پاسخ

بزودي ، در خصوص نحوه پياده سازي بوسيله توسعه دهندگان (برنامه نويسان) شرح خواهم داد.
تورج ۶۰۳۲۹

#SQLDAILY 
Now see this incredible Query is called Partition Outer Join: 
the partitioned outer join (10g) selects the partition key of the outer table even where there are no matching rows: 
 
Select 
  d.deptno, 
  e.job, 
  count(e.empno) 
 From 
Emp e 
         Partition by (e.job) 
right join dept d 
on(e.deptno=d.deptno) 
group by 
d.deptno,e.job 
order by d.deptno,e.job;
Dear Mr attarzadeh please explain some example and if possible compare sql query results with each other.
for example compare outer join with partition outer join.
Thanks.
محرمعلی ۱۳۹۶۱۰

محرمعلی ۱۳۹۶۱۰

#پاسخ

در خصوص سال جدید و کریسمس در اوراکل می باشد. می خواستم کمی روح تازه به گروه اعطا کنم.
محرمعلی ۱۳۹۶۱۰

#مطلب

رونمايي برندگان جايزه انتخاب توسعه دهندگان ديتابيس اوراكل سال (2015)
👇👇👇
محرمعلی ۱۳۹۶۱۰

لینک ورود:
https://telegram.me/joinchat/CFJHpj08PnGUxxzTQx4yRA
مدوسا ۱۰۶۹۵۵

Dear Mr attarzadeh please explain some example and if possible compare sql query results with each other.
for example compare outer join with partition outer join.
Thanks.
#پاسخ
مدوسا ۱۰۶۹۵۵

Dear Mr attarzadeh please explain some example and if possible compare sql query results with each other.
for example compare outer join with partition outer join.
Thanks.
#Answer 
Another example: please prepare some prerequisite and execute this script in which schema do you prefer : 
 
CREATE TABLE customer_orders (name, dt, amt) 
    AS 
       SELECT * 
       FROM ( 
SELECT owner 
              , TRUNC(created) + MOD(ROWNUM,6) 
              , TRUNC(object_id/ROWNUM) 
              FROM all_objects 
              WHERE 
                created >= TRUNC(SYSDATE-interval '9' YEAR,'YEAR') 
             AND     
owner IN ('ORDSYS','WMSYS','HR') 
             ORDER BY 
                    DBMS_RANDOM.RANDOM) WHERE ROWNUM <= 1000; 
--------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------- 
with years_ as (select yearmm from 
xmltable('for $i in xs:int(D/S) to xs:int(D/E)  
          for $j in 1 to 12 return concat($i,if (string-length(string($j)) eq 2) then $j else concat(0,$j))' 
passing XMLELEMENT(d,XMLELEMENT(S,2009),XMLELEMENT(E,2015)) 
columns yearmm varchar2(6) path'.')), 
 
 
customer_names as 
(select distinct cn.name 
from customer_orders cn 

select y.yearmm,cn.name, 
sum(nvl(co.amt,0)) 
from customer_orders co right outer join (customer_names cn cross join years_ y) 
on(to_char(co.dt,'yyyymm')=y.yearmm and co.name=cn.name) 
group by y.yearmm,cn.name 
order by 2;
مدوسا ۱۰۶۹۵۵

مدوسا ۱۰۶۹۵۵

Dear Mr attarzadeh please explain some example and if possible compare sql query results with each other.
for example compare outer join with partition outer join.
Thanks.
#Answer  
please compare previous with this one:  
 
with years_ as (select yearmm from 
xmltable('for $i in xs:int(D/S) to xs:int(D/E)  
          for $j in 1 to 12 return concat($i,if (string-length(string($j)) eq 2) then $j else concat(0,$j))' 
passing XMLELEMENT(d,XMLELEMENT(S,2009),XMLELEMENT(E,2015)) 
columns yearmm varchar2(6) path'.')) 
select y.yearmm,co.name, 
sum(nvl(co.amt,0)) 
from customer_orders co partition by (co.name) right outer join years_ y 
on(to_char(co.dt,'yyyymm')=y.yearmm) 
group by y.yearmm,co.name 
order by 2;
مدوسا ۱۰۶۹۵۵

مدوسا ۱۰۶۹۵۵

Dear Mr attarzadeh please explain some example and if possible compare sql query results with each other.
for example compare outer join with partition outer join.
Thanks.
#Answer 
as you see the second query is more readable and more efficient
محرمعلی ۱۳۹۶۱۰

#پاسخ
Step By Step on Linux with images:
http://www.idevelopment.info/data/Oracle/DBA_tips/Linux/LINUX_22.shtml
مهرشاد ۱۴۳۲۷۷

#سوال
کسی از دوستان به خطای زیر برخورد کرده؟
oall8 is in an inconsistent state oracle 11g
قائم ۹۰۳۴۴

#سوال
کسی از دوستان به خطای زیر برخورد کرده؟
oall8 is in an inconsistent state oracle 11g
#پاسخ
http://www.oracleracexpert.com/2013/03/oall8-is-in-inconsistent-state.html
ملیکا ۵۹۱۹۵

با سلام و احترام،، من همین سوال رو برای نصب بر روی ویندوز دارم! و #سوال دوم اینه که آیا راه اندازی asm بر روی ویندوز 10 امکان پذیر است؟ با تشکر
گلثوم ۱۰۱۱۶۱

#پاسخ
آدرس جديد گروه لينك:
https://telegram.me/joinchat/CFJHpj08PnGUxxzTQx4yRA
امیرفتاح ۴۶۲۴۵

#پاسخ
تا اونجایی که میدونم تعداد رکوردهای برگشتی رو شامل میشه
هما ۱۰۵۴۸۷

پاسخ شما به بنده در حل مسءله کمک کرد.با تشکر از شما دوست گرامی
گلثوم ۱۰۱۱۶۱

#نظر
به به چه سعادتی
کتاب مدیریت اوراکل 11 جی ایشون را مطالعه نمودم
بسیار روشن و زیبا توضیح داده اند
ازشون تشکر میکنم
هما ۱۰۵۴۸۷

#سلام من کار کردم تا حدی ...
تورج ۶۰۳۲۹

#سوال
اگر جدولی داشته باشیم که دارای ستون blob باشد آیا موقع full table scan جدول، سگمنت blob نیز خوانده می شود؟
تورج ۶۰۳۲۹

#Answer 
as you see the second query is more readable and more efficient
با سلام
ممنون از پاسخ شما.
حیدرعلی ۴۰۳۲۷

#نظر
ممنونم بابت پاسخ کامل و واضحتون
كرم ۱۲۳۸۷۶

#سوال
کوری هست که با آن بشه رشد داده های مثلا یک جدول رو بر اساس ماه نشان داد؟
مدوسا ۱۰۶۹۵۵

مدوسا ۱۰۶۹۵۵

#SQLDaily 
ANSI syntax is recommended by Oracle. it has more capabilities, like outer joining with more than one table and with OR predicates, but it also has several limitations, one of which is that it cannot be used in fast refreshable materialized views.
مهرشاد ۱۴۳۲۷۷

#سوال
معادل full text search sql در اوراکل چیه؟
محمدمنصور ۷۶۳۲۲

#سوال
من وقتی میخوام تو pentaho به اوراکلم کانکشن بزنم خطای jdbc رو میده کسی تجربه‌ای داره؟
Driver class 'oracle.jdbc.driver.OracleDriver' could not be found, make sure the 'Oracle' driver (jar file) is installed.
oracle.jdbc.driver.OracleDriver