PL/SQL±í
/*
PL/SQL±í---table()º¯ÊýÓ÷¨£º
ÀûÓÃtable()º¯Êý£¬ÎÒÃÇ¿ÉÒÔ½«PL/SQL·µ»ØµÄ½á¹û¼¯´úÌætable¡£
oracleÄÚ´æ±íÔÚ²éѯºÍ±¨±íµÄʱºòÓõıȽ϶࣬ËüµÄËÙ¶ÈÏà¶ÔÎïÀí±íÒª¿ì¼¸Ê®±¶¡£
simple example£º
1¡¢table()½áºÏÊý×飺
*/
create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);
create or replace type t_test_table as table of t_test;
create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;
/
select * from table(f_test_array(10));
select * from the(select f_test_array(10) from dual);
/*
2¡¢table()½áºÏPIPELINEDº¯Êý£º
*/
create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;
/
select * from table(f_test_pipe(20));
select * from the(select f_test_pipe(20) from dual);
/*
3¡¢table()½áºÏϵͳ°ü£º
*/
create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);
Ïà¹ØÎĵµ£º
¼ò½é£º
Êý¾ÝÀàÐÍת»»¿ÉÒÔͨ¹ýCAST()ºÍCONVERT()º¯ÊýÀ´ÊµÏÖ¡£´ó¶àÊýÇé¿öÏ£¬ÕâÁ½¸öº¯ÊýÊÇÖصþµÄ£¬ËüÃÇ·´Ó³ÁËSQLÓïÑÔµÄÑÝ»¯ÀúÊ·¡£ÕâÁ½¸öº¯ÊýµÄ¹¦ÄÜÏàËÆ£¬²»¹ýËüÃǵÄÓï·¨²»Í¬¡£ËäÈ»²¢·ÇËùÓÐÀàÐ͵ÄÖµ¶¼ÄÜת±äΪÆäËûÊý¾ÝÀàÐÍ£¬µ«×ܵÄÀ´Ëµ£¬ÈκοÉÒÔת»»µÄÖµ¶¼¿ÉÒÔÓüòµ¥µÄº¯ÊýʵÏÖ× ......
ÕâƪÎÄÕÂÊÇÐþ»úÒÝʿΪijÉÏÊй«Ë¾µÄERPϵͳµ÷ÓÅËù׫д±¨¸æµÄ²¿·Öժ¼£¬Ï£ÍûÄܹ»¶Ô´ó¼ÒÓÐËù°ïÖú£º
ÏÂÃæ¸ø³öSQLÓÅ»¯¾³£Óöµ½µÄÇéÐΡ£ËüÃÇÊÇÔںܶàÈ˳¤ÆÚ»ýÀ۵ľÑéÉÏ×ܽá³öÀ´µÄ£¬ÕâЩ¶¼ÊÇһЩһ°ãÐԵĽáÂÛ£¬ÔÚʵ¼Ê¹¤×÷ÖУ¬»¹Ó¦µ±¾ßÌåÇé¿ö¾ßÌå·ÖÎö£¬²¢¼ÓÒÔÁé»îÔËÓá£
(1) ÓÃNOT EXISTSÌæ»»NOT IN ½«¸üÏÔÖøµØÌá¸ßЧÂÊ£¬¾ ......
²Ù×÷·ûÓÅ»¯
IN ²Ù×÷·û
ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ׶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵ģº
Êý¾Ý¿âÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£¬Èç¹ûת»»³É¹¦ÔòÖ±½Ó²ÉÓöà¸ö±íµÄÁ¬½Ó·½Ê½²éѯ¡£Óɴ˿ɼû ......
father±í son±í
fid fname sid sname fid height money
1 a 100 s1 1 1.7 7000
2 b 101 s2 2 1.6 8000
3 c 102&nbs ......
--º¯ÊýÈçÏ£º
/*
CREATE FUNCTION dbo.fnpbGetPYFirstLetter
(
@str NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
--WITH ENCRYPTION
AS
BEGIN
DECLARE @WORD NCHAR(1),@PY NVARCHAR(4000)
SET @PY=''
WHILE LEN(@STR)>0
BEGIN
SET @WORD=LEF ......